[ORACLE] 인덱스-액세스 효율향상

http://blog.empas.com/jjh7266/read.html?a=7154072&c=376971

인덱스-액세스 효율향상

1.인덱스의 활용
-.인덱스란 옵티마이져가 최적의 처리경로를 결정하기 위해 사용하는 중요한 요소.
-.인덱스는 테이블의 Row와 하나씩 대응되는 별도의 객체로,
인덱스를 생성시킨 컬럼(Column)과 테이블의 논리적인 주소(RowID)로 구성됨.
-.인덱스의 변화에 따라 실행결과는 결코 달라지지 않으며 다만 처리경로에 영향을 미칠 따름

1.1 인덱스의 구조
-.Column + RowID
-.RowID 란 테이블에 있는 Row의 물리적인 주소가 아님
테이블에는 RowID가 실제로 존재하지 않음, 즉 인덱스에 있는 RowID는 다만 테이블에 있는
해당 Row를 찾기 위해 사용되는 논리적인 정보
문자형식으로 출력 시 18자리(실제 6바이트를 가진 ROWID 데이터 타입)
-.옵티마이져가 인덱스를 이용하는 실행계획을 수립하였다면 조건에 맞는 Row를 B-Tree 방식으로
접근하여 처리할 범위까지 Scan 처리하며 RowID 정보를 이용하여 실제 테이블에 Random Access함.
이때 Row단위로 처리되는 것이 아니라 Block단위의 I/O를 통해 메모리로 읽혀지게 됨.
물론 이미 읽혀진 Block에 있다면 No

1.2인덱스의 적용원칙
-.옵티마이져의 판단 조건: ①주어진 조건 ②인덱스 구성 ③통계정보 ④클러스터링
⑤Select List ⑥사용자의 코딩 ⑦Hint ⑧옵티마이져 Mode
1) SUPPRESSING
인덱스 컬럼은 비교되기 전에 변형이 일어날 경우 상수로 인식되어 인덱스를 사용할 수 없게 된다.
즉 인덱스는 가공되기 전 값으로 생성되어 있는 것이기 때문.

● 외부적 변형: 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수, 연산,
결합( || ) 등으로 가공을 시킨 후 비교할 때 발생함.

SELECT DEPT, ENAME, SAL
FROM EMP
WHERE SUBSTR( JOB, 1, 4 ) = ‘SALE’

WHERE문을 다음과 같이 바꾸면 변형이 일어나지 않도록 기술할 수 있음

WHERE JOB LIKE ‘SALE%’

● 내부적 변형: 서로 다른 데이터 타입을 비교하고자 할 때 DBMS가 어느 한 쪽을 기준으로
동일한 타입이 되도록 내부적으로 변형함.
이러한 변형은 원하지 않은 액세스 경로를 만들게 하여 수행속도에 막대한
영향을 미치게 함.

＊문자타입의 컬럼 WHERE Chr = 10

① Chr 문자타입을 숫자로 변형함. 즉 TO_NUMBER(Chr)과 동일

＊숫자타입의 컬럼
상수값의 타입에 상관없이 변형이 일어나지 않음.

단 다음과 같이 프로젝트 개발중 사용자의 다양한 요구사항을 반영하기 위해
LIKE문의 사용이 불가피하다면 문자타입으로 지정하는 것이 좋음. 그렇지않을 경우

WHERE NUM LIKE ‘10%’

① 10%를 숫자타입으로 변경 시도: % 라는 문자값이 있어 불가능
② 따라서 NUM을 문자값으로 변형함. 즉 TO_CHAR( NUM )과 동일
결국 변형이 일어나게 됨.

조인의 연결고리가 되는 컬럼들의 데이터 타입은 서로 다르지 않도록 함.

SELECT X.ORDNO, X.ORD_DATE, Y.ITEM, Y.ORDQTY
FROM ORDER1T X, ORDER2T Y
WHERE X.ORDNO = Y.ORDNO
AND X.ORD_DATE = ‘950223’

