SQL 12-2. 인덱스란?
관련링크
본문
․ SQL server인덱스는 포인터를 사용하여 행 검색 속도를 높일 수 있는 스키마 객체다. ․ 인덱스는 명시적(explicit)으로 또는 자동으로 생성할 수 있으며 열에 인덱스가 없는 경우에는 테이블 전체가 스캔된다. ․ 인덱스는 테이블의 행에 대해 직접적으로 빠른 액세스를 제공하며 인덱스화 된 경로를 사용하여 데이터 위치를 빠르게
찾음으로써 디스크I/O를 줄여 준다. ․ 인덱스는 SQL server에 의해 사용되며 자동으로 유지 관리된다.
인덱스가 생성되면 사용자가 인덱스에 직접적인 작업을 할 필요가 없다. ․ 인덱스는 논리적을 또는 물리적으로 인덱스화 된 테이블과 독립되어 존재한다.
즉 인덱스는 언제든지 생성 또는 삭제할 수 있으며 기본 테이블이나 기타 인덱스에 아무런 영향도 주지 않는다.
참고 : 테이블을 삭제하며 해당하는 인덱스도 삭제된다.
① 인덱스 생성 방법
· 자동 : 테이블 정의에 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의하면 고유 인덱스가 자동으로 생성된다.
· 수동 : 사용자가 열에 고유하지 않은 인덱스를 생성하여 행에 대한 액세스 시간을 줄일 수 있다.
☞ 인덱스 유형
고유 인덱스 : 테이블의 열이 PRIMARY KEY 또는 UNIQUE키 제약 조건을 포함하도록 정의하면 SQL server는 이러한
고유 인덱스를 자동으로 생성한다.
인덱스의 이름은 제약 조건에 지정한 이름과 동일하다.
사용자가 생성할 수 있는 또 하나의 인덱스 유형은 고유하지 않은 인덱스이다.
예를 들어, 질의에서 조인할 FOREIGN KEY열 인덱스를 생성하여 검색 속도를 향상시킬 수 있다.
참고 : 고유 인덱스를 수동으로 생성할 수 있지만 고유 제약 조건을 생성하여 암시적(implicit)으로 고유 인덱스를 생성하
는 것이 좋다.
② 인덱스 생성
· 하나 이상의 열에 대해 인덱스를 생성합니다.
CREATE INDEX index
ON table (column [, column] ...);
구문 설명 :
index 인덱스 이름입니다.
table 테이블 이름입니다.
column 인덱스화될 테이블의 열 이름입니다.
MSSQL 디비 예제 :
CREATE INDEX emp_last_name_idx
ON employee(lname)
/* nonclustered index 데이터의 중복이 있는 경우 -- 수동 인덱스*/
CREATE UNIQUE CLUSTERED INDEX MYTABLE_EMP_ID
ON employee(lname,fname,minit) /* 자동인덱스*/
MSSQL 디비 예제 :
/* 테이블의 인덱스 확인 */
sp_helpindex employee
예제)
오라클 디비 예제 : CREATE INDEX emp_last_name_idx
ON employees(last_name);
Index created.
☞설명)
EMPLOYEES 테이블의 LAST_NAME 열에 대한 질의 액세스 속도를 향상 시킨다.
CREATE INDEX문을 실행하여 하나 이상의 열에 대해 인덱스를 생성한다.
③ 인덱스 생성이 필요한 경우
인덱스가 많을수록 좋은 것은 아니다.
테이블에 인덱스가 많을수록 질의 속도가 빨라지는 것은 아니다.
인덱스를 포함하는 테이블에서는 DML작업이 커밋될 때 마다 해당 인덱스도 갱신되어야 한다.
따라서 테이블과 연관된 인덱스가 많을수록 SQL server는 DML 작업 후 더 많은 인덱스를 갱신해야 한다.
☞ 인덱스 생성이 필요한 경우
· 열에 광범위한 값이 포함된 경우
· 열에 널 값이 많이 포함된 경우
· WHERE절 또는 조인 조건에서 하나 이상의 열이 함께 자주 사용되는 경우
· 큰 테이블에서 대부분의 질의에 의해 검색되는 행이 2%-4%미만인 경우
고유성을 보존하려면 테이블 정의에 고유 제약 조건을 정의해야 한다. 그러면 고유 인덱스가 자동으로 생성된다.
☞ 인덱스를 사용하지 않는 경우
다음의 경우에는 인덱스를 생성하지 않는 것이 좋습니다.
· 테이블이 작은 경우
· 열이 질의의 조건으로 자주 사용되지 않는 경우
· 대부분의 질의가 테이블에 있는 행의 2%-4% 이상을 검색할 경우
· 테이블의 자주 갱신되는 경우
· 인덱스화된 열이 표현식의 일부로 참조되는 경우
④ 인덱스 확인
· USER_INDEXES 데이터 딕셔너리 뷰는 인덱스 이름 및 고유성을 포함한다.
· USER_IND_COLUMNS 뷰는 인덱스 이름, 테이블 이름 및 열 이름을 포함한다.
오라클 디비 예제 : SELECT ic.index_naem, ic.column_name,
ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';
⑤ 함수 기반 인덱스
· 함수 기반 인덱스는 표현식을 기반으로 하는 인덱스다.
· 인덱스 표현식은 테이블 열, 상수, SQL 함수 및 사용자가 정의한 함수로부터 생성된다.
오라클 디비 예제 : CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
Index created.
다음과 같은 질의를 쉽게 처리할 수 있도록 해준다.
SELECT *
FROM departments
WHERE UPPER(department_name) = 'SALES';
☞설명)
UPPER(cloumn_name) 또는 LOWER(column_name) 키워드와 함께 정의된 함수 기반 인덱스를 사용하면 대소문자 구분
없이 검색할 수 있다.
SQL server가 전체 테이블을 스캔하는 대신 인덱스를 사용하도록 만들려면 후속 질의의 함수 값이 널이 아니어야 한다.
예제)
SELECT *
FROM employees
WHERE UPPER (last_name) IS NOT NULL
ORDER BY UPPER (last_name);
☞설명)
인덱스를 사용하도록 되어있지만 WHERE절이 없으면 SQL server가 전체 테이블을 스캔한다.
함수 기반 인덱스는 SQL server가 열에 DESC가 표시된 인덱스를 함수 기반 인덱스로 처리한다.
DESC가 표시된 열은 내림차순으로 정렬된다.
⑥ 인덱수 제거
· DROP INDEX 명령을 사용하여 데이터 딕셔너리에서 인덱스를 제거한다.
· 인덱스를 삭제하려면 인덱스 소유자이거나 DROP ANY INDEX권한이 있어야 한다.
· 인덱스는 수정할 수 없다.
인덱스를 변경하려면 삭제한 후 다시 생성해야 한다.
DROP INDEX index_name;
참고 : 테이블을 삭제할 경우 인덱스와 제약 조건은 자동으로 삭제되지만 시퀀스는 그대로 남는다.
예제)
오라클 디비, MSSQL 예제 : DROP INDEX upper_last_name_idx;
Index dropped.