DB index(데이터베이스 인덱스)

1. UNIQUE INDEX

 - 중복 값이 입력될 경우 에러가 발생합니다.

 CREATE UNIQUE INDEX IDX_DEPT2_DNAME ON DEPT2(DNAME ASC);

 

2. NON UNIQUE INDEX 

 - 중복 값 입력이 가능합니다.

CREATE INDEX IDX_PROF_POSITION ON PROFESSOR(POSITION DESC);

 

3. FUNCTION BASED INDEX

 - WHERE 절에 오는 조건 컬럼에서 PAY + 100 같은 연산으로 조건을 사용할 때 사용합니다. (이 방식은 임시 해결책입니다.)

CREATE INDEX IDX_PROF_PAY_FBI ON PROFESSOR(PAY + 100);

 

4. DESCENDING INDEX

 - 계좌 조회 같은 주로 최근 날짜부터 검색을 많이 하는 데이터에 적합합니다.

 - 또는 회사의 매출 테이블이 있을 때 상위 매장이나 사원을 보는 메뉴라면 매출이 큰 값부터 조회하는데 이런 데이터에 적합합니다.
 - 인덱스 선언 후 DESC로 정렬을 지정하면 됩니다.

CREATE INDEX IDX_PROF_PAY ON PROFESSOR(PAY DESC);

 

5. COMPOSITE INDEX 

 - 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 뜻합니다.

 - 컬럼 배치 순서는 해당 컬럼 조건으로 데이터를 더 적게 가질 수 있게 작성합니다.

 

 50명 -> 여자 -> 25명 -> 신사임당(25회 검사) - > 2명

 50명 -> 신사임당 -> 2명 -> 여자(2회 검사) -> 2명

 50명 -> 여자 -> 25명 -> 신사임당(25회 검사) - > 2명

 50명 -> 신사임당 -> 2명 -> 여자(2회 검사) -> 2명

 

6. BITMAP INDEX

 - 데이터의 변경량이 적고 값의 종류 또한 적은 데이터에 적용하는 것이 좋습니다.

CREATE BITMAP INDEX IDX_EMP_JOB_BIT ON EMP(JOB);

 

7. 해당 테이블에 인덱스 조회하기

SELECT TABLE_NAME, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'DEPT2';

 

8. 모니터링 시작하기

ALTER INDEX IDX_DEPT2_DNAME MONITORING USAGE;

 

9. 모니터링 중단하기

ALTER INDEX IDX_DEPT2_DNAME NOMONITORING USAGE;

 

10. 인덱스 사용 유무 확인하기

SELECT INDEX_NAME, USED FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'IDX_DEPT2_DNAME';

 

11. 인덱스 리빌드 하기

 - 인덱스 상태 조회 

ANALYZE INDEX IDX_ITEST_NO VALIDATE STRUCTURE;

 

- 0에 가까울수록 좋은 상태를 의미합니다.

SELECT (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 BALANCE FROM INDEX_STATS;

 

- 리빌드 실행 후 인덱스 상태 조회를 합니다.

ALTER INDEX IDX_ITEST_NO REBUILD;

 

- ONLINE 옵션을 사용하면 리빌드 작업 중에 데이터를 사용할 수 있지만 전체적인 성능이 많이 떨어집니다.

ALTER INDEX IDX_ITEST_NO REBUILD ONLINE;

 

12. 인덱스를 사용한 정렬

CREATE TABLE 동물
(
    NO  NUMBER,
    NAME VARCHAR2(10),
    SAL NUMBER
);



INSERT INTO 동물 VALUES(1, '강아지', 300);
INSERT INTO 동물 VALUES(2, '고양이', 250);
INSERT INTO 동물 VALUES(3, '소', 430);
INSERT INTO 동물 VALUES(4, '돼지', 220);
INSERT INTO 동물 VALUES(5, '염소', 620);
INSERT INTO 동물 VALUES(6, '토끼', 810);



CREATE INDEX IDX_동물_NAME ON 동물(NAME);



SELECT NAME FROM 동물;

 

 - NAME 필드로 만든 인덱스를 사용합니다.

SELECT NAME FROM 동물 WHERE NAME > '0';

 

13. 인덱스를 활용하여 MIN, MAX 구하기

 - SORT 발생

SELECT MIN(NAME) FROM 동물;

 

 - SORT 발생 안함

SELECT NAME FROM 동물 WHERE NAME > '0' AND ROWNUM=1;

 

- SORT 발생

SELECT MAX(NAME) FROM 동물;

 

 - 오라클 힌트 사용(인덱스 아래부터 읽기)

SELECT /*+ INDEX_DESC(S IDX_동물_NAME) */ NAME
FROM 동물 S
WHERE NAME >= '0' AND ROWNUM=1;

 

- 만약 인덱스가 삭제되거나 인덱스 컬럼이 변경될 경우를 대비하여 아래와 같이 사용(FIRST_ROW MAX/MIN 방식)

SELECT /*+ INDEX_DESC(S IDX_동물_NAME) */ MAX(NAME)
FROM 동물 S
WHERE NAME >= '0' AND ROWNUM=1;

 

14. 인덱스 삭제 전에 테스트 용도로 쓰는 인덱스 사용 안함 방식

  - 인덱스 사용 여부 확인

SELECT TABLE_NAME, INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE TABLE_NAME = 'EMP';

 

 - 인덱스 사용 안함 실행

ALTER INDEX IDX_EMP_ENAME INVISIBLE;

 

 - 인덱스 사용으로 변경

ALTER INDEX IDX_EMP_ENAME VISIBLE;