인덱스 적용 예제#

SQL> create table indextest
  2  as
  3  select *
  4  from dba_objects
  5  where owner in ('OUTLN', 'PUBLIC', 'SCOTT', 'SYS', 'SYSTEM');

테이블이 생성되었습니다.

SQL> analyze table indextest compute statistics;

테이블이 분석되었습니다.

SQL> set autotrace trace explain;
SQL> select owner, object_name
  2  from indextest
  3  where object_name='DBA_INDEXES';

Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   | SELECT STATEMENT  |           |     |    58 |   139   (2)00:00:02 |
|*  |  TABLE ACCESS FULL| INDEXTEST |     |    58 |   139   (2)00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - filter("OBJECT_NAME"='DBA_INDEXES')

SQL> create index indextest_objname_idx
  2  on indextest(object_name);

인덱스가 생성되었습니다.

SQL> select owner, object_name
  2  from indextest
  3  where object_name='DBA_INDEXES';

Execution Plan
----------------------------------------------------------
Plan hash value: 1845921808

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   | SELECT STATEMENT            |                       |     |    58 |  2   (0)00:00:01 |

|   |  TABLE ACCESS BY INDEX ROWID| INDEXTEST             |     |    58 |  2   (0)00:00:01 |

|*  |   INDEX RANGE SCAN          | INDEXTEST_OBJNAME_IDX |     |       |  1   (0)00:00:01 |

-----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   - access("OBJECT_NAME"='DBA_INDEXES')

위 소스는 인덱스를 가지지 않는 테이블(indextest)을 생성한뒤 쿼리를 통해 풀 테이블 스캔을 하는 것을 확인한뒤 인덱스 생성을 통해 인덱스 스캔을 하는 과정을 보여준다.

인덱스가 유용한 경우#

SQL> set autotrace off
SQL> select owner, count(*)
  2  from indextest
  3  group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                              19987
OUTLN                                   8
SYSTEM                                454
SCOTT                                  26
SYS                                 22986

SQL> create index indextest_owner_idx
  2  on indextest(owner);

인덱스가 생성되었습니다.

SQL> set autotrace trace explain
SQL> select owner, object_name
  2  from indextest
  3  where owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   | SELECT STATEMENT  |           |  8692 |   246K|   139   (2)00:00:02 |
|*  |  TABLE ACCESS FULL| INDEXTEST |  8692 |   246K|   139   (2)00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - filter("OWNER"='SYS')

SQL> analyze table indextest compute statistics for columns owner;

테이블이 분석되었습니다.

SQL> select owner, object_name
  2  from indextest
  3  where owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   | SELECT STATEMENT  |           | 22986 |   650K|   139   (2)00:00:02 |
|*  |  TABLE ACCESS FULL| INDEXTEST | 22986 |   650K|   139   (2)00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - filter("OWNER"='SYS')

SQL> select owner, object_name
  2  from indextest
  3  where owner='SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3851517165

---------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   | SELECT STATEMENT            |                     |    26 |   754 |2   (0)00:00:01 |

|   |  TABLE ACCESS BY INDEX ROWID| INDEXTEST           |    26 |   754 |2   (0)00:00:01 |

|*  |   INDEX RANGE SCAN          | INDEXTEST_OWNER_IDX |    26 |       |1   (0)00:00:01 |

---------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   - access("OWNER"='SCOTT')

위 소스에서 보면 owner기준으로 SYS와 SCOTT인 경우의 실행계획이 다르다. 물론 둘다 인덱스가 생성되어 있는 경우이지만 다른 이유는 일단 두 조건으로 반환되는 데이터의 건수가 다르다는 것이다. SYS의 경우 22986 건이고 SCOTT의 경우 26건이다. 여기서 보여주고자 하는 것은 전체 건수 대비 해당 데이터의 건수 비율이 일정 비율이하라고 하면 옵티마이저가 인덱스를 거치도록 실행계획을 선택하고 이상이라면 풀 테이블 스캔이 유리하기 때문에 인덱스를 무시한다. 여기서 사용된 analyze table indextest compute statistics for columns owner 명령은 owner 칼럼에 대한 값의 히스토그램을 생성하도록 처리하는 것이다.

인덱스의 물리적 정보 확인#

SQL> alter index indextest_objname_idx rebuild pctfree 0;

인덱스가 변경되었습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_blks, pct_used, lf_rows, lf_rows_len
  2  from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED    
------------------------------ ---------- ---------- ---------- 
INDEXTEST_OBJNAME_IDX                   2        195        100      


