[ORACLE] INDEX

인덱스?

인덱스는 검색 속도를 높이기 위해 사용하는 도구(인덱스를 사용한다고 무조건 빨라지는 건 아님)

오라클은 테이블 생성 시 인덱스를 따로 생성하지 않아도 primary key 또는 unique 제약 조건을 정의하면 자동으로 생성됨

행에 엑세스하는 속도를 높이기 위해 유저가 열의 비고유 인덱스를 생성할 수 있음

 

 

인덱스 사용해야 하는 경우

테이블 행의 갯수가 많은 경우 (찾아야할 데이터가 많은 경우)

인덱스를 적용한 컬럼이 where절에서 많이 사용되는 경우

join할 때 사용하는 컬럼(on 부모테이블.PK = 자식테이블.FK)

검색 결과가 원본 테이블 데이터 2~4%에 해당하는 경우

 

 

인덱스를 사용하면 안 좋은 경우

테이블의 행의 갯수가 적은 경우

검색결과가 원본테이블의 많은 비중을 차지하는 경우

원본 테이블의 인덱스가 많은 경우 

원본 테이블의 삽입, 수정, 삭제가 빈번한 경우-> DML작업 후에 모든 인덱스를 갱신해야하므로 서버의 부담이 늘어남

 

 

인덱스를 사용하는 SQL 작성방법

WHERE절 검색에 사용되는 컬럼에 인덱스가 존재하는지 확인

WHERE절 검색에 사용되는 컬럼을 가공하면 FULL TABLE SCAN을 함

WHERE절 검색에 사용되는 컬럼이 가공되지 않았는데도 인덱스를 엑세스하지 못하는 경우에 암시적 형변화 여부를 확인해봐야함

 

 

접속한 계정이 소유한 인덱스 정보 조회 방법

SELECT * FROM USER_INDEXES;

일부정보만 캡처

 

접속한 계정이 소유한 인덱스 컬럼 정보 조회 방법

SELECT * FROM USR_IND_COLUMNS;

 

 

인덱스 생성 방법

CREATE INDEX 인덱스명 
	ON 테이블명(KEY로지정할컬럼명1 ASC or DESC,
    		   KEY로지정할컬럼명2 ASC or DESC,
               ...);

 

인덱스 힌트

SELECT /*+ INDEX(a INDEX_NAME) */
	a.NAME
FROM emp3 ;

SELECT절 첫 줄에 힌트 주석(/*+ */)을 작성하여 적절한 인덳를 부여하면 됨. 주석에 "+"를 붙여야 힌트절이 실행되며 "+"가 없으면 일반 주석으로 간주하여 아무런 이벤트가 없음

• 적절한 인덱스 힌트 사용시 쿼리의 수행 속도를 향상시킴
• ORDER BY를 사용하지 않아도 인덱스의 컬럼 순서로 정렬되어 조회됨
• INDEX(INDEX_ASC) : 오름차순 정렬, INDEX_DESC : 내임차순으로 정렬
• 멀티라인 주석(/*+ */)과 싱글라인 주석(--+) 모두 인덱스 힌트를 사용할 수 있음
• 여러개의 복합 인덱스 힌트를 사용할 수 있음(/*+ INDEX(...) INDEX(...) */

 

 

 

여러개의 인덱스가 있을 경우

IDX_NO 인덱스는 NO,SALARY 컬럼으로 구성되어 있으며 WHERE절에 NO기준으로 데이터를 조회할 경우 사용

만약  NAME으로 조회한다면 IDX_NAME 인덱스를 사용해야 함

 

 

 

---

 

 

[인덱스 활용 간단예제]

 

 

 

'EMP3'테이블 생성

create table emp3(
    no number,
    name varchar2(10),
    salary number
  );

 

 

'EMP3'테이블에 데이터를 인서트

  insert into emp3 values(1, '강호동', 200);
  insert into emp3 values(2, '이경규', 300);
  insert into emp3 values(3, '이경실', 100);
  insert into emp3 values(4, '유재석', 400);
  insert into emp3 values(5, '홍길동', 150);
  insert into emp3 values(6, '홍길자', 250);
  insert into emp3 values(7, '김구라', 150);
  insert into emp3 values(8, '홍현희', 250);
  insert into emp3 values(9, '양세형', 200);
  insert into emp3 values(10, '박나래', 300);
  insert into emp3 values(11, '장도연', 100);
  insert into emp3 values(12, '양세찬', 400);
  insert into emp3 values(13, '최양락', 150);
  insert into emp3 values(14, '팽현숙', 250);
  insert into emp3 values(15, '안영미', 150);
  insert into emp3 values(16, '박미선', 250);

 

 

'EMP3'테이블의 'NAME'을 사용하는 'IDX_NAME'인덱스 생성

CREATE INDEX IDX_NAME ON EMP3(NAME);

 

 

'EMP3'테이블의 인덱스 컬럼 정보 조회

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME='EMP3';

인덱스 생성 후 조회시 아래와같이 생성내용 확인가능

(인덱스 생성시 따로 정렬조건(ASC/DESC) 안 줄 경우 기본으로 'ASC' 지정됨)

 

인덱스를 사용하지 않고 조회

인덱스를 사용하지 않고 'NAME'을 조회시 따로 정렬없이 인서트한 순서로 조회됨

SELECT NAME FROM EMP3;

 

인덱스를 사용 조회

인덱스를 사용해서 'NAME'을 조회시 'NAME'기준 오름차순(ASC)로 정렬해서 데이터 조회됨

 

쿼리실행속도를 확인하는 쿼리

SET TIMING ON; 
--TIMING START; 
  select min(name) from emp3; 
--TIMING STOP;

 

 

 

 

 

 

 

 

참고 :

오라클로 배우는 데이터베이스 입문 [이지스퍼블리싱] 교재

sas-study.tistory.com/104

rongscodinghistory.tistory.com/113

gent.tistory.com/306