Oracle Technical Note DB Tuning Oracle SQL Tuning 실무사례 (9) 데이타웨어하우스아키텍처와컴포넌트이번호에서는배치 (batch) 프로그램에서활용할수있는튜닝방법을소개하고자한다. 일반적으로배치프로그램은최소 1분에서수십시간까지의실행시간을가지는데, 이동안시스템의자원을최대한끌어당겨사용하여처리하므로같이수행되는다른프로그램에미치는영향도크고또실행하는일의양도엄청나다고할수있다. 이렇게엄청난일을하는배치프로그램의내부를들여다보면, 실제처리해야하는데이타의양이많아서오래걸리는경우는어쩔수없지만주로문제가되는프로그램들은동일한유형의 SQL 을루핑 (looping) 을돌리면서반복처리하기때문에많은시간이소요되고있음을알수있다. 이렇게루핑을도는 SQL 의튜닝은그로직을이해한뒤전혀새로운 SQL 을만들어내는방식인로직튜닝으로만가능하며, 이러한 SQL 은주로한두개의 SQL 내에서필요한데이타를모두가져와처리하므로수백, 수천개의낱개의 SQL 을오라클커널이일일이처리하는방식보다수십, 수백배의성능향상을가져올수있다. 특히그처리내용은배치이지만마치온라인프로그램처럼사용하는온라인성배치프로그램인경우에매우유용하게활용할수있다.
사례 1 배치집계테이블을이용한온라인처리 소개 이경우는배치로처리하는합계테이블과온라인으로처리하는테이블을서로같이 사용하여어떤범위의조건이들어와도항상온라인처리속도로결과를화면에표 시하는요구조건을충족시키는사례이다. 집계테이블에는항상지난달인경우맨마지막날짜에그달의합계가들어있고, 이번달인경우항상어제까지의합계가어제날짜로들어있다. 만약오늘이 96 년 4월 27 일이라면집계테이블의데이타는다음과같이들어있다. 집계일자 COL1 COL2 960131 AAA 123 960131 ABC 321 960228 AAA 231 960331 AAA 351 960331 ABC 255 960427 AAA 142 이러한상태로운영하다가 4월 28 일에시스템증설작업관계로매일수행해야하는집계테이블업데이트작업을수행하지못했다. 이러한상황에서오는 4월 29 일현재까지의 SUM(COL2) 을 COL1 별로 GROUP BY 해서보고서를작성해야한다. 독립데이타마트 (independent data mart) 아키텍처는전사적데이타웨어하우스구축없이소수의사용자들 ( 부서별 ) 을위한제한된주제를가지고소규모의데이타마트를하나또는여러개구축하는시스템이다. 주로한주제에대한다양한분석, 예측을위한시스템이기때문에주로 MOLAP 솔루션을사용하는경우가많다. 필요한데이타마트를단기간에구축할수있다는장점이있으나, 직접데이타를운영계시스템에서추출해야하고, 이를주기적으로자동화해야하며, 데이타마트가많아질경우데이타추출에문제가생겨전체시스템관리가어렵게된다는단점도있다. 문제점 4월 28 일과 4월 29 일에집계작업을하지못했으므로집계테이블의데이타를 3월 31 일자료까지만사용하고 4월자료는온라인테이블에서가져와서작업하려고하니온라인테이블의건수가너무많아서수행속도가매우느리다. 또한프로그램에코딩된내용은지난달까지의집계데이타의합과어제까지의합계를더하여사용하도록고정되어있는데, 이번과같이어제의집계작업을하지못한경우에는어제까지의집계작업을먼저해주어야만보고서를작성할수있다. 또한어제까지의합계를온라인테이블에서구하는데, 월초인경우는데이타가많지않아서별문제가없지만월말인경우는온라인테이블의해당건수가많으므로항상문제가되고있다. DB Tuning Oracle SQL Tuning 실무사례 (9) 2
해결방안우선그날의작업마감후일일배치작업으로합계를구하는방식은그전날까지의합계에그날의합계를더하여집계테이블을업데이트하면된다. 그다음현재월에대한해결방안인데, 이는최대한집계테이블의데이타를사용할수있는데까지사용하고나머지를온라인테이블에서구하는방식을사용한다. 여기서집계테이블의배치작업을며칠간하지못했다하더라도자동적으로집계작업이된부분은집계테이블을이용하고나머지는온라인테이블에서찾는방법을알아보자. 그림 1 실제값을변수로받아서처리하는완전한 SQL 문은다음과같다. SELECT COL1, SUM(COL2),...... FROM (SELECT COL1, COL2,.... FROM 집계테이블 WHERE 집계일자 BETWEEN :BEGIN_DATE AND TO_CHAR(SYSDATE,'YYMMDD') UNION ALL SELECT FLD1 AS COL1, FLD2 AS COL2.... FROM 현행테이블 WHERE 처리일자 > (SELECT /*+ INDEX_DESC( 집계테이블집계일자 _IX) */ 집계일자 FROM 집계테이블 WHERE ROWNUM = 1) AND 처리일자 <= TO_CHAR(SYSDATE,'YYMMDD') ) GROUP BY COL1 ; DB Tuning Oracle SQL Tuning 실무사례 (9) 3
사례 2 일별집계테이블에서직접온라인조회소개일일마감배치작업으로처리되는일별통계정보테이블과월별통계정보테이블을동시에이용하여월별평가금액합계를온라인으로직접조회할수있는사례이다. 이전에는이런월별통계자료를보기위해서는오랜시간을기다려야결과를볼수있었지만, 이제부터는화면상에서온라인프로그램과같은응답속도로볼수있다. 월별통계정보에는매월의합계값이 YYMM을 KEY로하여각각의로우에저장되어있고, 일별통계정보에는매일의합계값이 YYMMDD 를 KEY 로하여각각의로우에저장되어있다. 사용자는어떤범위의조건을주더라도자동적으로일별통계정보와월별통계정보테이블을가장효율적으로이용하여평가금액의합계를구하도록한다. 예를들어, 사용자가 1996.1.15부터 1996.5.10까지의조건을주었다면, 1996.1.15부터 1996.1.31까지와 1996.5.1부터 1996.5.10까지의데이타는일별통계정보테이블을이용하고 1996.2와 1996.3과 1996.4 데이타는월별통계정보테이블을이용하여구하도록해야한다. 그림 2 문제점일별통계정보와월별통계정보테이블을각각읽어서그합계를구하여처리할경우온라인프로그램과같은응답속도가나오지않는다. 해결방안 IN-LINE VIEW를사용하여하나의 SQL 문으로처리하는방법을찾아보자. 우선일별통계정보와월별통계정보테이블을사용할범위를서로구분하는것이가장우 DB Tuning Oracle SQL Tuning 실무사례 (9) 4
선되어야한다. 시작일 (:S_DATE) 과종료일 (:E_DATE) 이속하는범위는일별통계정보테이블을사용하고, 시작일의다음달과종료일의이전달까지는월별통계정보테이블을사용한다. 시작일이속하는월의마지막일자를알기위해 LAST_DAY 함수를사용하고시작일다음달을알기위해 ADD_MONTHS 함수를이용했다. 그림 3 이를정확하게 SQL 로구현하면다음과같이된다. SELECT V. 영업월, V. 평가금액합 FROM (SELECT SUSTR( 영업일,1,4) AS 영업월, SUM( 평가금액 ) AS 평가금액합 FROM 일별통계정보 WHERE 지점번호 = :B1 AND ( ( 영업일 BETWEEN :S_DATE AND TO_CHAR(LAST_DAY(TO_DATE( :S_DATE,'YYMMDD')), 'YYMMDD')) OR ( 영업일 BETWEEN SUBSTR(:E_DATE,1,4) '01' AND :E_DATE) ) GROUP BY SUSTR( 영업일,1,4) UNION ALL SELECT 영업월, 평가금액 AS 평가금액합 FROM 월별통계정보 WHERE 지점번호 = :B1 AND 영업월 >= SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE (:S_DATE,'YYMMDD'),1),'YYMMDD'),1,4) AND 영업월 <= SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE (:E_DATE,'YYMMDD'),-1),' YYMMDD'),1,4) ) V ORDER BY V. 영업월 ; DB Tuning Oracle SQL Tuning 실무사례 (9) 5
사례 3 어떤범위의평균잔액이라도온라인으로조회 소개 I이번사례는전형적으로배치작업을통해서만결과를얻을수있었던것을온라인 으로조회할수있도록한통계처리 SQL 문의대표적인형태라하겠다. 집계테이 블없이거래실적테이블의데이타를이용하여바로실시간으로조회하는것이며 일반적인은행에서대표적으로적용되는경우이다. 다음과같이보관된일별거래기 록데이타를이용하여설명한다. 계좌번호 거래마감일 잔액 23-12345 1996.01.08 1000000 23-12345 1996.01.12 600000 37-34567 1996.01.16 250000 23-12345 1996.02.07 800000 37-34567 1996.02.15 1100000 23-12345 1996.02.17 1350000 23-12345 1996.03.18 500000 37-34567 1996.03.30 1200000 23-12345 1996.04.13 1000000 36-34567 1996.04.15 2000000 23-12345 1996.04.17 500000 위와같은일별거래기록을갖고있을때 1996.4.16일에계좌번호 23-12345에대 한 3개월간평균잔액을구하는요구조건이다. 사용자가평균잔액을구하는기간을어떻게지정하더라도자동적으로지정기간의 조건에맞는계좌에대해서만합계를구하여지정기간의일자로나누어평균잔액 을산출해내어야한다. 위와같은경우에평균잔액을구하는과정은다음과같다. 96.04.16 기준3개월해당기간 예치일자 금액 1996.01.16~1996.02.06 21일간 600,000 1996.02.07~1996.02.16 9일간 800,000 1996.02.17~1996.03.17 29일간 1,350,000 1996.03.18~1996.04.12 25일간 500,000 1996.04.13~1996.04.16 3일간 1,000,000 위의표에의거하여 3개월평균잔액을계산하면다음과같다. 1. 1996. 1. 16 ~ 1996. 4. 16 기간의일자수 ; 91일간 2. 3개월간잔액합계 = (21*600000 + 9*800000 + 29*1350000 + 25*500000 + 3*1000000) 91 = 818,131 원 DB Tuning Oracle SQL Tuning 실무사례 (9) 6
문제점일반적인구현형태는다음과같이프로그래밍으로해결하며루프를돌리며처리하므로속도가느리다. SELECT ADD_MONTHS ( SYSDATE, -3 ) INTO :3개월전일자 FROM DUAL; DECLARE CURSOR SELECT 거래마감일, 잔액 FROM 일별거래기록 WHERE 거래마감일 >= ( SELECT MAX( 거래마감일 ) FROM 일별거래기록 WHERE 계좌번호 = '23-12345' AND 거래마감일 <= :3개월전일자 ) AND 계좌번호 = '23-12345' AND 거래마감일 <= SYSDATE ORDER BY 거래마감일 ; OPEN CURSOR FOR ( ; ; ) FETCH CURSOR INTO : 거래마감일, : 잔액 ;................ 해결방안 IN-LINE VIEW를이용하여다음과같이하나의 SQL 문으로해결할수있다. 우선 3개월전일자를기준으로가장가까운과거거래일자를찾아서그잔액을가져온다. 위의데이타를사용하여예를들면, 오늘 4.16일부터 3개월전이라면 1.16일이되는데, 1.16일에거래가없으므로 1.16일기준으로가장최근에거래한 1.12일자를알아내어그때의금액을가져와 1.16일부터계산하는데사용한다. 마찬가지로계산종료일인 4.16일에도실제거래가없으므로가장최근에거래한 4.13일자를알아내어그때의금액을가져와사용한다. 그림 4 DB Tuning Oracle SQL Tuning 실무사례 (9) 7
이렇게하여시작일과종료일의금액을알아내었다면이제는예치기간을알아내야하는데, 이는원래의데이타와원래의행을한칸씩뒤로시프트시킨데이타의 ROWNUM을이용하면알아낼수있다. 그림 5 해결방안이와같은아이디어를갖고구현한실제 SQL 문은다음과같다. SELECT SUM ( (V2. 거래마감일 - V1. 거래마감일 ) * V1. 잔액 ) / (SYSDATE - (ADD_MONTHS(SYSDATE, -3)) ) AS 평균잔액 FROM (SELECT ROWNUM AS RNUM1, 잔액, DECODE(SIGN( 거래마감일 - ADD_MONTHS (SYSDATE,-3) ), >> 1-1, ADD_MONTHS(SYSDATE,-3), 거래마감일 ) AS 거래마감일 FROM 일별거래기록 WHERE 계좌번호 = '23-12345' AND 거래마감일 < SYSDATE AND 거래마감일 >= (SELECT NVL(MAX( 거래마감일 ), ADD_MONTHS(SYSDATE,-3)) >> 2 FROM 일별거래기록 WHERE 계좌번호 = '23-12345' AND 거래마감일 <= ADD_MONTHS (SYSDATE, -3)) ) V1, >> 3 (SELECT ROWNUM AS RNUM2, 거래마감일, 잔액 FROM (SELECT 잔액, DECODE(SIGN( 거래마감일 - ADD_MONTHS(SYSDATE, -3)-1, ADD_MONTHS(SYSDATE,-3), 거래마감일 ) AS 거래마감일 FROM 일별거래기록 WHERE 계좌번호 = '23-12345' AND 거래마감일 < SYSDATE AND 거래마감일 >= (SELECT NVL(MAX( 거래마감일 ), ADD_MONTHS(SYSDATE,-3) ) FROM 일별거래기록 WHERE 계좌번호 = '23-12345' AND 거래마감일 <= ADD_MONTHS(SYSDATE, -3) ) UNION ALL SELECT 0 AS 잔액, SYSDATE AS 거래마감일 >> 4 FROM DUAL ) V2 >> 5 WHERE V1.RNUM1 + 1 = V2.RNUM2 ; DB Tuning Oracle SQL Tuning 실무사례 (9) 8
1은일별거래기록테이블의거래마감일데이타와 3개월전일자 (1996.1.16.) 를비교하여 3개월이내이면거래마감일을가져오고 3개월이전이면정확히 3개월전일자를가져온다. 위의데이타를이용하여예를들어보면, 거래마감일이 1996.02.07이거나 1996.04.13이면그냥그날짜를가져오고만약 3개월범위를벗어나는 1996.1.12일이면 1996.01.16일자로변환하여가져온다. 2는 3개월이전날짜를기준으로가장최근의일자를가져온다. 즉, WHERE 조건이거래마감일 <= ADD_MONTHS(SYSDATE,-3) 이면서 MAX( 거래마감일 ) 이라고하면된다. 이때만약최초거래일이후의기간이아직 3개월이안된고객이있다면 WHERE 조건이 NULL 이되어 V1 전체의결과가 1건도나오지않으므로이를방지하기위해 NVL 을사용하여 NULL일경우 ADD_MONTHS(SYS- DATE, -3) 값으로대치되도록하였다. 3은첫번째 IN-LINE VIEW이며, 4는 V2 IN-LINE VIEW에서 1칸을시프트하기위한것이고, 5는두번째 IN-LINE VIEW이다. 사례 4 GROUP BY를온라인부분범위처리로유도소개이사례는온라인으로대량데이타를조회하여보고자하는경우에전체범위로처리하기때문에첫화면이나오기까지많은시간이걸리는부분을부분범위처리로유도함에따라첫화면이나오는속도를비약적으로향상시켜서응답속도를개선한경우이다. 위와같은상태에서영업지점 (A.BR_CD), 기준일 (B.GIJUN_IL) 이 WHERE 조건으로들어왔을때잔액의합계즉, SUM(B.CUR_BAL) 이입력된기준금액 (:ISUMBAL) 이상인고객에대한리스트를보여달라는조건이다. 다음과같이 SQL 문을작성할수있다. SELECT A.CUST_NAME, SUM(B.CUR_BAL) FROM CMF_CUST A, WDDA_ACCT B WHERE A.ID_NO = B.ID_NO AND A.BR_CD = :IBRCD AND B.GIJUN_IL = :IDATE GROUP BY A.CUST_NAME HAVING SUM(B.CUR_BAL) >= :ISUMBAL ; Excution Plan SELECT FILTER DB Tuning Oracle SQL Tuning 실무사례 (9) 9
SORT ( GROUP BY ) >> 전체범위의원인 NESTED LOOPS TABLE ACCESS ( BY ROWID ) OF 'ICMF_CUST' INDEX ( RANGE SCAN ) OF 'ICMF_CUST_IDX1' TABLE ACCESS ( BY ROWID ) OF 'WDDA_ACCT' INDEX ( RANGE SCAN ) OF 'WDDA_ACCT_IDX1' 문제점 GROUP BY A.CUST_NAME 때문에전체범위로처리된다. 따라서첫화면이나오기까지의수행속도가너무오래걸린다. 해결방안 GROUP BY 절이사용되는부분을없애서부분범위처리가되도록유도한다. GROUP BY 절이없어지면 HAVING SUM (B.CUR_BAL)... 절도같이사용할수없으므로서브쿼리로대치한다. SELECT A.CUST_NAME, GET_SUM(A.ID_NO, :IDATE) FROM ICMF_CUST A WHERE A.BR_CD = :IBRCD AND :ISUMBAL <= (SELECT SUM(CUR_BAL) FROM WDDA_ACCT B WHERE A.ID_NO = B.ID_NO AND B.GIJUN_ IL = :IDATE) ; 이 SLQ 문실행에앞서 GET_SUM이라는 FUNCTION을먼저작성해주어야한다. CREATE OR REPLACE FUNCTION GET_SUM(VIDNO IN NUMBER, VIDATE IN DATE ) RETURN NUMBER IS SUM_BAL NUMBER; BEGIN SELECT SUM(CUR_BAL) INTO SUM_BAL FROM WDDA_ACCT WHERE ID_NO = VIDNO AND GIJUN_IL = VIDATE; RETURN SUM_BAL; END; 이때의 EXECUTION PLAN은다음과같다. Excution Plan SELECT FILTER TABLE ACCESS ( BY ROWID ) OF 'ICMF_CUST' INDEX ( RANGE SCAN ) OF 'ICMF_CUST_IDX1' >> 부분범위처리화 SORT AGGREGATE TABLE ACCESS ( BY ROWID ) OF 'WDDA_ACCT' INDEX ( RANGE SCAN ) OF 'WDDA_ACCT_IDX1' DB Tuning Oracle SQL Tuning 실무사례 (9) 10
해결방안 GROUP BY 절이없어지려면 SUM 등의함수를 MAIN SQL 내에서사용할수없으므로이를따로수행해주어야한다. 이를위해서는 SUM 기능만별도로수행해주는 FUNCTION의사용이불가피하게되므로사전에별도의 FUNCTION을미리만들어두어야한다. 이렇게되면 MAIN 쿼리에서한건이나오면그때마다 FUNCTION을한번씩수행하여 SUM 을구하므로부분범위처리가되어첫화면이나오는속도가매우빨라지게되어사용자가느끼는응답속도는획기적으로개선된다. 그러나전체적으로모든범위의데이타를처리하는데는매건마다 FUNCTION을한번씩수행하므로 GROUP BY를사용하는것보다느리다. 따라서이와같은 SQL 은반드시첫화면의응답속도를중요하게여기는온라인프로그램과같은분야에서만사용되어야하고배치형태의처리는당연히 GROUP BY를사용하여처리하는것이더빠르다. 여기서하나더생각할수있는것은 FUNCTION을사용하지않기위해 IN- LINE VIEW를하나더씌워다음과같이 SUM(CUR_BAL) 을할수도있다. SELECT V1.CUST_NAME, SUM(CUR_VAL) FROM WDDA_ACCT C, (SELECT ID_NO, CUST_NAME FROM ICMF_CUST A WHERE A.BR_CD = :IBRCD AND :ISUMBAL <= (SELECT SUM(CUR_BAL) FROM WDDA_ACCT B WHERE A.ID_NO = B.ID_NO AND B.GIJUN_IL = :IDATE)) V1 WHERE C.ID_NO = V1.ID_NO GROUP BY V1.CUST_NAME ; 그러나결국위의 SQL 문도맨마지막에 GROUP BY 절이들어가지않으면안되기때문에전체범위로처리되게되므로답은같이나올지모르지만원하는부분범위처리가되지못한다. 결론적으로 GROUP BY 문을없애고이를대신할 SUM 을구하는 FUNCTION을사용해야만하며이렇게하면전체범위처리를부분범위처리로유도할수있다. DB Tuning Oracle SQL Tuning 실무사례 (9) 11
사례 5 EXISTS, IN LINE VIEW를이용한조인소개대부분의마스터-디테일관계에서조인은디테일의테이블이마스터테이블의내용을확인하는성격인데, 이경우마스터테이블의용도가단순확인을위해서라면조인대신 IN-LINE VIEW나 EXISTS 문을사용하여수행속도를개선할수있다. 고객정보 (CUSTOMER) 테이블의영업점 (BR_CD) 이 B10001에서 B10100 사이에있는고객에대하여매출거래내역 (SALE) 테이블을조사하여 1995년 1/4 분기의매출액 (SALE_AMT) 중 1000원이상되는매출거래가몇건이나되는지알아보고자한다. 이요구사항을만족하는일반적인 SQL 문을작성하면다음과같다. SELECT COUNT(*) AS 거래건수 FROM SALE A, CUSTOMER B WHERE A.SALEDATE BETWEEN '19950101' AND '19950331' AND A.SALE_AMT >= 1000 AND A.CUST_NO = B.CUST_NO AND B.BR_NO BETWEEN 'B10001' AND 'B10100' ;>> 수행시간 :28.30초 Rows Excution Plan 0 SELECT STATEMENT HINT: CHOOSE 0 SORT (AGGREGATE) 28800 NESTED LOOPS 28800 TABLE ACCESS ( BY ROWID ) OF 'SALE' 28801 INDEX ( RANGE SCAN ) OF 'SALE_PK'( UNIQUE ) 28800 TABLE ACCESS ( BY ROWID ) OF 'CUSTOMER' 28800 INDEX ( UNIQUE SCAN )OF'CUSTOMER_PK'( UNIQUE ) DB Tuning Oracle SQL Tuning 실무사례 (9) 12
문제점인덱스를사용하기는하나연결하는횟수가많아서비효율이발생하고따라서수행시간이오래걸린다. WHERE 조건에서걸러져나온 28,800건의모든 SALE 데이타를 CUSTMER 테이블에연결한후 CUSTOMER 테이블에서 28,800 건의 BR_NO 조건을체크하여일치되는것만카운트하여구한다. 해결방안 IN-LINE VIEW를사용하여해결우선 SALE 테이블에서먼저 GROUP BY 하여 COUNT를구하고, 그다음 CUSTOMER 테이블에연결하여 BR_NO 조건을체크한다. 이렇게되면 SALE 테이블에서각 CUST_NO에대하여한번씩만 CUSTOMER 테이블을연결해보면되므로 (320번) 조인의횟수가 28,800번에서 320 번으로줄어들고그만큼수행속도가빨라진다. SELECT SUM(CNT) AS 거래건수 FROM (SELECT CUST_NO, COUNT(*) AS CNT FROM SALE WHERE SALEDATE BETWEEN '19950101' AND '19950331' AND A.SALE_AMT >= 1000 GROUP BY CUST_NO) V, CUSTOMER B AND V.CUST_NO = B.CUST_NO AND B.BR_NO BETWEEN 'B10001' AND 'B10100' ; >> 수행시간 :12.11초 Rows Excution Plan 0 SELECT STATEMENT HINT: CHOOSE 320 SORT (AGGREGATE) 320 NESTED LOOPS 320 VIEW OF ' FROM$_SUBQUERY$_ 1 ' 28800 SORT (GROUP BY) 28800 TABLE ACCESS (BY ROWID) OF 'SALE' 28801 INDEX (RANGE SCAN) OF 'PK_SALE' (UNIQUE) 320 TABLE ACCESS (BY ROWID) OF 'CUSTOMER' 320 INDEX (UNIQUE SCAN) OF 'PK_CUSTOMER' (UNIQUE) DB Tuning Oracle SQL Tuning 실무사례 (9) 13
문제점 실행계획을그림으로표시하면다음과같다. EXISTS를사용하여해결위의경우에서 IN-LINE VIEW 내에서수행된결과는결국 CUSTOMER 테이블에가서 BR_NO 조건의 CUST_NO가있나없나만확인하는절차이므로이경우는 EXISTS 문을사용하여다음과같이해결할수도있다. SELECT COUNT(*) AS 거래건수 FROM SALE A WHERE A.SALEDATE BETWEEN '19950101' AND '19950331' AND A.SALE_AMT >= 1000 AND EXISTS (SELECT 'X' FROM CUSTOMER B WHERE A.CUST_NO = B.CUST_NO AND B.BR_NO BETWEEN 'B10001' AND 'B10100'); >> 수행시간 :12.28초 Rows Excution Plan 0 SELECT STATEMENT HINT: CHOOSE 0 SORT ( AGGREGATE ) 28800 FILTER 28800 TABLE ACCESS ( BY ROWID ) OF 'SALE' 28801 INDEX ( RANGE SCAN ) OF 'PK_SALE' (UNIQUE) 320 TABLE ACCESS ( BY ROWID ) OF 'CUSTOMER' 320 INDEX ( UNIQUE SCAN ) OF 'PK_CUSTOMER' (UNIQUE) 실행되는과정을그림으로표시하면다음과같다. DB Tuning Oracle SQL Tuning 실무사례 (9) 14
사례 6 DYNAMIC SQL을 WHERE...DECODE... 로해결소개이번사례는요구사항이매우복잡하여 DYNAMIC SQL로구현해야만할것같지만발상의전환으로새로운시각에서바라보면하나의 SQL 로통합될수있음을보여주는사례이다. 이러한사례를통하여우리는 SQL 의무한한가능성을엿볼수있다. 통계자료를온라인으로조회하는프로그램인데 그림 6 과같은환경이다. 이와함께사용되는회계단위테이블의구조와샘플데이타는 표 1 과같다. 회계단위테이블유형그룹 유형 대상1 대상2 대상3 UNIT ACCT 1 + B1 SEL PUS DNS 1 1 2 + B1 DGU USN 1 2 3 + B2 GB GN GS 2 1 4 + B3 A B 3 1 5 - B1 SEL PUS 1 2 6 - B2 GN GD 2 2 7 - B3 A B C 3 2 DB Tuning Oracle SQL Tuning 실무사례 (9) 15
소개회계단위테이블이란이런다양한조건의통계자료에대해여러가지 WHERE 조건을정의한테이블이다. 읽는방법은유형그룹이 '+' 인기호는지사, 지점, 대리점등을포함하여통계를내고 '-' 인기호는제외하고통계를내고싶은것이고, 유형이 B1은지사의조건이고 B2는지점의조건이고 B3는대리점의조건이다. 유형의실제값이들어가는대상은대상1, 대상2, 대상3으로여기서는 3가지만입력가능한것으로제한하였다. UNIT 컬럼은 ACCT 컬럼의그때의조건을사용하는회계단위를 UNIQUE하게정의한것이다. 예를들어, 유형그룹이 '+' 인 1번행은회계단위 11번으로계산을하는데매출테이블에서서울, 부산, 대전등 3개지점의매출액만합계를내고싶은것이고, 2 번행은회계단위가 12번으로대구, 울산지점의합계를내고싶은것이다. 3 번행은회계단위 21번으로강북, 강남, 강서지점의합계만내고싶은것이고, 유형그룹이 '-' 인 5번행은전체매출중에서서울, 부산지역을제외한매출합계를내고싶은것이다. 참고로각지사, 각지점, 각대리점별로모두고유한코드를갖고있다. 이와같은요구사항은일반적인프로그램방식을사용하여코딩하면다음과같다...... CURSOR EXP_CURSOR IS SELECT * FROM 회계단위 ; OPEN EXP_CURSOR ; LOOP FETCH EXP_CURSOR INTO EXP_REC; IF EXP_REC. 유형그룹 = '+' AND EXP_REC. 유형 = 'B1' THEN SELECT SUM( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 지사 IN (EXP_REC. 대상1, EXP_REC. 대상2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); ELSIF EXP_REC. 유형그룹 = '+' AND EXP_REC. 유형 = 'B2' THEN SELECT SUM( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 지점 IN (EXP_REC. 대상1, EXP_REC. 대상2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); ELSIF EXP_REC. 유형그룹 = '+' AND EXP_REC. 유형 = 'B3' THEN SELECT SUM ( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 대리점 IN (EXP_REC. 대상1, EXP_REC. 대상2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); ELSEIF EXP_REC. 유형그룹 = '-' AND EXP_REC. 유형 = 'B1' THEN SELECT SUM( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 지사 NOT IN (EXP_REC. 대상1, EXP_REC. 대상 2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) DB Tuning Oracle SQL Tuning 실무사례 (9) 16
VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); ELSIF EXP_REC. 유형그룹 = '-' AND EXP_REC. 유형 = 'B2' THEN SELECT SUM( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 지점 NOT IN (EXP_REC. 대상1, EXP_REC. 대상 2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); ELSIF EXP_REC. 유형그룹 = '-' AND EXP_REC. 유형 = 'B3' THEN SELECT SUM ( 매출액 ) INTO SUM_SALE FROM 매출 WHERE 대리점 NOT IN (EXP_REC. 대상1, EXP_REC. 대상2, EXP_REC. 대상3); INSERT INTO 통계테이블 (UNIT, ACCT, MECHUL) VALUES (EXP_REC.UNIT, EXP_REC.ACCT, SUM_SALE); END LOOP;..... DB Tuning Oracle SQL Tuning 실무사례 (9) 17
한국오라클 ( 주 ) 서울특별시강남구삼성동 144-17 삼화빌딩대표전화 : 2194-8000 FAX : 2194-8001 한국오라클교육센타서울특별시영등포구여의도동 28-1 전경련회관 5 층, 7 층대표전화 : 3779-4242~4 FAX : 3779-4100~1 대전사무소대전광역시서구둔산동 929 번지대전둔산사학연금회관 18 층대표전화 : (042)483-4131~2 FAX : (042)483-4133 대구사무소대구광역시동구신천동 111 번지영남타워빌딩 9 층대표전화 : (053)741-4513~4 FAX : (053)741-4515 부산사무소부산광역시동구초량동 1211~7 정암빌딩 8 층대표전화 : (051)465-9996 FAX : (051)465-9958 울산사무소울산광역시남구달동 1319-15 번지정우빌딩 3 층대표전화 : (052)267-4262 FAX : (052)267-4267 광주사무소광주광역시서구양동 60-37 금호생명빌딩 8 층대표전화 : (062)350-0131 FAX : (062)350-0130 고객에게완전하고효과적인정보관리솔루션을제공하기위하여오라클사는전세계 145 개국에서제품, 기술지원, 교육및컨설팅서비스를제공하고있습니다. http://www.oracle.com/ http://www.oracle.com/kr