04 소트가 발생하지 않도록 SQL 작성

 

불필요한 소트가 발생하도록 SQL을 작성하는 경우

 

(1)   Union all

 

SELECT empno,job,mgr FROM emp WHERE deptno = 10

UNION

SELECT empno,job,mgr FROM emp WHERE deptno = 20

;

 

PK 컬럼인 empno를 select-list에 포함하므로 두 집합간에는 중복 가능성이 없다.

따라서 union all을 사용해야 한다.

Union all은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트부하가 없다.

 

*union을 즐겨 사용하는 이유

           Union과 union all과의 처리 방식 차이를 모르기 때문

           집합 개념이 부족해 혹시 중복이 발생할지도 모른다는 불안감 때문

 

(2)   Distinct를 사용하는 경우

대부분 exists 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다.

 

사례) 특정지역에서 특정월 이전에 과금이 발생했던 연월을 조회

 

SELECT DISTINCT 과금연월

FROM   과금

WHERE  과금연월 <= :yyyymm

AND    지역 LIKE :reg || '%'

 

입력한 과금연월 이전에 발생한 과금 데이터를 모두 스캔하는 동안 1,586,208개 블록을 읽었고, 무려 1,000만 건에 가까운 레코드에서 중복 값을 제거하고 고작 35건을 출력.

 

ð  각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 되므로

소량의 데이터만을 갖는 연월테이블을 먼저 드라이빙해 과금 테이블을 exists 서브쿼리로 필터링 하는 방식으로 변경.

 

SELECT 연월

FROM   연월테이블 a

WHERE  연월 <= :yyyymm

AND    EXISTS (

              SELECT 'x'

              FROM   과금

              WHERE  과금연월 = a.연월

              AND    지역 LIKE :reg || '%'

              )

 

l  Exists 서브쿼리의 가장 큰 특징

메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행을 마친다는 점.

따라서 과금 테이블에 [과금연월 + 지역] 순으로 인덱스를 구성해 주기만 하면 가장 최적으로 수행.

 

l  일자 및 연월 테이블을 생성해 두면 활용도가 높다.

CREATE TABLE 일자테이블 AS

SELECT TO_CHAR( ymd , 'yyyymmdd' ) ymd ,

       TO_CHAR( ymd , 'yyyy' ) year ,

       TO_CHAR( ymd , 'mm' ) month ,

       TO_CHAR( ymd , 'dd' ) day ,

       TO_CHAR( ymd , 'dy' ) weekday ,

       TO_NUMBER( TO_CHAR( NEXT_DAY( ymd , '일' ) - 7 , 'ww' ) ) week_yearl

 

FROM   (

        SELECT TO_DATE( '19691231' , 'yyyymmdd' ) + ROWNUM ymd

        FROM   dual

        CONNECT BY LEVEL <= 365 * 100

       )

;

 

CREATE TABLE 연월테이블 AS

SELECT SUBSTR( ymd , 1 , 6 ) yyyymm ,

       MIN( ymd ) first_day ,

       MAX( ymd ) LAST_DAY ,

       MIN( year ) year ,

       MIN( month ) month

FROM   일자테이블

GROUP  BY SUBSTR( ymd , 1 , 6 )

;