시소당

ORACLE INDEX (인덱스 설계 시 주의사항)

SQL> show parameter db_file_multiblock_read_count;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ----------
db_file_multiblock_read_count        integer                          16

- 컬럼의 분포도가 10 ~ 15% 이내의 경우

  분포도 = (조건을 만족하는 행수 /전체 행수)* 100

- 분포도가 범위 이상이더라도 부분 범위의 검색이 가능하다면 인덱스를 사용하라

  분포도가 좋은 경우와 나쁜 조건이 같이 저장되어 있는 경우에는 분포도는 나쁘더라도
  인덱스를 사용하는 것이 유리하다. 이런 경우 분포도가 좋은 컬럼의 값은 인덱스를 통해
  검색을 하게 되고 분포도가 나쁜 컬럼의 값은 테이블 전체 스캔방법으로 검색하게 되는
  실행계획을 결정하면 된다.

SQL> show parameter optimizer_mode;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ----------
optimizer_mode                       string                           CHOOSE

SQL> select a.index_name,a.index_type,a.status,a.uniqueness,
  2  a.table_name,b.column_name
  3  from user_indexes a,user_ind_columns b
  4  where a.index_name=b.index_name
  5  and a.table_name = 'BIG_EMP';

선택된 레코드가 없습니다.

SQL> conn scott99/tiger99
연결되었습니다.

인덱스가 없다면 만든다.

SQL> create unique index i_big_emp_empno on big_emp(empno);
create unique index i_big_emp_empno on big_emp(empno)
                    *
1행에 오류:
ORA-00955: 이미 사용된 객체명입니다

SQL> create index i__big_emp_deptno on big_emp(deptno);
create index i__big_emp_deptno on big_emp(deptno)
                                          *
1행에 오류:
ORA-01408: 열 목록에는 이미 인덱스가 작성되어 있습니다

SQL> select a.index_name,a.index_type,a.status,a.uniqueness,
  2  a.table_name,b.column_name
  3  from user_indexes a,user_ind_columns b
  4  where a.index_name=b.index_name
  5  and a.table_name = 'BIG_EMP';

INDEX_NAME                     INDEX_TYPE                  STATUS   UNIQUENES TABLE_NAME
------------------------------ --------------------------- -------- --------- ----------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
I_BIG_EMP_EMPNO                NORMAL                      VALID    UNIQUE    BIG_EMP
EMPNO

I_BIG_EMP_DEPTNO               NORMAL                      VALID    NONUNIQUE BIG_EMP
DEPTNO

*분포도가 좋은 컬럼과 나쁜컬럼의 비교
deptno는 상대적으로 중복되는 값(나쁜컬럼)이 많지만 empno는 중복되는 값(좋은컬럼)이 적다.

SQL> set autotrace traceonly
SQL> select empno,ename
  2  from big_emp
  3  where deptno = 10;

5336 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=294 Bytes=35
          28)

   1    0   TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=294 Bytes=3
          528)

Statistics
----------------------------------------------------------
        130  recursive calls
          0  db block gets
        565  consistent gets
        185  physical reads
          0  redo size
     105771  bytes sent via SQL*Net to client
       4408  bytes received via SQL*Net from client
        357  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       5336  rows processed

SQL> select empno,ename
  2  from big_emp
  3  where empno = 166;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=2 Card=1
          Bytes=10)

   2    1     INDEX (UNIQUE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) (Cost=
          1 Card=28955)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        440  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+index(big_emp i_big_emp_deptno)*/ empno,ename
  2  from big_emp
  3  where deptno = 10;

5336 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=294 Bytes=35
          28)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=53 Card=2
          94 Bytes=3528)

   2    1     INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (C
          ost=1 Card=294)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        893  consistent gets
         12  physical reads
          0  redo size
     105771  bytes sent via SQL*Net to client
       4408  bytes received via SQL*Net from client
        357  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5336  rows processed

SQL>

*추출결과물이 인덱스로 되어진 컬럼만을 검색한다면 분포도가 나쁘더라도 인덱스를 생성하라

SQL> select deptno
  2  from big_emp
  3  where deptno=10;

5336 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=294 Bytes=588
          )

   1    0   INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cos
          t=1 Card=294 Bytes=588)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        368  consistent gets
          0  physical reads
          0  redo size
      70065  bytes sent via SQL*Net to client
       4408  bytes received via SQL*Net from client
        357  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5336  rows processed