SQL 분석 도구 (2)

-- #####################################################
-- SQL 튜닝 도구
--
-- 1. 테이블의 인덱스 정보
-- 2. 테이블, 인덱스 통계정보 확인
-- 3. 인덱스 효율성 판단 (통계정보 활용하여 카디널리티 확인)
-- 4. 인덱스 효율성 판단 (통계정보 없는경우 카디널리티 확인)
-- #####################################################
 
-- 1. 테이블의 인덱스 정보
SELECT index_name
     , blevel
     , num_rows
     , (SELECT listagg(column_name, ' + ') within group (order by column_position) 
          FROM dba_ind_columns 
         WHERE index_owner = i.owner
           AND index_name  = i.index_name) as key_columns
  FROM all_indexes i
 WHERE owner = 'ORAKIM'
   AND table_name = 'MID_TABLE'
;
 
 
-- 2. 테이블, 인덱스 통계정보
SELECT OWNER,TABLE_NAME,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED
  FROM DBA_TABLES 
 WHERE OWNER='ORAKIM' 
   AND TABLE_NAME='T1'
 ;
 
SELECT OWNER,INDEX_NAME,TABLE_NAME,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED 
  FROM DBA_INDEXES 
 WHERE OWNER='ORAKIM' 
   AND TABLE_NAME='T1'
 ;
 
 
-- 3. 인덱스 효율성 판단 (통계정보 활용하여 카디널리티 확인)
SELECT t.table_name
     , c.column_name
     , c.data_type
     , c.num_distinct
     , c.num_nulls
     , t.num_rows
     , ((t.num_rows - c.num_nulls)/c.num_distinct) as "Cardinality"   -- 예상추출 건수가 적을수록 인덱스 효율성 좋다.(인덱스는 큰테이블에서 소량의 데이터 추출시 활용하는 것)
  FROM dba_tables t, dba_tab_columns c
 WHERE t.table_name = c.table_name
   AND t.TABLE_NAME = 'DAILY_PRICE'
;
 
-- 4. 인덱스 효율성 판단 (통계정보 없는경우 카디널리티 확인)
SELECT num_rows
     , num_distinct
     , num_nulls
     , ((num_rows - num_nulls)/num_distinct) as "Cardinality"
  FROM ( SELECT count(*) as num_rows
              , count(distinct code) as num_distinct
              , count(decode(code,null,1)) as num_nulls
           FROM DAILY_PRICE )
;