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

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

       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 윤재현 :


SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  //date포맷 생성

query = " INSERT INTO TBLTIME ( TIMETEST ) VALUES ( ? ) ";
pstmt = conn.prepareStatement(query);
pstmt.setTimestamp(1 , new Timestamp( sdf.parse("2009-06-01 19:02:33").getTime() )); //TimeStamp 생성 & set
pstmt.executeUpdate();

ORACLE, MSSQL, MYSQL 확인완료;
DB마다 상이한 내장함수를 사용할 필요가 없음;
(ex. TO_DATE() )


psmt.setDate()는 날짜까지만 입력이 되는데... 
원래그런건지... 그이유는..잘.; 
Posted by 알 수 없는 사용자 :

기간검색시 종료일 처리

2009. 5. 22. 10:59 from DB
기간 검색시 시작일 2009-01-01 종료일 2009-01-03의 범위로 검색하게 되면

종료일이 2009-01-03 00:00:00 으로 변환되어 비교되므로 3일의 레코드를 검색할 수가 없어서

종료일에 하루를 더해서 작은것을 검색하도록 쿼리를 정리했습니다.
ORACLE
등록일 < to_date('2009-01-01', 'yyyy-mm-dd') + 1
MSSQL
등록일 < dateadd( day, 1, convert(datetime, '2009-01-01') )
MYSQL
등록일 < date_add(STR_TO_DATE('2009-01-01', '%Y-%m-%d' ) INTERVAL 1 DAY )
가격과 역순의 쿼리길이
Posted by 윤재현 :
SELECT REPLACE(TO_CHAR('1', '00,00'), ',' ,':' )
FROM DUAL
-> 00:01 출력
데이터 베이스에 varchar 형식으로 저장된 필드를
시간:분 형식으로 select 하려는데 TO_CHAR 형식은 . 또는 , 등의 숫자형 구분자만을 허용하므로
일단은 허용되는 구분자로 포맷 후 replace를 이용해서 원하는 구분자로 변환
Posted by 윤재현 :

Mysql Cron설정

2009. 3. 16. 22:48 from DB/MYSQL


cd 백업경로
vi 백업파일
#!/bin/sh
DATE=`date +"%Y%m%d"`
PREV_DATE=`TX=KST+15 date +"%Y%m%d"`
echo ${PREV_DATE}
BACKUP_DIR=/백업경로(/db/backup)
/db.mysql-5.0.60/bin/mysqldump -u karts -pkarts1234 karts > ${BACKUP_DIR}/karts_${DATE}.sql
rm -Rf ${BACKUP_DIR}/karts_${PREV_DATE}.sql

:wq


CRONTAB 설정
crontab -i // 크론 설정을 볼수 있음

crontab -e // 크론 설정 편집
//솔라리스
EDITOR=vi
export EDITOR
crontab -e

편집
*//분*//시*//일*월*//년 실행경로

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 알 수 없는 사용자 :
select date_add(now(), INTERVAL -2 MONTH)

3월 29일에서 한달전 조회하려면 자바에서 calendar객체도 가져와야 하고 귀찮습니다.
DB에서 제공하는 기능을 사용하면 한줄에 땡.

위의 인터넷 뒤에
+- 증가값과 증가단위 YEAR, MONTH, DAY등을 사용하시면 자유롭게 셀렉트 가능!
Posted by 윤재현 :
일대 다 관계의 두 테이블에서
A 테이블의 제목과 B 테이블의 시간을 출력하게 되면
결과물이 B 테이블의 시간 레코드 수 만큼 출력되는데

select
A.ATITLE
,GROUP_CONCAT( B.BTIME SAPARATOR '|' )
from tableA A
left join tableB B on A.seq = B.fseq

와 같이하여 한번에 여러행의 결과를 그룹으로 출력할 수 있다.

A테이블의 데이터가 아래와 같고

ENO, ETITLE
22    테스트
23    테스트2
24    공연
31    테스트
32    인형극 '나의 열살'

B테이블의 데이터가

22    2009-02-05 오후 12:12:00
23    2009-02-15 오전 11:11:00
23    2009-02-16 오후 12:12:00
24    2009-02-12 오전 1:01:00
24    2009-02-13 오전 1:01:00
24    2009-02-14 오전 1:01:00
24    2009-02-15 오전 1:01:00
24    2009-02-16 오전 1:01:00
24    2009-02-17 오전 1:01:00
24    2009-02-18 오전 1:01:00
24    2009-02-19 오전 1:01:00
24    2009-02-20 오전 1:01:00
31    2009-02-12 오후 1:24:00
31    2009-02-14 오후 2:15:00
31    2009-02-16 오후 10:33:00
32    2009-02-28 오후 12:12:00

위와 같다면

select * from(
select 
A.ENO
ETITLE
,group_concat( substr(B.ETDATE, 1, 16) SEPARATOR  ' \\| ' ) as ETDATE
from tblexhibition A
left join 
tblexhibitiontime B on A.ENO = B.ENO
group by A.eno
)E
이런 쿼리를 이용해서

22    2009-02-05 오후 12:12:00
23    2009-02-15 오전 11:11:00|2009-02-16 오후 12:12:00
24    2009-02-12 오전 1:01:00|2009-02-13 오전 1:01:00|2009-02-14 오전 1:01:00 ~~~
형식으로 레코드를 합칠 수 있다.

다른 DBMS에서는 동일한 기능을 수행하는 함수를 찾지 못했음.
Posted by 윤재현 :