제약 조건과 인덱스

제약 조건은 인덱스 사용에 영향을 미칠 수 있다. 

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(10), c2 VARCHAR2(10));
CREATE INDEX t1_x1 ON t1 (c1);

아래 쿼리는 c1 열에 UPPER 함수를 사용하여 인덱스를 사용하지 못했다.

-- 2
SELECT * FROM t1 WHERE UPPER (c1) = 'A';

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T1   |
----------------------------------

아래와 같이 c1 열에 NOT NULL 제약 조건과 CHECK 제약 조건을 생성하면 인덱스를 사용할 수 있다. 제약 조건에 의해 값의 무결성이 보장되기 때문이다.

-- 3-1
ALTER TABLE t1 MODIFY c1 NOT NULL;
ALTER TABLE t1 ADD CONSTRAINT t1_c1 CHECK (c1 = UPPER (c1));

-- 3-2
SELECT * FROM t1 WHERE UPPER (c1) = 'A';

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"='A')
       filter(UPPER("C1")='A')

아쉬운 점은 NOT NULL 조건이 존재하지 않는 경우 인덱스를 사용할 수 없다는 점이다.

-- 4-1
ALTER TABLE t1 MODIFY c1 NULL;

-- 4-2
SELECT * FROM t1 WHERE UPPER (c1) = 'A';

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T1   |
----------------------------------

-- 4-3
SELECT * FROM t1 WHERE UPPER (c1) = 'A' AND c1 IS NOT NULL;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|*  2 |   INDEX RANGE SCAN          | T1_X1 |
---------------------------------------------