(오라클SQL튜닝, 동영상)다중 UNION 대신 CASE WHEN 또는 OR를 사용하자.

(오라클SQL튜닝, 동영상)다중 UNION 대신 CASE WHEN 또는 OR를 사용하자.


----------------------------------------------------------------------------------

-- 다중 UNION 대신 CASE WHEN 또는 OR를 사용하자.

----------------------------------------------------------------------------------


SELECT a.index_name, a.column_name, b.visibility

FROM USER_IND_COLUMNS a, USER_INDEXES b

WHERE a.table_name = 'MYEMP1'

AND a.index_name = b.index_name;


select * from myemp1;


-- 2.5초

select count(ename)

from (

    select empno, ename 

    from myemp1

    where job = 'CLERK'

    and    sal > 90000

    union all

    select empno, ename 

    from myemp1

    where job = 'PROGRAMMER'

    and    ename like '가%'

    union all

    select empno, ename 

    from myemp1

    where job = 'DESIGNER'

    and    addr like '서울%'

) a;

 

f58f1309993a9088ed9b72bd33aa799a_1595934
 


-- 1.8초

select count(ename)

from (

    SELECT empno, ename 

    FROM (

        SELECT empno, ename,

        CASE

            WHEN job ='CLERK' and sal > 90000 THEN 1

            WHEN job = 'PROGRAMMER' and  ename like '가%' THEN 1

            WHEN job = 'DESIGNER' and  addr like '서울%' THEN 1

            ELSE 0 

        END AS chk

        FROM myemp1

    ) sub

    WHERE chk = 1

) a;


 

f58f1309993a9088ed9b72bd33aa799a_1595934
 

 

-- 1.5~1.6초

select count(ename)

from (

    select empno, ename  

    from myemp1

    where (job = 'CLERK' and   sal > 90000)

    or (job = 'PROGRAMMER'  and   ename like '가%')

    or (job = 'DESIGNER'       and   addr like '서울%')

) a;


f58f1309993a9088ed9b72bd33aa799a_1595934

Comments