..这段代码效率极低...如何优化能提高效率呢?------------------------------------------------
SELECT TMPCORE.PARTNAME,
TMPCORE.GROUPNAME,
'NON-OS' OSPARTFLG,
TMPCORE.APPLNDESC,
TMPCORE.NATURECD,
TMPVALUE.RESULTCD,
TMPVALUE.THSQUAR,
TMPVALUE.THSQUAR,
TMPVALUE.LSTYEARQUAR
FROM
(
SELECT TMPAPPLN1.PARTNO,
TMPAPPLN1.PARTNAME,
TMPAPPLN1.GROUPNO,
TMPAPPLN1.GROUPNAME,
LPNVAPPLQUA.APPLNCD,
LAPTYPE.APPLNDESC,
LPNVAPPLQUA.NATURECD
-- TMPRESULTCD.RESULTCD
FROM LPNVAPPLQUA,
LNATNCD,
LAPTYPE, -- To get application type.
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
/* (SELECT 'ASS' RESULTCD FROM DUAL UNION
SELECT 'APP' FROM DUAL UNION
SELECT 'REF' FROM DUAL UNION
SELECT 'OTH' FROM DUAL UNION
SELECT 'UP' FROM DUAL
) TMPRESULTCD -- To create all posibile combinations.*/
WHERE LPNVAPPLQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LPNVAPPLQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LPNVAPPLQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LPNVAPPLQUA.NATCD = TMPAPPLN1.NATCD))
AND ((TMPAPPLN1.TDTY IS NULL) OR (LPNVAPPLQUA.TDTY = TMPAPPLN1.TDTY))
AND LPNVAPPLQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND (TMPAPPLN1.NENATCD IS NULL) OR (LPNVAPPLQUA.NATCD <> TMPAPPLN1.NENATCD)
AND LPNVAPPLQUA.APPLNCD = LAPTYPE.APPLNCD
UNION
SELECT TMPCORE.PARTNAME,
TMPCORE.GROUPNAME,
'NON-OS' OSPARTFLG,
TMPCORE.APPLNDESC,
TMPCORE.NATURECD,
TMPVALUE.RESULTCD,
TMPVALUE.THSQUAR,
TMPVALUE.THSQUAR,
TMPVALUE.LSTYEARQUAR
FROM
(
SELECT TMPAPPLN1.PARTNO,
TMPAPPLN1.PARTNAME,
TMPAPPLN1.GROUPNO,
TMPAPPLN1.GROUPNAME,
LPNVAPPLQUA.APPLNCD,
LAPTYPE.APPLNDESC,
LPNVAPPLQUA.NATURECD
-- TMPRESULTCD.RESULTCD
FROM LPNVAPPLQUA,
LNATNCD,
LAPTYPE, -- To get application type.
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
/* (SELECT 'ASS' RESULTCD FROM DUAL UNION
SELECT 'APP' FROM DUAL UNION
SELECT 'REF' FROM DUAL UNION
SELECT 'OTH' FROM DUAL UNION
SELECT 'UP' FROM DUAL
) TMPRESULTCD -- To create all posibile combinations.*/
WHERE LPNVAPPLQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LPNVAPPLQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LPNVAPPLQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LPNVAPPLQUA.NATCD = TMPAPPLN1.NATCD))
AND ((TMPAPPLN1.TDTY IS NULL) OR (LPNVAPPLQUA.TDTY = TMPAPPLN1.TDTY))
AND LPNVAPPLQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND (TMPAPPLN1.NENATCD IS NULL) OR (LPNVAPPLQUA.NATCD <> TMPAPPLN1.NENATCD)
AND LPNVAPPLQUA.APPLNCD = LAPTYPE.APPLNCD
UNION
TMPAPPLN1.PARTNAME,
TMPAPPLN1.GROUPNO,
TMPAPPLN1.GROUPNAME,
LPNVFINQUA.APPLNCD,
LAPTYPE.APPLNDESC,
LPNVFINQUA.NATURECD
-- TMPRESULTCD.RESULTCD
FROM LPNVFINQUA,
LNATNCD,
LAPTYPE, -- To get application type.
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
/* (SELECT 'ASS' RESULTCD FROM DUAL UNION
SELECT 'APP' FROM DUAL UNION
SELECT 'REF' FROM DUAL UNION
SELECT 'OTH' FROM DUAL UNION
SELECT 'UP' FROM DUAL
) TMPRESULTCD -- To create all posibile combinations. */
WHERE LPNVFINQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LPNVFINQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LPNVFINQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LPNVFINQUA.NATCD = TMPAPPLN1.NATCD))
AND LPNVFINQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND (TMPAPPLN1.NENATCD IS NULL) OR (LPNVFINQUA.NATCD <> TMPAPPLN1.NENATCD)
AND LPNVFINQUA.APPLNCD = LAPTYPE.APPLNCD ) TMPCORE,
(
SELECT TMPAPPLN1.PARTNO,
TMPAPPLN1.GROUPNO,
LPNVAPPLQUA.APPLNCD,
LPNVAPPLQUA.NATURECD,
'ASS' RESULTCD,
SUM(CASE WHEN TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'Q') = TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
THEN LPNVAPPLQUA.ASSCNT +LPNVAPPLQUA.ADJCNT ELSE 0 END) THSQUAR,
SUM(CASE WHEN
( TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'Q') = TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q')
AND TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'YYYY') = TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')
)
OR
(
TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'Q') = TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q')
AND TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'YYYY') = TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
)
THEN LPNVAPPLQUA.ASSCNT +LPNVAPPLQUA.ADJCNT ELSE 0 END) LSTQUAR,
SUM(CASE WHEN TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'Q') = TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
AND TO_CHAR(LPNVAPPLQUA.DTENDQUAR,'YYYY') = TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
THEN LPNVAPPLQUA.ASSCNT +LPNVAPPLQUA.ADJCNT ELSE 0 END) LSTYEARQUAR
FROM LPNVAPPLQUA,
LNATNCD,
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
WHERE LPNVAPPLQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LPNVAPPLQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LPNVAPPLQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LPNVAPPLQUA.NATCD = TMPAPPLN1.NATCD))
AND ((TMPAPPLN1.TDTY IS NULL) OR (LPNVAPPLQUA.TDTY = TMPAPPLN1.TDTY))
AND LPNVAPPLQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND (TMPAPPLN1.NENATCD IS NULL) OR (LPNVAPPLQUA.NATCD <> TMPAPPLN1.NENATCD)
GROUP BY TMPAPPLN1.PARTNO,
TMPAPPLN1.GROUPNO,
LPNVAPPLQUA.APPLNCD,
LPNVAPPLQUA.NATURECD
UNION
LPNVFINQUA.APPLNCD, LPNVFINQUA.NATURECD,
CASE WHEN LPNVFINQUA.RESULTCD LIKE 'A%' THEN 'APP'
WHEN LPNVFINQUA.RESULTCD LIKE 'R%' THEN 'REF'
WHEN SUBSTR(LPNVFINQUA.RESULTCD,1,2) NOT IN ('A','R') THEN 'OTH'
WHEN LPNVFINQUA.RESULTCD IS NULL THEN 'UP' END RESULTCD,
SUM(CASE WHEN
TO_CHAR(LPNVFINQUA.DTENDQUAR,'Q')= TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
THEN LPNVFINQUA.FINCNT ELSE 0 END) THSQUAR,
SUM(CASE WHEN
(
TO_CHAR(LPNVFINQUA.DTENDQUAR,'Q')=TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q' ) AND
TO_CHAR(LPNVFINQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
)OR
(
TO_CHAR(LPNVFINQUA.DTENDQUAR,'Q')=TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q' ) AND
TO_CHAR(LPNVFINQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')
)
THEN LPNVFINQUA.FINCNT ELSE 0 END) LSTQUAR,
SUM(CASE WHEN
TO_CHAR(LPNVFINQUA.DTENDQUAR,'Q')= TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
AND
TO_CHAR(LPNVFINQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
THEN LPNVFINQUA.FINCNT ELSE 0 END) LSTYEARQUAR
FROM LPNVFINQUA,
LNATNCD,
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
AND ROWNUM<50
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
WHERE LPNVFINQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LPNVFINQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LPNVFINQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LPNVFINQUA.NATCD = TMPAPPLN1.NATCD))
AND LPNVFINQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND (TMPAPPLN1.NENATCD IS NULL) OR (LPNVFINQUA.NATCD <> TMPAPPLN1.NENATCD)
GROUP BY TMPAPPLN1.PARTNO, TMPAPPLN1.GROUPNO,
LPNVFINQUA.APPLNCD, LPNVFINQUA.NATURECD,
CASE WHEN LPNVFINQUA.RESULTCD LIKE 'A%' THEN 'APP'
WHEN LPNVFINQUA.RESULTCD LIKE 'R%' THEN 'REF'
WHEN SUBSTR(LPNVFINQUA.RESULTCD,1,2) NOT IN ('A','R') THEN 'OTH'
WHEN LPNVFINQUA.RESULTCD IS NULL THEN 'UP' END
) TMPVALUE
WHERE TMPCORE.PARTNO = TMPVALUE.PARTNO(+)
AND TMPCORE.GROUPNO = TMPVALUE.GROUPNO(+)
AND TMPCORE.APPLNCD = TMPVALUE.APPLNCD(+)
AND TMPCORE.NATURECD = TMPVALUE.NATURECD(+)UNION
SELECT TMPAPPLN1.PARTNAME,
TMPAPPLN1.GROUPNAME,
'OS' OSPARTFLG,
LAPTYPE.APPLNDESC,
LOVERQUA.NATURECD,
'' RESULTCD,
SUM(CASE WHEN
TO_CHAR(LOVERQUA.DTENDQUAR,'Q')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
THEN LOVERQUA.ASSCNT+LOVERQUA.ADJCNT ELSE 0 END) THSQUAR,
SUM(CASE WHEN
(
TO_CHAR(LOVERQUA.DTENDQUAR,'Q')=TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q')
AND
TO_CHAR(LOVERQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')
)OR(
TO_CHAR(LOVERQUA.DTENDQUAR,'Q')=TO_CHAR(ADD_MONTHS(TO_DATE('11-11-2007','DD-MM-YYYY'),-3),'Q')
AND
TO_CHAR(LOVERQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
)
THEN LOVERQUA.ASSCNT+LOVERQUA.ADJCNT ELSE 0 END) LSTQUAR,
SUM(CASE WHEN
TO_CHAR(LOVERQUA.DTENDQUAR,'Q')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'Q')
AND
TO_CHAR(LOVERQUA.DTENDQUAR,'YYYY')=TO_CHAR(TO_DATE('11-11-2007','DD-MM-YYYY'),'YYYY')-1
THEN LOVERQUA.ASSCNT+LOVERQUA.ADJCNT ELSE 0 END) LSTYEARQUAR
FROM LOVERQUA,
LNATNCD,
LAPTYPE, -- To get application type.
(SELECT LRPTPRAM.SEQNOLVL1 PARTNO,
LRPTPRAM.TYLVL1 PARTNAME,
LRPTPRAM.SEQNOLVL2 GROUPNO,
LRPTPRAM.TYLVL2 GROUPNAME,
LRPTPRAM.SECTCD,
LRPTPRAM.APPLNCD,
LRPTPRAM.NATURECD,
LRPTPRAM.NATCD,
LRPTPRAM.TDTY,
LRPTPRAM.POLTGRPCD,
LRPTPRAM.NENATCD
FROM LRPTPRAM
WHERE LRPTPRAM.RPTID = 'P354R1W1'
) TMPAPPLN1 -- To indicate part one and groups within. SECTCD = 'AC' for part identified.
WHERE LOVERQUA.SECTCD = TMPAPPLN1.SECTCD
AND ((TMPAPPLN1.APPLNCD IS NULL) OR (LOVERQUA.APPLNCD = TMPAPPLN1.APPLNCD))
AND ((TMPAPPLN1.NATURECD IS NULL) OR (LOVERQUA.NATURECD = TMPAPPLN1.NATURECD))
AND ((TMPAPPLN1.NATCD IS NULL) OR (LOVERQUA.NATCD = TMPAPPLN1.NATCD))
AND LOVERQUA.NATCD = LNATNCD.NATCD
AND LNATNCD.DTEFF <= TO_DATE('11-11-2007','DD-MM-YYYY')
AND LNATNCD.DTEND > TO_DATE('11-11-2007','DD-MM-YYYY')
AND ((TMPAPPLN1.POLTGRPCD IS NULL) OR (LNATNCD.POLTGRPCD = TMPAPPLN1.POLTGRPCD))
AND ((TMPAPPLN1.NENATCD IS NULL) OR (LOVERQUA.NATCD <> TMPAPPLN1.NENATCD))
AND LOVERQUA.APPLNCD = LAPTYPE.APPLNCD
GROUP BY TMPAPPLN1.PARTNAME,
TMPAPPLN1.GROUPNAME,
LAPTYPE.APPLNDESC,
LOVERQUA.NATURECD
其次,最好别用这种欠套查询,建个视图不好吗?
第三,where条件的执行顺序是:从where语句条件的最后一个开始逐渐执行,因此希望楼主把能把范围缩小到最小的条件放在where语句的最后,具体条件,楼主可以根据自己的情况自己写。