index를 사용하여 쿼리를 튜닝해보자

아래와 같은 비효율적인 쿼리를 바꿔보자

select deptno, max(sal)
from emp
where deptno = 20
group by deptno;

첫번째 방법

MVIEW(Materialized view)를 이용한다.
일반 VIEW는 SELECT문이 저장되지만, MVIEW와 같은 경우는 SELECT문의 결과값이 물리적으로 디스크에 저장된다.
그래서 데이터가 바뀌거나 하면 다른 결과를 얻을 수 있다.
-> 이 문제를 해결하기 위해서 DBMS_MVIEW패키지를 이용하여 Refresh할 수 있다.
    사용방법은 여기를 참조할 것

create materialized view v1
as
select deptno, max(sal) as max_sal
from emp
group by deptno;

잘 되는지 확인해보자

select * from v1;

 

두번째 방법

index를 사용한다.

create index emp_deptno_sal_idx on emp(deptno, sal);

위와 같은 index는 다음과 같은 구조를 가진다.

select deptno, sal, rowid
from emp order by 1, 2, 3;

 수정된 쿼리는 다음과 같다.

select /*+ index(emp emp_deptno_sal_idx) */
 deptno, sal
from emp
where deptno = 20
and rownum = 1;

index를 내림차순으로 읽도록 변화

select /*+ index_desc(emp emp_deptno_sal_idx)*/
 deptno, sal
from emp
where deptno = 20
and rownum = 1;

이하는 function내에 정의해서 사용자 정의함수로 사용. 아래의 사용자 정의함수는 oracle이 제공하는 single row function과 같다.

create or replace function uf_emp_max_sal
  (p_deptno emp.deptno%type)
  return emp.sal%type
is
  v_sal emp.sal%type;
begin
  select /*+ index(emp emp_deptno_sal_idx) */
 sal into v_sal
  from emp
  where deptno = 20
  and rownum = 1;

  return v_sal;
end;
/

제대로 되는지 확인해보자

select deptno, dname, loc, uf_emp_max_sal(deptno) as max_sal
from dept;

참고 : mview : http://kr.forums.oracle.com/forums/thread.jspa?threadID=453711