인덱스 생성 및 Rebuild 시 통계정보 자동 갱신 파라미터
==> _optimizer_compute_index_stats
    디폴트 값이 TRUE 로 인덱스 신규생성이나 리빌드시에 자동으로 통계정보가 갱신된다.

    테이블에 row 가 유/무에 상관없이 항상 동일하게 작동 합니다.

-- 관련 파라미터 체크
-- RUN BY SYS
SELECT
a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM
x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND   a.indx = c.indx
AND   a.ksppinm LIKE ’%_optimizer_compute_index_stats%’
/

-- 파라미터 의미
-- This parameter means that Optimizer Statistics will be generated
-- on the Indexes during creation or rebuild of these
-- _optimizer_compute_index_stats    TRUE    TRUE  

-- TEST 시작

-- 테이블 생성
CREATE TABLE CHECK_INDEX ( A1 NUMBER, A2 VARCHAR2(100), A3 NUMBER ) ;

-- 통계정보 수집
exec dbms_stats.gather_table_stats(ownname => ’OPS$ORACLE’, tabname=> ’CHECK_INDEX’) ;

-- 테이블 통계정보 관련 체크
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    CHECK_INDEX     2009/11/25 15:15:33

-- 인덱스 통계정보 관련 체크 ( 아직 인덱스 만들기 전 ^^;)
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_NAME =’CHECK_INDEX’;
-- 값없음

-- 인덱스 생성
CREATE INDEX XAK1_CHECK_INDEX ON CHECK_INDEX ( A1, A2 ) ;

-- 테이블 통계정보 관련 체크
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    CHECK_INDEX     2009/11/25 15:15:33
-- 변경 없음 (인덱스 생성에 따른 테이블 통계정보 갱신 없음 )

-- 인덱스 통계정보 관련 체크
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    XAK1_CHECK_INDEX    2009/11/25 15:16:40
-- 인덱스 생성과 동시에 인덱스에 대한 통계정보가 생성 된다.

-- 인덱스 리빌드
ALTER  INDEX XAK1_CHECK_INDEX REBUILD ;

-- 테이블 통계정보 관련 체크
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    CHECK_INDEX     2009/11/25 15:15:33
-- 변경 없음

-- 인덱스 통계정보 관련 체크
SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    XAK1_CHECK_INDEX    2009/11/25 15:19:07
-- 인덱스 리빌드와 동시에 인덱스에 대한 통계정보가 생성 된다.

-- 테이블에 대한 통계정보 수집
-- CASCADE = YES 가 디폴트다 - 즉 테이블 통계정보 수집 시에,  인덱스에 대한 통계정보도 수집된다.
exec dbms_stats.gather_table_stats(ownname => ’OPS$ORACLE’, tabname=> ’CHECK_INDEX’) ;

SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    CHECK_INDEX     2009/11/25 15:21:11

SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    XAK1_CHECK_INDEX    2009/11/25 15:21:11
-- 테이블에 대한 통계정보 수집 시에 인덱스도 자동으로 갱신된다.

-- 인덱스 삭제
DROP INDEX XAK1_CHECK_INDEX ;

-- 테이블 통계정보 갱신 체크
SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’CHECK_INDEX’;
-- OPS$ORACLE    CHECK_INDEX     2009/11/25 15:22:45
-- 변화 없음 ( 인덱스를 삭제 하여도, 테이블에 대한 통계정보가 갱신되지 않는다 )

인덱스 생성/삭제/리빌드시에 테이블에 대한 통계정보를 갱신하는게 맞습니다.

일요일 테이블 통계정보를 생성하고, 화요일 해당 테이블에 대한 인덱스를 생성한다면,

그리고, 테이블 통계정보와 인덱스의 통계정보가 많이 상이하다면, 엉뚱한 플랜이 나올수 있겠죠