SQL - INDEX 데이터검색속도 높이기(2) order by 없이 index 정렬 (desc 도 가능!)

인덱스를 이용한 튜닝방법중에 정렬을 일으키는 SQL 튜닝

• order by 절을 대용량 데이터때 사용하게 되면 성능이 아주 느려진다.
• 따라서 order by 절 사용을 가급적 자제하는게 좋다.
• 따라서 order by 절을 안쓰고 데이터를 정렬하는 방법이 있는데 그게 바로 인덱스를 활용하는것이다.
• 인덱스는 생성할때만 정렬하고 그이후에는 정렬된 데이터가 인덱스의 구조로 이미 저장되어있으므로 인덱스에서 데이터를 차례대로 읽어오면 정렬된 결과를 볼 수 있다.

 

튜닝전

select ename,sal
from emp
order by sal asc;

튜닝후

create index emp_sal_idx
on emp(sal);

→ 인덱스를 생성시키거나, 생성되어있다면

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

→ order by 절을 쓰지않고 index 의 조건만 적어주게 되면 자동적으로 asc 하게정렬된다.

order by desc 없이 index 로 desc 한 결과를 내는법

튜닝전

select ename , sal
from emp
order by sal desc;

튜닝후 (desc 은 힌트를 줘야 적용이 된다)

select /**+ index_desc(emp emp_sal_idx) **/ ename , sal
from emp
where sal >= 0 ;

→ index_sac 힌트는 인덱스를 ascending 하게 스캔
→ index_desc 힌트 는 인덱스를 descending 하게 스캔

• index_desc(테이블 인덱스명) 으로 작성 하면 된다.
• 그러나 반드시 힌트와 함께 where 절에 어느 컬럼에 인덱스를 사용하는지에 대한 검색 조건을 입력해야한다.

 

532 튜닝하시오

튜닝전

select ename, sal, job
from emp
where job = 'SALESMAN'
order by sal desc;

튜닝후

select /*+ index_desc(emp emp_sal_idx) */ ename, sal, job
from emp
where job = 'SALESMAN'
         and sal >= 0 ;

sal index 를 힌트 입력하였는데 where 절 에 sal 에 대한 내용이 없으므로 sal 전체를 다 출력 하겠다는 조건문을 입력해 주어야 한다. 

533 튜닝하시오

튜닝전

select ename, hiredate
from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD') AND
                       to_date('1981/12/31','RRRR/MM/DD')
order by hiredate desc;

튜닝후

select /*+ index_desc(emp hiredate_idx) */ ename, hiredate
from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD') AND
                       to_date('1981/12/31','RRRR/MM/DD');

→ 위의 문제와는 다르게 이미 where 절에 인덱스를 사용할 컬럼의 조건을 적었기 때문에 굳이 and hiredate < to_date~~~~ bla bla 쓸 필요없다

534 튜닝하시오

튜닝전

select ename, job, sal
from emp
where job like 'SALES%'
order by sal desc;

튜닝후

select /*+ index_desc(emp emp_sal_idx) */ ename, job, sal
from emp
where job like 'SALES%'
and sal >= 0 ;

535 emp14 에 걸려잇는 인덱스 모두 삭제

select index_name
from user_indexes
where table_name ='EMP14';

→ 조회후

drop index emp14_age_idx;

→ 삭제