오라클 인덱스(INDEX), 단일/복합(결합) 인덱스(Single Column/Composite Index), 고유/비고유 인덱스(Unique/Non Unique Index), 함수기반 인덱스(Function Based Index), 인덱스 숨기기(Index Invisible),

오라클 인덱스(INDEX), 단일/복합(결합) 인덱스(Single Column/Composite Index), 고유/비고유 인덱스(Unique/Non Unique Index), 함수기반 인덱스(Function Based Index), 인덱스 숨기기(Index Invisible),

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=638

 

오라클 인덱스(INDEX), 단일/복합(결합) 인덱스(Single Column/Composite Index), 고유/비고유 인덱스(Unique/Non Unique Index), 함수기반 인덱스(Function Based Index), 인덱스 숨기기(Index Invisible),

오라클 인덱스(INDEX), 단일/복합(결합) 인덱스(Single Column/Composite Index), 고유/비고유 인덱스(Unique/Non Unique Index), 함수기반 인덱스(Function Based Index), 인덱스 숨기기(Index Invisible),11. 인덱스(INDEX) n 테이블의 칼럼에 대해 생성되는 객체로 별도의 영역…

ojc.asia

11. 인덱스(INDEX)

n 테이블의 칼럼에 대해 생성되는 객체로 별도의 영역(테이블스페이스)에 칼럼값들을 정렬한 후 생성한다. 데이터 검색 속도를 향상 시키기 위해 사용되며, 포인터를 이용하여 데이터를 램덤 액세스 한다.

n 한번 생성되면 오라클에 의해 내부적으로 자동 관리된다.

n 오라클에서 Create Index로 인덱스를 생성하면 B*Tree(balanced Tree) 인덱스가 생성되며 어떠한 행을 접근하든지 액세스 타임이 동일한다.

n WHERE절 or 조인절에 빈번히 출현되는 칼럼의 경우 대부분 인덱스가 필요하다.

n 칼럼값의 분포도가 10%~15% 인 경우 인덱스가 효율적이다.

n 삽입, 삭제가 빈번한 테이블에는 인덱스의 사용을 자제해야 한다.

[형식]

CREATE [UNIQUE] INDEX index_name

ON table_name (Column|Expr[,Column|Expr]...);

11.1 단일/복합(결합) 인덱스(Single Column/Composite Index)

n 단일 인덱스는 하나의 칼럼으로 인덱스가 구성된 것이며, 복합 인덱스란 두개 이상의 칼럼으로 인덱스가 생성된 경우이다.

1. EMP 테이블 ENAME 칼럼에 단일 인덱스를 생성하자. create index idx_emp_ename on emp(ename) -- 생성된 인덱스에 대한 확인은 USER_INDEXES 뷰에서 확인 가능하다. select table_name, index_name, visibility from user_indexes where table_name = 'EMP' TABLE_NAME INDEX_NAME VISIBILITY ------------------------------ ------------------------------ ---------- EMP IDX_EMP_ENAME VISIBLE EMP PK_EMP VISIBLE 2. EMP 테이블에서 ENAME, SAL로 복합 인덱스를 구성해 보자. create index idx_emp_ename_sal on emp(ename, sal) select table_name, index_name, visibility from user_indexes where table_name = 'EMP' TABLE_NAME INDEX_NAME VISIBILITY ------------------------------ ------------------------------ ---------- EMP IDX_EMP_ENAME_SAL VISIBLE EMP IDX_EMP_ENAME VISIBLE EMP PK_EMP VISIBLE

11.2 고유/비고유 인덱스(Unique/Non Unique Index)

n 고유 인덱스는 칼럼의 값들이 유일한 경우에 만들 수 있는 인덱스이고 비고유 인덱스는 칼럼 값에 중복된 값이 있는 경우 만드는 인덱스이다.

1. EMP 테이블에서 ENAME 칼럼에 대해 생성되어 있는 인덱스를 제거하시오. -- EMP 테이블의 ENAME 칼럼에 대해 생성되어 있는 인덱스는 아래 쿼리로 확인하자. select a.index_name from USER_IND_COLUMNS a, USER_INDEXES b where a.table_name = 'EMP' and a.index_name = b.index_name and a.column_name = 'ENAME' INDEX_NAME ------------------------------ IDX_EMP_ENAME IDX_EMP_ENAME_SAL Drop index IDX_EMP_ENAME; Drop index IDX_EMP_ENAME_SAL; 2. EMP 테이블에서 ENAME 칼럼에 대해 고유인덱스(Unique Index)를 생성 하시오. -- 만약 ename 칼럼에 중복되는 값이 있다면 아래 create index 구문은 오류가 발생한다. create unique index idx_emp_ename on emp(ename); 3. EMP 테이블에서 DEPT 칼럼에 대해 비고유인덱스(Non Unique Index)를 생성 하시오. create index idx_emp_deptno on emp(deptno)

