[10g] IOT 관리 - ver.dbrang

/********************************************************************************************
-- Title : [10g] IOT 관리 - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word :
********************************************************************************************/

/****************************
-- IOT 관리 뷰
****************************/
-- 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';

-- IOT의 경우 위 쿼리와 상호 비교
SELECT owner, table_name, tablespace_name,  iot_name, iot_type
FROM dba_tables
WHERE owner = 'SCOTT'
AND (table_name = 'TBL_TEST1'
    OR table_name LIKE 'SYS_IOT%');
   

-- IOT만 보는 뷰
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
  AND t.owner = 'SCOTT';
 

 

/****************************
-- 정규 B-Tree 인덱스 생성
****************************/
-- IOT 생성
-- drop table scott.tbl_test1;
CREATE TABLE scott.tbl_test1
( id NUMERIC NOT NULL
, membr_id VARCHAR2(10) NOT NULL
, membr_nm VARCHAR2(10) NOT NULL
, membr_addr CHAR(5) NOT NULL
, membr_sex CHAR(1) NOT NULL
, enter_dt CHAR(8) NOT NULL
, membr_pnt NUMERIC(3) NOT NULL
, membr_use NUMERIC(5) NOT NULL
, note VARCHAR2(50) NULL
, CONSTRAINT pk_tbl_test1 PRIMARY KEY (id)
)
ORGANIZATION INDEX
TABLESPACE ts_data1
PCTTHRESHOLD 20
OVERFLOW TABLESPACE ts_data2;

-- IOT 확인
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';

SELECT owner, table_name, tablespace_name,  iot_name, iot_type
FROM dba_tables
WHERE owner = 'SCOTT'
AND (table_name = 'TBL_TEST1'
    OR table_name LIKE 'SYS_IOT%');

 

/****************************
-- IOT 검토
****************************/
-- 데이터 입력
INSERT INTO scott.tbl_test1
VALUES (1, 'ididid', 'namename', 'seoul', 'M', '20100909', 10, 20, NULL);
COMMIT;

-- ROWID 검색
SELECT rowid, id, membr_id, membr_nm, membr_addr FROM scott.tbl_test1;

-- 인덱스 생성
CREATE INDEX scott.ix_tbl_test1
ON scott.tbl_test1(membr_id)
TABLESPACE ts_data4;

CREATE INDEX scott.ix_tbl_test1_2
ON scott.tbl_test1(membr_nm)
TABLESPACE ts_data5;

-- 인덱스 확인
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';

SELECT owner, table_name, tablespace_name,  iot_name, iot_type
FROM dba_tables
WHERE owner = 'SCOTT'
AND (table_name = 'TBL_TEST1'
    OR table_name LIKE 'SYS_IOT%');

-- 세컨더리 INDEX의 rowid 온라인 갱신
-- ROWID가 변질되었을 때 갱신
ALTER INDEX scott.ix_tbl_test1
UPDATE BLOCK REFERENCES;