운영 환경에서 INDEX TEST - Virtual Index, Invisible Index

운영 환경에서 새로운 Index 를 추가하게 되면

자칫 optimizer 가 예상하지 나쁜 못한 결과를 내거나 다른 쿼리들에 안좋은 영향을 끼칠 수 있습니다.

 

운영환경에서 이러한 부분을 검증하기 위한 용도로 Virtual Index 와 Invisible Index 를 생각해 볼 수 있습니다.

 

위 두가지 Index 를 optimizer 가 사용하도록 하기 위해서는

session 에서 parameter 설정을 해 줘야 합니다.

 

그럼 index 생성 및 사용 방법을 알아 보겠습니다.

 

[ Virtual Index ]

-- virtual index 생성
-- 뒤에 nosegment 옵션을 설정해 줘야 합니다.
SQL> create index index_name on table_name(column_name) nosegment;
Index created.


-- 세선에서 optimizer 가 virtual index 를 사용하도록 parameter 를 설정합니다.
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;
Session altered.


-- Virtual index 제거 문법은 동일합니다.
SQL> drop index index_name;

※ 참조 URL : http://oracle-help.com/oracle-database/virtual-index-fake-index-oracle/

Virtual Index or Fake Index in Oracle - ORACLE-HELP

 

[ Invisible Index ]

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

 

invisible index 는 Oracle 11g 부터 사용 가능하며 다른 index 들과 동일하게 관리됩니다.

하지만 session 또는 instance 에서 OPTIMIZER_USE_INVISIBLE_INDEXES parameter 를 TRUE 로 설정하기 전에는 optimizer 에 의해 무시됩니다.

-- invisible index 생성
create index index_name on table_name(column_name) invisible;

-- invisible <> visible 인덱스 간 전환
alter index index_name invisible;
alter index index_name visible;

-- 통계수치 수집
exec dbms_stats.gather_table_stats(null, 'table_name', cascade=> true);


-- invisible 인덱스를 optimizer 가 사용하도록 변경
alter session set optimizer_use_invisible_indexes=true;

※ 참조 URL : https://oracle-base.com/articles/11g/invisible-indexes-11gr1

Invisible Indexes in Oracle Database 11g Release 1

 

Invisible Index 와 Virtual index 모두 DDL 권한이 필요하며 사용이 완료된 후 불필요한 경우 삭제해주는 것을 잊지 않도록 하자.