|
|
|
![]()
| [일반] not in 보다는 exist 를 많이 사용해보자 |
|
| 호석 님께서 쓴 글입니다 - 121.♡.39.7 |
읽음:2796 |
|
|
not in 에 대한 조건은 효율적이지 못함
SELECT FINDAY
FROM DFIN_BP
WHERE RESTCD = 'RESTCD'
AND FINDAY >= '20110331'
AND FINDAY NOT IN (SELECT DEALDAY FROM STOCK_BP
WHERE RESTCD = 'RESTCD'
AND PRODCD = '상품코드'
AND SEQ = 'SEQ'
AND DEALDAY >= '20110301'
GROUP BY DEALDAY) ;
SELECT FINDAY
FROM DFIN_BP
WHERE RESTCD = 'RESTCD'
AND FINDAYS >= '20110331'
AND FINDAY NOT IN (SELECT /*+ index( STOCK_BP IDX_STOCK_X0 ) */ DEALDAY FROM STOCK_BP
WHERE RESTCD = 'RESTCD'
AND PRODCD = '상품코드'
AND SEQ = 'SEQ'
AND DEALDAY >= '20110301'
GROUP BY DEALDAY) ;
/*+ index( STOCK_BP IDX_STOCK_X0 ) */
-----------------------------------------------------
퀴리튜닝 exist 조건을 주어서 보다 효과적인 쿼리를 구성한다.
SELECT A.FINDAY
FROM DFIN_BP A
WHERE A.RESTCD = 'RESTCD'
AND A.FINDAY >= '20110331'
AND NOT EXISTS ( SELECT 'X'
FROM STOCK_BP B
WHERE B.RESTCD = 'RESTCD'
AND B.PRODCD = '상품코드'
AND B.SEQ = 'SEQ'
AND B.DEALDAY >= '20110301'
AND A.FINDAY = B.DEALDAY
GROUP BY DEALDAY) ;
SELECT * FROM V$MYSTAT ;
// 튜닝정보 -- analyize 보다 아래 프로시져를 돌려서
튜닝하는 편이 좋다고 함
DBMS_STAT.GATHER_SYSTEM_STAT
DBMS_STAT.GATHER_SCHEMA_STAT
DBMS_STAT.GATHER_TABLE_STAT
|
|
|
|
|
|
