INVISIBLE 인덱스

11.1 버전에 추가된 INVISIBLE 인덱스를 살펴보자.

테스트를 위해 아래와 같이 데이터를 생성하자. t1_x1은 INVISIBLE 인덱스다. *_INDEXES 뷰의 visibility 열이 INVISIBLE로 표시된다.

-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 1000');
CREATE INDEX t1_x1 ON t1 (c1) INVISIBLE;

-- 1-2
SELECT visibility FROM user_indexes WHERE index_name = 'T1_X1';

VISIBILITY
----------
INVISIBLE

1개의 행이 선택되었습니다.

INVISIBLE 인덱스는 optimizer_use_invisible_indexes 파라미터가 TURE인 경우에만 사용된다. 신규 인덱스 생성, 기존 인덱스 삭제의 영향도 파악 등에 활용할 수 있다.

-- 2-1
SELECT * FROM t1 WHERE c1 = 1;

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

-- 2-2
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

SELECT * FROM t1 WHERE c1 = 1;

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|*  1 |  INDEX RANGE SCAN| T1_X1 |
----------------------------------

INVISIBLE 인덱스를 사용하면 동일한 열에 다수의 인덱스를 생성할 수 있다. 예제 3-3을 응용하면 로그온 트리거를 통해 세션별로 사용할 인덱스를 지정할 수 있다.

-- 3-1
ALTER INDEX t1_x1 VISIBLE;

인덱스가 변경되었습니다.

CREATE BITMAP INDEX t1_x2 ON t1 (c1);

ORA-01408: 열 목록에는 이미 인덱스가 작성되어 있습니다

ALTER INDEX t1_x1 INVISIBLE;

인덱스가 변경되었습니다.

CREATE BITMAP INDEX t1_x2 ON t1 (c1);

인덱스가 생성되었습니다.

ALTER INDEX t1_x1 VISIBLE;

ORA-14147: 동일한 열 집합에 정의된 기존 VISIBLE 인덱스가 있습니다.

-- 3-2
ALTER SESSION SET optimizer_use_invisible_indexes = FALSE;

SELECT * FROM t1 WHERE c1 = 1;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  BITMAP CONVERSION TO ROWIDS|       |
|*  2 |   BITMAP INDEX SINGLE VALUE | T1_X2 |
---------------------------------------------

-- 3-3
ALTER INDEX t1_x2 INVISIBLE;
ALTER INDEX t1_x1 VISIBLE;

SELECT * FROM t1 WHERE c1 = 1;

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|*  1 |  INDEX RANGE SCAN| T1_X1 |
----------------------------------