과정목적

본 과정에서는 일반적으로 사용되는 다른 데이터베이스 객체 중 일부를 생성하고 유지하는 법을 배우게 됩니다. 이 객체는 시퀀스 , 인덱스, 동의어를 포함합니다.

 

데이터베이스 객체

많은 어플리케이션들은 유일한 숫자 값을 기본 키 값으로 사용합니다. 이 요구사항을 처리하기 위해 코드를 어플리케이션에서 구축하거나 유일한 번호를 생성하기 위해 시퀀스를 사용할 수 있습니다.

일부 질의의 성능을 향상시키길 원한다면, 인덱스의 생성을 고려해야 합니다.  또한  열 또는 열집합에서 유일성을 강화시키기 위해 인덱스를 사용할 수 있습니다. 동의어를 사용하여 객체를 위한 대체명을 제공할 수 있습니다.

 

인덱스란?

오라클 서버 인덱스는 포인터를 사용하여 행의 검색을 촉진할 수 있는 스키마 객체입니다. 인덱스는 명시적으로  또는 자동적으로 생성될 수 있습니다. 열에서 인덱스를 가지고 있지 않다면, 이때는 전체 테이블 스캔(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%보다 적게 읽어들일 것으로 예상됩니다.

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

 

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

 

인덱스 확인

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 권한을 가지고 있어야 합니다.

구문에서

        index                    인덱스의 이름입니다.

 emp table의 모든 column에 대하여 nonunique index가 생성되어 있다고 할 때, 다음 문장을 실행할 경우 어떤 column의 index가 사용되겠는가?
SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 30
ORDER BY sal, ename;
A. empno
B. ename
C. sal
D. deptno
답

X 정답:D


X 정답:D


X 정답:C