Oracle의 ROWNUM과 INDEX를 이용한 Paging

Oracle의 ROWNUM과 INDEX를 이용한 Paging ORACLE 2009/04/10 16:16 작성자: 베레(lsj403) http://blog.naver.com/lsj403/memo/77722428 Oracle의 ROWNUM과 INDEX를 이용한 Paging 딱딱한(?) 놈들과 3년 정도 놀다가 다시 최상위 어플리케이션으로 올라왔더니 모르는 건 많고  공부해야될것도 많고... ... 하지만 집에가서 TV 리모콘 만지면 시간이 2시간이 휙가고..  어느덧 잘시간이고 의지 박약.. 아무튼 진작에 알고 넘어갔어야 할 내용을 요령만 피우다가 반년만에 정리를 해본다. 여전히 개념이 아리까리(?) 하기때문에 이렇게 퍼온글로 대신한다. 읽어보니 아주 쉽게 설명을 해준것 같다. ㅎㅎ --------------------------------------------------------------------------- Mysql 의 limit란 넘이 있다... 유용한 기능이다... 예로 limit 10  -----> 처음부터 10개까지를 의미 limit 1,10 -----> 2번째부터 10개를... limit 1,-1-----> 2번째부터 마지막까지를 의미한다... ----------------------------------------------------- limit 시작인덱스,가져올갯수 이 구문을 오라클에서 쓰려고 노력한 적이 있다... 방법은 여러가지 존재한다.... 간단히 다음과 같이 쓸 수 있다. SELECT * FROM (select a.*, rownum as rnum from 테이블명 a) where rnum between 처음지점 and 끝지점;   But, 이렇게 쓸 경우, 자신이 원하는 결과를 얻지 못할 것이다. ROWNUM은 가상컬럼이다.. 물리적으로 저장시킨 넘이 아니기 때문이다. 만약 A라는 테이블에 num이라는 기본키가 있을 때, num이 순차적으로 1부터 20까지 insert 되어있다고 가정하자 num을 5개 단위로 끊어서 select 쿼리를 위와 같은 식으로 한다면     1. SELECT num FROM (select num, rownum as rnum from 테이블명) where rnum between 1 and 5; 2. SELECT num FROM (select num, rownum as rnum from 테이블명) where rnum between 6 and 10;   이것을 순차적으로 실행해보면 알것이다... 사용자는 첫번째 결과를 1에서 5까지를 두번째는 6에서 10까지를 얻기를 원한다... 하지만 결과는 그리 않 나올 것이다... 뒤에 order by 옵션을 붙여도 마찬가지이다....이유는 가상 컬럼인 ROWNUM만을 이용하려 하기 때문이다..... 하나의 SQL구문 때마다 생성되는 일련번호이다...   내가 원하는 잘 정리된 결과를 얻기 위한 방법은???   ---> index를 이용하는 방법이다... num이 기본키이므로 당연 unique 인덱스가 생성되어있다.. 이것을 이용하자   1.SELECT * FROM (select /*+index_desc(a 인덱스명)*/ rownum rnum, a.* from 테이블명 a where num>0 and rownum<=5) where rnum>=1; 2.SELECT * FROM (select /*+index_desc(a 인덱스명)*/ rownum rnum, a.* from 테이블명 a where num>0 and rownum<=10) where rnum>=6; ...............................   이렇게 쓰면 1부터 5를 6부터 10을 .........   잘 정돈된 결과를 얻을 수 있다.... ㅎㅎ [출처] Mysql 의 limit를 Oracle의 ROWNUM과 INDEX로 구현|작성자 난나다 ------ 검색을 통해서도 좋은 글이 보여서.. 퍼온다 질문은 오라클인데 SQL은 MySQL문법이군요. 각 DBMS마다 문법이 차이가 있습니다...기본적인 문법은 숙지하고 사용해야 합니다.   일단 오라클은 Limit 없습니다. 단순하게만 생각하면 페이지 처리는 rownum으로 대치하는게 가장 비슷합니다. SELECT * FROM(         SELECTROWNUM AS rn, a.*     FROM(         SELECT * FROM board  ORDER BY ref desc, re_step asc         ) a     ) WHERE rn BETWEEN ? AND ?   하지만 MySQL에서는 페이지 처리 시 order by...Limit 처럼 일단 전체범위를 Access하여 sort하고 잘라버리는 비효율적인 방법 외에는 페이징방법이 없습니다.   오라클은 이런 경우 인덱스 부분범위처리방법을 이용하여 페이징처리 시 비약적인 성능향상을 꾀 할 수 있습니다. MySQL과 오라클은 근본은 RDB로 같지만 대용량을 지원하는 솔루션의 깊이는 다르다는 말입니다.오라클을 쓰려면 오라클에 맞게 이론 및 원리를 숙지하고 사용하는게 맞다고 생각합니다. 보통 데이터 건수가 비교적 많아서 페이지 처리를 하려고 하는 것인데 전체를 access하고 sort하는 방법은 분명히 서버에 많은 부담을 주는 처리방법임을 명심하세요.   "인덱스부분범위 처리"의 기본적인 절차는 아래와 같습니다. 참고하세요. 1. 테이블에 ref desc, re_step asc로 시작하는 인덱스가 있는지 확인하고 없다면 이런 인덱스를 (a_idx라고 가정) 만든다. 2. 옵티마이저 힌트를 사용하여 이 인덱스로 access path를 유도하여 rownum의 방법으로 인덱스만 부분범위로 scan하여 가져온다.   직관적인 이해를 위해 입력변수를 :start, :end로 만들겠습니다. 인덱스 부분범위처리방법의 쿼리예시는 아래와 같습니다. SELECT * FROM(     SELECT /*+ INDEX(a a_idx) */ --> 옵티마이저힌트         ROWNUM as rn, a.*     FROM board a     WHERE ROWNUM <= :end --> scan stopkey 처리...이 때문에 부분범위로 되는 것임.     ) WHERE rn BETWEEN :start AND :end --> 주의 : 오라클 psuedo-column인 rownum은 항상 1을 포함하여야 하기 때문에 안에서 rn처럼 구체적인 컬럼으로 만들어 밖에서 나머지 조건들을 filtering해야 함.   order by 등의 구문이 없으니 대량데이터의 sort가 없어 훨씬 효율적인 방법입니다.   건승하시길...수고하세요~~