---------------------------------------------
Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.
---------------------------------------------
You can upload a Korean translation of use-the-index-luke.com on your blog
Thank you from the bottom of my heart to author Makus Winand for allowing me.
These are translations that I use for studying by using a papago (google translate)
The translations may not be correct or there may be a typo.
I'd appreciate it if you could point it out in the comments.
---------------------------------------------
---------------------------------------------
use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고
허락해주신 Makus Winand 님께 진심으로 감사합니다.
이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
---------------------------------------------
https://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search
ㄴ
UPPER 또는 LOWER 사용하여 대소문자를 구분하지 않고 검색
where 절에서 대소문자를 무시하는 것은 매우 간단합니다. 예를 들어 비교 해야하는 값의 양쪽을 모두 대문자 표기법으로 변환할 수 있습니다.
SELECT first_name, last_name, phone_number
FROM employees
WHERE UPPER(last_name) = UPPER('winand')
검색어나 LAST_NAME 열에 사용된 대문자에 관계없이 UPPER는 원하는 대로 일치 시킵니다.
---------------------------------------------
메모
대소문자를 구분하지 않는 일치를 위한 또다른 방법은 다른 "collation"을 사용하는 것 입니다. SQL Server 및 MySQL에서 사용하는 기본 데이터 정렬은 대문자와 소문자를 구분하지 않으며 기본적으로 대소문자를 구분하지 않습니다.
---------------------------------------------
이 쿼리의 논리는 완벽하게 합리적이지만 실행계획은 그렇지 않습니다.
---------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 10 | 477 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("LAST_NAME")='WINAND')
---------------------------------------------
좋은 친구 FULL TABLE SCAN이 찾아오네요.
LAST_NAME에 인덱스가 있어도 사용할 수 없습니다. 검색한 것이 LAST_NAME이 아니라 UPPER(LAST_NAME)에 있기 때문입니다.
데이터베이스 관점에서 둘은 완전히 다른 것 입니다.
이것은 우리 모두가 빠질 수 있는 함정입니다.
우리는 LAST_NAME와 UPPER(LAST_NAME) 사이의 관계를 즉시 인식하고 데이터베이스도 이를 "인지" 할 것으로 기대합니다.
실제로 옵티마이저의 보는 SQL문은 다음과 같습니다.
SELECT first_name, last_name, phone_number
FROM employees
WHERE BLACKBOX(...) = 'WINAND'
UPPER FUNCTION은 그냥 BLACKBOX 입니다. 함수의 매개변수와 결과 사이에 일반적인 관계가 없시 때문에 함수에 대한 매개변수는 관련이 없습니다.
---------------------------------------------
TIP
옵티마이저의 시점을 이해할려면 BLACKBOX를 함수이름으로 치환합니다.
---------------------------------------------
해당 쿼리를 지원하려면 실제 검색어를 포함하는 인덱스가 필요합니다.
즉, 우리는 LAST_NAME으로 구성된 INDEX가 필요하지 않습니다 하지만 UPPER(LAST_NAME)으로 구성된 인덱스가 필요합니다.
CREATE INDEX emp_up_name
ON employees (UPPER(last_name))
정의에 함수(FUNCTION) 또는 표현식(expressions)이 포함된 인덱스를 FUNCTION BASED INDEX (FBI)이라고 합니다.
컬럼 데이터를 인덱스에 직접 복사하는 대신 함수 기반 인덱스는 함수를 먼저 적용하고 그 결과를 인덱스에 넣는다.
결과적으로 인덱스는 모두 대문자 표기법으로 이름을 저장합니다.
위의 예와 같이 인덱스 정의의 정확한 표현이 SQL문에 나타나는 경우 데이터베이스는 함수 기반 인덱스를 사용할 수 있습니다. 실행계획은 다음을 확인합니다.
---------------------------------------------
--------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT | | 100 | 41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 100 | 41 |
|*2 | INDEX RANGE SCAN | EMP_UP_NAME | 40 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME")='WINAND')
---------------------------------------------
1장에서 설명한 대로 일반적인 INDEX RANGE SCAN 입니다. 데이터베이스는 B-TREE를 통과하고 리프노드 체인을 따릅니다. 함수 기반 인덱스에 대한 전용 작업이나 키워드가 없습니다.
---------------------------------------------
경고
때때로 ORM 도구는 개발자 모르게
UPPER와 LOWER를 사용합니다.
예를 들어 Hibernate는 대소문자를 구분하지 않는 검색을 위해 암시적인 LOWER를 넣습니다.
https://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow#myth-dynamic-sql-sample
ㄴ 참조
Hibernate: 자바언어를 위한 ORM 프레임 워크
ORM : Object Relational Mapping
객체와 관계형 데이터베이스를 매핑한다는 의미임
---------------------------------------------
실행 계획은 UPPER가 없는 이전 섹션과 아직 동일하지 않습니다. ROW 수 추정치가 너무 높습니다. 옵티마이저가 처음에 INDEX RANGE SCAN이 제공하는 것보다 더 많은 row를 테이블에서 가져올 것으로 예상하는 것이 특히 이상합니다. 이전 인덱스 스캔에서 40개의 row만 반환 된경우 어떻게 테이블에서 100개의 row를 가져올 수 있습니까?
질문의 답변은 "불가능 하다"라는 것입니다.
이와 같이 모순되는 추정치는 종종 통계에 문제가 있음을 나타냄니다. 이 특별한 경우에는 Oracle 데이터베이스가 새 인덱스를 생성할 때 테이블 통계를 업데이트 하지 않기 때문입니다.
("FUNCTION BASED INDEX 에대한 Oracle 통계" 참조)
---------------------------------------------
FUNCTION BASED INDEX 에대한 Oracle 통계
Oracle 데이터베이스는 테이블 통계의 일부로 개별 row 값의 수에 대한 정보를 유지 관리합니다. row가 여러 인덱스의 일부인 경우 이러한 수치가 재사용됩니다.
FUNCTION BASED INDEX(FBI)에 대한 통계도 테이블 수준에서 가상 ROW로 유지 됩니다.
ORACLE database는 새 인덱스에 대한 인덱스통계를 자동으로 수집하지만(10g 부터) 테이블 통계를 업데이트하지는 않습니다.
이러한 이유로 ORACLE 공식 문서에는 FUNCTION BASED INDEX를 생성한 후 테이블 통계를 업데이트할 것을 권장합니다.
함수 기반 인덱스를 생성한 후 DBMS_STATS패키지를 사용하여 인덱스와 기본 테이블 무도에 대한 통계를 수집합니다.
이러한 통계를 통해 Oracle Database 는 인덱스를 사용할 시기를 올바르게 결정할 수 있습니다.
-오라클 데이터베이스 SQL 언어 레퍼런스
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-INDEX.html
내 개인적인 권장 사항은 더 나아가 모든 인덱스 변경 후 기본 테이블 및 모든 인덱스에 대한 통계를 업데이트 하는 것입니다. 그러나 이는 원치않는 부작용을 초래할 수도 있습니다. DBA와 이 활동을 조정하고 원래 통계를 백업하십시오.
---------------------------------------------
통계를 업데이트한 후 옵티마이저는 보다 정확한 추정치를 계산합니다
---------------------------------------------
--------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME")='WINAND')
---------------------------------------------
---------------------------------------------
메모
https://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#BEIEEIJA
표현식 및 column 그룹에 대한 "확장 통계"는 Oraclee 11g 에서 도입되었 습니다.
---------------------------------------------
이 경우 업데이트된 통계가 실행 성능을 향상시키지는 않지만(어쨌든 인덱스가 적절하게 사용됨) 최적화 프로그램의 추정치를 확인하는 것은 항상 좋은 생각입니다. 각 작업에 대해 처리되는 Row 수(cadinality 추정치)는
SQL Server 및 PsotsgreSQL 실행계획에도 표시되는 특히 중요한 수치입니다.
---------------------------------------------
Tip
https://use-the-index-luke.com/sql/explain-plan
부록 A, "실행 계획" 에서는 다른 데이터베이스의 실행 계획에 있는 Rows 추정치를 설명합니다.
---------------------------------------------
SQL Server 및 MySQL은 설명된 대로 함수 기반 인덱스를 지원하지 않지만 둘 다 계산 열 또는 생성 열을 통해 해결 방법을 제공합니다. 이를 활용하려면 나중에 인덱싱할 수 있는 테이블에 생성된 row을 먼저 추가해야 합니다.
MySQL 5.7부터 다음과 같이 생성된 열을 인덱싱할 수 있습니다 .
ALTER TABLE employees
ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX emp_up_name ON employees (last_name_up);
SQL Server 및 MySQL은 인덱싱된 식이 명령문에 나타날 때마다 이 인덱스를 사용할 수 있습니다. 일부 간단한 경우 SQL Server와 MySQL은 쿼리가 변경되지 않은 경우에도 이 인덱스를 사용할 수 있습니다. 그러나 경우에 따라 인덱스를 사용하기 위해 새 열의 이름을 참조하도록 쿼리를 변경해야합니다. 의심스러운 경우 항상 실행 계획을 확인하십시오.
'use-the-index-luke' 카테고리의 다른 글
| 2.2-3 Over-Indexing (0) | 2023.07.03 |
|---|---|
| 2.2-2 User-Defined Functions (0) | 2023.06.30 |
| 2.2 Functions (0) | 2023.06.26 |
| 2.1-3 Slow Indexes, Part II (0) | 2023.06.26 |
| 2.1-2 Concatenated Keys (0) | 2023.06.22 |