2) 부정형의 비교
-.인데스 컬럼은 비교되는(주어진) 상수값에 대해 B-Tree방식으로 string을 비교하여 찾음.
따라서 비교시 상수가 아닌 값을 찾는 부정형 조건은 논리적으로 비교할 방법이 없게 됨.
따라서 긍정형으로 변형하는 것이 좋은 SQL문 구현 습관임.
-.대안 및 예

존재여부

SELECT ‘NOT FOUND’ FROM EMP WHERE EMPNO <> ‘1234’

⇒SELECT ‘NOT FOUND’FROM DUAL WHERE NOT EXISTS (SELECT ‘X’ FROM EMP
WHERE EMPNO = ‘1234’)

조건에 일치하는 결과집합 : ①NOT EXISTS, ②NOT-IN, ③IN-MINUS

① SELECT * FROM TAB1 A
WHERE A.YYMM = ‘9502’
AND NOT EXISTS (SELECT * FROM TAB2 WHERE COL2 = A.COL1 AND YYMM = ‘9502’)

주SQL의 인덱스 컬럼(A.COL1)이 보조SQL문의 조건절에 포함될 경우 보조 SQL문이 먼저 실행
되는 것이 아니라 주SQL의 ROW마다 실행됨.

②SELECT * FROM TAB1
WHERE YYMM = ‘9502’ AND COL1 NOT IN ( SELECT * FROM TAB2 WHERE YYMM = ‘9502’)

NOT IN을 사용한 보조SQL은 항상 나중에 사용되거나 FILTERING 조인방식으로 수행됨.
이는 ①과 동일한 결과를 초래하게 됨.

③ SELECT * FROM TAB1
WHERE (YYMM, COL1) IN (SELECT ‘9502’, COL1 FROM TAB1 WHERE YYMM = ‘9502’
MINUS
SELECT ‘9502’, COL2 FROM TAB2 WHERE YYMM = ‘9502’ )

이 경우 YYMM+COL 의 결합인덱스가 존재할 경우 ①, ②에 비해 유리함.
즉 환경과 조건에 따라 유리할 수도 불리할 수도 있음을 말함.

3) NULL을 사용한 비교
Single인덱스의 경우 인덱스를 구성하고 있는 모든 컬럼의 값이 NULL인 ROW를 허용하지 않음.
다시 말해 결합 인덱스의 첫번째 컬럼을 NULL로 비교하지 않을 경우 인덱스는 사용된다는 의미임

IS NOT NULL WHERE ORD_DATE IS NOT NULL → WHERE ORD_DATE > ‘’

IS NULL
해결안이 없음, 단 컬럼의 값이’NULL’인 ROW가 많다면 손해가 되지 않으나 적다면 손해가 큼.따라서

컬럼의 기본값을 지정하여 주는 것이 중요함.

CREATE TABLE ORDER1T
( STATUS CHAR(2) DEFAULT ‘00’ …)→ WHERE STATUS = ‘00’

자료의 일관성을 유지할 수 있도록 설계하여야 함.

AVG( ORDQTY + ASNQTY ) Vs AVG( NVL(ORDQTY, 0) + NVL(ASNQTY, 0))

위의 예에서 결과는 동일하지 않음 즉 ORDQTY와 ASNQTY중 하나만 NULL이어도 첫 번째 예에는
반영되지 않는데 반해 두 번째에서는 반영이 됨.

4) 옵티마이져에 의한 취사 선택

RANKING 차이에 의한 선택 : Rule Based 정의된 순위에 의해 어떤 인덱스를 사용할 것인가가 결정됨

