인덱스 분포도

- 해당 컬럼 분포도 확인

SELECT (1/CNT)*100
FROM (
    SELECT /*+ parallel (a 4) */  COUNT(DISTINCT UNI_KEY) CNT 
    FROM VANBT.TLFRICAD a 
    ) B;

 

 

# 인덱스 조회
select decode(DIC.COLUMN_POSITION,1,DIC.INDEX_OWNER,null)   as INDEX_OWNER,
       decode(DIC.COLUMN_POSITION, 1, DIC.table_name, null) as table_name ,
--       DIC.INDEX_NAME || ' (' || DIC.COLUMN_NAME || ')' ,
        decode(DIC.COLUMN_POSITION, 1, DI.INDEX_TYPE, null) as INDEX_TYPE , 
        decode(DIC.COLUMN_POSITION, 1, DIC.INDEX_NAME, null) as INDEX_NAME ,
       DIC.COLUMN_NAME,
       DIC.COLUMN_POSITION,
       CC.COMMENTS,
       DI.VISIBILITY
  from DBA_IND_COLUMNS DIC, ALL_COL_COMMENTS CC , DBA_INDEXES DI
 where DIC.INDEX_OWNER = CC.OWNER 
 AND DIC.TABLE_NAME = CC.TABLE_NAME
 AND DIC.COLUMN_NAME = CC.COLUMN_NAME
 AND DIC.TABLE_NAME = DI.TABLE_NAME
 AND DIC.INDEX_OWNER = DI.TABLE_OWNER
 AND DIC.INDEX_NAME = DI.INDEX_NAME 
 AND DIC.table_name ='REQUEST_MASTER'
 order by DIC.INDEX_NAME ,
       DIC.COLUMN_POSITION 
       ;
     

 

-- Index Clustering Factor 
-- # 인덱스 군집되어 있는지 확인 
-- ## CF 값이 Blocks 가까울수록 좋으며, ROW 수에 가까울스록 나쁨
-- 해결 방안 : 인덱스 재생성
SELECT T.OWNER,
       I.INDEX_NAME,
       T.BLOCKS AS T_BLOCKS,
       I.NUM_ROWS,
       I.CLUSTERING_FACTOR
  FROM DBA_INDEXES I,
       DBA_TABLES T
 WHERE I.TABLE_NAME = :TABLE_NAME
   AND I.TABLE_OWNER = T.OWNER
   AND I.TABLE_NAME = T.TABLE_NAME;
 

 

 

  기타 SQL

 

1. 

SELECT 
    INDEX_OWNER, 
    TABLE_NAME, 
    INDEX_NAME, 
    INDEX_MB, 
    INDEX_TYPE,
    TBS_NAME, 
    ANALYZED, 
    VISIBILITY, 
    REGEXP_REPLACE(LISTAGG(COLUMN_NAME, ' ,') WITHIN GROUP (ORDER BY COLUMN_POSITION), '([^,]+)(,\1)+', '\1') COLUMN_NAME
FROM (
SELECT DIC.INDEX_OWNER AS INDEX_OWNER,
        DIC.TABLE_NAME AS TABLE_NAME ,
       DIC.INDEX_NAME,
      ROUND((DS.BYTES/1024/1024),2) AS INDEX_MB,
      DI.INDEX_TYPE AS INDEX_TYPE,
      DS.TABLESPACE_NAME AS TBS_NAME,
      DI.LAST_ANALYZED AS  ANALYZED,
      DI.VISIBILITY AS VISIBILITY,
      DIC.COLUMN_NAME || '  ['|| CC.COMMENTS || ', ' || DIC.COLUMN_POSITION || ']' AS COLUMN_NAME,
      DIC.COLUMN_POSITION
  FROM DBA_IND_COLUMNS DIC 
       LEFT OUTER JOIN ALL_COL_COMMENTS CC ON DIC.INDEX_OWNER = CC.OWNER  AND DIC.TABLE_NAME = CC.TABLE_NAME AND  DIC.COLUMN_NAME =  CC.COLUMN_NAME,
    DBA_SEGMENTS DS, DBA_INDEXES DI
 WHERE  DIC.INDEX_NAME = DS.SEGMENT_NAME
 AND DIC.INDEX_OWNER = DS.OWNER
 AND DIC.INDEX_OWNER = DI.OWNER 
 AND DIC.INDEX_NAME = DI.INDEX_NAME
 AND DIC.TABLE_NAME ='STKKY'
 ORDER BY DIC.INDEX_NAME ,
       DIC.COLUMN_POSITION )
GROUP BY  INDEX_OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE, INDEX_MB, TBS_NAME, ANALYZED, VISIBILITY      ;
   

 

2. TABLE SIZE && INDEX_SIZE

