현재 테이블 구조 및 관련 컬럼 별 통계 정보 현황,
밀도와 선택도 확인 하는 SQL 스크립트 입니다.
이 결과에 따라 인덱스를 만들면 됩니다.
<SQL 스크립트>
[oracle11]yspark-linux:/home/oracle11> cat tab_stat.sql
set document off
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ +
+ FileName : tab_stat.sql +
+ Genarated : 2018/01/16 by UNIONE INC. +
+ Modified : +
+ +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
set verify off
set linesize 300
ACCEPT owner_name PROMPT ‘Input owner_name : ‘
ACCEPT T_NAME PROMPT ‘Input table_name : ‘
COLUMN TAB_COL01 FORMAT A50
COLUMN TAB_COL02 FORMAT A50
COLUMN TABLE_NAME FORMAT A20
COLUMN COLUMN_NAME FORMAT A20
COLUMN NULLABLE FORMAT A15
COLUMN COLUMN_TYPE FORMAT A15
COLUMN LOW_VALUE FORMAT A25
COLUMN HIGH_VALUE FORMAT A25
COLUMN NUM_DISTINCT FORMAT 999,999,999,999
COLUMN AVG_COL_LEN FORMAT 999,999,999,999
COLUMN NUM_BUCKETS FORMAT 999,999,999,999
COLUMN INDEX_INFO FORMAT A150
prompt 01. table stats
— table
select
table_name, PARTITION_NAME, num_rows, blocks, sample_size,
to_char(last_analyzed,’yyyy/mm/dd hh24:mi:ss’) as last_anal
from
dba_tab_statistics
where
table_name = upper(‘&T_NAME’)
and OWNER = upper(‘&owner_name’)
/
prompt 02. column stats
— column
select
s.table_name, s.column_name, s.num_distinct, s.num_nulls, s.density,
s.low_value, s.high_value, s.histogram
from
dba_tab_cols s
where
s.table_name = upper(‘&T_NAME’)
and s.OWNER= upper(‘&owner_name’)
/
col COLUMN_NAME for a30
col ENDPOINT_NUMBER for a30
col endpoint_value format a70
prompt 03. histogram stats
— histogram
select
table_name, column_name, endpoint_number,
endpoint_value||'(‘||endpoint_actual_value||’)’ as endpoint_value
from
dba_tab_histograms
where
table_name = upper(‘&T_NAME’)
and OWNER = upper(‘$owner_name’)
order by column_name, endpoint_number
;
set serveroutput off
[oracle11]yspark-linux:/home/oracle11> cat table_info.sql
set document off
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ +
+ FileName : table_info.sql +
+ Purpose : +
+ Genarated : 2018/01/16 by UNIONE INC. +
+ Modified : +
+ +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
SET LINESIZE 400
SET PAGESIZE 500
SET RECSEP OFF
SET VERIFY OFF
CLEAR COLUMN
COLUMN TAB_COL01 FORMAT A50
COLUMN TAB_COL02 FORMAT A50
COLUMN COLUMN_NAME FORMAT A40
COLUMN NULLABLE FORMAT A15
COLUMN COLUMN_TYPE FORMAT A15
COLUMN NUM_DISTINCT FORMAT 999,999,999,999
COLUMN AVG_COL_LEN FORMAT 999,999,999,999
COLUMN NUM_BUCKETS FORMAT 999,999,999,999
COLUMN INDEX_INFO FORMAT A150
ACCEPT tb_name PROMPT ‘Input table_name : ‘
ACCEPT owner_name PROMPT ‘Input owner_name : ‘
SELECT DECODE(B.ID,1,OWNER||’.’||TABLE_NAME,
2,’——————–‘,
3,’Rows=’||TRIM(TO_CHAR(NUM_ROWS,’999,999,999,999,999′)),
4,’Empty Blocks=’||TRIM(TO_CHAR(EMPTY_BLOCKS,’999,999,999,999,999′)),
5,’Chain Count=’||TRIM(TO_CHAR(CHAIN_CNT,’999,999,999,999,999′)),
6,’Avg Space Freelist Blocks=’||TRIM(TO_CHAR(AVG_SPACE_FREELIST_BLOCKS,’999,999,999,999,999′)),
7,’Sample Size=’||TRIM(TO_CHAR(SAMPLE_SIZE,’999,999,999,999,999′)),
8,’Partitione=’||PARTITIONED) TAB_COL01,
DECODE(B.ID,1,’ ‘,
2,’ ‘,
3,’Blocks=’||TRIM(TO_CHAR(BLOCKS,’999,999,999,999,999′)),
4,’Avg Space=’||TRIM(TO_CHAR(AVG_SPACE,’999,999,999,999,999′)),
5,’Avg Row Length=’||TRIM(TO_CHAR(AVG_ROW_LEN,’999,999,999,999,999′)),
6,’Freelist Blocks=’||TRIM(TO_CHAR(FREELISTS,’999,999,999,999,999′)),
7,’Last Analyze=’||TO_CHAR(LAST_ANALYZED,’YYYY/MM/DD HH24:MI:SS’)) TAB_COL02
FROM DBA_TABLES A ,( SELECT LEVEL AS id FROM dual CONNECT BY LEVEL <= 99 ) B
WHERE TABLE_NAME = UPPER(‘&&tb_name’)
AND OWNER = UPPER(‘&&owner_name’)
AND B.ID <= 8
/
SELECT COLUMN_NAME,
DECODE(NULLABLE,’N’,’NOT NULL’) NULLABLE,
DATA_TYPE||DECODE(DATA_PRECISION,NULL,NULL,'(‘||DATA_PRECISION||’)’) COLUMN_TYPE,
NUM_DISTINCT,
AVG_COL_LEN,
NUM_BUCKETS
FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME = UPPER(‘&&tb_name’)
AND OWNER = UPPER(‘&&owner_name’)
ORDER BY COLUMN_ID
/
SELECT /*+ ORDERED USE_NL(A,B) */
DECODE(ID,1,INDEX_NAME||’ : ‘||COLUMN_NM,
2,’ ‘||’Type=’||INDEX_TYPE||’, ‘||
‘Uniq=’||DECODE(UNIQUENESS,’UNIQUE’,’Yes’,’No’)||’, ‘||
‘Distinct=’||TRIM(TO_CHAR(DISTINCT_KEYS,’999,999,999,999,999′))||’, ‘||
‘Rows=’||TRIM(TO_CHAR(NUM_ROWS,’999,999,999,999,999′))||’, ‘||
‘Last Analyzed=’||TO_CHAR(LAST_ANALYZED,’YYYY/MM/DD HH24:MI:SS’),
3,’ ‘||’B*-Tree level=’||TRIM(TO_CHAR(BLEVEL,’999,999,999,999,999′))||’, ‘||
‘Leaf Blocks=’||TRIM(TO_CHAR(LEAF_BLOCKS,’999,999,999,999,999′))||’, ‘||
‘Clustering Factor=’||TRIM(TO_CHAR(CLUSTERING_FACTOR,’999,999,999,999,999′))||’, ‘||
‘Sample Size=’||TRIM(TO_CHAR(SAMPLE_SIZE,’999,999,999,999,999′))||’, ‘||
‘Partition Type=’||DECODE(PARTITIONED,’NO’,’N/A’,PARTITIONED)) INDEX_INFO
FROM
(
SELECT A.INDEX_NAME,
MIN(INDEX_TYPE) INDEX_TYPE,
MIN(UNIQUENESS) UNIQUENESS,
MIN(TABLESPACE_NAME) TABLESPACE_NAME,
MIN(DISTINCT_KEYS) DISTINCT_KEYS,
MIN(CLUSTERING_FACTOR) CLUSTERING_FACTOR,
MIN(STATUS) STATUS,
MIN(LAST_ANALYZED) LAST_ANALYZED,
MIN(BLEVEL) BLEVEL,
MIN(NUM_ROWS) NUM_ROWS,
MIN(LEAF_BLOCKS) LEAF_BLOCKS,
MIN(PARTITIONED) PARTITIONED,
MIN(SAMPLE_SIZE) SAMPLE_SIZE,
SUBSTR(XMLAGG(XMLELEMENT(COLUMN_NAME,’ + ‘||COLUMN_NAME) ORDER BY COLUMN_POSITION).EXTRACT(‘//text()’).GetStringVal(),4) COLUMN_NM
FROM DBA_INDEXES A, DBA_IND_COLUMNS B
WHERE A.TABLE_NAME = UPPER(‘&&tb_name’)
AND A.OWNER = UPPER(‘&&owner_name’)
AND A.OWNER = B.TABLE_OWNER
AND A.INDEX_NAME = B.INDEX_NAME
GROUP BY A.INDEX_NAME
) A, ( SELECT LEVEL AS id FROM dual CONNECT BY LEVEL <= 99 ) B
WHERE B.ID <= 3
/
SET VERIFY ON
<수행 결과>
