03. INDEX(인덱

INDEX

■ DB에서 데이터 검색의 성능향상을 위해 별도의 INDEX를 활성화 하여 빠르게 검색이 가능하도록 돕는기능

(SQL명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는오라클 객체로 내부 구조는 B*트리 형식으로 구성되어 있음)
■ 사전에 색인과 같이 File의 위치, Block의 위치와 같은 실제 물리주소를 저장하는 원리
■ 조회할시 Key 값으로 조회하는 경우 일반값을 조회하는 것보다 10배 이상 빠르게 탐색 가능 ★★★★★

 

장점 & 단점

■ 장점

검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상

 

■ 단점

- 인덱스를 위한 추가 저장 공간이 필요하고 인덱스를 생성하는데 시간이 걸림
- 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우 오히려 성능 저하

 

INDEX 표현식

CREATE [UNIQUE] INDEX 인덱스 명
ON 테이블 명(컬럼 명, 컬럼 명 | 함수 명, 함수 계산식);

SELECT * FROM USER_IND_COLUMNS;

 

INDEX 구조

SELECT ROWID, EMP_ID, EMP_NAME
FROM EMPLOYEE;

 

INDEX  종류

1. 고유 인덱스 (UNIQUE INDEX): 고유한 값으로만 이뤄진 INDEX, ※ PK 선언시 기본적으로 같이 선언됨(DEFAULT) ★★★★★ 

2. 비고유 인덱스 (NOUNIQUE INDEX): 중복값을 허용하고, 일반적으로 사용자가 활용하는 인덱스 ★★★

3. 단일 인덱스 (SINGLE INDEX): INDEX로만 구성된 테이블(한개의 컬럼) -> 순서를 기준으로 탐색할 때 활용

4. 결합 인덱스 (COMPOSITE INDEX) : 두개 이상의 컬럼을 INDEX로 활용 할 때

 5. 함수기반 인덱스 (FUNCTION BASED INDEX): SELECT절이나 WHERE 절에 산술계산/ 함수식이 사용되는 경우

                                                                              -> 날짜 이외에 활용 거의 없음

 

고유 인덱스 (UNIQUE INDEX)

CREATE UNIQUE INDEX IDX_EMPNO
ON EMPLOYEE(EMP_NO);


SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMPLOYEE’;


CREATE UNIQUE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

--* UNIQUE INDEX는 중복 값이 있는 컬럼에 생성 시 에러 발생

■ 예문

-- 고유 인덱스 생성하기 (유니크한 값만 가능, PK)
CREATE UNIQUE INDEX IDX_EMP_NO 
ON EMPLOYEE(EMP_NO);
-- 유니크 하지 않은경우 생성되지 않음을 주의할것!

SELECT * FROM EMPLOYEE;
DELETE FROM EMPLOYEE WHERE EMP_ID = 231;

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMPLOYEE';
SELECT * FROM EMPLOYEE WHERE EMP_NO = '861015-1356452';
-- 현재는 체감불가능하나 속도가 비약적으로 상승함.

 

 

비고유 인덱스 (NOUNIQUE INDEX)

* 값이 고유하지 않아도 생성가능, 일반적으로 활용됨

CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMPLOYEE’;
* NONUNIQUE INDEX는 중복 값이 있는 컬럼에도 생성 가능

■ 예문

CREATE INDEX IDX_DEPT_CODE 
ON EMPLOYEE(DEPT_CODE);

SELECT * FROM EMPLOYEE 
WHERE DEPT_CODE = 'D5';

 

 

결합 인덱스 (COMPOSITE INDEX)

CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE);

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘DEPARTMENT’;
* COMPOSITE INDEX는 두 개 이상의 컬럼을 하나의 인덱스로 생성 가능
* COLUMN_POSITION의 순서에 의해 성능이 차이날 수 있음

 

CREATE INDEX IDX_NAME_PHONE 
ON EMPLOYEE(EMP_NAME, PHONE);

SELECT * FROM EMPLOYEE 
WHERE EMP_NAME LIKE '이%' AND PHONE LIKE '%5%';

 

함수기반 인덱스 (FUNCTION BASED INDEX)

CREATE TABLE EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY, BONUS,
		(SALARY + (SALARY + NVL(BONUS, 0)))*12 연봉
	FROM EMPLOYEE;
    
CREATE INDEX IDX_SALCALC
ON EMP_SAL ((SALARY + SALARY*NVL(BONUS, 0)))*12);

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMP_SAL’;

 

CREATE INDEX IDX_SALARY 
ON EMPLOYEE( (SALARY +  SALARY * NVL(BONUS,0)) * 12); -- 연봉

SELECT EMP_NAME, ((SALARY + SALARY * NVL(BONUS,0)) * 12) as 연봉 
FROM EMPLOYEE
WHERE ((SALARY +  SALARY * NVL(BONUS,0)) * 12) > 7000000;
-- ※ 주의 선언한 함수 그대로 활용해야한다.

 

INDEX 재생성

■ DML 작업(특히 DELETE 명령)을 수행한 경우,  해당 인덱스 엔트리가 논리적으로만 제거되고
실제 엔트리는 남아있게 되므로 제거된 인덱스가 필요 없는 공간을 차지하고 있지 않도록 인덱스를 재생성 해야 함

 

■ 사용하는 이유 :  주기적으로 최적화 할때 사용 --> DB 운영중에는 트리 최적화가 되지 않으므로 주기적 필요.

ALTER INDEX 인덱스명 REBUILD;

 

INDEX 삭제

DROP INDEX IDX_SALARY;

 

 

INDEX 활용한 정렬

SELECT * FROM EMPLOYEE
ORDER BY EMP_NO;

SELECT * FROM EMPLOYEE
WHERE EMP_NO > ‘0’;

* 둘 다 주민번호 기준 오름차순 정렬이지만 ORDER BY로 정렬하는 것보다
인덱스를 활용하는 것이 더 좋은 성능을 보임

---

"본 인터넷 사이트 내의 모든 이미지, 문구, 콘텐츠, 내용 등에 대한 저작권은 76beny에게 있습니다.

이를 무단으로 도용, 복사, 전재, 재배포, 2차 변형 등을 할 경우

민, 형사상 법적 조치 등 저작권법에 의거하여 처벌 받을 수 있습니다."