WITH TMP_WRK AS (
  SELECT
                 (select /*+  ALL_ROWS */ sum(bytes)
                   from dba_segments
                  where owner NOT IN ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS',
                    'BI','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES',
                    'HR','IX','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS',
                    'ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS',
                    'OWBSYS_AUDIT','PM','SCOTT','SH','SI_INFORMTN_SCHEMA',
                    'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN',
                    'SYSTEM','WMSYS','XDB','XS','DVF','DVO','DVSYS','LBACSYS','TSMSYS')) TOT_SIZE,
            NVL((select /*+  ALL_ROWS */ sum(bytes)
                  from  dba_segments x1
                 where  x1.owner = :owner and x1.segment_name = :tname),0) TABLE_SIZE,
            NVL((select /*+  ALL_ROWS ORDERED*/ sum(bytes)
                  from  dba_indexes x1, dba_segments x2
                  where x1.owner = :owner and x1.index_name = x2.segment_name and
                        x1.owner = :owner and x1.table_name = :tname),0) INDEX_SIZE,
            NVL((select /*+ ALL_ROWS LEADING(V1) NO_MERGE(V1) */ sum(x2.bytes)
                   from (select /*+ ALL_ROWS */ owner,segment_name segment_name
                         from dba_lobs x1
                         where x1.owner = :owner and x1.table_name = :tname
                         union all
                         select owner,index_name segment_name
                         from /*+ ALL_ROWS */ dba_lobs x1
                         where x1.owner = :owner and x1.table_name = :tname) V1,
                         dba_segments x2
                  where x2.owner = V1.owner and x2.segment_name = V1.segment_name),0) LOB_SIZE
   FROm DUAL
)
SELECT /*+ ALL_ROWS */ DECODE(VAL, 'VAL1', 'Etc Seg Size', 'VAL2', 'TABLE Tot Size', 'VAL3', 'TABLE Size', 'VAL4', 'INDEX Size', 'VAL5', 'LOB Size') AS "SEGMENT Usage", SEG_SIZE_MB
FROM  (
  select round((sum(TOT_SIZE) - (sum(TABLE_SIZE) + sum(INDEX_SIZE) + sum(LOB_SIZE)))/(1024*1024),0) VAL1 ,
         round((sum(TABLE_SIZE) + sum(INDEX_SIZE) + sum(LOB_SIZE))/(1024*1024),0) VAL2 ,
         round(sum(TABLE_SIZE)/(1024*1024),0) VAL3 ,
         round(sum(INDEX_SIZE)/(1024*1024),0) VAL4 ,
         round(sum(LOB_SIZE)/(1024*1024),0)   VAL5
  from TMP_WRK
   ) V1
   UNPIVOT INCLUDE NULLS (SEG_SIZE_MB FOR VAL IN (VAL1, VAL2, VAL3, VAL4, VAL5))
   
   

 

3. 
 select /* use_hash(a pk) */
    a.column_name,
        pk.pk ispk,
        data_type,
        data_length dl,
        nullable n,
        num_nulls nn,
        num_distinct,
         CASE 
         WHEN num_distinct = 0 THEN 0
         ELSE ROUND((1/num_distinct)*100,10)
         END AS "density",
        ROUND(density,10) AS HISTOGRAM_DENSITY,   -- 분포도, 히스토그램이 있을 경우 DBA_TAB_COL_STATISTICS.DENSITY COLUMN 을 통해 확인 가능
        avg_col_len acl,
        num_buckets as nbk,
        sample_size,
        substr(histogram, 1, 5) histogram,
            (select comments 
            from ALL_COL_COMMENTS
            where table_name = a.table_name
            and column_name = a.column_name
            and owner = a.owner
            ) "COMMENT"
from ALL_TAB_COLUMNS a, 
    (
        select b.column_name,
                'PK' pk
        from 
        ( select decode(a.cnt, 0, /* 인덱스명 */
                ( select index_name
                from ALL_INDEXES
                where table_name = upper(:1)
                and owner = upper(:2)
                and uniqueness = 'UNIQUE'
                and rownum <= 1 
                ), (
                select constraint_name
                from ALL_CONSTRAINTS
                where owner =UPPER(:2)
                and table_name =upper(:1)
                and constraint_type ='P'
                ) ) INDEX_NAME
            from (
                select count(constraint_name) cnt
                from ALL_CONSTRAINTS a 
                where table_name = upper(:1)
                and owner = upper(:2)
                and constraint_type ='i'
                ) a 
            ) a,
            ALL_IND_COLUMNS b 
            where b.index_owner = upper(:2)
            and b.index_name = a.index_name 
            ) pk 
    where a.owner = upper(:2)
    and a.table_name = upper(:1)
    and a.column_name = pk.column_name(+)
    order by pk.pk, a.num_distinct DESC ;