인덱스 invisible, visible 변경

인덱스 INVISIBLE 처리 방법은 매우 유용하게 사용할 수 있는 기술이다. 

​

[ 활용 ]

1. 신규 INDEX를 생성시 INVISIBLE OPTION을 주면 타 어플리케이션에 방해 주지 않고,

인덱스 생성한 담당자의 SQL이 정상적으로 튜닝이 되었는지 체크 가능.

​

2. INDEX DROP전 INVISIBLE로 영향도 파악이 가능.

- INVISIBLE 처리 > 성능이슈 발생 > 즉시 INDEX VISIBLE > 성능 정상 복귀

- 오랜 기간 INVISIBLE 된 상태 인덱스를 힌트로 강제로 사용 중인 것이 있다면 해당 SQL 찾기는 다른 방법이 필요함.

​

[ Parameter ]

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'optimizer_use_invisible_indexes';

- FALSE가 되어야만 INVISIBLE 인덱스를 OPTIMIZER가 실행플랜 생성시 이용을 못한다.

 

[ 힌트 ]

: USE_INVISIBLE_INDEXES, NO_USE_INVISIBLE_INDEXES

SELECT * FROM V$SQL_HINT WHERE NAME LIKE '%INVISIBLE%';

​

[ INVISIBLE INDEX 사용하기 ]

CREATE INDEX IX_EMP3_01 ON EMP3 ( EMPNO ) TABLESPACE USERS INVISIBLE;

[ INVISIBLE INDEX 사용하기 테스트 ]


#1 ORIGINAL => 기본적으로 일반 SQL에서 사용못함
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE;
SELECT * FROM EMP3 WHERE EMPNO = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP3 |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


#2 ALTER SESSION => 성공 예시 ( 티베로 DB는 #2번만으로 사용이 가능하다. HINT 사용으로는 불가 ) 
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
SELECT * FROM EMP3 WHERE EMPNO = 1;
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP3       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP3_01 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


#3 HINT => 성공 예시
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE;
SELECT /*+ USE_INVISIBLE_INDEXES(A) */ * FROM EMP3 A WHERE EMPNO = 1;
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP3       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP3_01 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------  


#4 OPT_PARAM => 안되는 예시
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE;
SELECT /*+ OPT_PARAM('OPTIMIZER_USE_INVISIBLE_INDEXES','true') */ * FROM EMP3 WHERE EMPNO = 1;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP3 |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


#5 VISIBLE 처리 
ALTER INDEX IX_EMP3_01 VISIBLE;
SELECT * FROM EMP3 WHERE EMPNO = 1;
--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP3       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX_EMP3_01 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------