WHERE LIKE '%'|| :1 || '%' 양 퍼센트 성능 개선을 위한 방법을 알아보자
[ 문제 ]
개발자들은 Like :1 ||'%' 쓰기도하나, 습관 또는 어플리케이션에 적용해도 업무 데이터에 문제가 없다는 이유로 LIKE '%' || :1 || '%' 두기도 한다. 그래서 다양한 방법으로 Like 양 '%' 를 테스트하여 성능 개선이 되는지 테스트 하려고 한다. 우선 최적의 솔루션인 =, 앞 '%' 제거하는 2가지의 결과는 생략한다. 불가피하게 '%' || :1 || '%' 를 쓸 수 밖에 없는 상태에서 어떻게 해야할까? 고민의 흔적이다.
[ Test 테이블 ]
1 .테이블명 : TB_EMP_BIG2 / 1,000,000 건 / 61,440 Block / 480 MB
2. 인덱스 : IX_EMP_BIG2_01 ( ENAME );
3. 컬럼 : ENAME Nullable
[ Case 1 : LIKE '%' || :1 || '%' + INDEX RANGE SCAN ]
: Sql에 어떠한 추가 내용 없이, 질의 결과 Index Range Scan 처리한다. 인덱스는 출발 시작점을 찾는 수직적 탐색을 하는데, 해당 쿼리는 어디서 부터 시작할지 지정이 되지 않았으니, Index Full Scan과 동일하게 조회되어야 하지 않나? 생각이 들었다. 그런데 Index Range Scan 을 하고 있다. 다음 Case 인 Index Full Scan 결과를 보자.
SELECT COUNT(*) FROM TB_EMP_BIG2 A WHERE ENAME LIKE '%'|| :1 || '%';
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 (100)| 1 |00:00:02.79 | 26510 | 2755 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:02.79 | 26510 | 2755 |
|* 2 | INDEX RANGE SCAN| IX_EMP_BIG2_01 | 1 | 24 (0)| 200 |00:00:02.79 | 26510 | 2755 |
-----------------------------------------------------------------------------------------------------------
[ Case 2 : LIKE '%' || :1 || '%' + INDEX FULL SCAN ]
: Index Full Scan을 유도하기 위해 Where 조건절을 변경했다. 그리고 읽은 I/O 수치는 위 Range와 Full Scan이 동일하였다. 그러므로 Case 1 은 Range Scan으로 특정 범위가 지정된 것처럼 보이나, 실은 전체 범위 Range Scan 의미로 볼 수 있다.
SELECT /*+ INDEX( A (ENAME) ) */ COUNT(*) FROM TB_EMP_BIG2 A WHERE ENAME IS NOT NULL;
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2673 (100)| 1 |00:00:02.66 | 26510 | 3133 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:02.66 | 26510 | 3133 |
|* 2 | INDEX FULL SCAN| IX_EMP_BIG2_01 | 1 | 2673 (1)| 10M|00:00:02.06 | 26510 | 3133 |
----------------------------------------------------------------------------------------------------------
[ Case 3 : LIKE '%' || :1 || '%' + FTS ]
: Index Scan은 Single Block I/O 이다. Segment의 전체 Block을 읽어야 하는 상황이라면, MultiBlock I/O인 FTS, Index Fast Full Scan으 유도하는 것이 좋다. 먼저 FTS 이다. Buffer는 Index Test에 비해 2배 이상 높아졌으나, 실행시간이 빠른 것을 보면 MultiBlock I/O가 전체를 읽을 경우 좋은 대안이 된다는 것을 알 수 있다. 심지어 모두 Disk Read를 했음에도불구하고 말이다.
SELECT /*+ FULL(A) */ COUNT(*) FROM TB_EMP_BIG2 A WHERE ENAME LIKE '%'|| :1 || '%';
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16532 (100)| 1 |00:00:02.35 | 60175 | 60173 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:02.35 | 60175 | 60173 |
|* 2 | TABLE ACCESS FULL| TB_EMP_BIG2 | 1 | 16532 (1)| 200 |00:00:02.35 | 60175 | 60173 |
---------------------------------------------------------------------------------------------------------
[ Case 4 : LIKE '%' || :1 || '%' + INDEX FAST FULL SCAN ]
: MuilBlock I/O는 FTS과 Index Fast Full Scan 2가지가 있다. FTS보다 당연히 INDEX가 전체 Block이 적으므로 index를 전체 읽어 원하는 값을 도출해낼수 있다면, Index Fast Full Scan을 유도하는 것이 성능에 좋다. FTS 에 비해 Buffer I/O가 줄어들었음을 알수 있다.
SELECT /*+ INDEX_FFS(A (ENAME) ) */ COUNT(*) FROM TB_EMP_BIG2 A WHERE ENAME LIKE '%'|| :1 || '%';
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7342 (100)| 1 |00:00:02.00 | 26578 | 60 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:02.00 | 26578 | 60 |
|* 2 | INDEX FAST FULL SCAN| IX_EMP_BIG2_01 | 1 | 7342 (2)| 200 |00:00:02.00 | 26578 | 60 |
---------------------------------------------------------------------------------------------------------------
[ Case 5 : Instr + INDEX FAST FULL SCAN 실패 ]
: Like 보다 빠른 Instr이라고 알려져 있다. 하지만 index fast full scan 할려니 FTS 유도가 된다. Like 보다 성능상 크게 개선 된 점은 없다. 테스트 하다보면 성능적으로 소폭 개선되나, 고객 감동을 실현 할 정도는 아니다. 최대한 기술적으로 최대한 힘썻다는 의미로 느껴진다. Instr 은 왜 Index Fast Full Scan이 안될까? 다음 Case를 보자
SELECT /*+ INDEX_FFS(A (ENAME) ) */ COUNT(*) FROM TB_EMP_BIG2 A WHERE INSTR(ENAME, :1 ) > 0 ;
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16492 (100)| 1 |00:00:02.35 | 60175 | 60174 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:02.35 | 60175 | 60174 |
|* 2 | TABLE ACCESS FULL| TB_EMP_BIG2 | 1 | 16492 (1)| 200 |00:00:02.35 | 60175 | 60174 |
---------------------------------------------------------------------------------------------------------
[ Case 6 : Instr + INDEX FAST FULL SCAN ]
: Ename 컬럼은 Nuallable 이다. Nullable 컬럼을 Instr를 이용하려면 Index Fast Full Scan 유도가 안된다. 단일 컬럼이므로 Null 컬럼이 빠지는 것으로 인한 문제가 없게 하기 위해 장치를 마련해 둔것으로 판단된다. 그래서 where 절에 Ename is not null기입을 했다. Index Fast Full Scan이 유도 됨을 볼 수 있다.
SELECT /*+ INDEX_FFS( A (ENAME) ) */ COUNT(*) FROM TB_EMP_BIG2 A WHERE INSTR(ENAME,:1) > 0 AND ENAME IS NOT NULL;
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7302 (100)| 1 |00:00:01.99 | 26578 | 3283 |
| 1 | SORT AGGREGATE | | 1 | | 1 |00:00:01.99 | 26578 | 3283 |
|* 2 | INDEX FAST FULL SCAN| IX_EMP_BIG2_01 | 1 | 7302 (2)| 200 |00:00:01.99 | 26578 | 3283 |
---------------------------------------------------------------------------------------------------------------
[ Case 7 : Domain Index ]
: 양 '%' || :1 || '%'를 쓸 수 밖에 없는 상태에서 최적 성능은 Domain Index 이다. 하지만 실행 계획 아래 설명한 바와 같이 "유지보수" 측면에서 좋지 못하다. 하지만 반드시 업무상 조회 성능이 나와야만 하는 업무라면 좋은 솔루션이 되겠다.
SELECT COUNT(*) FROM TB_EMP_BIG2 A WHERE CONTAINS(ENAME,'%'||:1||'%') > 0;
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (100)| 2 |00:00:00.29 | 3116 |
| 1 | SORT UNIQUE NOSORT| | 1 | 4 (25)| 2 |00:00:00.29 | 3116 |
|* 2 | INDEX RANGE SCAN | DR$IX_EMP_BIG2_03$X | 1 | 3 (0)| 16 |00:00:00.29 | 3116 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOKEN_TEXT" LIKE :LKEXPR ESCAPE '\')
filter(("TOKEN_TEXT" LIKE :LKEXPR ESCAPE '\' AND (INTERNAL_FUNCTION("TOKEN_TYPE") OR ("TOKEN_TYPE">=16 AND "TOKEN_TYPE"<=74) OR (MOD("TOKEN_TYPE",10000)>=1001 AND MOD("TOKEN_TYPE",10000)<=1333))))
※ 도메인 인덱스 유지보수 어려움
1) 설정 이해
- 일반적으로 도메인 인덱스를 만들면 대소문자 구별이 없다. 위 Case 1~6의 결과값은 200건 인데 비해, Case 7은 실은 400건이 나왔다. 왜냐하면 대소문자 구별이 없었기 때문이다. 대소문자 구별을 하려면 아래와 같이 Preference , Attribute 설정 후 인덱스 생성을 해야한다. 그 외 많은 옵션이 있기에 어플리케이션을 위한 도메인 인덱스를 생성하려면 도메인 인덱스 자체 구조 이해가 별도로 필요하다.
EXEC CTX_DDL.CREATE_PREFERENCE('KOREA_LEXER', 'KOREAN_MORPH_LEXER');
EXEC CTX_DDL.SET_ATTRIBUTE('KOREA_LEXER', 'to_upper', 'false');
create index IX_EMP_BIG2_03 on TB_EMP_BIG2 ( ENAME ) indextype is ctxsys.context parallel 8 PARAMETERS ('LEXER KOREA_LEXER') ;
alter index IX_EMP_BIG2_03 noparallel;
=== 참고 ===
select * from CTX_PARAMETERS;
select * from CTX_PREFERENCES;
select * from CTX_PREFERENCE_VALUES;
select * from CTX_OBJECT_ATTRIBUTES;
select * from ctx_user_indexes;
select * from CTX_USER_PREFERENCES;
select * from CTX_USER_PREFERENCE_VALUES;
select * from dba_objects where object_name like 'CTX_%';
2) 용량 유의
- 도메인 인덱스 1개 설정 했지만, 내부적으로 용량을 소모하는 Object가 5개가 생성 된다. 테이블 2개, 인덱스 3개 이다. 아래를 보시면 아시겠지만, 테이블은 480MB인데, 도메인 인덱스 관련하여 생성된 Segemnt 합계가 1,160MB이다. 보통 테이블 보다, 인덱스가 많을때 "배보다 배꼽이 더 크다"라는 표현을 썼다. 그런데 인덱스 1개 생성 했을뿐이다. 이미 배꼽이 더 커진 상황이다.
[ 정리 ]
1. '%'|| :1 || '%' 의 최적의 솔루션은 = , like :1 || '%' 처리해도 괜찮도록 업무 담당자와 협의 하는 것이다. 성능이 나오지 않기 때문에 최대한 = , like :1 || '%'로 해야 원하는 성능이 조회된다고 가이드 해야 한다.
2. 불가피 하게 양 '%' 사용시
1) 성능
Domain Index > Index Fast Full Scan > FTS > Index Range = Index Full
※ Domain Index는 인덱스 이해 , Data 동기화, 권한 , 문제 발생시 해결 방법 등 많은 기술적 파악이 필요하므로,
유지 보수 차원에서 관리 및 Trouble Shooting 발생시 해결할 능력이 되는지 보수적으로 확인이 필요하다.
3. Instr은 not null 컬럼에서 사용가능하며, nullable 컬럼인 경우 where 조건절에 컬럼 is not null을 명시해야한다. Instr 처리는 성능상 소폭 개선될 뿐이다.
'ORACLE > SQL Tuning' 카테고리의 다른 글
| 성능 수치 단위 (0) | 2024.10.22 |
|---|---|
| 사용자함수 ( 함수명, 서브쿼리변환, Deterministic, Result Cache ) 호출 결과 분석 (0) | 2024.10.15 |
| LITERAL SQL 찾기 - 심화 (1) | 2024.10.01 |
| [ OZ Report ] Literal SQL 때려잡기 (2) | 2024.09.25 |
| V$SQL_MONITOR에 SQL 조회 안 될 시 해결 방법 (0) | 2024.09.09 |