데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

SELECT SER1.CD_MAEJANG CD_MAEJANG,
(SELECT DSC_SNAME FROM GURAEMST GM WHERE GM.CD_GU = 'M' AND SER1.CD_MAEJANG = GM.CD_CODE) DSC_SNAME,
SUM(DECODE(CD_GU,'0',AMT_01, 0)) AMT_01,
SUM(DECODE(CD_GU,'0',AMT_02, 0)) AMT_02,
SUM(DECODE(CD_GU,'0',AMT_03, 0)) AMT_03,
SUM(DECODE(CD_GU,'0',AMT_SUM,0)) AMT_SUM,
SUM(DECODE(CD_GU,'1',AMT_01, 0)) MOKPYO_01,
SUM(DECODE(CD_GU,'1',AMT_02, 0)) MOKPYO_02,
SUM(DECODE(CD_GU,'1',AMT_03, 0)) MOKPYO_03,
SUM(DECODE(CD_GU,'1',AMT_SUM,0)) MOKPYO_SUM,
SUM(DECODE(CD_GU,'2',AMT_01, 0)) MAMT_01,
SUM(DECODE(CD_GU,'2',AMT_02, 0)) MAMT_02,
SUM(DECODE(CD_GU,'2',AMT_03, 0)) MAMT_03,
SUM(DECODE(CD_GU,'2',AMT_SUM,0)) MAMT_SUM,
SUM(DECODE(CD_GU,'3',AMT_01, 0)) JMAMT_01,
SUM(DECODE(CD_GU,'3',AMT_02, 0)) JMAMT_02,
SUM(DECODE(CD_GU,'3',AMT_03, 0)) JMAMT_03,
SUM(DECODE(CD_GU,'3',AMT_SUM,0)) JMAMT_SUM,
SUM(DECODE(CD_GU,'4',AMT_01, 0)) JMSAMT_01,
SUM(DECODE(CD_GU,'4',AMT_02, 0)) JMSAMT_02,
SUM(DECODE(CD_GU,'4',AMT_03, 0)) JMSAMT_03,
SUM(DECODE(CD_GU,'4',AMT_SUM,0)) JMSAMT_SUM,
SUM(DECODE(CD_GU,'7',AMT_01, 0)) YMOKPYO_01,
SUM(DECODE(CD_GU,'7',AMT_02, 0)) YMOKPYO_02,
SUM(DECODE(CD_GU,'7',AMT_03, 0)) YMOKPYO_03,
SUM(DECODE(CD_GU,'7',AMT_SUM,0)) YMOKPYO_SUM,
SUM(DECODE(CD_GU,'5',AMT_01, 0)) YAMT_01,
SUM(DECODE(CD_GU,'5',AMT_02, 0)) YAMT_02,
SUM(DECODE(CD_GU,'5',AMT_03, 0)) YAMT_03,
SUM(DECODE(CD_GU,'5',AMT_SUM,0)) YAMT_SUM,
SUM(DECODE(CD_GU,'6',AMT_01, 0)) JYAMT_01,
SUM(DECODE(CD_GU,'6',AMT_02, 0)) JYAMT_02,
SUM(DECODE(CD_GU,'6',AMT_03, 0)) JYAMT_03,
SUM(DECODE(CD_GU,'6',AMT_SUM,0)) JYAMT_SUM
FROM
(SELECT '0' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE = :S_DATE
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '1' CD_GU,
CD_MAEJANG,
DECODE(MODIFY_MOKPYO_01,'0',MOKPYO_01,MODIFY_MOKPYO_01) MOKPYO_01,
DECODE(MODIFY_MOKPYO_02,'0',MOKPYO_02,MODIFY_MOKPYO_02) MOKPYO_02,
DECODE(MODIFY_MOKPYO_03,'0',MOKPYO_03,MODIFY_MOKPYO_03) MOKPYO_03,
DECODE(MODIFY_MOKPYO_01,'0',MOKPYO_01,MODIFY_MOKPYO_01)+
DECODE(MODIFY_MOKPYO_02,'0',MOKPYO_02,MODIFY_MOKPYO_02)+
DECODE(MODIFY_MOKPYO_03,'0',MOKPYO_03,MODIFY_MOKPYO_03) MOKPYO_SUM
FROM MOKPYO
WHERE MOKPYO.MONTH = :S_MONTH
AND MOKPYO.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '2' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE BETWEEN :S_DATE_01 AND :S_DATE
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '3' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE BETWEEN :J_DATE_01 AND :J_DATE
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '4' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE BETWEEN :J_DATE_01 AND :J_DATE_31
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '5' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE BETWEEN :S_0101 AND :S_DATE
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '6' CD_GU,
CD_MAEJANG,
DECODE(TP_SALE_GU,'0',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'6',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_01,
DECODE(TP_SALE_GU,'1',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'7',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_02,
DECODE(TP_SALE_GU,'2',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)),
'5',DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI))) AMT_03,
DECODE(TP_SALE,'0', (AMT_SALE - AMT_TICKET - AMT_ENURI),
'1',-(AMT_SALE - AMT_TICKET - AMT_ENURI)) AMT_SUM
FROM SALEMST
WHERE SALEMST.DT_SALE BETWEEN :J_0101 AND :J_DATE
AND SALEMST.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG
UNION ALL
SELECT '7' CD_GU,
CD_MAEJANG,
DECODE(MODIFY_MOKPYO_01,'0',MOKPYO_01,MODIFY_MOKPYO_01) MOKPYO_01,
DECODE(MODIFY_MOKPYO_02,'0',MOKPYO_02,MODIFY_MOKPYO_02) MOKPYO_02,
DECODE(MODIFY_MOKPYO_03,'0',MOKPYO_03,MODIFY_MOKPYO_03) MOKPYO_03,
DECODE(MODIFY_MOKPYO_01,'0',MOKPYO_01,MODIFY_MOKPYO_01)+
DECODE(MODIFY_MOKPYO_02,'0',MOKPYO_02,MODIFY_MOKPYO_02)+
DECODE(MODIFY_MOKPYO_03,'0',MOKPYO_03,MODIFY_MOKPYO_03) MOKPYO_SUM
FROM MOKPYO
WHERE MOKPYO.MONTH BETWEEN :S_MONTH_01 AND :S_MONTH
AND MOKPYO.CD_MAEJANG BETWEEN :S_MAEJANG AND :E_MAEJANG) SER1,
(SELECT DISTINCT CD_MAEJANG FROM MGROUPMST WHERE CD_GU = :S_MGU) SER2
WHERE SER1.CD_MAEJANG = SER2.CD_MAEJANG
GROUP BY SER1.CD_MAEJANG

