[SQL] 인덱스

테이블 검색 성능

1. 데이터베이스 성능을 좌우하는 요소
   1. 논리적 , 물리적으로 최적화된 데이터베이스 설계 및 체계적인 데이터 모델링에 의해 좌우
2. Index Scan
   1. 물리적 데이터베이스 설계과정에서 SQL 명령문의 처리 속도 최적화를 위해 가장 우선적으로 고려되는 항목
3. Full Table Scan
   1. 열에 인덱스가 없는 경우 오라클 서버가 전체 테이블을 검색하는 방법

 

인덱스

1. 스키마 객체 중의 하나
   1. 검색 시 조건으로 사용되는 열에 생성
   2. 테이블과 별도로 데이터베이스에 포인터와 함께 저장
   3. 포인터를 사용하여 테이블로부터 임의의 행을 검색하는 동안 행 검색
2. 테이블 검색 속도 향상
   1. 데이터베이스 시스템의 디스크 I / O 최적화
   2. 인덱스화된 경로 사용으로 테이블의 행에 대해 직접적이고 빠른 접근방식 제공
   3. 데이터 위치를 빠르게 찾음으로써 전체 데이터베이스의 성능 향상
3. 다양한 인덱스의 유형 제공
4. 테이블의 볼륨, 데이터의 분포도, SQL 문장의 구조 등에 따라 선택

 

인덱스의 생성

1. 단일 열 또는 조합 열에 정의
2. 논리적 또는 물리적으로 인덱스화 된 테이블과 독립되어 존재
   1. 기본 테이블이나 다른 인덱스에 영향 없이 인덱스만 별도로 언제든지 생성 또는 삭제 가능
3. 인덱스 생성
   1. 명령문을 이용해 사용자가 수동으로 생성
   2. 오라클 서버에 의해 자동으로 생성
4. 테이블을 삭제하면 테이블과 연관된 인덱스들은 함께 삭제

 

자동으로 생성되는 인덱스

1. 테이블에 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의하면 고유 인덱스가 자동으로 생성
   1. 제약 조건 이름과 동일한 이름으로 생성
   2. 제약 조건으로 인해 자동으로 생성된 인덱스는 제약 조건의 상태나 존재 여부에 따라 오라클 서버에 의해 자동 생성 또는 삭제
2. 사용자가 수동으로 삭제 불가능

 

• y_emp, y_dept 테이블에 정의된 제약 조건 조회

SELECT table_name, constraint_name
FROM user_constraints
WHERE table_name IN ('Y_EMP', 'Y_DEPT');

 

• y_emp, y_dept 테이블에 연관된 인덱스의 목록을 조회해 보며 제약 조건과 동일한 이름의 인덱스가 자동으로 생성되어 있는지 확인

SELECT table_name, index_name, uniqueness
FROM user_indexes
WHERE table_name IN ('Y_EMP', 'Y_DEPT');

 

• 실습환경 조성을 위해 Y_EMP 테이블과 Y_DEPT 테이블에 PRIMARY KEY가 정의되어 있다면 삭제 후 다시 생성한다. 그리고 Y_EMP 테이블의 DEPT_ID 열에 Y_DEPT 테이블의 PRIMARY KEY를 참조하는 FOREIGN KEY도 다시 정의해 준다.

ALTER TABLE y_emp DROP PRIMARY KEY CASCADE;
ALTER TABLE y_dept DROP PRIMARY KEY CASCADE;
ALTER TABLE y_emp
ADD CONSTRAINT emp_empid_pk PRIMARY KEY(emp_id);
ALTER TABLE y_dept
ADD CONSTRAINTS dept_deptid_pk PRIMARY KEY(dept_id);
ALTER TABLE y_emp
ADD CONSTRAINT emp_deptid_fk FOREIGN KEY(dept_id)
REFERENCES y_dept(dept_id);

 

• 데이터 딕셔너리 USER_INDEXES를 조회하여 특정 테이블과 연관된 인덱스에 대한 정보를 조회

SELECT table_name, index_name, uniqueness 
FROM user_indexes
WHERE table_name IN ('Y_EMP', 'Y_DEPT');

 

• y_emp 테이블의 PRIMARY KEY를 DISABLE 하고 인덱스를 조회

ALTER TABLE y_emp DISABLE PRIMARY KEY CASCADE;

SELECT table_name, index_name, uniqueness 
FROM user_indexes
WHERE table_name = 'Y_EMP';

 

• y_emp 테이블의 PRIMARY KEY를 ENABLE 하고 인덱스를 조회

ALTER TABLE y_emp ENABLE PRIMARY KEY;

SELECT table_name, index_name, uniqueness 
FROM user_indexes
WHERE table_name = 'Y_EMP';

 

수동 인덱스 생성

1. CREATE INDEX 문을 실행하여 사용자 정의 인덱스 생성
   1. 고유 인덱스, 비고유 인덱스 모두 가능
   2. 응용프로그램의 요구사항에 맞추어 다양한 유형의 인덱스 생성
2. 구문
   1. 기본적으로 고유하지 않은 인덱스 생성
   2. 고유 인덱스를 생성하기 위해 CREATE UNIQUE INDEX 명령 사용

CREATE [UNIQUE] INDEX index명
ON table명 (column명 [, column_name]...);

 

인덱스 고려 사항

