[10g] 인덱스 관리 - ver.dbrang

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

/****************************
-- 인덱스 관리 뷰
****************************/
-- 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';

-- 인덱스가 정의된 열 찾기
SELECT index_name, table_owner, table_name, column_name, column_position
FROM dba_ind_columns
WHERE index_owner = 'SCOTT'
ORDER BY index_name, column_position;

/****************************
-- 인덱스 유효성 검사
-- SQL의 dbcc show_statistics와 유사
****************************/
ANALYZE INDEX scott.pk_emp     /* 인덱스 정보를 index_stats에 기록 */
VALIDATE STRUCTURE;

SELECT blocks, pct_used, lf_rows, del_lf_rows
FROM index_stats;              /* 기록된 인덱스 정보 보기 */

/****************************
-- 정규 B-Tree 인덱스 생성
****************************/
-- 테이블 생성
-- 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)
) TABLESPACE users;

-- B* 인덱스 생성
-- drop index scott.ix_tbl_test1_btree;
CREATE UNIQUE INDEX scott.ix_tbl_test1_btree
ON scott.tbl_test1(membr_id, id)
TABLESPACE ts_data1;

-- Reverse Key 인덱스 생성
-- drop index scott.ix_tbl_test1_reversekey;
CREATE INDEX scott.ix_tbl_test1_reversekey
ON scott.tbl_test1(enter_dt) REVERSE
TABLESPACE ts_data2;

-- Bitmap 인덱스 생성
-- drop index scott.ix_tbl_test1_bitmap;
CREATE BITMAP INDEX scott.ix_tbl_test1_bitmap
ON scott.tbl_test1(membr_addr, membr_sex)
TABLESPACE ts_data3;

-- Function-Based 인덱스 생성
-- drop index scott.ix_tbl_test1_function;
CREATE INDEX scott.ix_tbl_test1_function
ON scott.tbl_test1(membr_pnt + membr_use)
TABLESPACE ts_data4;

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

/****************************
-- 인덱스 스토리지 변경
-- Locally-Managed Tablespace 방식에서는 안됨.
****************************/
ALTER INDEX SCOTT.IX_TBL_TEST1_BTREE
STORAGE(NEXT 400K
        MAXEXTENTS 100
       );

ERROR: #25150, ORA-25150: ALTERING OF extent PARAMETERS NOT permitted.

/****************************
-- 인덱스 공간 할당과 해제
****************************/
-- 공간 할당
ALTER INDEX scott.IX_TBL_TEST1_BTREE
ALLOCATE EXTENT (SIZE 300K
                 DATAFILE '/home/oracle/oradata/INFRAORA/ts_data1_01.dbf');

-- 공간 해제
ALTER INDEX scott.IX_TBL_TEST1_BTREE
DEALLOCATE UNUSED;

/****************************
-- 인덱스 병합
****************************/
ALTER INDEX scott.IX_TBL_TEST1_BTREE COALESCE;

/****************************
-- 인덱스명 변경
****************************/
ALTER INDEX scott.IX_TBL_TEST1_BTREE
RENAME TO scott."arbracatabra*&#===0234#$";

/****************************
-- 인덱스 유효성 검사
****************************/
-- 인덱스 정보를 index_stats에 기록
ANALYZE INDEX IX_TBL_TEST1_BTREE
VALIDATE STRUCTURE;

-- 기록된 인덱스 정보 보기
SELECT blocks, btree_space, used_space, pct_used, lf_rows, del_lf_rows
FROM index_stats;

/****************************
-- 인덱스 재구축의 일례
****************************/
-- 테이블 잠금 상태로 REBUILD
ALTER INDEX SCOTT.PK_TBL_TEST1 REBUILD
TABLESPACE ts_data1;

-- 테이블 오픈 상태로 REBUILD
ALTER INDEX SCOTT.PK_TBL_TEST1 REBUILD ONLINE
TABLESPACE ts_data2;

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

/****************************
-- 인덱스 삭제
****************************/
DROP INDEX SCOTT.PK_TBL_TEST1; -- 될까?
DROP INDEX scott.IX_TBL_TEST1_BTREE;

DROP TABLE scott.TBL_TEST1;