얼마전에 필자는 한 지인으로 부터 페이징 처리가 소용이 없을것 같은 쿼리를 봐달라는 요청을 받았다.
SQL 을 보니 WHERE 절에 대해서는 인덱스가 적절하게 잡혀 있었으나 ORDER BY 절에 대해서는
인덱스로 해결될수 있는 성격의 쿼리가 아니었다.
다시말해 ORDER BY 절 대로 인덱스를 생성할 경우 WHERE 절이 다치는 경우가 종종 있는데 그 SQL 이
그런경우 였다..
그 지인은 웹환경에서 결과건수가  1000 건 이상이 될수도 있는 쿼리 임에도 불구하고 "ORDER BY 절 때문에 부분범위 처리가 되지 않으니 페이징 처리가 필요없다"   는 주장이 었다.
얼핏보면 전체범위가 될수 밖에 없으니 맞는말 같지만 그말은 페이징처리 (Oracle 의 Rownum) 의 특성을 모르는데서 기인한다.
페이지 처리나 TOP SQL 등은 인덱스 상황이나 ORDER BY 상황 등의 여부에 따라서 하느냐 안하는냐를 결정하는것이 아니다.
ROWNUM 처리는 무조건 하는것이 이득이다.
그이유는 3가지이다.

1.전체건을 client 로 다가져온뒤에 다버리고 첫번째 페이지만 보여주는것은 비효율적이다.
  DB 입장에서도 전체건을 fetch 하는 비효율을 범했고 client 측에서도 filtering 해서 첫화면만 보여주는 Logic이
  추가되어야 하기 때문이다.

2.전체건을 다가져오게되면 DB 에서 페이지 처리되어 첫번째 화면의 데이터만 가져오는경우와 비교해보면
  네트웍의 전송량이 많아진다.

3.인덱스가 없는 ORDER BY 에 대해서 페이지 처리(ROWNUM 처리)를 하면 전체범위에 대하여 SORT 를
  수행하지 않고 해당 페이지건만 SORT 한다.

여기서는 1, 2번에 대해서는 논하지 않고  3번문제에 대해서만 논한다.
그러면 ORDER BY 절에 관련된 인덱스도 없는데 어떻게 해당건만 SORT 를 할수 있을까?
그이유는 ORDER BY + ROWNUM  작업은 ROWNUM 이 없는 ORDER BY 작업과는 구현로직이 완전히 다르다는데 있는 것이다.
아래는 ORDER BY + ROWNUM 과 ROWNUM 이 없는 ORDER BY 와의 차이점을 잘보여준다.

테이블 건수가 100만건이고 가장큰값 MAX 10 개를 찾는걸로 가정하면

select ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;

첫번째로 위의 ORDER BY + ROWNUM <= 10 작업은 5단계로 나뉜다.

1. 맨처음 10 건을 읽어서 SORT 한후 배열에 저장한다.
2. 11건 째부터는 테이블의 값과 배열의 값을 비교한다.-->테이블의 값과 배열에서 값이 가장 작은값과
   큰지 작은지 비교한다.
3. 비교후 작으면 버린다. --> 이경우 추가작업 없음.
4. 비교후 크면 기존의 배열에서 MIN 인건을 버리고 새로 찾은건을 10 개 내에서만 SORT 하여 배열에서
   자신의  위치를 찾아서  적재한다.  
5. 2~4 번을 100 만번 반복한다.


select ...
from T
ORDER BY unindexed_column;

두번째로 ORDER BY 만 하는작업은 위의 첫번쨰 예제에서 1~ 3번에 해당하는 작업이 없다.

1. 1~3번 작업(버리는건)이 없으므로 10 건만 SORT 하는것이 아니라 배열에 있는 전체건에 대해서
   SORT 하여 자신의 위치를 찾아서 적재한다.
2. 1번을 100 만번 반복한다.

위의 가설을 증명하기위한 예제가 아래에 있다.
먼저  from 절의 테이블 T 는 어떤 테이블이라도 상관은 없으나 대용량 일수록 차이가 크다.
또한 order by 절의 컬럼은 인덱스에 없어야 한다.(있으면  sort order by 가 되지 않는다.)
그리고 테스트를 위하여 PL/SQL 이 필요하다.

1.먼저 trace 나 10046 이벤트를 활성화 한다.

2. ORDER BY + ROWNUM 조합 테스트

select ...
bulk collect into ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;


3. ORDER BY ONLY 테스트

select ...
bulk collect into ... limit 10
from T
ORDER BY unindexed_column;

4. 2개의 Tkprof 보고서를 비교해보면 아래처럼 실행시간은 물론이고 sort order by 시 메모리 사용량 차이가 엄청난걸 알수 있다.

1) ORDER BY + ROWNUM 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      COUNT STOPKEY (cr=27065 r=26550 w=0 time=9537102 us)
... 이하생략

2) ORDER BY ONLY 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      SORT ORDER BY (cr=27065 r=45303 w=31780 time=29061743 us)
... 이하생략


결론 :
첫번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 10건만 SORT 한다.(그나마 버리는건은 SORT 가 없다)
두번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 백만건을 SORT 한다.
이 두가지의 차이는 어떤경우에서든 확연히 들어난다는걸 기억하자.

Reference : Effective Oracle by Design

신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://www.jjanglive.com/hompy/ljh1148 BlogIcon 이재현 2010.12.24 11:18 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요..

    한가지 궁금한점이 있습니다.

    ROWNUM 을 사용하면 해당 로우를 픽스 해야만 소트 부하를 감소 시킬수 있다는 건 잘 알겠습니다.

    물로 부분범위 처리도 가능하다는 것도요..

    그런데 ROWNUM으로 해당 로우를픽스 시킬수 없는 경우에는 어떻게 하는게 좋을까요??

    우선 ROWNUM 으로 10건을 가져오고 추가적인 테이블 스켄을 더하는게 효율적인가요??

    아님 그냥 한번 스캔을 해서.. 전체 소트 시키는게 효율적인가요??

    가져오는 양은 그때 그때 달라요.. 아직 2만건 이상은 넘지 않습니다.( 우선 전체로 소트 방식으로 해놓고 나중에 테이타가 많

    아지면 ... 고려 할 상황인것 같아서요 )

    항상 감사하는 맘으로 블로그 구독하는 구독자가...

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.12.25 00:44 신고  댓글주소  수정/삭제

      모든건을 한번에 보고자 할때는 부분범위처리가 불리합니다. 나중에 건수가 많아지면 페이징 처리를 하시는 것이 바람직 합니다.

      즐거운 연말보내세요.
      감사합니다.