[오라클] Oracle 8i Nosegment Index vs. Oracle 11g Invisible Index

0016. Oracle 8i Nosegment Index vs. Oracle 11g Invisible Index

Oracle 11g New Feature에 새로운 이야기들을 많이 들었을 것이다.
이중 유난히 나의 관심을 끄는 한 가지가 Invisiblie Index이다. Invisilble Index란 말 그대로 "보이지" 않는 인덱스를 의미한다. 더 정확하게 말하면 인덱스 세그먼트로서 물리적으로 존재는 하지만 Optimizer에게는 보이지 않는 인덱스를 의미한다.

테이블에 새로운 인덱스를 추가하면 Optimizer의 비용(Cost) 계산에 영향을 주어 기존 쿼리의 실행 계획에 영향을 주게 된다. 물론 이것은 바람직한 것이고, 인덱스의 존재 이유이기도 하다.

하지만, 간혹 인덱스를 추가했을 때 이 인덱스로 인해 성능 개선이 이루어질 것인지만을 테스트해고 싶은 경우가 있다. 이런 경우에는 어떻게 해야할까?

잘 알려지지 않은 사실이지만, Oracle 8i에서 Virtual Index 기능이 추가되었다. Virtual Index란 말 그대로 가상의 인덱스로 실제로는 인덱스가 존재하지 않지만, 인덱스가 존재하는 것처럼 데이터를 조작하는 것을 의미한다. 이런 의미에서 흔히 Fake Index(가짜 인덱스)라고 부르기도 한다. 아래 테스트 스크립트를 보자.

select * from v$version;
--------------------------------------------------------
BANNER
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.0.0 - Development
NLSRTL Version 3.4.1.0.0 - Production

-- Table 생성
create table t_noseg(id int, id2 int);

-- Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_noseg
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Virtual Index를 생성한다. (NOSEGMENT 속성 부여)
-- id 컬럼에 대한 인덱스는 Clustering Factor가 매우 우수하다.
-- 하지만 id2 컬럼에 대한 인덱스는 Clustering Factor가 매우 불량하다.
create index t_noseg_idx on t_noseg(id) nosegment;
create index t_noseg_idx2 on t_noseg(id2) nosegment;

-- 통계 정보를 생성한다.
exec dbms_stats.gather_table_stats(user,'T_NOSEG', cascade=>true);

-- Virtual Index는 실제로는 사용되지 않으며 실행 계획을 테스트하는 목적으로만 사용된다.
-- _use_nosegment_indexes 값을 true로 변경하면 실행 계획에 반영할 수 있다.
alter session set "_use_nosegment_indexes" = true;

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 4이고, Index는 Analyze가 되지 않았다는 사실에 주목하자.
select * from t_noseg
where id between 1 and 100;

SELECT STATEMENT CHOOSE-Cost : 4
TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX(NU) (ID) NOT ANALYZED

-- id2 컬럼에 대해서도 역시 인덱스를 선택하는 실행 계획이 수립된다.
-- 하지만, Clustering Factor에 의한 비용 증가는 계산되지 않는다.
select * from t_noseg
where id2 between 1 and 100;

SELECT STATEMENT CHOOSE-Cost : 4
TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX2(NU) (ID2) NOT ANALYZED

-- 세그먼트가 존재하는가?
select count(*) from dba_segments where segment_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
0

select count(*) from dba_objects where object_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
1

위의 테스트 결과를 보면 다음과 같은 사실을 추론할 수 있다.
- Virtual Index는 실제로는 세그먼트가 생성되지 않으며, 딕셔너리에만 존재하는 인덱스이다.
- 따라서 실행 계획을 추측해보는데는 도움이 되지만, 실제로 실행에 반영할 수는 없다.
- 실제 세그먼트가 존재하지 않기 때문에 통계 정보를 수집할 수 없다. 따라서 Clustering Factor와 같은 중요한 요소는 계산되지 않는다. 이는 비용 계산에 오류를 초래할 수 있다.

Virtual Index는 오라클 내부적으로만 사용될 목적으로 만들어졌으며 대중적으로 공개가 되어 있지 않다.

Oracle 11g는 Virtual Index를 계승 발전시켜서 Invisible Index라는 전혀 새로운 개념을 소개하고 있다. Invisible Index는 Virtual Index와는 달리 실제로 세그먼트 형태로 존재하는 인덱스이다. 다만 Optimizer에게 보이지 않을 뿐이다. 아래 테스트 스크립트를 보자.

-- Table 생성
create table t_invisible(id int, id2 int);

-- 역시 Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_invisible
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Invisible Index를 생성한다.
create index t_invisible_idx on t_invisible(id) invisible;
create index t_invisible_idx2 on t_invisible(id2) invisible;

-- Invisible Index를 사용하게끔 변경한다. 이렇게 해야만 통계 정보가 수집된다.
alter session set optimizer_use_invisible_indexes=true;

-- 통계 정보 수집
exec dbms_stats.gather_table_stats(user,'T_INVISIBLE', cascade=>true);

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 3이고, Index가 Analyze되었음에 유의하자.
select * from t_invisible
where id between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 3
TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX (ID)

-- id2 컬럼에 대해서는 Full Table Scan을 사용하다.
-- id2 컬럼은 Clustering Factor가 매우 불량하기 때문에 이것이 비용에 반영된 결과이다.
select * from t_invisible
where id2 between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 21
TABLE ACCESS FULL UKJA.T_INVISIBLE(1)

-- 실제 id2 컬럼에 대해 인덱스를 사용하면 비용이 어떻게 계산되지는 확인해보면 아래와 같다.
-- 비용이 102로 Full Table Scan보다 오히려 비싸게 계산되는 것을 확인할 수 있다.
select /*+ index(t_invisible t_invisible_idx2) */* from t_invisible
where id2 between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 102
TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX2 (ID2)

-- 세그먼트가 존재하는가?
select count(*) from dba_segments where segment_name = 'T_INVISIBLE_IDX';
-----------------------------------------------------------------
1

Invisible Index는 Virtual Index와는 달리 실제로 존재하는 인덱스이고 따라서 통계 정보가 수집된다. 따라서 보다 정확한 테스트를 수행할 수 있다. 테스트후 반영여부가 결정되면 다음과 같이 손쉽게 Visible Index로 전환할 수 있다.

alter index t_invisible_idx visible;

위의 간단한 테스트 결과를 보면 Invisible Index가 기존의 Virtual Index에 비해서 훨씬 유용한 기능을 제공하는지 잘 알 수 있다. 아무쪼록 많이 활용되었으면 한다.

(참고) Invisible Index가 존재하는 테이블/인덱스에 대해 통계 정보를 수집하려고 하면 ORA-904: Invalid Column 에러가 발생한다. 이것은 11g R1의 버그이다. 패치가 나오기 전까지는 optimizer_use_invisible_indexes 값을 true로 변경한 후 통계 정보를 수집해야 한다.