📔설명

INDEX RANGE SCAN ASCENDING과 INDEX RANGE SCAN DESCENDING를 알아보자!

---

🍺정렬 작업을 하는 SQL

• order by 절
• sort merge join
• create index 문

---

🥨정렬 작업이 DB에 부하를 주는 이유

사용자가 정렬 SQL을 던지면, 서버 프로세스는 정렬을 해서 사용자에게 전달을 해줘야한다.

이때, 정렬작업을 위해 사용하는 공간이 개별 메모리 공간(PGA)인데, 만약 데이터가 엄청 많다면 정렬시 PGA에서 한번에 하지 못한다.

나눠서 정렬해야 하기 때문에 그때 별도의 공간을 사용하는데 DB의 임시 테이블 스페이스를 사용한다. 그러면 디스크 I/O때문에 성능이 나빠지게 된다.

이럴 때 사용하는 것이 index_asc 힌트와 같은 것이 있다.

---

🍣INDEX RANGE SCAN ASCENDING

create index emp_sal on emp(sal);

select /*+ gather_plan_statistics */ ename, sal
from emp
order by sal desc;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
-- 튜닝 전

select /*+ index_asc(emp emp_sal) */ ename, sal
from emp
where sal>0;
--order by 절을 사용하지 않고, index를 활용해 정렬을 함

emp_sal인덱스를 사용해 sal을 asc순으로 정렬한 대로 결과를 들고오는 것이다.
만약 where절이 없다면, index_asc가 작동하지 않게 된다. 아니면 인덱스를 사용할 수 없게 된다.

---

🍔INDEX RANGE SCAN DESCENDING

인덱스를 desc순으로 스캔하는 것이다.

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

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

위에 있던 sort order by가 없어진 것을 확인할 수 있다.

select /*+ gather_plan_statistics */ max(sal)
from emp;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

MIN/MAX함수를 사용했을 때도, 집계 sort가 일어난다.

위의 쿼리문도 desc 힌트를 주면 해결할 수 있다.

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

위의 쿼리 결과가

이렇게 정렬이 되는 것을 볼 수 있다.

여기서, MAX는 5000을 가진 KING만 가져오면 되기 때문에 rownum=1로 주면 된다.

select /*+ gather_plan_statistics index_desc(emp emp_sal) */ ename, sal
from emp
where sal>=0 and rownum=1;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

select /*+ gather_plan_statistics */ ename, sal
from emp
where sal=(select max(sal) from emp);

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

위의 쿼리문도 똑같이 월급이 제일 높은 사람의 이름과 월급을 출력하는 것이므로

select /*+ gather_plan_statistics index_desc(emp emp_sal) */ ename, sal
from emp
where sal>=0 and rownum=1;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

아까와 같이 출력하면 된다.