1. ROWID = constant
2. Cluster join with unique or primary key = constant
3. Hash Cluster key with unique or primary key = constant
4. Entire unique composite index = constant
5. Unique or primary key = constant
6. Entire cluster key = corresponding cluster key in another table in same cluster
7. Hash cluster key = constant
8. Entire cluster key = constant
9. Entire non unique composite index = constant
10. Non unique index = constant
11. Entire composite index >= lower bound
12. Most leading composite index specified
13. Unique indexed column BETWEEN low value AND high value, Or unique indexed column LIKE ‘C%’
14. Non unique indexed column BETWEEN low value AND high value Or unique indexed column LIKE ‘C%’
15. Unique indexed column < or > constant
16. Non unique indexed column < or > constant
17. Equality on non-indexed column < or > constant
18. MAX or MIN function
19. ORDER BY clause
20. Full table scan

낮은 처리비용의 선택 : Cost Based
주어진 조건 및 통계정보에 따라 최저비용을 가진 액세스를 선택함.
단 비용기준에서도 옵티마이져 Goal이 FIRST_ROWS냐 혹은 ALL_ROWS냐에 따라 비용계산 방법이
다르므로 확인이 필요함.

HINT에 의한 선택
HINT란 사용자가 액세스 경로의 변경을 위해서 SQL내에 요구사항을 기술하면 옵티마이져가
액세스 경로를 결정할 때 이를 참조하도록 하는 사용자 Interface를 말함.

-. Syntax SELECT, UPDATE, DELETE-------/*+ HINT */ or --+ HINT……..

(힌트의 기능)
RULE:RULE BASE 옵티마이져 사용
FIRST_ROWS : 첫째 레코드의 추출시간을 최소화 할 목적으로 최적화
ALL_ROWS : 해당 레코드에 대해 모두 처리하는 시간의 최소화를 목적으로 최적화
FULL :지정된 테이블에 대한 전체 스캔
ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔
CLUSTER : 지정된 테이블에 대한 클러스터 스캔
HASH : 지정된 테이블에 대한 해쉬 스캔
INDEX_ASC : 내림차순으로 INDEX 를 스캔
INDEX_DESC : 오름차순으로 INDEX 를 스캔
AND_EQUALS : 여러 개의 INDEX들을 MERGE하여 사용(2 ~ 5개)
ORDERED : FROM 절에 기술된 TABLE순으로 JOIN
USE_NL : 먼저 특정 테이블의 ROW를 엑세스하고 그 값에 해당하는 다른 테이블의 ROW를 찾는 작업을 실행 USE_MERGE : 먼저 각각의 테이블의 처리범위를 스캔하여 정렬한 후 서로 MERGE 하면서 JOIN 하는 방식

1.3 인덱스

(인덱스 Merge)
.일반적으로 인덱스 Merge를 하는 것보다는 분포도가 좋은 하나의 인덱스를 사용하는 것이 유리함.
.일반적으로 결합 인덱스란 인덱스 Merge가 일어 났을 때 Merge가 성공한 ROW들만 미리 모아 둔
것을 말함. 따라서 액세스 속도가 향상됨.
.인덱스 Merge는 서로 다른 몇 개의 인덱스를 같은 ROWID로 Merge하여야 하므로 실제 일한 양과
Merge에 성공한 양에는 차이가 있음 그 만큼 불필요한 일을 한 것임.

(결합인덱스)
.결합 인덱스의 첫번째 컬럼이 조건절에 없다면 인덱스는 사용되지 않음.
.결합 인덱스를 구성하는 컬럼의 순서는 수행속도에 영향을 미침
.결합 인덱스의 첫번째 컬럼에 대해 분포도보다 더 중요한 요소는 『‘=’로 사용되었느냐』라는 것임.
즉 어떤 컬럼순으로 인덱스를 생성해야 ‘=’조건을 보다 많이 충족시킬지를 고려해야 함.
.B*Tree 구조의 인덱스는 전체의 ROW수 보다는 이분화해 가는 깊이에 영향을 받음.
즉 한 번 이분화를 했을 때 얼마나 처리할 범위를 줄였느냐가 깊이에 영향을 줌.
5개의 컬럼까지만 이분화를 수행하고 그 이상의 컬럼은 인덱스 Row를 스캔함