히스토그램의 가상(virtual) 컬럼이용

개념 :
FBI (Function Based Index) 를 사용하고 히스토그램을 생성하면 오라클은 유져의 의지와는 상관없이 히스토그램에 가상컬럼을 사용한다.
가상컬럼은 11g 에서는 아예 컬럼값으로 인정하고 파티션및 인덱스도 생성할수 있게 되었다.
예를 들면 upper(컬럼1) 로 해서 컬럼을 생성할수 있는것이다.
하지만 11g 에서도 가상컬럼을 인덱스로 만들면 FBI 로 생성된다.
FBI 사용시 히스토그램에서 가상(virtual) 컬럼이용의 개념과 주의사항을 살펴본다.
아래예제는 오라클 10gR2 에서 테스트 하였다.

--테이블 생성
create table HIDDEN_COL_TEST (A varchar2(20) , B varchar2(100));

-- 데이터 생성
--컬럼 A 에는 분포도가 'a' 가 50%, 'A' 가 50% 로 생성한다.
insert into HIDDEN_COL_TEST
select 'a' , rpad('b',100) from all_objects
union all
select 'A' , rpad('b',100) from all_objects;

commit;

--통계정보생성
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);

--인덱스 생성
create index HIDDEN_COL_IDX on HIDDEN_COL_TEST ( upper(A) ) ;

--인덱스 통계생성
EXEC dbms_stats.gather_index_stats(user,'HIDDEN_COL_IDX');

이제 준비가 다되었다.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
-->변수에 'a' 사용함.

위의쿼리의 경우 전체건이 조회된다.
Plan은 당연히 FTS( Full Table Scan) 으로 풀려야함에도 불구하고 옵티마이져는 우리의 기대를 져버린다.

select * from table(dbms_xplan.display);

---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------

전체건수의 100% 에 해당하는 데이터를 오라클은 인덱스를 사용하였다.왜그럴까?
아래는 dynamic_sampling 을 최고수준으로 주었지만 별소용이 없었다.

explain plan for
select /*+ dynamic_sampling(HIDDEN_COL_TEST 10) */ *
from HIDDEN_COL_TEST where upper(A) = :v_bind;

select * from table(dbms_xplan.display);

---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------

오라클이 비정상적으로 인덱스를 사용한 이유는 히스토그램에 가상컬럼을 생성시켜 주지 않았기 때문이다.

히스토그램을 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35

아직 가상컬럼이 나타나지 않았다.

--테이블 통계정보를 다시생성한다.
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);

히스토그램을 다시 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
HIDDEN_COL_TEST SYS_NC00003$ 5585 3.37499295804764E35

히스토그램에 가상컬럼이 생성됬다.
이제 실행계획을 다시 생성시켜보자.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;

select * from table(dbms_xplan.display);

-----------------------------------------------------
Id Operation Name Rows
-----------------------------------------------------
0 SELECT STATEMENT 79061
* 1 TABLE ACCESS FULL HIDDEN_COL_TEST 79061
-----------------------------------------------------

예상대로 실행계획이 정상으로 돌아왔다.

결론 :
FBI 생성시 반드시 테이블 통계정보를 다시생성해야 한다는걸 알수 있다.
그렇지않으면 가상컬럼이 히스토그램에 생성되지 않을 뿐만아니라 성능도 저하될수 있다는걸 반드시 기억하여야 한다.

참조 URL:
http://www.oracledba.co.uk/tips/collect_stats_subtle.htm