11.3 Descending Index

n 인덱스 생성시 칼럼명 다음에 아무값도 안써주면 기본적으로 오름차순(ASCENDING) 이지만 DESC 라고 기술하면 내림차순(DESCENDING)으로 인덱스가 생성된다.

1. EMP 테이블에서 JOB은 오름차순, SAL 칼럼은 내림차순으로 인덱스를 생성 하시오. create index idx_emp_job_sal on emp(job, sal desc)

11.4 함수기반 인덱스(Function Based Index)

n SQL 문장의 WHERE 절에 SQL함수(SQL Function)가 사용되는 경우 인덱스 컬럼에 변형이 생겨 인덱스를 사용하지 못하게 되는데 이 경우 함수 기반 인덱스를 사용하면 된다.

n 칼럼에 대해 SQL함수 등을 적용하여 인덱스를 생성한다.

n 함수 기반 인덱스는 내부적으로 비트맵 인덱스로 생성된다.

1. 아래와 같은 쿼리를 보자. -- 아래 쿼리를 보면 EMP 테이블의 ENAME 칼럼은 인덱스가 생성되어 있다. select a.index_name from USER_IND_COLUMNS a, USER_INDEXES b where a.table_name = 'EMP' and a.index_name = b.index_name and a.column_name = 'ENAME' INDEX_NAME ------------------------------ IDX_EMP_ENAME -- 아래 쿼리는 인덱스 칼럼에 변형이 생긴 경우 이므로 인덱스를 이용하지 못하게 된다. 데이터 건수가 많은 경우 성능상문제가 될 수 있다. SQL> set autotrace on explain SQL> select ename, sal from emp 2 where lower(ename) = 'smith'; ENAME SAL ---------- ---------- SMITH 1000 Execution Plan -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 9 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- -- lower(ename) 전체를 함수기반 인덱스로 생성하자. SQL> conn / as sysdba; SQL>alter session set QUERY_REWRITE_ENABLED = TRUE; SQL>alter session set QUERY_REWRITE_INTEGRITY=TRUSTED; SQL>grant query rewrite to scott; SQL>conn scott/tiger SQL> create index fidx_emp_ename on emp( lower(ename) ); --함수기반인덱스 생성 SQL> select ename, sal from emp 2 where lower(ename) = 'smith'; ENAME SAL ---------- ---------- SMITH 1000 Execution Plan -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 2 ( | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 ( |* 2 | INDEX RANGE SCAN | FIDX_EMP_ENAME | 1 | | 1 (

11.5 인덱스 재구성 및 삭제

칼럼에 대해 생성된 인덱스에 변형(데이터 삽입, 삭제, 수정)이 자주 발생하는 경우 균형잡힌 트리(Balanced Tree)의 모양이 아닌 트리가 한쪽으로 기울어 지는 현상이 생길 수 있다. 이러한 경우 인덱스를 재구성 또는 삭제 후 재생성 해야 한다.

1. EMP 테이블의 ENAME 칼럼에 대해 생성된 인덱스를 재구성 하세요. Alter index idx_emp_ename rebuild; 2. EMP 테이블의 ENAME 칼럼에 대해 생성된 인덱스를 삭제 하시요. Drop index idx_emp_ename;

11.6 인덱스 숨기기(Index Invisible)

QUERY 작업을 할 때 가끔은 인덱스가 없다면 실행계획이 어떻게 될까, 고민 하면서 인덱스를 DROP 하고 실행 계획을 본 후 다시 INDEX를 생성하여 실행 계획을 본 적이 있을 것이다. 적은 량의 데이터를 가진 테이블이라면 문제가 아닐텐데 많은 양의 데이터가 있는 테이블 이라면 삭제하고 생성하는데 부담이 있다. 이때 사용할 수 있는 것이 Oracle 11g에서 새로 소개된 INVISIBLE INDEX인데 인덱스를 invisible로 만들어서 옵티마이저에서 명시적으로 사용하지 말라고 지시를 할 수 있다.

SQL> set autotrace on explain -- 인덱스가 생성되어 있으니 CBO에서 이용한다. SQL> SELECT count(*) FROM emp WHERE sal > 1500; COUNT(*) ---------- 10 Execution Plan --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | INDEX RANGE SCAN| IDX_EMP_SAL | 10 | 40 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------- -- SAL 칼럼에 생성되어 있는 인덱스를 숨기자. SQL> ALTER INDEX IDX_EMP_SAL invisible; 인덱스가 변경되었습니다. SQL> SELECT count(*) FROM emp WHERE sal > 1500; COUNT(*) ---------- 10 Execution Plan --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| EMP | 10 | 40 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- SQL> ALTER INDEX IDX_EMP_SAL visible; 인덱스가 변경되었습니다.

​