개요
운영 중인 티베로DB 환경에서 인덱스(Index)를 생성할 때, 인덱스 생성에 따른 영향도를 확인하기 위해 INDEX INVISIBLE 기능을 활용할 수 있습니다.
INVISIBLE Index를 통해 확인 가능한 영향도는 아래와 같습니다.
•
Index를 생성한 후 해당 Index를 사용한 실행 계획(Plan)이 효율적인지 사전에 검토하고 싶은 경우
•
기존의 효율적인 쿼리 Plan이 index 생성 후 효율성이 감소했는지 확인하고 싶은 경우
위 내용을 확인하기 위한 INDEX INVISIBLE 기능을 활용 방법을 안내합니다.
방법
INDEX INVISIBLE 을 사용하려면 파라미터 설정을 변경해야 하고, 인덱스 생성 시 INVISIBLE 옵션을 함께 사용해야 합니다.
파라미터 확인 (기본값 NO)
SHOW PARAM OPTIMIZER_USE_INVISIBLE_INDEXES
SQL
복사
파라미터 변경
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;
SQL
복사
INVISIBLE 인덱스 생성
CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;
SQL
복사
VISIBLE 인덱스 변경
ALTER INDEX IDX_TEST VISIBLE;
SQL
복사
테스트 시나리오
아래 시나리오는 INVISIBLE 인덱스 생성과 파라미터 설정 여부에 따라 실행 계획이 어떻게 달라지는지 보여줍니다.
시나리오 1
1.
INVISIBLE로 생성
CREATE TABLE TEST (N NUMBER);
INSERT INTO TEST SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
COMMIT;
CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;
SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='IDX_TEST';
SET AUTOT ON EXP PLANS STAT
SELECT * FROM TEST WHERE N=500;
SQL
복사
2.
INVISIBLE로 생성하고 파라미터 변경
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;
SELECT * FROM TEST WHERE N=700;
SQL
복사
3.
실행 결과
1) INVISIBLE 인덱스 생성
옵티마이저가 해당 인덱스를 인식하지 않기 때문에 인덱스가 존재하더라도 테이블은 전체 검색(Full Scan)으로 처리됩니다.
SQL> SHOW PARAM OPTIMIZER_USE_INVISIBLE_INDEXES
NAME TYPE VALUE
---------------------------- -------- ----------------------------------------
OPTIMIZER_USE_INVISIBLE_INDE Y_N NO
XES
SQL> CREATE TABLE TEST (N NUMBER);
Table 'TEST' created.
SQL> INSERT INTO TEST SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
10000 rows inserted.
SQL> COMMIT;
Commit completed.
SQL> CREATE INDEX IDX_TEST ON TEST (N) INVISIBLE;
Index 'IDX_TEST' created.
SQL> SELECT * FROM DBA_INDEXES WHERE INDEX_NAME='IDX_TEST';
-- (중략)
1 row selected.
SQL> SET AUTOT ON EXP PLANS STAT
SQL> SELECT * FROM TEST WHERE N=500;
N
----------
500
1 row selected.
SQL ID: gc26azr12fduq
Child number: 307
Plan hash value: 1463502807
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): TEST (Cost:24, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
1 - filter: ("TEST"."N" = 500) (0.000)
Note
--------------------------------------------------------------------------------
1 - dynamic sampling used for this table (32 blocks)
NAME VALUE
------------------------------ ----------
db block gets 137
consistent gets 138
physical reads 0
redo size 188
sorts (disk) 0
sorts (memory) 1
rows processed 1
Execution Stat
--------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): TEST (Time:0. ms, Rows:0, Starts:0)
SQL
복사
2) INVISIBLE 인덱스 생성 및 파라미터 변경
파라미터를 변경한 후에는 INVISIBLE 인덱스라 하더라도 옵티마이저가 이를 인식하므로, 해당 인덱스를 사용합니다.
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=Y;
Session altered.
SQL> SELECT * FROM TEST WHERE N=700;
N
----------
700
1 row selected.
SQL ID: 622bxdyk716p2
Child number: 324
Plan hash value: 1576365538
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:2, %%CPU:0, Rows:1)
2 INDEX (RANGE SCAN): IDX_TEST (Cost:2, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
2 - access: ("TEST"."N" = 700) (0.000)
Note
--------------------------------------------------------------------------------
2 - dynamic sampling used for this table (32 blocks)
NAME VALUE
------------------------------ ----------
db block gets 137
consistent gets 39
physical reads 1
redo size 60
sorts (disk) 0
sorts (memory) 1
rows processed 1
Execution Stat
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Time:0. ms, Rows:0, Starts:0)
2 INDEX (RANGE SCAN): IDX_TEST (Time:0. ms, Rows:0, Starts:0)
SQL
복사