[8i] 인덱스 관리(OLN)

/********************************************************************************************
-- Title : [8i] 인덱스 관리(OLN)
-- Reference : OLN
-- Key word : index
********************************************************************************************/

/****************************************************************************************
-- 인덱스 정보 얻기
****************************************************************************************/

  

/****************************************************************************************
-- 인덱스와 유효성 검사
****************************************************************************************/
-- User가 소유한 인덱스 이름, 유형, 상태 확인
SQL> SELECT index_name, tablespace_name, index_type, uniqueness, status
  2  FROM dba_indexes
  3  WHERE owner = 'SCOTT';

INDEX_NAME        TABLESPACE_NAME INDEX_TYPE UNIQUENESS  STATUS
---------------   --------------- ---------- ---------   ------
EMP_LNAME_IDX     INDX01          NORMAL     NONUNIQUE   VALID
ORD_ORD_NO_IDX    INDX01          NORMAL     UNIQUE      VALID
ORD_REGION_ID_IDX INDX02          BITMAP     NONUNIQUE   VALID

3 rows selected.

-- 모든 REVERSE 키 인덱스 이름 보기
SQL> SELECT o.object_name
  2  FROM dba_objects o
  3  WHERE owner = 'SCOTT'
  4  AND o.object_id IN (SELECT l.obj#
  5                      FROM ind$ l
  6                      WHERE BITAND(l.property, 4) = 4);

OBJECT_NAME
-----------------------
ORD_ORD_NO_IDX

1 row selected.

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

INDEX_NAME         TABLE_OWNER    TABLE_NAME     COLUMN_NAME
------------------ -------------- -------------- -------------------
COUNTRY_C_ID_PK    SCOTT          COUNTRIES_T    COUNTRY_ID
DEPT_CLU_IDX       SCOTT          DEPT_CLU       DEPT_ID
DEPT_DEPTNO_PK     SCOTT          DEPT3_T        DEPTNO
DEPT_DNAME_UK      SCOTT          DEPT2_T        DNAME
DEPT_DNAME_UK2     SCOTT          DEPT3_T        DNAME
...

/****************************************************************************************
-- 인덱스 유효성 검사
****************************************************************************************/
SQL> ANALYZE INDEX scott.emp_ix     /* 인덱스 정보를 index_stats에 기록 */
  2  VALIDATE STRUCTURE;

Index analyzed.

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

    BLOCKS   PCT_USED    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- -----------
        32          4         17           0
       
       
/****************************************************************************************
-- 정규 B-Tree 인덱스 생성
****************************************************************************************/
SQL> CREATE INDEX scott.emp_ix
  2  ON scott.emp(ename)
  3  PCTFREE 30
  4  STORAGE(INITIAL 200K
  5          NEXT 200K
  6          PCTINCREASE 0
  7          MAXEXTENTS 50)
  8  TABLESPACE indx;

Index created.

/****************************************************************************************
-- REVERSE 키 인덱스 생성
****************************************************************************************/
SQL> CREATE UNIQUE INDEX scott.dept_t_deptno_idx
  2  ON scott.dept_t(deptno) REVERSE
  3  PCTFREE 30
  4  STORAGE(INITIAL 200K
  5          NEXT 200K
  6          PCTINCREASE 0
  7          MAXEXTENTS 50)
  8  TABLESPACE indx;

Index created.

/****************************************************************************************
-- 비트맵 인덱스 생성
****************************************************************************************/
SQL> CREATE BITMAP INDEX scott.items_t_dept_code_idx
  2  ON scott.items_t(dept_code)
  3  PCTFREE 30
  4  STORAGE(INITIAL 200K
  5          NEXT 200K
  6          PCTINCREASE 0
  7          MAXEXTENTS 50)
  8  TABLESPACE indx;

Index created.

/****************************************************************************************
-- 인덱스에 대한 스토리지 파라미터 변경
****************************************************************************************/
SQL> ALTER INDEX scott.emp_ix
  2  STORAGE(NEXT 400K
  3          MAXEXTENTS 100);

Index altered.

/****************************************************************************************
-- 인덱스 공간 할당과 해제
****************************************************************************************/
SQL> ALTER INDEX scott.emp_ix     /* 공간 할당 */
  2  ALLOCATE EXTENT (SIZE 200K
  3                   DATAFILE '/export/home/oracle8i/iORCL/systs/indx01.dbf');

Index altered.

SQL> ALTER INDEX scott.emp_ix     /* 공간 해제 */
  2  DEALLOCATE UNUSED;

Index altered.

/****************************************************************************************
-- 인덱스 병합
****************************************************************************************/
SQL> ALTER INDEX scott.emp_ix COALESCE;

Index altered.

/****************************************************************************************
-- 인덱스 유효성 검사
****************************************************************************************/
SQL> ANALYZE INDEX scott.emp_ix     /* 인덱스 정보를 index_stats에 기록 */
  2  VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT blocks, btree_space, used_space, pct_used, lf_rows, del_lf_rows
  2  FROM index_stats;              /* 기록된 인덱스 정보 보기 */

BLOCKS BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
------- ----------- ----------- ----------- ------- -----------
4     7996     952         12         56     0

/****************************************************************************************
-- 인덱스 재구축의 일례
****************************************************************************************/
SQL> ALTER INDEX scott.emp_ix REBUILD
  2  TABLESPACE indx;

Index altered.

SQL> ALTER INDEX scott.emp_ix REBUILD ONLINE;   /* online 상에서의 인덱스 재구축 */

Index altered.

/****************************************************************************************
-- 인덱스 삭제
****************************************************************************************/
SQL> DROP INDEX scott.emp_ix; 

Index dropped.