시소당

오라클 rownum 과 rank() over를 사용한 리스트 페이징(paging) 방법

테스트 환경

ORACLE 9.2.0.1.0

USER - SH

TABLE - SALES (건수 백만건 이상 1,016,271)

성능비교 테스트 후 선택하자. 

약 10초 정도 소요.

***************1. rownum********************************************

 select *
  from -- for paging number
      (select rownum no, prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold
       from --for numbering
            (
            select prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold
            from
                 (select t.prod_id,t.cust_id,t.time_id,t.channel_id,t.promo_id,t.quantity_sold,t.amount_sold
                  from sales t) vTable --view
            where vTable.Quantity_Sold =47 --condition    --1827 results
            order by prod_id -- order by
            )
       )
  where no>=16 and no <=30

***************2. rank() over*******************************************

 select *
  from
      (     select rank() over(order by  prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold) as no, prod_id,cust_id,time_id,channel_id,promo_id,quantity_sold,amount_sold
            from
                 (select t.prod_id,t.cust_id,t.time_id,t.channel_id,t.promo_id,t.quantity_sold,t.amount_sold
                  from sales t) vTable
            where vTable.Quantity_Sold =47 --1827 results          
       )
  where no>=16 and no <=30