DATOR


유용한 SQL문(Oracle 11g Invisible Index)

튜닝을 하다보면 해당 인덱스를 적용해서 제대로 인덱스를 SCAN하고 수행속도가 올바르게 나오는지 체크 해보고 싶은 경우가 있다.

그러나 운영서버에 잘못 반영하다가 기존 ACCESS PATH에 영향을 주기 때문에 개발서버에 테스트 하는 경우가 있다.

개발서버는 해당 테이블의 데이터 및 시스템 스팩이 틀리기 때문에 정확한 수행시간 및 효과를 알기가 어렵다.

 

반가운 소식이 Oracle 11g 부터 Invisible Index 라는 새로운 개념이 나왔다.

 

즉 옵티마이저가 해당 인덱스는 실행계획을 수립 할때 무시하고 세우기때문에
해당 인덱스를 만들어도 기존 PLAN변경에 대한 두려움은 없어지고 해당 인덱스의 효율성을 테스트 한후 인덱스를 즉시 반영하면 된다.


CREATE TABLE TEST_ALL_OBJECTS
AS
SELECT *
  FROM ALL_OBJECTS;

SELECT COUNT(*)
 FROM TEST_ALL_OBJECTS  
  
69040건

 

 

만약에 OBJECT_ID의 싱글 인덱스가 필요하다면 아래와 같이 테스트 해볼수 있다.

 

CREATE INDEX TEST_ALL_OBJECTS_IDX01 ON TEST_ALL_OBJECTS(OBJECT_ID) INVISIBLE;

SELECT /*+ gather_plan_statistics */
         *
  FROM TEST_ALL_OBJECTS
WHERE OBJECT_ID  = '52'  


Plan_table_output

SQL_ID  5kyqarvgd1cuz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */          *   FROM TEST_ALL_OBJECTS
WHERE OBJECT_ID  = '52'
 
Plan hash value: 1888393159
 
------------------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |      1 |        |      1 |00:00:00.01 |     987 |
|*  1 |  TABLE ACCESS FULL| TEST_ALL_OBJECTS |      1 |     11 |      1 |00:00:00.01 |     987 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_ID"=52)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

인덱스를 SCAN하지 않는다.

INVISIBLE를 사용 하려면 세션 레벨로 아래와 같이 부여 해야 한다.

 

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

SELECT /*+ gather_plan_statistics */
         *
  FROM TEST_ALL_OBJECTS
WHERE OBJECT_ID  = '52'  

 

Plan_table_output

SQL_ID  5kyqarvgd1cuz, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */          *   FROM TEST_ALL_OBJECTS
WHERE OBJECT_ID  = '52'
 
Plan hash value: 2902588028
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ALL_OBJECTS       |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | TEST_ALL_OBJECTS_IDX01 |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=52)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

해당 인덱스를 옵티마이저가 실행계획을 수립할때 고려대상으로 넣을려고 한다면 아래와 같이 해줘야 한다.


ALTER INDEX TEST_ALL_OBJECTS_IDX01 VISIBLE;


위의 문장을 보면 이런 경우에도 사용 가능하다.

 

아주 큰 테이블이 있다.그런데 해당 인덱스를 사용하지 않는것 같지만
DROP 하기에는 부담스럽기 때문에 그런 인덱스를 INVISIBLE로 한후 특별한 문제가 없으면 실제로 DROP을 하면 된다.

 

ALTER INDEX TEST_ALL_OBJECTS_IDX01 INVISIBLE;

한후 몇일 지나서 정말 특별한 문제가 없으면 아래와 같이 실제로 DROP INDEX을 하면 되는 것이다.

DROP INDEX TEST_ALL_OBJECTS_IDX01;

 

도움이 되시기를 바랍니다.

 

Tag

Leave Comments