[8i] 클러스터 테이블과 IOT(OLN)

/********************************************************************************************
-- Title : [8i] 클러스터 테이블과 IOT(OLN)
-- Reference : OLN
-- Key word : cluster table index oriented table
********************************************************************************************/

/****************************************************************************************
-- 클러스터 정보
****************************************************************************************/

-- 클러스터와 클러스터 키 열
SQL> SELECT c.cluster_name, c.cluster_type, c.key_size,
  2         cc.column_name, cc.data_type,
  3         decode(cc.data_type, 'NUMBER',
  4         decode(cc.data_precision, NULL, NULL,
  5         cc.data_precision || ',' || cc.data_scale),
  6         'DATE', NULL, cc.data_length) AS "COLSIZE"
  7  FROM dba_clusters c, dba_tab_columns cc
  8  WHERE c.owner = cc.owner
  9      AND c.cluster_name = cc.table_name
 10      AND c.owner = 'SCOTT';

CLUSTER_NAME  CLUSTER_TYPE  KEY_SIZE  COLUMN_NAME  DATA_TYPE  COLSIZE
------------- ------------- --------- ------------ ---------- --------
EMP_CLU       INDEX         200       EMPNO        NUMBER     4,0
OFF_CLU       HASH          500       COUNTRY      VARCHAR2   2
OFF_CLU       HASH          500       POSTCODE     VARCHAR2   8

3 rows selected

-- 클러스터 키 열과 테이블의 열 일치
SQL> SELECT *
  2  FROM dba_clu_columns
  3  WHERE owner = 'SCOTT'
  4  ORDER BY cluster_name, table_name;

OWNER        CLUSTER_NAME       CLU_COLUMN_NAME    TABLE_NAME     TAB_COLUMN_NAME
------------ ------------------ ------------------ -------------- ----------------
SCOTT        OFF_CLU            COUNTRY            OFFICE         COUNTRY
SCOTT        OFF_CLU            POSTCODE           OFFICE         POSTCODE

-- 해쉬 클러스터에 대한 추가 정보 얻기
SQL>SELECT c.cluster_name, c.hashkeys, c.function, h.hash_expression
  2 FROM dba_clusters c, dba_cluster_hash_expressions h
  3 WHERE c.owner = h.owner(+)
  4     AND c.cluster_name = h.cluster_name(+)
  5     AND c.owner = 'SCOTT'
  6     AND c.cluster_type = 'HASH'
   
CLUSTER_NAME HASHKEYS FUNCTION HASH_EXPRESSION
--------------- --------------- --------------- -------------------
OFF_CLU         1009            DEFAULT2 

/****************************************************************************************
-- 데이터 딕셔너리의 IOT(Index_Organized Table) 정보
-- 관리용(iot_table.sql)
****************************************************************************************/

-- iot_table.sql
SQL> SELECT t.table_name AS "IOT", o.table_name AS "Overflow",
  2         i.index_name AS "Index",
  3         o.tablespace_name AS "Overflow TS",
  4         i.tablespace_name AS "Index TS", I.pct_threshold
  5  FROM dba_tables t, dba_tables o, dba_indexes I
  6  WHERE t.owner = o.owner
  7      AND t.table_name = o.iot_name
  8      AND t.owner = I.owner
  9      AND t.table_name = I.table_name
 10  --  AND t.owner = 'SCOTT';
 
IOT     Overflow           Index    Overflow TS     Index TS        PCT_THRESHOLD
------- ------------------ -------- --------------- --------------- --------------
SALES   SYS_IOT_OVER_3276  SALES_PK USER_DATA       INDX            20

/****************************************************************************************
-- 관리용
****************************************************************************************/
-- iot_table.sql
SELECT t.table_name AS "IOT", o.table_name AS "Overflow",
       i.index_name AS "Index",
       o.tablespace_name AS "Overflow TS",
       i.tablespace_name AS "Index TS", I.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes I
WHERE t.owner = o.owner
  AND t.table_name = o.iot_name
  AND t.owner = I.owner
  AND t.table_name = I.table_name
/

-- index_cluster.sql
SELECT c.cluster_name, c.cluster_type, c.owner, c.key_size,
       cc.column_name, cc.data_type,
       decode(cc.data_type, 'NUMBER',
       decode(cc.data_precision, NULL, NULL,
       cc.data_precision || ',' || cc.data_scale),
       'DATE', NULL, cc.data_length) AS "COLSIZE"
FROM dba_clusters c, dba_tab_columns cc
WHERE c.owner = cc.owner
  AND c.cluster_name = cc.table_name
/

-- hash_cluster.sql
SELECT c.cluster_name, c.owner, c.hashkeys, c.function, h.hash_expression
FROM dba_clusters c, dba_cluster_hash_expressions h
WHERE c.owner = h.owner(+)
  AND c.cluster_name = h.cluster_name(+)
  AND c.cluster_type = 'HASH'
/