Data Architecture/Tunning
[Oracle] Not Null 제약 조건에 따른 인덱스 실행 계획
EstenPark
2015. 12. 23. 10:50
오라클 옵티마이저가 수행할 때 제약 설정에 따라서 인덱스 사용/비사용 할 수 있습니다. 우리가 말하는 오라클 옵티마이저 행동 중에서 영향을 미치는 요소 중에 하나 입니다.
/* EST_OBJECT 테이블의 OBJECT_NAME 컬럼에 OBJECT_TYPE이 INDEX가 아닌 나머지는 NULL로 처리 합니다. */
CREATE TABLE EST_OBJECT AS
SELECT A.OWNER
,CASE
WHEN A.OBJECT_TYPE = 'INDEX' THEN A.OBJECT_NAME
ELSE NULL
END AS OBJECT_NAME
FROM DBA_OBJECTS A
;
/* 인덱스 생성 */
CREATE INDEX EST_OBJECT_IDX01 ON EST_OBJECT ( OBJECT_NAME );
/* EST_OBJECT.OBJECT_NAME 컬럼의 NOT NULL 확인 */
SELECT A.TABLE_NAME, A.COLUMN_NAME, A.NULLABLE
FROM DBA_TAB_COLS A
WHERE A.TABLE_NAME = 'EST_OBJECT'
AND A.COLUMN_NAME = 'OBJECT_NAME'
;
[결과]
TABLE_NAME: EST_OBJECT
COLUMN_NAME: OBJECT_NAME
NULLABLE: Y
/* 힌트를 사용하여 INDEX FULL SCAN을 유도 */
SELECT /*+ INDEX (A EST_OBJECT_IDX01) */
OBJECT_NAME, COUNT(*)
FROM EST_OBJECT A
GROUP BY OBJECT_NAME
;
[실행계획]
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 42 (100)| | 501 |00:00:00.05 | 137 | | | |
| 1 | HASH GROUP BY | | 1 | 68155 | 4392K| 42 (10)| 00:00:01 | 501 |00:00:00.05 | 137 | 7808K| 2119K| |
| 2 | TABLE ACCESS FULL| EST_OBJECT | 1 | 68155 | 4392K| 39 (3)| 00:00:01 | 73599 |00:00:00.02 | 137 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
위 내용을 보시면 힌트를 사용해서 INDEX를 사용할 수 있게 유도 했지만, TABLE FULL SCAN으로 나오는 것을 볼 수 있습니다. 이러한 이유는 해당 컬럼의 제약 조건이 NULL을 허용하게 설정 되어 있으면 정상적으로 인덱스를 사용할 수 없게 되는 것입니다. 따라서 아래는 NOT NULL 제약 설정을 하고, 동일한 쿼리를 다시 수행 해보겠습니다.
/* EST_OBJECT 테이블에서 OBJECT_NAME 컬럼의 값이 NULL인 데이터를 모두 삭제 합니다.*/
DELETE EST_OBJECT A
WHERE a.OBJECT_NAME IS NULL
;
COMMIT;
/* 힌트를 사용하여 INDEX FULL SCAN을 유도 */
SELECT /*+ INDEX (A EST_OBJECT_IDX01) */
OBJECT_NAME, COUNT(*)
FROM EST_OBJECT A
GROUP BY OBJECT_NAME
;
[실행계획]
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 42 (100)| | 501 |00:00:00.05 | 137 | | | |
| 1 | HASH GROUP BY | | 1 | 68155 | 4392K| 42 (10)| 00:00:01 | 501 |00:00:00.05 | 137 | 7808K| 2119K| |
| 2 | TABLE ACCESS FULL| EST_OBJECT | 1 | 68155 | 4392K| 39 (3)| 00:00:01 | 73599 |00:00:00.02 | 137 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
/* 컬럼 NOT NULL 제약 설정 */
ALTER TABLE EST_OBJECT MODIFY (OBJECT_NAME VARCHAR2(30) NOT NULL);
/* 힌트를 사용하여 INDEX FULL SCAN을 유도 */
SELECT /*+ INDEX (A EST_OBJECT_IDX01) */
OBJECT_NAME, COUNT(*)
FROM EST_OBJECT A
GROUP BY OBJECT_NAME
;
[실행계획]
------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5932 | 98K| 25 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 5932 | 98K| 25 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EST_OBJECT_IDX01 | 5932 | 98K| 25 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
너무나 간단한 문제라고 생각 할 수 있지만, 힌트를 사용해도 인덱스를 제대로 활용하지 못할 때 오라클 옵티마이저가 가지고 있는 독특한 개념 때문에 그럴 수 있습니다. 저 또한 이러한 이유로 원인을 찾아내지 못 한 적 있었습니다. 실습을 통해서 확인 해보시면 바로 알 수 있는 부분입니다.