[자작] 특정 Table의 Index 조회 SQL

자작임을 감안하자.

accept table_name prompt '[http://sarc.io] Enter Table Name : '

set serveroutput on
set pagesize 100
set linesize 130

col table_owner for a15
col table_name for a30
col index_name for a30
col column_name for a25
col column_position for 99
col descend for a8

select table_owner
     , table_name
     , index_name
     , column_name
     , column_position
     , descend
  from dba_ind_columns
 where table_name = upper('&table_name')
 order by table_owner, table_name, index_name, column_position;

col owner for a15
col segment_name for a30
col bytes for 999,999,999,999.99

select owner
     , segment_name
     , sum(bytes/1024/1024) bytes
  from dba_segments
 where segment_name = upper('&table_name')
 group by owner, segment_name;

select owner
     , segment_name
     , sum(bytes/1024/1024) bytes
  from dba_segments
 where segment_name in (select index_name
                          from dba_indexes
                         where table_name = upper('&table_name')
                       )
 group by owner, segment_name;

결과 한 번 보고 가신다.

SQL> @index
[http://sarc.io] Enter Table Name : TABLE_ARTICLE
old   8:  where table_name = upper('&table_name')
new   8:  where table_name = upper('TABLE_ARTICLE')

TABLE_OWNER     TABLE_NAME                     INDEX_NAME                     COLUMN_NAME               COLUMN_POSITION DESCEND
--------------- ------------------------------ ------------------------------ ------------------------- --------------- --------
SARC_IO         TABLE_ARTICLE                  INDEX_ARTICLE                  ID                                      1 ASC
SARC_IO         TABLE_ARTICLE                  INDEX_ARTICLE                  TIME                                    2 ASC

old   5:  where segment_name = upper('&table_name')
new   5:  where segment_name = upper('TABLE_ARTICLE')

OWNER           SEGMENT_NAME                                 BYTES
--------------- ------------------------------ -------------------
SARC_IO         TABLE_ARTICLE                             1,937.19

old   7:                          where table_name = upper('&table_name')
new   7:                          where table_name = upper('TABLE_ARTICLE')

OWNER           SEGMENT_NAME                                 BYTES
--------------- ------------------------------ -------------------
SARC_IO         INDEX_ARTICLE                               755.75