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

2009. 7. 9. 18:45 from DB/ORACLE
간만에 대형 월척을 낚았다.

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

       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
에 있다.


저작자표시 비영리 동일조건
Posted by 알 수 없는 사용자 :

오라클 파티션 테이블

2009. 7. 1. 11:12 from DB/ORACLE
http://www.oracleclub.com/lecture/1908
후배가 SM하다가 언놈이 로그테이블 날려먹었다고 SBSB 한후에 아는거 있냐고 해서
파티션 테이블이라는걸 처음 봤네요

일단 생성쿼리는 다른 테이블과 동일하고 마지막줄에
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
       (PARTITION sales_q1 VALUES LESS THAN (2005, 01, 01) TABLESPACE ASSM_TBS1,
        PARTITION sales_q2 VALUES LESS THAN (2005, 07, 01) TABLESPACE ASSM_TBS2,
        PARTITION sales_q3 VALUES LESS THAN (2006, 01, 01) TABLESPACE ASSM_TBS3,
        PARTITION sales_q4 VALUES LESS THAN (2006, 07, 01) TABLESPACE ASSM_TBS4 );
와 같이 추가적인 정의를 적어줍니다.
range에 포함된 필드값에 따라 저장될 파티션이 정해지죠
위의경우 20050101 보다 작은값은 sales_q1에, 20050701보다 작은값은 sales_q2에 저장되는 식입니다.

select는
SELECT sales_no FROM sales PARTITION (sales_q1); 하면 20050101 이하의 값들이 저장된 데이터만 조회하겠죠

처음에 로그테이블을 날렸다고 했듯 자료가 무진장 쌓이고 기간별로만 사용하는 데이터를 저장할때는 사용해도 좋을듯 싶습니다.

결론은 테이블의 저장데이터범위에 따른 파티션을 설정하고 입력은 하던대로, 조회는 파티션별로.

우리회사에서 쓸일은 없겠지만 다른회사 개발자가 덜컥 얘기 꺼냈을때 포커페이스를 유지하시려면 알아두세요
저작자표시 비영리 동일조건
Posted by 윤재현 :

4자리 문자를 시간:분 형식으로 셀렉트

2009. 5. 19. 15:42 from DB/ORACLE
SELECT REPLACE(TO_CHAR('1', '00,00'), ',' ,':' )
FROM DUAL
-> 00:01 출력
데이터 베이스에 varchar 형식으로 저장된 필드를
시간:분 형식으로 select 하려는데 TO_CHAR 형식은 . 또는 , 등의 숫자형 구분자만을 허용하므로
일단은 허용되는 구분자로 포맷 후 replace를 이용해서 원하는 구분자로 변환
저작자표시 비영리 동일조건
Posted by 윤재현 :

오라클 쿼리관련 힌트.

2009. 3. 2. 14:15 from DB/ORACLE
중요 Hint
1. Select --+ RULE empno, ename from emp where empno=7788
한 SQL문 블록에 대해 RBO 사용

2. Select /*+ALL_ROWS*/ empno, ename from emp where empno=7788
총자원 소비의 최소화 가장 좋은 처리 성능을 목표로 SQL문 블록을 최적화하기 위해 CBO 접근 선택

3. Select /*+FIRST_ROWS*/ empno, ename from emp where empno=7788
첫 번째 행을 찾는 최소의 자원 사용 CBO 접근

4. Select /*+CHOOSE*/ empno, ename from emp where empno=7788
엑세스되는 테이블의 통계 정보 존재 여부에 따라 옵티마이저로 하여금 RBO, CBO 중 하나를 선택

5. select /*+FULL(a)*/ empno, ename from emp a where empno=7788
해당 테이블의 Full Table Scan을 유도

6. select /*+ROWID(EMP)*/ empno, ename from emp a where rowid > 'AAAAtkAAABFV'
지정된 테이블의 Rowid를 이용한 검색을 유도

7. select /*+CLUSTER(EMP)*/ empno, deptno from emp, dept where deptno=10 and emp.deptno = dept.deptno
지정된 테이블 Access에 Cluster Scan을 유도

8. select /*+INDEX(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno=10
지정된 테이블 Access에 Index Scan을 유도

9. select /*+INDEX_ASC(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno<10
지정된 Index의 오름차순 Scan을 유도

10. select /*+INDEX_DESC(EMP EMPNO_INDEX)*/ empno, ename from emp where deptno>10
지정된 Index의 내림차순 Scan을 유도

11. select /*+INDEX_COMBINE(EMP SAL_BMI HIREDATE_BMI)*/ sal, hiredate from emp where sal < 50000
Index 명이 주어지지 않으면 옵디카이저는 해당 테이블의 Best Cost로 선택된 Boolean Combination Index를 사용
Index 명이 주어지면 특정 Bitmap Index의 Boolean Combination의 사용을 시도

11. select /*+INDEX_JOIN(EMP SAL_BMI HIREDATE_BMI)*/ sal, hiredate from emp where sal < 50000
인덱스 조인을 유도한다. 효과적이기 위해서는 중분히 작은 수의 인텍스들이 쿼리를 풀기 위해 필요한 모든 컬럼을 가져야 함.

12. select /*+INDEX_FFS(EMP EMP_EMPNO)*/ empno, ename from emp where empno > 10
조회하는 컬럼들이 인덱스 내에 모두 있는 경우 사용 multi block I/O를 하므로 성능이 좋음

12. select /*+NO_INDEX(EMP EMP_EMPNO)*/ empno, ename from emp where empno > 10
지정된 테이블의 인덱스 중에서 제외 시킬 Index 지정

13. /*+AND_EQUAL(table index1, index2...) */
Sing Colum Index의 Merge를 이용한 Access Path를 선택

14. select /*+USE_CONCAT*/ empno, ename from emp where empno > 10 OR
where 절의 OR 조건을 Union ALL 형식으로 변형

15. NO_EXPAND
where 절에서 OR 절이나 IN-LIST 절의 전계를 CBO 모드에서 사용하지 않게 함

16. /*+ORDERED*/
from 절에 기술된 테이블 순서대로 Join이 일어나도록 유도

17. /*+ORDERED USE_NL(table, table)*/
테이블의 Join 시 드라이빙 테이블의 각 Row가 드리븐 테이블을 NESTED LOOP 형식으로 조인

18. /*+USE_MERGE(table, table)*/
지정된 테이블의 조인이 Sort Merge 조인으로 처리되도록 유도

19. /*+USE_HASH(table, table)*/
각 테이블 간에 HASH JOIN이 일어나도록 유도

20. /*+STAR*/
STAR QUERY PLAN이 사용가능하다면 이를 이용하기 위한 HINT

21. /*+DRIVING_SITE(table)*/
쿼리의 실행이 다른 Site에서 일어나도록 유도(DBLINK 사용하여 Remote Server와 조인시)

22. /*+LEADING(table)*/
조인 순서에서 지정된 테이블이 Driving 테이블이 되도록 함. Ordered 힌트와 같이 사용되는 경우 Leading 힌트는 무시

23. /*+FULL(DEPT) CACHE(table)*/
전체 범위로 읽은 Table을 데이터베이스 버퍼의 LRU 리스트 앞쪽에 두어 버퍼에 오래 남아있을 확률을 높인다. 자주 액세스되는 적은 크기으 테이블에 유용

24. /*+NOCACHE(table)*/
버퍼에서 제거되는 확률을 높이게 하는 힌트로 사용 빈도가 적고 큰 크기의 테이블에 사용

Posted by 알 수 없는 사용자 :

티스토리툴바