21. 색인(Index)
○ Index (색인)
- 데이터를 빠르게 찾을 수 있는 수단
- 테이블에 대한 조회 속도를 높여 주는 자료구조
- PK칼럼은 자동으로 인덱스 생성된다
○ Index 방식
① full scan
- 처음부터 끝까지 일일이 검사하는 방법, 전수조사 -
- 장점: 쓰레기데이터를 걸러낼 수 있다
- 단점: 시간이 많이 소모된다
② index range scan
- 이름이 여러개인 경우 목차를 찾아서 페이지를 찾아감
- 가장 일반적, 책 뒤의 인덱스 같은 느낌
- 훨씬 빠름, 별도의 메모리가 있어야 함
③ index unique scan
- 학번은 1개만 존재함, 유일한 값
○ Index 생성/삭제
① 생성
- 형식) create index 인덱스명 on 테이블명(칼럼명)
② 삭제
-형식) drop index 인덱스명
drop index emp2_name_idx;
drop index emp4_name_idx;
drop index emp4_namee_sal_idx;
③ 인덱스 데이터 사전
select * from user_indexes;
select * from user_indexes where index_name like 'EMP4%';
○ 연습1) emp2 테이블로 index 생성 전후 비교 (PK가 없는 테이블)
- 테이블 생성
create table emp2(
id number(5)
,name varchar2(25)
,salary number(7, 2)
,phone varchar2(15)
,dept_id number(7)
);- 행추가
insert into emp2(id,name) values (10,'kim');
insert into emp2(id,name) values (20,'park');
insert into emp2(id,name) values (30,'hong');
① 인덱스 생성 전
- 아래 select문 실행 후, 커서를 쿼리문 위에 두고 F10키를 누르면 계획설명 탭이 나옴
- OPTIONS : FULL → full scan했다는 뜻
- COST : 2 → COST 작업처리 속도
select * from emp2 where name='hong';
② 인덱스 생성 후
- 인덱스 생성
create index emp2_name_idx on emp2(name);- 아래 select문 실행 후, 커서를 쿼리문 위에 두고 F10키를 누르면 계획설명 탭이 나옴
select * from emp2 where name='hong';- OPTIONS : FULL
- COST : 2
○ 연습2) emp3 테이블로 index 생성 전후 비교 (PK가 있는 테이블)
- 테이블 생성
- pk는 인덱스가 자동으로 생성되면서 정렬된다
create table emp3 (
no number primary key
,name varchar2(10)
,sal number
);
- 아래 select문 실행 후, 커서를 쿼리문 위에 두고 F10키를 누르면 계획설명 탭이 나옴
select * from emp3 where no=3;- OPTIONS : UNIQUE SCAN
- COST : 1
○ 연습3) emp4 테이블 100만건의 레코드 대상으로 cost 비교
- 테이블 생성
create table emp4 (
no number
,name varchar2(10)
,sal number
);- 프로시저를 이용해서 100만행 추가하기
declare --선언문
--변수선언
i number := 1; --i변수에 1 대입 := 연산자
name varchar(20) := 'kim';
sal number := 0;
begin
-- T-SQL문
while i<=1000000 loop
if i mod 2 = 0 then
name := 'kim' || to_char(i);
sal := 300;
elsif i mod 3 = 0 then
name := 'park' || to_char(i);
sal := 400;
elsif i mod 5 = 0 then
name := 'hong' || to_char(i);
sal := 500;
else
name := 'shin' || to_char(i);
sal := 250;
end if;
insert into emp4(no, name, sal) values (i, name, sal); --행추가
i := i+1;
end loop;
end;
① 인덱스를 사용하지 않은 경우
- 아래 select문 실행 후, 커서를 쿼리문 위에 두고 F10키를 눌러서 계획설명 확인
select * from emp4 where name = 'kim466'; -- OPTIONS FULL / COST 900
select * from emp4 where no = 466; -- OPTIONS FULL / COST 899
select * from emp4 where sal>300; -- OPTIONS FULL / COST 901
② 인덱스 생성 후
- 인덱스 생성
-- name칼럼을 기준으로 인덱스 생성
create index emp4_name_idx on emp4(name);
-- name칼럼과 sal칼럼을 기준으로 인덱스 생성
create index emp4_name_sal_idx on emp4(name, sal);- 아래 select문 실행 후, 커서를 쿼리문 위에 두고 F10키를 눌러서 계획설명 확인
select * from emp4 where name = 'kim466'; -- OPTIONS RANGE SCAN / COST 3
select * from emp4 where name = 'kim466' and sal>200; -- OPTIONS RANGE SCAN / COST 3