sql优化
..这段代码效率极低...如何优化能提高效率呢？
------------------------------------------------
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
[解决办法]
看LZ的SQL语句，首先呢，把表名去掉，表名换成a和b的形式，比如：a.PARTNAME,b.RESULTCD。。。具体的楼主自己改，
其次，最好别用这种欠套查询，建个视图不好吗？
第三，where条件的执行顺序是：从where语句条件的最后一个开始逐渐执行，因此希望楼主把能把范围缩小到最小的条件放在where语句的最后，具体条件，楼主可以根据自己的情况自己写。