SQL INDEX

INDEX

: 검색속도를 높이는 데이터베이스의 오브젝트

  데이터가 매일매일 테라급으로 쌓이고 있기 때문에, 테이블 크기가 점점 대용량이 되어가고 있다.

  그래서 데이터를 검색할 때 시간이 많이 걸리게 된다.

  검색 속도를 높이기 위해서는 SQL 튜닝이 필요

 

Ex) 월급이 1600인 사원의 이름과 월급을 출력 , 실행계획 확인

select ename, sal

from emp

where sal = 1600;

-> Table Access Full 이라는 것을 볼 수가 있다.

    이는 Full table scan을 하고 있다는 뜻이다.

 

여기서 인덱스가 없다는 의미는, 책의 목차가 없다는 것과 같은데, 쉽게 말해서

책의 목차가 없는 책에서 원하는 내용을 찾고자 할 때 책 전체를 훑어야한다.

 

이러한 것을 방지하기 위해서 인덱스를 생성한다.

 

Ex2) 사원 테이블의 월급이 인덱스를 생성

Create index 인덱스 이름 on 테이블(컬럼)

 

Create index emp_sal on emp(sal);

 

Ex3) 월급이 1600인 사원의 이름과 월급 실행계획

explain plan for

select ename, sal

from emp

where sal = 1600;

 

select * from table(dbms_xplan.display);

계획 순서는 2 -> 1 -> 0 순이며 INDEX RANGE SCAN이라는 것을 확인할 수가 있다.

 

Ex4) emp 테이블의 rowid를 확인한다.

Rowid는 테이블의 행(row)의 물리적 주소

File# + block# + row#로 되어져있고 unique한 값을 갖는다.

 

select rowid, empno, ename, sal, job from emp;

하나의 데이터를 검색하는 가장 빠른 방법은 rowid에 의한 데이터 검색이 가장 빠르다.

 

Ex6) rowid가 AAAR/MAAHAAAARmAAC인 데이터 모든 컬럼 조회

select * from emp

where rowid = 'AAAR/MAAHAAAARmAAC';

 

Ex7) emp_sal 인덱스의 구조를 확인한다

인덱스는 구조가 컬럼명 + rowid로 되어있다.

컬럼명 -> 색인 단어 / rowid -> 페이지번호

인덱스의 컬럼명이 ascending하게 정렬되어 있다.

 

select sal, rowid
from emp
where sal>=0;

인덱스에서 읽어온 것이기 때문에, order by절이 아니여도 정렬이 된 결과를 볼 수가 있다.

 

explain plan for

select sal, rowid

from emp

where sal>=0;

select * from table(dbms_xplan.display);

 

 

오라클 힌트

: sql을 실행계획을 만드는 옵티마이저에게 실행을

  이렇게 했으면 좋겠다라고 실행계획에 관여하는 명령어

 

   힌트 사용하는 곳 -> select /*+ 힌트 */ 컬럼명,….

 

 

인덱스 Function 생성

 

Index_asc(emp emp_sal) ?

Index_asc(테이블명 인덱스 이름)

Emp 테이블에 emp_sal 인덱스를 ascending 하게 스캔

 

Index_desc(emp emp_sal)?

Index_desc(테이블명 인덱스 이름)

Emp 테이블에 emp_sal 인덱스를 descending 하게 스캔

 

create index 테이블 이름 on 테이블(함수(컬럼))

---

문제 374. 사원 테이블의 이름에 인덱스를 걸고 이름의 인덱스에 구조를 확인하여, 이름이 abcd 순으로 정렬되어

             있는 지 확인하세요.

 

create index emp_ename on emp(ename);

select ename, rowid

from emp

where ename > ' ';

create index emp_ename on emp(ename);

select ename, rowidfrom emp
where ename > ' ';

- 공백 문자 입력 시 큰 것은 다 나온다.

 

 

문제 375. 이름이 scott인 사원의 이름과 월급과 직업을 출력하세요

Explain plan for

select ename, sal

from emp

where ename = 'SCOTT';

select * from table(dbms_xplan.display);

Explain plan for
select ename, salfrom emp
where ename = 'SCOTT';
select * from table(dbms_xplan.display);

 

문제 376. 사원 테이블에 입사일에 인덱스를 거세요

create index emp_hiredate on emp(hiredate);

create index emp_hiredate on emp(hiredate);

 

문제 377. emp_hiredate 인덱스의 구조를 확인하세요

select hiredate, rowid

from emp

where hiredate < to_date('9999/12/31','RRRR/MM/DD');

select hiredate, rowidfrom emp
where hiredate < to_date('9999/12/31','RRRR/MM/DD');

문제 378. 81년 11월 17일에 입사한 사원들의 이름과 입사일을 출력하는

             sql에 실행계획을 확인하세요

 

explain plan for

select ename, hiredate

from emp

where hiredate = to_date('81/11/17','RR/MM/DD');

 

select * from table(dbms_xplan.display);

explain plan forselect ename, hiredate
from emp
where hiredate = to_date('81/11/17','RR/MM/DD');
select * from table(dbms_xplan.display);

 

문제 379. 아래의 sql이 데이터 검색할 때 index scan하는지 full scan 하는지 확인하고 full table scan 하면 튜닝하세요

select ename, sal,job

from emp

where sal*12 = 36000;

select ename, sal,job
from emp
where sal*12 = 36000;

 

explain plan for

select ename, sal,job

from emp

where sal = 36000/12;

select * from table(dbms_xplan.display);

 

문제 380. 아래의 sql을 튜닝하세요

Select ename, sal, job

From emp where substr(job,1,5) = 'SALES';

 

-> Select ename, sal, job

    From emp where job like 'SALES%';

Select ename, sal, job    
From emp 
where job like 'SALES%';

 

 

문제 381. 아래의 sql 튜닝

Select ename, hiredate

from emp

where to_char(hiredate,'RRRR') = '1980';

 

-> Select ename, hiredate

    From emp

    Where hiredate between to_date('1980/01/01','RRRR/MM/DD')

         and to_date('1980/12/31','RRRR/MM/DD');

Select ename, hiredate    
From emp    
Where hiredate between to_date('1980/01/01','RRRR/MM/DD')          
               and to_date('1980/12/31','RRRR/MM/DD');

 

문제 382. 아래의 sql을 튜닝하세요

 

Select ename, sal,job

From emp

Where job||sal = 'SALESMAN1600';

 

-> select ename, sal, job

    from emp

    where job = 'SALESMAN' and sal = 1600;

 select ename, sal, job    
 from emp    
 where job = 'SALESMAN' and sal = 1600;

 

문제 383. 아래의 sql을 튜닝하세요

(대용량 데이터를 ordering 하면 성능이 느려서 order by 절이 성능이 느린경우에 영향을 미치게 된다)

Select ename, sal, job

From emp

Where job = 'SALESMAN'

Order by sal asc;

 

-> Select ename,sal,job

    From emp

    Where job='SALESMAN' and sal>0;

Select ename,sal,job
From emp
Where job='SALESMAN' and sal>0;

 

문제 384. 아래의 sql을 튜닝하세요

Select ename, sal

From emp

Order by sal desc;

 

Select /*+ index_desc(emp emp_sal) */ ename, sal

From emp

Where sal>0;

Select /*+ index_desc(emp emp_sal) */ ename, sal
From emp
Where sal>0;