인덱스란?

오라클 서버 인덱스는 포인터를 사용하여 행의 검색을 촉진할 수 있는 스키마 객체입니다. 인덱스는 명시적으로  또는 자동적으로 생성될 수 있습니다. 열에서 인덱스를 가지고 있지 않다면, 이때는 전체 테이블 스캔(scan)이 발생할 것 입니다.

인덱스는 테이블의 행에 대해 직접적이고  빠른 액세스를 제공합니다. 이것의 목적은  빠르게 데이터를 찾기 위해 인덱스 된 경로를 사용하여 디스크 I/O의 필요성을 경감시키는 것 입니다. 인덱스는 오라클 서버에 의해 자동적으로 사용되고 유지됩니다. 한번 인덱스가 생성되면 , 사용자에게 요구되는 직접적인 작업은 없습니다. 인덱스는 그들이 인덱스한 테이블에 대해 논리적으로 물리적으로 독립적입니다. 즉 인덱스는  어느 때에나 생성되거나 제거될 수 있고 기본 테이블 또는 다른 인덱스에 영향이 없음을 의미합니다.

주: 테이블을 제거했을 때, 해당 인덱스도 역시 제거됩니다.

자세한 내용은 Oracle Server Concepts Manual, Release 7.3 또는 8.0, “Schema Objects” section, “Indexes” topic을 참조하십시오.


인덱스를 생성하는 방법은?

2가지 타입의 인덱스가 생성될 수 있습니다. 한 타입은 유일한 인덱스입니다. 오라클 서버는  PRIMARY Key 또는 UNIQUE Key 제약조건을 가지기 위해 테이블에서 열을 정의할 때 자동으로 이 인덱스를 생성합니다.

사용자가 생성할 수 있는 다른 타입의 인덱스는 유일하지 않은 인덱스입니다. 예를 들면, 검색 속도를 향상시키키 위해 질의 안의 조인을 위한 FOREIGN KEY 열 인덱스를 생성할 수 있습니다.



인덱스 생성

CREATE INDEX 문장을 생성함으로써 하나 이상의 열의 인덱스를 생성합니다.

구문에서:

        index                  인덱스의 이름입니다.
        table                  테이블의 이름입니다.
        column              인덱스 되기 위한 테이블의 열 이름입니다.

자세한 내용을 보시려면
Oracle Server SQL Reference, Release 7.3 또는 8.0, “CREATE INDEX.”를 참조하십시오.

많은 것이 항상 더 좋은 것은 아니다.

테이블의 많은 인덱스가 질의의 스피드 향상을 꼭 의미하는 것은 아닙니다. 인덱스를 가지고  있는 테이블에 대한 각 DML 작업은 인덱스도 갱신되어야 함을 의미합니다. 많은 인덱스가 테이블과 관련되어 있으면, 오라클 서버는 DML 후에 모든 인덱스를 갱신시키기 위해 더 많은 노력이 필요하게 됩니다.

언제 인덱스를 생성하는가?


열은 WHERE 절 또는 조인 조건에서 자주 사용됩니다.

열은 광범위한 값을 포함합니다.

열은 많은 수의 null 값을 포함합니다.

둘 또는 이상의 열은 WHERE 절 또는 조인 조건에서 자주 함께 사용됩니다.

테이블은 대형이고 대부분의 질의들은 행의 2-4%보다 적게 읽어들일 것으로 예상됩니다.

유일성을 강행하기를 원한다면, 테이블 정의에 유일한 제약조건을 정의해야 함을 명심 하십시오. 이때 유일한 인덱스는 자동으로 생성됩니다.




언제 인덱스를 생성해서는 안되는가

테이블이 작다.

열이 질의의 조건으로 자주 사용되지 않는다.

대부분의 질의들은 행의 2-4%이상을 읽어들일 것으로 예상된다.

테이블은 자주 갱신됩니다. 테이블에 하나 이상 인덱스를 가지고 있다면 테이블을 액세스하는 DML 문장은 인덱스의 유지 때문에 상대적으로 더 많은 시간이 걸리게 됩니다.


인덱스 확인

USER_INDEXES 데이터 사전 뷰에서 인덱스의 존재를 확인합니다. 또한USER_IND_COLUMNS 뷰를 질의함으로써 인덱스에 관계된 열을 확인합니다. 위의 예는 EMP 테이블에서 이전에 생성된 인덱스, 관련 이름, 유일성 모두를 디스플레이합니다 INDEX_NAME        COLUMN_NAME    COL_POS UNIQUENES
-----------------  --------------- ------- ----------
EMP_EMPNO_PK      EMPNO                1 UNIQUE
EMP_ENAME_IDX      ENAME                1 NONUNIQUE



주: 위의 출력은 포맷되었습니다.


함수 기반 인덱스

UPPER(column_name) 또는 LOWER(column_name) 키워드로 정의된 함수 기반 인덱스를 사용하면 대소문자 구분 없이 검색할 수 있습니다. 예를 들어, 다음 인덱스는

  SQL> CREATE INDEX uppercase_idx ON emp (UPPER(ename));

다음과 같은 질의를 처리합니다.

    SQL> SELECT * FROM emp WHERE UPPER(ename) = 'KING';

전체 테이블을 스캔하는 대신 인덱스를 사용하려면 이후 질의의 함수 값이 널이 아니어야 합니다. 예를 들어, 다음 명령문은 인덱스를 사용하도록 되어 있지만 where 절이 없으면 Oracle이 전체 테이블을 스캔합니다.

  SQL> SELECT * FROM emp

      2  WHERE UPPER (ename) IS NOT NULL

      3  ORDER BY UPPER (ename);

Oracle은 열에 DESC가 표시된 인덱스를 함수 기반 인덱스로 처리합니다. DESC가 표시된 열은 내림차순으로 정렬됩니다.



인덱스 제거

인덱스를 수정할 수 없습니다. 인덱스를 변경하기 위해서는, 그것을 제거하고 다시 작성해야 합니다. DROP INDEX 문장을 생성하여 데이터 사전에서 인덱스 정의를 제거합니다. 인덱스를 제거하기 위해서는 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 있어야 합니다.