SQL 인덱스 INDEX

인덱스

---

-- 자동 인덱스 -
-- 테이블에 PRIMARY KEY, UNIQUE 제약조건 설정을 하면, 자동으로 인덱스가 생성된다
-- B-TREE 구조로 인덱스 생성됨

-- 수동 인덱스 -
-- CREATE[UNIQUE] INDEX [스키마명.]인덱스명
-- ON [스키마명.]테이블명(컬럼1, 컬럼2, ...);

-- 인덱스는 언제 사용?
-- 1. 구별이 많이 되는 컬럼
--    테이블에 PRIMARY KEY 설정한 컬럼은 자동으로 UNIQUE INDEX가 생성된다
--    기본키 컬럼이 SELECT문으로 시작하는 조건식(WHERE절)에 사용된다

인덱스 목적

• 테이블의 데이터를 빨리 찾기위한 목적. 즉 조회성능을 높이려는 목적

인덱스를 생성할 때 고려해야 할 사항을 정리하면 다음과 같다.

1. 일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다 물론 15%는 정해진 것은 아니며 테이블 건수, 데이터 분포 정도에 따라 달라진다.

2. 테이블 건수가 적다면(코드성 테이블) 굳이 인덱스를 만들 필요가 없다

   데이터 추출을 위해 테이블이나 인덱스를 탐색하는 것을 스캔(scan)이라고 하는데,

   테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 빠르다.

3. 데이터의 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 인덱스로 만드는 것이 좋다

4. NULL이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기 적당치 않다

5. 결합 인덱스를 만들 때는, 컬럼의 순서가 중요하다

   보통, 자주 사용되는 컬럼을 순서상 앞에 두는 것이 좋다.

6. 테이블에 만들 수 있는 인덱스 수의 제한은 없으나, 너무 많이 만들면 오히려 성능 부하가 발생한다

---

인덱스 생성

-- 연습용 테이블생성
CREATE TABLE EX_IDX_TBL (
    COL1 NUMBER,
    COL2 VARCHAR2(10)
);

-- UNIQUE INDEX 생성
CREATE UNIQUE INDEX EX_IDX_TBL_IDX01
ON EX_IDX_TBL(COL1);

-- EX_IDX_TBL 테이블의 COL1 컬럼에 중복된 값을 허용하지 않는다는 의미이다

-- USER_INDEXES : 인덱스 정보를 관리하는 시스템 뷰
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EX_IDX_TBL';

/*
테이블에 PRIMARY KEY 제약객체명을 수동으로 생성하면,
자동으로 UNIQUE INDEX가 생성되는데, 그 이름이 동일하게 인덱스명으로 생성된다
*/

-- 제약조건 정보
-- USER_CONSTRAINTS : 제약조건을 관리하는 시스템 뷰
-- PRIVARY KEY 제약조건 개채이름 : PK_JOB_HISTORY 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'JOB_HISTORY';

-- 인덱스 정보
-- 인덱스명으로 PK_JOB_HISTORY 이름을 확인이 가능함
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'JOB_HISTORY';

-- NON-UNIQUE INDEX 생성
CREATE INDEX EX_IDX_TBL_IDX02
ON EX_IDX_TBL(COL1, COL2);

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES
WHERE TABLE_NAME = 'EX_IDX_TBL';

---