[Oracle] Not Null 제약 조건에 따른 인덱스 실행 계획

오라클 옵티마이저가 수행할 때 제약 설정에 따라서 인덱스 사용/비사용 할 수 있습니다. 우리가 말하는 오라클 옵티마이저 행동 중에서 영향을 미치는 요소 중에 하나 입니다.

/* 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 |
------------------------------------------------------------------------------------------

너무나 간단한 문제라고 생각 할 수 있지만, 힌트를 사용해도 인덱스를 제대로 활용하지 못할 때 오라클 옵티마이저가 가지고 있는 독특한 개념 때문에 그럴 수 있습니다. 저 또한 이러한 이유로 원인을 찾아내지 못 한 적 있었습니다. 실습을 통해서 확인 해보시면 바로 알 수 있는 부분입니다.