{"id":2002,"date":"2018-01-25T18:38:18","date_gmt":"2018-01-25T09:38:18","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2002"},"modified":"2018-01-25T18:39:19","modified_gmt":"2018-01-25T09:39:19","slug":"sql-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8-%ec%98%a4%eb%a0%8c%ec%a7%80-%ec%b2%98%eb%9f%bc-%ec%bb%ac%eb%9f%bc-%ed%86%b5%ea%b3%84-%ec%a0%95%eb%b3%b4-%ed%99%95%ec%9d%b8-%ed%95%98%eb%8a%94-%eb%b0%a9","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2002","title":{"rendered":"[SQL \uc2a4\ud06c\ub9bd\ud2b8] \uc624\ub80c\uc9c0 \ucc98\ub7fc \uceec\ub7fc \ud1b5\uacc4 \uc815\ubcf4 \ud655\uc778 \ud558\ub294 \ubc29\ubc95"},"content":{"rendered":"

\ud604\uc7ac \ud14c\uc774\ube14 \uad6c\uc870 \ubc0f \uad00\ub828 \uceec\ub7fc \ubcc4 \ud1b5\uacc4 \uc815\ubcf4 \ud604\ud669, <\/p>\n

\ubc00\ub3c4\uc640 \uc120\ud0dd\ub3c4 \ud655\uc778 \ud558\ub294 SQL \uc2a4\ud06c\ub9bd\ud2b8 \uc785\ub2c8\ub2e4. <\/p>\n

\uc774 \uacb0\uacfc\uc5d0 \ub530\ub77c \uc778\ub371\uc2a4\ub97c \ub9cc\ub4e4\uba74 \ub429\ub2c8\ub2e4.<\/p>\n

<SQL \uc2a4\ud06c\ub9bd\ud2b8><\/p>\n

[oracle11]yspark-linux:\/home\/oracle11> cat tab_stat.sql<\/b><\/p>\n

set document off<\/p>\n

\/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<\/p>\n

+ +<\/p>\n

+ FileName : tab_stat.sql +<\/p>\n

+ Genarated : 2018\/01\/16 by UNIONE INC. +<\/p>\n

+ Modified : +<\/p>\n

+ +<\/p>\n

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*\/<\/p>\n

set verify off<\/p>\n

set linesize 300<\/p>\n

ACCEPT owner_name PROMPT ‘Input owner_name : ‘<\/p>\n

ACCEPT T_NAME PROMPT ‘Input table_name : ‘<\/p>\n

COLUMN TAB_COL01 FORMAT A50<\/p>\n

COLUMN TAB_COL02 FORMAT A50<\/p>\n

COLUMN TABLE_NAME FORMAT A20<\/p>\n

COLUMN COLUMN_NAME FORMAT A20<\/p>\n

COLUMN NULLABLE FORMAT A15<\/p>\n

COLUMN COLUMN_TYPE FORMAT A15<\/p>\n

COLUMN LOW_VALUE FORMAT A25<\/p>\n

COLUMN HIGH_VALUE FORMAT A25<\/p>\n

COLUMN NUM_DISTINCT FORMAT 999,999,999,999<\/p>\n

COLUMN AVG_COL_LEN FORMAT 999,999,999,999<\/p>\n

COLUMN NUM_BUCKETS FORMAT 999,999,999,999<\/p>\n

COLUMN INDEX_INFO FORMAT A150<\/p>\n

prompt 01. table stats<\/p>\n

— table<\/p>\n

select <\/p>\n

table_name, PARTITION_NAME, num_rows, blocks, sample_size,<\/p>\n

to_char(last_analyzed,’yyyy\/mm\/dd hh24:mi:ss’) as last_anal<\/p>\n

from <\/p>\n

dba_tab_statistics <\/p>\n

where <\/p>\n

table_name = upper(‘&T_NAME’)<\/p>\n

and OWNER = upper(‘&owner_name’) <\/p>\n

\/<\/p>\n

prompt 02. column stats<\/p>\n

— column<\/p>\n

select <\/p>\n

s.table_name, s.column_name, s.num_distinct, s.num_nulls, s.density, <\/p>\n

s.low_value, s.high_value, s.histogram <\/p>\n

from <\/p>\n

dba_tab_cols s<\/p>\n

where<\/p>\n

s.table_name = upper(‘&T_NAME’)<\/p>\n

and s.OWNER= upper(‘&owner_name’)<\/p>\n

\/<\/p>\n

col COLUMN_NAME for a30<\/p>\n

col ENDPOINT_NUMBER for a30<\/p>\n

col endpoint_value format a70<\/p>\n

prompt 03. histogram stats<\/p>\n

— histogram<\/p>\n

select <\/p>\n

table_name, column_name, endpoint_number, <\/p>\n

endpoint_value||'(‘||endpoint_actual_value||’)’ as endpoint_value<\/p>\n

from<\/p>\n

dba_tab_histograms<\/p>\n

where <\/p>\n

table_name = upper(‘&T_NAME’)<\/p>\n

and OWNER = upper(‘$owner_name’)<\/p>\n

order by column_name, endpoint_number<\/p>\n

;<\/p>\n

set serveroutput off<\/p>\n

[oracle11]yspark-linux:\/home\/oracle11> cat table_info.sql<\/b><\/p>\n

set document off<\/p>\n

\/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<\/p>\n

+ +<\/p>\n

+ FileName : table_info.sql +<\/p>\n

+ Purpose : +<\/p>\n

+ Genarated : 2018\/01\/16 by UNIONE INC. +<\/p>\n

+ Modified : +<\/p>\n

+ +<\/p>\n

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*\/<\/p>\n

SET LINESIZE 400<\/p>\n

SET PAGESIZE 500<\/p>\n

SET RECSEP OFF<\/p>\n

SET VERIFY OFF<\/p>\n

CLEAR COLUMN<\/p>\n

COLUMN TAB_COL01 FORMAT A50<\/p>\n

COLUMN TAB_COL02 FORMAT A50<\/p>\n

COLUMN COLUMN_NAME FORMAT A40<\/p>\n

COLUMN NULLABLE FORMAT A15<\/p>\n

COLUMN COLUMN_TYPE FORMAT A15<\/p>\n

COLUMN NUM_DISTINCT FORMAT 999,999,999,999<\/p>\n

COLUMN AVG_COL_LEN FORMAT 999,999,999,999<\/p>\n

COLUMN NUM_BUCKETS FORMAT 999,999,999,999<\/p>\n

COLUMN INDEX_INFO FORMAT A150<\/p>\n

ACCEPT tb_name PROMPT ‘Input table_name : ‘<\/p>\n

ACCEPT owner_name PROMPT ‘Input owner_name : ‘<\/p>\n

SELECT DECODE(B.ID,1,OWNER||’.’||TABLE_NAME,<\/p>\n

2,’——————–‘,<\/p>\n

3,’Rows=’||TRIM(TO_CHAR(NUM_ROWS,’999,999,999,999,999′)),<\/p>\n

4,’Empty Blocks=’||TRIM(TO_CHAR(EMPTY_BLOCKS,’999,999,999,999,999′)),<\/p>\n

5,’Chain Count=’||TRIM(TO_CHAR(CHAIN_CNT,’999,999,999,999,999′)),<\/p>\n

6,’Avg Space Freelist Blocks=’||TRIM(TO_CHAR(AVG_SPACE_FREELIST_BLOCKS,’999,999,999,999,999′)),<\/p>\n

7,’Sample Size=’||TRIM(TO_CHAR(SAMPLE_SIZE,’999,999,999,999,999′)),<\/p>\n

8,’Partitione=’||PARTITIONED) TAB_COL01,<\/p>\n

DECODE(B.ID,1,’ ‘,<\/p>\n

2,’ ‘,<\/p>\n

3,’Blocks=’||TRIM(TO_CHAR(BLOCKS,’999,999,999,999,999′)),<\/p>\n

4,’Avg Space=’||TRIM(TO_CHAR(AVG_SPACE,’999,999,999,999,999′)),<\/p>\n

5,’Avg Row Length=’||TRIM(TO_CHAR(AVG_ROW_LEN,’999,999,999,999,999′)),<\/p>\n

6,’Freelist Blocks=’||TRIM(TO_CHAR(FREELISTS,’999,999,999,999,999′)),<\/p>\n

7,’Last Analyze=’||TO_CHAR(LAST_ANALYZED,’YYYY\/MM\/DD HH24:MI:SS’)) TAB_COL02<\/p>\n

FROM DBA_TABLES A ,( SELECT LEVEL AS id FROM dual CONNECT BY LEVEL <= 99 ) B<\/p>\n

WHERE TABLE_NAME = UPPER(‘&&tb_name’)<\/p>\n

AND OWNER = UPPER(‘&&owner_name’)<\/p>\n

AND B.ID <= 8<\/p>\n

\/ <\/p>\n

SELECT COLUMN_NAME,<\/p>\n

DECODE(NULLABLE,’N’,’NOT NULL’) NULLABLE,<\/p>\n

DATA_TYPE||DECODE(DATA_PRECISION,NULL,NULL,'(‘||DATA_PRECISION||’)’) COLUMN_TYPE,<\/p>\n

NUM_DISTINCT,<\/p>\n

AVG_COL_LEN,<\/p>\n

NUM_BUCKETS<\/p>\n

FROM DBA_TAB_COLUMNS<\/p>\n

WHERE TABLE_NAME = UPPER(‘&&tb_name’)<\/p>\n

AND OWNER = UPPER(‘&&owner_name’)<\/p>\n

ORDER BY COLUMN_ID<\/p>\n

\/<\/p>\n

SELECT \/*+ ORDERED USE_NL(A,B) *\/<\/p>\n

DECODE(ID,1,INDEX_NAME||’ : ‘||COLUMN_NM,<\/p>\n

2,’ ‘||’Type=’||INDEX_TYPE||’, ‘||<\/p>\n

‘Uniq=’||DECODE(UNIQUENESS,’UNIQUE’,’Yes’,’No’)||’, ‘||<\/p>\n

‘Distinct=’||TRIM(TO_CHAR(DISTINCT_KEYS,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Rows=’||TRIM(TO_CHAR(NUM_ROWS,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Last Analyzed=’||TO_CHAR(LAST_ANALYZED,’YYYY\/MM\/DD HH24:MI:SS’),<\/p>\n

3,’ ‘||’B*-Tree level=’||TRIM(TO_CHAR(BLEVEL,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Leaf Blocks=’||TRIM(TO_CHAR(LEAF_BLOCKS,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Clustering Factor=’||TRIM(TO_CHAR(CLUSTERING_FACTOR,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Sample Size=’||TRIM(TO_CHAR(SAMPLE_SIZE,’999,999,999,999,999′))||’, ‘||<\/p>\n

‘Partition Type=’||DECODE(PARTITIONED,’NO’,’N\/A’,PARTITIONED)) INDEX_INFO<\/p>\n

FROM<\/p>\n

(<\/p>\n

SELECT A.INDEX_NAME,<\/p>\n

MIN(INDEX_TYPE) INDEX_TYPE,<\/p>\n

MIN(UNIQUENESS) UNIQUENESS,<\/p>\n

MIN(TABLESPACE_NAME) TABLESPACE_NAME,<\/p>\n

MIN(DISTINCT_KEYS) DISTINCT_KEYS,<\/p>\n

MIN(CLUSTERING_FACTOR) CLUSTERING_FACTOR,<\/p>\n

MIN(STATUS) STATUS,<\/p>\n

MIN(LAST_ANALYZED) LAST_ANALYZED,<\/p>\n

MIN(BLEVEL) BLEVEL,<\/p>\n

MIN(NUM_ROWS) NUM_ROWS,<\/p>\n

MIN(LEAF_BLOCKS) LEAF_BLOCKS,<\/p>\n

MIN(PARTITIONED) PARTITIONED,<\/p>\n

MIN(SAMPLE_SIZE) SAMPLE_SIZE,<\/p>\n

SUBSTR(XMLAGG(XMLELEMENT(COLUMN_NAME,’ + ‘||COLUMN_NAME) ORDER BY COLUMN_POSITION).EXTRACT(‘\/\/text()’).GetStringVal(),4) COLUMN_NM<\/p>\n

FROM DBA_INDEXES A, DBA_IND_COLUMNS B<\/p>\n

WHERE A.TABLE_NAME = UPPER(‘&&tb_name’)<\/p>\n

AND A.OWNER = UPPER(‘&&owner_name’)<\/p>\n

AND A.OWNER = B.TABLE_OWNER<\/p>\n

AND A.INDEX_NAME = B.INDEX_NAME<\/p>\n

GROUP BY A.INDEX_NAME<\/p>\n

) A, ( SELECT LEVEL AS id FROM dual CONNECT BY LEVEL <= 99 ) B<\/p>\n

WHERE B.ID <= 3<\/p>\n

\/<\/p>\n

SET VERIFY ON<\/p>\n

<\uc218\ud589 \uacb0\uacfc> <\/p>\n

\"clip_image001\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

\ud604\uc7ac \ud14c\uc774\ube14 \uad6c\uc870 \ubc0f \uad00\ub828 \uceec\ub7fc \ubcc4 \ud1b5\uacc4 \uc815\ubcf4 \ud604\ud669, \ubc00\ub3c4\uc640 \uc120\ud0dd\ub3c4 \ud655\uc778 \ud558\ub294 SQL \uc2a4\ud06c\ub9bd\ud2b8 \uc785\ub2c8\ub2e4. \uc774 \uacb0\uacfc\uc5d0 \ub530\ub77c \uc778\ub371\uc2a4\ub97c \ub9cc\ub4e4\uba74 \ub429\ub2c8\ub2e4. <SQL \uc2a4\ud06c\ub9bd\ud2b8> [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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2002"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2002"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2002\/revisions"}],"predecessor-version":[{"id":2006,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2002\/revisions\/2006"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2002"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2002"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2002"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}