[10g] CLUSTER 관리 - ver.dbrang

/********************************************************************************************
-- Title : [10g] CLUSTER 관리 - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word : 클러스터
********************************************************************************************/

/****************************
-- Cluster 관리 뷰
****************************/
-- User가 소유한 인덱스 이름, 유형, 상태 확인
SELECT table_owner || '.' || table_name "TABLE_NAME"
     , owner || '.' || index_name "INDEX_NAME", num_rows
     , index_type, uniqueness, tablespace_name, status, logging, funcidx_status
     , ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents
     , pct_increase, include_column, freelists, pct_free
FROM dba_indexes
WHERE owner = 'SCOTT'
AND table_name = 'TBL_TEST1';
   

-- Index_Cluster 보는 뷰
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
  AND c.owner = 'SCOTT';

-- Hash_Cluster 보는 뷰
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'
  AND c.owner = 'SCOTT';

 

/****************************
-- Index Cluster 생성 및 확인
****************************/
-- 1. index cluster 생성
CREATE cluster scott.dept_emp_clu
(deptno number(2))
SIZE 10k
tablespace users;

-- 2. cluster index 생성
CREATE INDEX scott.dept_emp_clu_idx
ON cluster scott.dept_emp_clu;

-- 3. cluster 안에 table 생성
CREATE TABLE scott.c_emp
cluster scott.dept_emp_clu(deptno)
AS
SELECT * FROM scott.emp;

-- 4. 추가 인덱스 생성
CREATE INDEX emp_deptno_idx ON scott.c_emp(deptno);

-- 5. index cluster 확인
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
  AND c.owner = 'SCOTT';

 

/****************************
-- Hash Cluster 생성 및 확인
****************************/
-- 1. hash cluster 생성
CREATE cluster scott.emp_hash_clu
(empno number(4))
SIZE 1k
hashkeys 100;

-- 2. table 생성
CREATE TABLE scott.h_emp
cluster scott.emp_hash_clu(empno)
AS
SELECT * FROM scott.emp;

-- 3. hash index 생성
ALTER TABLE scott.h_emp
ADD CONSTRAINT h_emp_empno_pk PRIMARY KEY(empno);

-- 4. hash cluster 확인
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'
  AND c.owner = 'SCOTT';

 

/****************************
-- Sorted Hash Cluster 생성 및 확인
****************************/
-- 1. sorted hash cluster 생성
CREATE cluster scott.calls_cluster
( origin_number number
, call_timestamp TIMESTAMP sort
, call_duration number sort)
hashkeys 10000
single TABLE
HASH IS origin_number
SIZE 50;

-- 2. 이전에 만든 cluster에 table 생성
CREATE TABLE scott.calls
( origin_number number
, call_timestamp TIMESTAMP
, call_duration number
, other_info varchar2(30))
cluster scott.calls_cluster
(origin_number, call_timestamp, call_duration
);

-- 3. cluster 확인
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'
  AND c.owner = 'SCOTT';