오라클 페이징 쿼리에 대해.

간만에 대형 월척을 낚았다.

현재 페이징 쿼리를 만들어주는 메서드를 보면

       buffer.append("select * from (select rownum as row_num, ")
              .append(getRemoveAlias(tblField))
              .append(" from (select ")
              .append(tblField)
              .append(" from ")
              .append(tblName);
        if(wh!=null && wh.length()>0) {
          buffer.append(" where ")
                .append(wh);
        }       
        buffer.append(" order by ")
              .append(ob);
        if(isDesc) buffer.append(" desc)) ");
        else buffer.append(" asc)) ");
        buffer.append(" where row_num <= ")
              .append((currentPage)*pageSize)
              .append(" and row_num > ")
              .append((currentPage-1)*pageSize);

와 같이 가져온다.

간단히 말해 안쪽에 있는 쿼리에서 데이터를 전부 가져와서는 맨 바깥에서 row_num 으로 페이지를 자른다.
이렇게 되면 문제가 테이블에서 건수가 100만건이라 가정하면 100만건에 대해 전부 select 해서 rownum 을 전부 매겨서 맨 마지막에 페이지를 자르니, 부하가 많아져서 쿼리 속도가 엄청 늦다.

이렇게 하면 안되고, 안쪽에서 미리 페이지 수에 맞는 만큼( 1페이지면 10개, 2페이지면 20개 등 ) 가져오고 마지막에 row_num 으로 잘라준다.

       buffer.append("select * from (select rownum as row_num, ")
              .append(getRemoveAlias(tblField))
              .append(" from (select ")
              .append(tblField)
              .append(" from ")
              .append(tblName);
        buffer.append(" where rownum <= ")
              .append((currentPage)*pageSize);
        if(wh!=null && wh.length()>0) {
          buffer.append(" and ")
                .append(wh);
        }       
        buffer.append(" order by ")
              .append(ob);
        if(isDesc) buffer.append(" desc)) ");
        else buffer.append(" asc)) ");
        buffer.append(" where row_num > ")
              .append((currentPage-1)*pageSize);

이렇게 수정해준다. 라고 생각하면 큰 오산이다.
왜냐면, rownum 은 select 를 해오고, sort 를 하기전에 붙여진다. 즉 위처럼 하면 order by 하기전에 임의로 rownum 이 붙여지고는 order by 된다.

따라서

       buffer.append("select * from (select rownum as row_num, ")
              .append(getRemoveAlias(tblField))
              .append(" from (select ")
              .append(tblField)
              .append(" from ")
              .append(tblName);       
        if(wh!=null && wh.length()>0) {
          buffer.append(" where ")
                .append(wh);
        }       
        buffer.append(" order by ")
              .append(ob);
        if(isDesc) buffer.append(" desc) ");
        else buffer.append(" asc) ");
        buffer.append(" where rownum <= ")
              .append((currentPage)*pageSize)
              .append(" ) ");
       
        buffer.append(" where row_num > ")
              .append((currentPage-1)*pageSize);

이렇게 수정해준다.

"아니 이렇게 하면 전체를 order by 할 때 비용이 많이 들지 않냐"고 궁금해하면 훌륭하다!
order by 되는 곳에 index를 적절히 걸어주면 되고, 맨 안쪽에서 select 를 할 때 그 인덱스를 타기 때문에 성능은 그리 떨어지지 않는다.

참고할만한 페이지는
http://www.oracle.com/technology/global/kr/oramag/oracle/06-sep/o56asktom.html
에 있다.