[Oracle] #6-5 인덱스(index)

1. index

 

1) 개념

  - 더 빠른 검색을 위한 인덱스
  - 데이터를 빠르게 찾을 수 있는 수단
  - 테이블에 대한 조회 속도를 높여 주는 자료구조
  - PK칼럼은 자동으로 인덱스 생성된다

 

2) 방식

 - full scan
 처음부터 끝까지 일일이 검사하는 방법. 전수조사        

 - index range scan
 이름이 여러개인 경우 목차를 찾아서 페이지를 찾아감
 훨씬 빠름. 별도의 메모리가 있어야 함.

 - index unique scan
 학번은 1개만 존재함. 유일한 값

 

3) 인덱스 생성

형식) create index 인덱스명 on 테이블명(칼럼명)

 

4) 인덱스 삭제

형식) drop index 인덱스명

 

 

2. index 연습하기

 

1) emp2 테이블 만들기

--테이블생성
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');

commit;

 

 

 2) index를 만들고 전 후 비교하기

-- 인덱스 생성 전
select * from emp2 where name='hong';  -- 커서 여기에 놓고 F10

-- 인덱스 생성 후
create  index emp2_name_idx on emp2(name);
drop    index emp2_name_idx on emp2(name); -- 인덱스 삭제


select * from emp2 where name='kim';    -- 커서 여기에 놓고 F10
-- F10 계획설명 보기 -> unique scan, cost 2

 

생성 전은 COST가 2이다

생성 후에는 COST가 1이다

 

 

* 여기서 COST란 무엇일까!? *

 Oracle Optimizer라는 것이 있다.

 사용자가 실행하는 SQL에 대하여 가장 적절한 데이터 추출 경로가 무엇인가를 결정하는 Oracle 내부 아키텍처이다.

 Oracle Optimizer는 크게 규칙기반(Rule-base)과 비용기반(Cost-based)으로 나누어 지는데 규칙기반(Rule-based)은 Oracle 데이터베이스 시스템이 미리 정해놓은 일정한 순위대로 실행이 된다.

 비용기반(Cost-based)은 분석된 데이터베이스 현재 상태를 기반으로 비용이 가장 적게 드는 실행 경로(Execution plan)를 채택하여 실행한다.

 

 일반적으로 비용기반(Cost-based) 접근이 규칙기반(Rule-based) 접근보다 나은 실행 계획을 제공하는데 비용기반접근을 통해 Oracle Optimizer가 실행 계획을 얻기 위해서는 통계 정보가 필요하다.

 

 인덱스를 부여하자 COST가 2에서 1로 줄어드는 것을 볼 수 있다.

 오라클에서 보다 더 나은 데이터 접근이 가능하다는 뜻이다.

 

 

 3) PK가 있는 테이블

 PK는 인덱스가 자동으로 생성되면서 정렬된다.

create table emp3 (
   no    number       primary key
  ,name varchar2(10)
  ,sal  number
);

select * from emp3 where no=3;
-- F10 계획설명 보기 -> unique scan, cost 1

 

 

3. 100만건의 레코드 대상으로 COST 비교하기

 

1) 테이블 만들기

create table emp4(
   no     number
  ,name   varchar2(10)
  ,sal    number
);

 

 

2) 프로시저를 이용해서 100만행 추가하기

declare -- 선언문
    -- 변수 선언
    i       number          := 1;       -- i변수에 1 대입, := 연산자
    name    varchar2(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 4 = 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;

데이터 100만개 똬다다다다닥

 

 

3) 인덱스 사용 전후 비교하기

 

 - 인덱스 사용 전

select * from emp4 where name='kim466'; -- 실행하고 난 후, 여기에 커서 놓고 F10 계획 설명
                                        -- FULL scan, COST 893

select * from emp4 where no=466;        -- FULL scan, COST 892

select * from emp4 where sal>300;       -- FULL scan, COST 893

 

 

 - 인덱스 사용 후

create  index emp4_name_idx on emp4(name);

select * from emp4 where name='kim466';

 

 

 이와 같이 COST가 확 내려가는 것을 볼 수 있다.

 인덱스가 개별로 붙었기 때문이다.

 

 

* 두 개의 칼럼을 기준으로 인덱스 생성하기

create index emp4_name_sal_idx on emp4(name, sal);

select * from emp4 where name='kim466' and sal>200; -- RANGE scan, COST 3