SQL> insert into indextest(owner, object_name)
  2  values('BBBBBBBBBBBBBBBBBa', 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');

개의 행이 만들어졌습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_blks, pct_used, lf_rows, lf_rows_len
  2  from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED    
------------------------------ ---------- ---------- ---------- 
INDEXTEST_OBJNAME_IDX                   2        196         99      

먼저 alter index명령어의 rebuild옵션으로 pctfree값을 조정했다. 오라클 10g를 기준으로 기본 생성하면 pctfree값은 10이 된다. 하지만 여기서 인덱스 추가시 블럭이 추가되는 것을 보기 위한 것이므로 임의로 pctfree값을 0으로 조정했다. 그리고 analyze index .. validate structure 는 인덱스의 물리적 정보를 조사해서 index_stats에 반영하는 명령이다.

index_stats에 대한 두차례의 쿼리에서 볼때 lf_blks값이 195에서 196으로 변경된것을 볼수 있다. pctfree값이 0(즉 인덱스 추가시 사용할수 있는 여유공간)이 없으므로 강제로 블럭을 추가하고 있는 것을 볼수 있다.

SQL> alter index indextest_objname_idx rebuild pctfree 10;

인덱스가 변경되었습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_blks, pct_used, lf_rows, lf_rows_len
  2  from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDEXTEST_OBJNAME_IDX                   2        217         90      


SQL> insert into indextest(owner, object_name)
  2  values('BBBBBBBBBBBBBBBBBca', 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');

개의 행이 만들어졌습니다.

SQL> select name, height, lf_blks, pct_used, lf_rows, lf_rows_len
  2  from index_stats;

NAME                               HEIGHT    LF_BLKS   PCT_USED
------------------------------ ---------- ---------- ----------
INDEXTEST_OBJNAME_IDX                   2        217         90      

위의 경우는 앞선 경우와는 달리 pctfree값을 10으로 조정했기 때문에 인덱스가 추가되더라도 블럭추가의 경우를 볼수는 없다. pctfree값인 10으로 넘어서는 인덱스의 추가라면 블럭 추가를 경험할수 있을것이다.

인덱스값 변경#

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_rows, del_lf_rows, pct_used
  2  from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS   PCT_USED
------------------------------ ---------- ---------- ----------- ----------
INDEXTEST_OBJNAME_IDX                   2      23469           0         89

SQL> update indextest set
  2  object_name='DBA_INDEXES2'
  3  where object_name='DBA_INDEXES';

행이 갱신되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_rows, del_lf_rows, pct_used
  2  from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS   PCT_USED
------------------------------ ---------- ---------- ----------- ----------
INDEXTEST_OBJNAME_IDX                   2      23470           1         89

여기서 볼 값은 DEL_LF_ROWS값이다. update구문으로 한개의 레코드를 수정했을 경우 내부적으로 기존의 인덱스 값을 지우고 새로운 인덱스 값을 할당하는게 아니라 기존 값은 그대로 두고(이를테면 삭제 플래그 처리) 다른 위치에 새로운 인덱스 값을 생성하는 형태를 취한다. 기존 값은 해당 위치에 새로운 인덱스가 생성될때 덮어쓰는 형태를 취한다. DEL_LF_ROWS 값이 1 증가한 것은 삭제 플래그 처리로 보면 된다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_rows, del_lf_rows, pct_used
  2  from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS   PCT_USED
------------------------------ ---------- ---------- ----------- ----------
INDEXTEST_OBJNAME_IDX                   2      23470           1         89

SQL> delete from indextest where object_name like 'ALL_%';

277 행이 삭제되었습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_rows, del_lf_rows, pct_used
  2  from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS   PCT_USED
------------------------------ ---------- ---------- ----------- ----------
INDEXTEST_OBJNAME_IDX                   2      23470         278         89

SQL> insert into indextest(owner, object_name)
  2  values('DDDDD', 'DDDDDDDDDD');

개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> analyze index indextest_objname_idx validate structure;

인덱스가 분석되었습니다.

SQL> select name, height, lf_rows, del_lf_rows, pct_used
  2  from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS   PCT_USED
------------------------------ ---------- ---------- ----------- ----------
INDEXTEST_OBJNAME_IDX                   2      23471         278         89
위 경우는 특정 인덱스값을 사용하는 레코드를 모두 지우고 난뒤와 새로운 값을 추가한뒤 인덱스 정보를 본 것이다. 삭제 작업은 결과적으로 DEL_LF_ROWS값을 증가시키고 입력작업은 LF_ROWS값을 증가시킴을 볼수 있다. 그리고 삭제 작업 직후 LF_ROWS 값이 변경되지 않음도 주의해서 볼것이다.

연결된 B-Tree 인덱스와 스킵 스캔#

SQL> select distinct owner from indextest group by owner;

OWNER
------------------------------
SYSTEM
SCOTT
SYS

SQL> select count(object_namefrom indextest order by object_name;

COUNT(OBJECT_NAME)
------------------
             23470

SQL> create index indextest_owner_objname_idx on indextest(owner, object_name);

인덱스가 생성되었습니다.

SQL> set autotrace trace explain;
SQL> analyze table indextest compute statistics;

테이블이 분석되었습니다.

SQL> analyze table indextest compute statistics for columns owner;

테이블이 분석되었습니다.

SQL> select owner, object_type
  2  from indextest
  3  where owner='SCOTT'
  4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2118604230

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   | SELECT STATEMENT            |                             |    30 |   390 |    22   (0)00:00:01 |

|   |  TABLE ACCESS BY INDEX ROWID| INDEXTEST                   |    30 |   390 |    22   (0)00:00:01 |

|*  |   INDEX RANGE SCAN          | INDEXTEST_OWNER_OBJNAME_IDX |    30 |  |     2   (0)00:00:01 |

-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   - access("OWNER"='SCOTT')

SQL> select owner, object_type
  2  from indextest
  3  where object_name='DBA_INDEXES';

Execution Plan
----------------------------------------------------------
Plan hash value: 1552774320

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   | SELECT STATEMENT            |                             |     |    37 |     5   (0)00:00:01 |

|   |  TABLE ACCESS BY INDEX ROWID| INDEXTEST                   |     |    37 |     5   (0)00:00:01 |

|*  |   INDEX SKIP SCAN           | INDEXTEST_OWNER_OBJNAME_IDX |     |  |     4   (0)00:00:01 |

-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   - access("OBJECT_NAME"='DBA_INDEXES')
       filter("OBJECT_NAME"='DBA_INDEXES')

위 결과는 10g에서 테스트한 결과이다. 스킵 스캔의 경우 오라클의 버전별로 다르게 나올것이다. 8i의 경우 인덱스 생성시 명시해주는 칼럼의 순서에 따라 인덱스 스캔을 하거나 풀 테이블 스캔을 선택적으로 하게 된다. 하지만 10g의 경우 기본적으로 스킵 스캔을 하는 것으로 보인다. 이것은 9i부터 디폴트로 되는 결과로 보인다. 다시 해보니 10에서도 인덱스 생성시 칼럼 명시 순서에 따라 실행계획이 다르게 나온다. 물론 옵티마이져의 분석 결과에 따라 다르게 나오는 셈이다.

인덱스 종류#

앞서 본 기본 인덱스외에 다음과 같은 인덱스 종류가 있다.

역 키 인덱스(reverse key index)#

sequence를 사용하는 것처럼 인덱스의 값이 순차적으로 증가해서 특정 블럭에서만 작업이 이루어지는 경우 다중 사용자 환경에서는 추가적인 부하로 성능장애를 야기할수 있다. 이럴 경우 역 키 인덱스를 사용하면 인덱스 처리 블럭를 분산할수 있다. 이를테면 인덱스 값이 1234 였다면 역 키 인덱스를 사용할때의 인덱스 값은 4321이 되는 셈이다. 결과적으로 하나의 블럭에 집중되던 처리를 분산시켜준다는 효과를 볼수 있으나 역으로 분산된 블럭의 블럭 분할을 야기하거나 인덱스 스캔시 효율을 떨어뜨릴수도 있다. 생성 방법은 다음과 같다.

SQL> create index indextest_owner_idx
  2  on indextest(ownerreverse;

인덱스가 생성되었습니다.

함수 기반 인덱스#

SQL> set autotrace traceonly explain
SQL> select *
  2  from emp
  3  where upper(ename)='BOB';

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   | SELECT STATEMENT  |      |     |    32 |     3   (0)00:00:01 |
|*  |  TABLE ACCESS FULL| EMP  |     |    32 |     3   (0)00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - filter(UPPER("ENAME")='BOB')

SQL> create index upper_ename_idx
  2  on emp(upper(ename));

인덱스가 생성되었습니다.

SQL> select *
  2  from emp
  3  where upper(ename)='BOB';

Execution Plan
----------------------------------------------------------
Plan hash value: 1333739548

-----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   | SELECT STATEMENT            |                 |     |    32 |     2(0)00:00:01 |

|   |  TABLE ACCESS BY INDEX ROWID| EMP             |     |    32 |     2(0)00:00:01 |

|*  |   INDEX RANGE SCAN          | UPPER_ENAME_IDX |     |       |     1(0)00:00:01 |

-----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   - access(UPPER("ENAME")='BOB')

먼저 upper(ename)='BOB' 라는 조건으로 쿼리를 실행할때 풀 테이블 스캔을 수행하는 것을 볼수 있다. 하지만 함수 기반 인덱스를 생성해서 쿼리를 돌려보면 인덱스 스캔을 하는 것을 다시 확인할수 있다.

함수 기반 인덱스를 사용할때 사전에 필요한 사항은 다음과 같다.

  1. 인덱스 생성을 위해서는 query rewrite 권한이 있어야 한다.
  2. 다른 스키마에 함수 기반 인덱스를 생성하기 위해서는 global query rewrite 권한이 필요하다.
  3. 인덱스가 만들어져 있다고 가정할때 query_rewrite_enabled 값이 true가 아니라면 옵티마이저는 이 함수 기반 인덱스를 사용하지 않는다.

SQL> set autotrace traceonly explain
SQL> select upper(object_name)
  2  from indextest;

Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   | SELECT STATEMENT  |           | 23472 |   550K|    76   (2)00:00:01 |
|   |  TABLE ACCESS FULL| INDEXTEST | 23472 |   550K|    76   (2)00:00:01 |
-------------------------------------------------------------------------------

SQL> select upper(object_name)
  2  from indextest;

Execution Plan
----------------------------------------------------------
Plan hash value: 2792531790

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   | SELECT STATEMENT  |           | 23472 |   550K|    76   (2)00:00:01 |
|   |  TABLE ACCESS FULL| INDEXTEST | 23472 |   550K|    76   (2)00:00:01 |
-------------------------------------------------------------------------------

SQL> alter table indextest modify object_name not null;

테이블이 변경되었습니다.

SQL> select upper(object_name)
  2  from indextest;

Execution Plan
----------------------------------------------------------
Plan hash value: 3938377006

-----------------------------------------------------------------------------------------------

| Id  | Operation            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   | SELECT STATEMENT     |                        | 23472 |   550K|    28(4)00:00:01 |

|   |  INDEX FAST FULL SCAN| INDEXTEST_OBJNAME_FIDX | 23472 |   550K|    28(4)00:00:01 |

-----------------------------------------------------------------------------------------------

함수 기반 인덱스의 경우 해당 칼럼에 not null 제약조건이 없다면 옵티마이저에서 인덱스를 타지 않도록 처리하기 때문에 함수 기반 인덱스를 사용하도록 하기 위해서는 위처럼 not null 제약조건이 설정되어 있어야 한다. 9i이상에서 변경된 사항으로 8i까지는 not null 제약조건에 관계없이 같은 실행계획을 만든다.

비트맵 인덱스#

흔히 boolean타입의 값처럼 선택범위가 적은 값을 가지는 칼럼은 인덱스로 지정할 경우 인덱스의 장점을 가질수가 없다. 이런 경우 B-Tree 인덱스를 사용할수가 없는데, 이런 경우를 위해 사용가능한 인덱스는 비트맵 인덱스이다.

SQL> create bitmap index indextest_owner_idx
  2  on indextest(owner);

인덱스가 생성되었습니다.

요약#

인덱스의 단점
  1. 모든 인덱스는 DML 처리속도를 느리게 만든다.
  2. 인덱스는 시간이 지날수록 애플리케이션의 성능을 악화시킨다.
  3. 인덱스 효율을 복구하려면, 인덱스를 재구성해야 하는데 이 작업 자체가 성능과 데이터의 이용성에 나쁜 영향을 끼친다.
  4. 인덱스 이용은 전적으로 옵티마이저의 몫이다. 만들어져 있다고 항상 사용하는 것은 아니다.

인덱스의 장점

  1. 좋은 인덱스는 쿼리의 선언부나 테이블 결합에 이용되는 칼럼에 만들어진다.
  2. 옵티마이저는 인덱스 사용으로 효과를 얻을수 있는 상황이나 인덱스 만으로도 쿼리에 답할수 있는 경우에만 B-Tree 인덱스를 사용한다.
  3. 연결 인덱스의 경우 연결 순서는 인덱스를 이용하는 쿼리의 특성에 따라 결정해야 한다.
  4. 모든 인덱스는 압축을 먼저 고려해본다.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-3) was last changed on 11-Jan-2008 13:35 by DongGukLee