이 글에 대한 댓글이 총 1건 있습니다.

상당히 긴 SQL이네요...

아마도 님께서 가장 잘 아실 수 있는 상황일 것입니다.

먼저

어떤 경우에 10초정도 걸리고 어떤 경우에 4~5분 걸리는지...

그때 사용한 변수에 의존적이지는 않은지...

이런 걸 검토해보시구요..

플랜을 떠서 의심이 가는 곳을 찾아보시구요...

이 SQL의 경우에는 많은 union들이 보이는데요...

쪼개서 찾아보십시오.

등등의 노력을 하셔야 할 것입니다.

단지 SQL만으로는 느려지는 이유를 파악할 수 없습니다.

최악의 상황에서는 님의 SQL이 최적의 SQL임에도 불구하고 느릴 수도 있지 않겠습니까?

만약 그렇다면 SQL이 아니라 데이타 관리에 관한 정책을 바꾸든가,...

기타 등등의 다른 접근법이 필요하겠죠...

어쨋든 가능한 시도해보시고 가능한 자세한 상황을 오려주십시오.

김흥수(protokhs)님이 2005-11-16 03:10에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
24848	특정기간 동안의 년월을 구하고 싶습니다. [8]	김인수	2005-11-14	5985
24847	ORACLE에도 프로필러가 있는지요??? [1]	김정호	2005-11-14	2182
24846	여러개의 테이블 (5개 이상) select 해서 출력하기 [1]	깡댕이	2005-11-14	1909
24845	이런 쿼리를 돌이는데 속도가 빨랐다 느렸다 하네요.. [1]	궁금이	2005-11-14	1587
24844	오라클서버 끊김현상(vpn사용) 도와주세요..ㅠㅠ [1]	차원기	2005-11-14	3778
24843	merge와 trigger중에.. [1]	나그네	2005-11-14	1345
24842	shared pool 과 LRU 메카니즘에 대한 조언을 구합니다. [1]	김진성	2005-11-14	1192

작업시간: 0.019초, 이곳 서비스는
PostgreSQL v16.2로 자료를 관리합니다