1. 응용프로그램의 요구사항에 따라 하나의 열에 인덱스가 여러 번 정의 가능
2. 인덱스가 많은 테이블은 DML 성능 저하 가능성 존재
   1. 인덱스는 저장구조의 관점에서 테이블과 별도로 디스크에 저장되는 객체
   2. 테이블이 변경될 경우 오라클 서버가 자동으로 갱신
3. 개발자 또는 관리자가 인덱스의 필요성을 면밀하게 검토한 후 반드시 필요한 경우에만 생성 고려

 

인덱스의 유형

1. 고유 인덱스
2. 비 고유 인덱스
3. 결합 인덱스
4. 함수 기반 인덱스

 

• FOREIGN KEY가 정의된 y_emp 테이블 dept_id 열에 비 고유 인덱스를 생성

CREATE INDEX emp_deptid_idx ON y_emp(dept_id);

 

• ON절의 괄호 안에 COLUMN 이름을 여러 개 지정하면 조합 열에 대한 단일 인덱스가 생성, 사원을 검색할 때 emp_id와 emp_name을 조건으로 자주 사용한다면 두열을 결합하여 결합 인덱스를 생성

CREATE INDEX emp_empid_lname_idx ON y_emp(emp_id, emp_name);

 

• 이미 결합 인덱스가 존재하는 y_emp 테이블의 emp_name 열에 단일 인덱스를 중복적으로 생성

CREATE INDEX emp_lname_idx ON y_emp(emp_name);

 

함수기반 인덱스

1. 인덱스화 된 열에 함수나 표현식을 사용해서 검색하면 전체 테이블 스캔 발생
   1. 표현식을 포함하여 정의하는 함수 기반 인덱스로 문제 해결
   2. 명령문을 실행하는 동안 표현식을 계산하지 않기 때문에 검색속도의 향상을 기대

 

• emp_name 열에 일반 인덱스를 생성한 경우 다음 두 명령문은 동일한 결과를 반환하지만 첫 번째 명령문은 인덱스를 사용하지 않고 두 번째 명령문은 인덱스를 사용한다.

SELECT emp_id, emp_name, salary, position
FROM y_emp
WHERE LOWER(emp_name) = 'mark kim';

SELECT emp_id, emp_name, salary, position
FROM y_emp
WHERE emp_name = INITCAP('mark kim');

 

• 급여를 조회할 때 사용할 표현식 salary * 12 * 0.9에 대한 함수 기반 인덱스를 생성

CREATE INDEX emp_sal_fidx ON y_emp(salary * 12 * 0.9);

 

• emp_sal_fidx 인덱스는 다음과 같은 쿼리에 대해 사용되어 SQL 명령문 처리 성능이 개선된다.

SELECT emp_id, emp_name, position
FROM y_emp
WHERE salary * 12 * 0.9 > 10000;

 

인덱스의 삭제

1. 수동 제거
   1. DROP INDEX 명령문 실행
2. 자동 제거
   1. 제약 조건에 의해 자동으로 생성된 인덱스는 제약 조건을 삭제하거나 비활성화하면 자동으로 제거
   2. 테이블을 삭제하거나 인덱스화된 열을 테이블로부터 삭제하는 경우
   3. 오라클 서버는 데이터 딕셔너리에서 인덱스 정의를 제거
3. 수동 제거 구문

DROP INDEX index_name;

 

• emp_sal_fidx 인덱스를 제거

DROP INDEX emp_sal_fidx;

 

• 제약 조건에 의해 자동으로 만들어진 인덱스를 수동으로 제거하려고 하면 다음과 같은 오류 반환

DROP INDEX emp_email_uk;

 

인덱스의 사용

1. 오라클 서버에 의해 사용되며 자동으로 유지관리
   1. 인덱스를 포함하는 테이블에서는 DML 작업이 커밋될 때마다 해당 인덱스 자동 갱신
2. 인덱스 사용 여부 확인 방법
   1. 사용자가 실행한 SQL이 인덱스를 액세스 했는지 여부를 알기 위해 오라클 서버의 SQL TRACE 기능을 이용
   2. 사용 모니터링 설정 후 v$object_usage를 통해 확인

 

인덱스를 생성해야 하는 경우

1. 열에 광범위한 값이 포함된 경우
2. 열에 NULL 값이 많이 포함된 경우
3. WHERE 절 또는 조인 조건에서 하나 이상의 열이 함께 자주 사용되는 경우
4. 큰 테이블에서 대부분의 쿼리에 의해 검색되는 행이 2% ~ 4% 미만인 경우

 

인덱스를 권장하지 않는 경우

1. 테이블이 작은 경우
2. 열이 쿼리의 조건으로 자주 사용되지 않는 경우
3. 대부분의 쿼리가 테이블에 있는 행의 2% ~ 4% 이상을 검색할 경우
4. 테이블이 자주 갱신되는 경우
5. 인덱스화된 열이 표현식의 일부로 참조되는 경우
   1. 함수기반 인덱스 사용 고려

 

인덱스 정보 보기

1. USER_INDEXES 데이터 딕셔너리
   1. 인덱스의 이름과 고유성, 관련 테이블 등을 확인
   2. 인덱스가 생성된 열의 이름을 확인 불가
2. USER_IND_COLUMNS 데이터 딕셔너리
   1. 인덱스와 관련된 열 확인

 

• USER_INDEXES와 USER_IND_COLUMNS를 조인하여 y_emp 테이블에서 생성된 모든 인덱스 이름, 관련된 열 이름 및 인덱스의 고유성을 조회하는 명령

SELECT ic.index_name, ic.column_name, uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'Y_EMP';