INDEX의 구조
INDEX는 KEY 값 + ROW ID 로 구성되어 있다.
같은 KEY 값이면 ROW ID 순으로 SORTING 되어 있다. DISK I/O를 줄이기 위해서이다.
Oracle 경우 ROW ID 는 Block# (8) . Loc (4) . File# (4) 로 구성되어 있다.
그리고 INDEX는 B-Tree (Balanced Tree)로 되어서 어떠한 Leap로의 Depth도 다 비슷하도록 생성된다.
INDEX의 사용
조건문이 긍정, 범위 ( = 포함 )로 이루어 졌을 경우 사용한다.
INDEX COULUMN을 변경 할 경우 사용할 수 없다.
Function 사용시 불가능하다. 단 Function based Index (FBI) 사용시는 예외로 INDEX 사용이 가능하다.
SUBSTR(DNAME,1,3) = 'ABC' 대신 DNAME LIKE 'ABC%' 을 사용하면 INDEX-SCAN이 가능해진다.
단, 검색할 단어 앞에 %가 붙으면 INDEX 사용이 불가능하다.
NOT Operator에는 INDEX 사용이 불가능하다. 당연히 해당 조건을 찾은 후 아닌 것을 다 찾는 것이니 FULL-SCAN이다.
단, B-Tree INDEX가 아닌 2차원으로 구성된 BITMAP INDEX의 경우에는 NOT 조건에도 INDEX-SCAN이 가능하다.
통상적으로 INDEX에서 사용하지 않기 위하여 값을 NULL로 사용한다.
NULL 값은 INDEX 생성시 제외한다.
단, A-B-C 등 여러개의 COLUMN으로 이루어진 INDEX의 경우 한 특정 COLUMN만 NULL인 경우는 생성되나 가장 마지막 위치로 SORT된다.
INDEX-SCAN을 원한다면 절대로 좌변은 가공하지 마라.
SELECT * FROM EMP WHERE SUBSTR(DNAME, 1, 3) = 'ABC'
=>
SELECT * FROM EMP WHERE DNAME LIKE 'ABC%'
모든 DNAME 에 대해서 SUBSTR() 함수 실행을 ?
SELECT * FROM EMP WHERE SAL * 12 = 12000000
=>
SELECT * FROM EMP WHERE SAL = 12000000 / 12
모든 SAL 값에대해서 * 12 해서 비교를 ? 그냥 12000000 / 12 는 상수값이 되어서 한번만 계산하면 되는데...
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYMMDD') = '940101'
=>
SELECT * FROM EMP WHERE HIREDATE = TO_DATE('940101','YYMMDD')
모든 HIREDAT에 대해서 TO_CHAR() 함수적용을 ? 그냥 940101 값에 대해서 TO_DATE()를 한번만 적용하면 되는데...
SELECT * FROM EMP WHERE NVL(COMM,0) < 100
=>
Table에서 COMM 을 NOT NULL , DEFAULT 0 로 지정하면 될것을 왜 굳이, 모든 COMM에 대해서 NVL 함수 실행을 ???
SELECT * FROM EMP WHERE EMPNO BETWEEN 100 AND 200 AND NVL(JOB,'X') = 'CLERK'
=>
SELECT * FROM EMP WHERE EMPNO BETWEEN 100 AND 200 AND JOB = 'CLERK'
NULL인 것을 'X'로 바꿔서 'CLERK'와 비교해도 FALSE, 그냥 NULL과 'CLERK'를 비교해도 FALSE
SELECT * FROM EMP WHERE DEPTNO || JOB = '10SALESMAN'
=>
SELECT * FROM EMP WHERE DEPTNO = '10' AND JOB = 'SALEMAN'
굳이 가공안하고 따로 비교해도 될것을 ...
의도적인 SUPRESSING
INDEX-SCAN 보다 FULL-SCAN이 더 빠른 경우도 있다.
전체 DATA의 10%가 넘는 DATA들은 대부분 FULL-SCAN이 더 빠르다.
INDEX-SCAN은 1 BLOCK I/O 지만 FULL-SCAN은 MULTI-BLOCK-I/O 이기 때문이다.
그래서 의도적으로 INDEX 사용이 불가능 하도록 좌변을 가공하는 경우도 있다.
예를 들어서 EMP에 대부분의 사람들이 MANAGER 인 경우 MANAGER를 찾는 경우는 FULL-SCAN으로 하고,
MANAGER가 아닌 사람들을 찾을때만 INDEX-SCAN을 하는 것이 더 효율이 좋다.
SELECT * FROM EMP WHERE JOB = 'MANAGER' => SELECT * FROM EMP WHERE RTRIM(JOB) = 'MANAGER'
SELECT * FROM EMP WHERE EMPNO = 8978 => SELECT * FROM EMP WHERE RTRIM(EMPNO) = 8978
하나의 TABLE에 2개 COLUMN이 각각 INDEX가 걸린 경우. 하나의 INDEX만 사용하는 것이 더 빠를 수도 있다.
아래의 SQL에서 CUSTNO 와 STATUS에 각각 INDEX가 있을 경우 2개의 INDEX를 다 사용하면 INDEX-MERGE 방식으로 동작한다.
INDEX-MERGE 방식이란 2개의 INDEX 의 조합들을 ROW-ID 순으로 검색하는 방법이다.
만약 STATUS 가 90인 값이 전체의 60% 가 넘는다면 CUSTNO의 INDEX만 사용하는 것이 훨씬 더 빠르다.
SELECT CUSTNO, 출고일 FROM 출고 WHERE CUSTNO = 'DN02' AND STATUS = '90'
=>
SELECT CUSTNO, 출고일 FROM 출고 WHERE CUSTNO = 'DN02' AND RTRIM(STATUS) = '90'
SELECT CUSTNO, 출고일 FROM 출고 WHERE CUSTNO LIKE 'DN%' AND STATUS LIKE '9%'
=>
SELECT CUSTNO, 출고일 FROM 출고 WHERE CUSTNO LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%'
TABLE JOIN을 할 경우 조건등을 보고 특정 TABLE을 DRIVING 하게 할려면 해당 TABLE의 COLUMN을 변경하면 된다.
그리고, ORDER BY로 정렬할 COLUMN에 INDEX가 있는 경우 해당 INDEX를 사용하게끔 유도하면 된다.
SELECT X.CUSTNO, 출고일 FORM 매출1 X, 매출2 Y WHERE X.SN0 = Y.SNO AND X.DEPT = '710' AND Y.DATE LIKE '9411%'
=>
SELECT X.CUSTNO, 출고일 FORM 매출1 X, 매출2 Y WHERE X.SN0 = Y.SNO AND RTRIM(X.DEPT) = '710' AND Y.DATE LIKE '9411%'
SELECT X.ORDNO, ORDDATE FROM 주문1 X, 주문2 Y WHERE X.ONO = Y.ONO
AND X.ORDDATE LIKE '9411%' AND Y.DEPT = '710' ORDER BY ORDDATE
=>
SELECT X.ORDNO, ORDDATE FROM 주문1 X, 주문2 Y WHERE RTRIM(X.ONO) = Y.ONO
AND X.ORDDATE LIKE '9411%' AND Y.DEPT = '710'
COLUMN의 DATA TYPE을 바꾸면 무조건 FULL-SCAN하게 된다.
TABLE 간에 비교하는 DATA-TYPE은 같은 TYPE이어야 한다. 아니면 FULL-SCAN이 발생한다.
NOT Operator 대신 NOT EXISTS 나 MINUS (차지합)을 활용하면 INDEX-SCAN이 가능하다.
NOT NULL인 경우 무조건 TRUE인 조건으로 대체가 가능하지만,
NULL 의 경우는 다른 조건으로 대체가 힘들다.
NULL 은 어떤 경우에 사용해야 할까 ?
- 미확정 값을 표현하고자 할때
언젠가는 NULL이 아닌 값으로 채워질 것이지만, 현재는 미확정인 경우에 유용
- 특정 값이 지나치게 많고 나머지 값만 주로 INDEX로 ACCESS하고자 할 때
예를 들어서 주문 TABLE에 대부분의 경우는 완결 된 값이고 최근 몇개의 값만 미결일 것이다.
하지만 SYSTEM에서 찾는 조건은 대부분 미결만을 찾을 것이다.
이럴때 완결을 NULl로 두고, 미결을 1 로 두면 빠른 검색이 가능하다.
- 결합인덱스의 구성 COLUMN은 NOT NULL로 해야한다.
- 입력조건값으로 자주 사용된다면 NOT NULL로 해야한다.
댓글 없음:
댓글 쓰기