今天在日常数据库检查中，发现一SQL运行时间特别长，于是抓取出来，进行优化。
优化前： 耗时：503s 返回：0
SQL执行计划
Plan hash value: 3405241672 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 884 | 1064K (1)| 03:33:00 | | 1 | FOR UPDATE | | | | | | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN ANTI | | 124 | 107K| 1952 (3)| 00:00:24 | |* 4 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 146 | 124K| 1601 (2)| 00:00:20 | |* 5 | INDEX RANGE SCAN | IDX_PAYABLEMONEY_02 | 199 | | 1438 (3)| 00:00:18 | | 6 | VIEW | VW_SQ_1 | 12620 | 160K| 350 (3)| 00:00:05 | |* 7 | HASH JOIN | | 12620 | 172K| 350 (3)| 00:00:05 | | 8 | TABLE ACCESS FULL | MM_POLICY_EVENTS_TD2 | 12620 | 88340 | 166 (1)| 00:00:02 | | 9 | INDEX FAST FULL SCAN | SYS_C00139261 | 372K| 2544K| 180 (3)| 00:00:03 | |* 10 | TABLE ACCESS FULL | MM_INVPLY_TD | 3 | 45 | 1147 (2)| 00:00:14 | |* 11 | FILTER | | | | | | |* 12 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 1 | 89 | 7 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | IDX_PAYABLEMONEY_04 | 8 | | 3 (0)| 00:00:01 | |* 14 | TABLE ACCESS FULL | MM_INVPLY_TD | 3 | 45 | 1147 (2)| 00:00:14 | | 15 | CONCATENATION | | | | | | |* 16 | FILTER | | | | | | |* 17 | TABLE ACCESS BY INDEX ROWID| MM_BATCHINFO_TD | 1 | 48 | 1758 (1)| 00:00:22 | |* 18 | INDEX SKIP SCAN | IDX_BATCHINFO_TD3 | 2 | | 1756 (1)| 00:00:22 | |* 19 | TABLE ACCESS BY INDEX ROWID | MM_BATCHINFO_TD | 1 | 48 | 1758 (1)| 00:00:22 | |* 20 | INDEX SKIP SCAN | IDX_BATCHINFO_TD3 | 2 | | 1756 (1)| 00:00:22 | |* 21 | TABLE ACCESS FULL | MM_BATCHINFO_TI | 1 | 48 | 11305 (1)| 00:02:16 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "MM_INVPLY_TD" "I" WHERE ("I"."VATSEQPOLICY"=:B1 OR "I"."SEQPOLICY"=:B2) AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') AND EXISTS (SELECT /*+ */ 0 FROM "MM_PAYABLEMONEY_TD" "MM_PAYABLEMONEY_TD" WHERE NOT EXISTS (SELECT /*+ */ 0 FROM "MM_INVPLY_TD" "I" WHERE ("I"."VATSEQPOLICY"=:B3 OR "I"."SEQPOLICY"=:B4) AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') AND "POLICYNO"=:B5 AND (:B6 IS NULL AND "OFFSETNO"=:B7 OR :B8 IS NOT NULL AND "CUSTSEQ"=:B9) AND "BASECURRENCYCODE"=:B10 AND "UNITCODE"=:B11 AND "AMOUNT"=(-:B12) AND "OPSTATUS"='0' AND "BASEAMOUNT">0.00 AND TRIM("CUSTOMERCODE")=TRIM(:B13) AND ("DATATYPE"='122' OR "DATATYPE"='302' OR "DATATYPE"='311' OR "DATATYPE"='314' OR "DATATYPE"='319' OR "DATATYPE"='325' OR "DATATYPE"='328' OR "DATATYPE"='332' OR "DATATYPE"='336' OR "DATATYPE"='340' OR "DATATYPE"='344' OR "DATATYPE"='348' OR "DATATYPE"='352' OR "DATATYPE"='354' OR "DATATYPE"='356' OR "DATATYPE"='357' OR "DATATYPE"='360' OR "DATATYPE"='361' OR "DATATYPE"='363' OR "DATATYPE"='366' OR "DATATYPE"='367' OR "DATATYPE"='370' OR "DATATYPE"='502' OR "DATATYPE"='504' OR "DATATYPE"='506' OR "DATATYPE"='508' OR "DATATYPE"='512' OR "DATATYPE"='514' OR "DATATYPE"='522' OR "DATATYPE"='526' OR "DATATYPE"='534' OR "DATATYPE"='536' OR "DATATYPE"='537' OR "DATATYPE"='540' OR "DATATYPE"='541' OR "DATATYPE"='C02' OR "DATATYPE"='C28' OR "DATATYPE"='C30' OR "DATATYPE"='C54' OR "DATATYPE"='C56' OR "DATATYPE"='C57' OR "DATATYPE"='C60' OR "DATATYPE"='C61' OR "DATATYPE"='C63' OR "DATATYPE"='C66' OR "DATATYPE"='C68' OR "DATATYPE"='C72' OR "DATATYPE"='C75' OR "DATATYPE"='C77' OR "DATATYPE"='C78' OR "DATATYPE"='C81' OR "DATATYPE"='C82' OR "DATATYPE"='F02' OR "DATATYPE"='F04' OR "DATATYPE"='F06' OR "DATATYPE"='F08' OR "DATATYPE"='F12' OR "DATATYPE"='F14' OR "DATATYPE"='F22' OR "DATATYPE"='F26' OR "DATATYPE"='F34' OR "DATATYPE"='F36' OR "DATATYPE"='F37' OR "DATATYPE"='F40' OR "DATATYPE"='F41' OR "DATATYPE"='Y22')) AND NOT EXISTS (SELECT /*+ */ 0 FROM "MM_BATCHINFO_TD" "MM_BATCHINFO_TD"???) 3 - access("FATHERNO"="P"."FATHERNO") 4 - filter("BASEAMOUNT"<"BASEUSEDAMOUNT") 5 - access("P"."SUBCOMPANY"=:B1 AND "P"."OPSTATUS"='0' AND "BASEAMOUNT"<0.00) filter("BASEAMOUNT"<0.00 AND "P"."OPSTATUS"='0' AND ("P"."DATATYPE"='132' OR "P"."DATATYPE"='304' OR "P"."DATATYPE"='313' OR "P"."DATATYPE"='316' OR "P"."DATATYPE"='321' OR "P"."DATATYPE"='323' OR "P"."DATATYPE"='330' OR "P"."DATATYPE"='334' OR "P"."DATATYPE"='338' OR "P"."DATATYPE"='342' OR "P"."DATATYPE"='346' OR "P"."DATATYPE"='350' OR "P"."DATATYPE"='351' OR "P"."DATATYPE"='353' OR "P"."DATATYPE"='355' OR "P"."DATATYPE"='358' OR "P"."DATATYPE"='359' OR "P"."DATATYPE"='362' OR "P"."DATATYPE"='364' OR "P"."DATATYPE"='365' OR "P"."DATATYPE"='367' OR "P"."DATATYPE"='370' OR "P"."DATATYPE"='516' OR "P"."DATATYPE"='518' OR "P"."DATATYPE"='524' OR "P"."DATATYPE"='528' OR "P"."DATATYPE"='532' OR "P"."DATATYPE"='535' OR "P"."DATATYPE"='538' OR "P"."DATATYPE"='539' OR "P"."DATATYPE"='542' OR "P"."DATATYPE"='C04' OR "P"."DATATYPE"='C28' OR "P"."DATATYPE"='C30' OR "P"."DATATYPE"='C53' OR "P"."DATATYPE"='C55' OR "P"."DATATYPE"='C58' OR "P"."DATATYPE"='C59' OR "P"."DATATYPE"='C62' OR "P"."DATATYPE"='C64' OR "P"."DATATYPE"='C65' OR "P"."DATATYPE"='C70' OR "P"."DATATYPE"='C74' OR "P"."DATATYPE"='C75' OR "P"."DATATYPE"='C76' OR "P"."DATATYPE"='C79' OR "P"."DATATYPE"='C80' OR "P"."DATATYPE"='C83' OR "P"."DATATYPE"='F16' OR "P"."DATATYPE"='F18' OR "P"."DATATYPE"='F24' OR "P"."DATATYPE"='F28' OR "P"."DATATYPE"='F32' OR "P"."DATATYPE"='F35' OR "P"."DATATYPE"='F38' OR "P"."DATATYPE"='F39' OR "P"."DATATYPE"='F42' OR "P"."DATATYPE"='Y32')) 7 - access("E2"."FATHERNO"="A"."SEQFEELIST") 10 - filter(("I"."VATSEQPOLICY"=:B1 OR "I"."SEQPOLICY"=:B2) AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') 11 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "MM_INVPLY_TD" "I" WHERE ("I"."VATSEQPOLICY"=:B1 OR "I"."SEQPOLICY"=:B2) AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4')) 12 - filter((:B1 IS NULL AND "OFFSETNO"=:B2 OR :B3 IS NOT NULL AND "CUSTSEQ"=:B4) AND "BASECURRENCYCODE"=:B5 AND "UNITCODE"=:B6 AND "AMOUNT"=(-:B7) AND "OPSTATUS"='0' AND "BASEAMOUNT">0.00 AND TRIM("CUSTOMERCODE")=TRIM(:B8) AND ("DATATYPE"='122' OR "DATATYPE"='302' OR "DATATYPE"='311' OR "DATATYPE"='314' OR "DATATYPE"='319' OR "DATATYPE"='325' OR "DATATYPE"='328' OR "DATATYPE"='332' OR "DATATYPE"='336' OR "DATATYPE"='340' OR "DATATYPE"='344' OR "DATATYPE"='348' OR "DATATYPE"='352' OR "DATATYPE"='354' OR "DATATYPE"='356' OR "DATATYPE"='357' OR "DATATYPE"='360' OR "DATATYPE"='361' OR "DATATYPE"='363' OR "DATATYPE"='366' OR "DATATYPE"='367' OR "DATATYPE"='370' OR "DATATYPE"='502' OR "DATATYPE"='504' OR "DATATYPE"='506' OR "DATATYPE"='508' OR "DATATYPE"='512' OR "DATATYPE"='514' OR "DATATYPE"='522' OR "DATATYPE"='526' OR "DATATYPE"='534' OR "DATATYPE"='536' OR "DATATYPE"='537' OR "DATATYPE"='540' OR "DATATYPE"='541' OR "DATATYPE"='C02' OR "DATATYPE"='C28' OR "DATATYPE"='C30' OR "DATATYPE"='C54' OR "DATATYPE"='C56' OR "DATATYPE"='C57' OR "DATATYPE"='C60' OR "DATATYPE"='C61' OR "DATATYPE"='C63' OR "DATATYPE"='C66' OR "DATATYPE"='C68' OR "DATATYPE"='C72' OR "DATATYPE"='C75' OR "DATATYPE"='C77' OR "DATATYPE"='C78' OR "DATATYPE"='C81' OR "DATATYPE"='C82' OR "DATATYPE"='F02' OR "DATATYPE"='F04' OR "DATATYPE"='F06' OR "DATATYPE"='F08' OR "DATATYPE"='F12' OR "DATATYPE"='F14' OR "DATATYPE"='F22' OR "DATATYPE"='F26' OR "DATATYPE"='F34' OR "DATATYPE"='F36' OR "DATATYPE"='F37' OR "DATATYPE"='F40' OR "DATATYPE"='F41' OR "DATATYPE"='Y22')) 13 - access("POLICYNO"=:B1) 14 - filter(("I"."VATSEQPOLICY"=:B1 OR "I"."SEQPOLICY"=:B2) AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') 16 - filter(:B1 IS NOT NULL) 17 - filter("POLICYNO"=:B1 AND "OPSTATUS"<>'3') 18 - access("SERIALNO"=:B1) filter("SERIALNO"=:B1) 19 - filter("POLICYNO"=:B1 AND "OPSTATUS"<>'3' AND (LNNVL(:B2 IS NOT NULL) OR LNNVL("SERIALNO"=:B3))) 20 - access("SERIALNO"=:B1) filter("SERIALNO"=:B1) 21 - filter("POLICYNO"=:B1 AND ("SERIALNO"=:B2 OR :B3 IS NOT NULL AND "SERIALNO"=:B4) AND "STATUS"<>'4')1）建立索引
create index idx_BATCHINFO_TI_test on MM_BATCHINFO_TI (POLICYNO, SERIALNO, STATUS); create index IDX_BATCHINFO_TEST on MM_BATCHINFO_TD (SERIALNO, POLICYNO, OPSTATUS);2)改写SQL语句
SELECT * FROM MM_PAYABLEMONEY_TD P LEFT JOIN (SELECT SEQPOLICY FROM MM_INVPLY_TD I WHERE I.OPSTATUS NOT IN ('3', '4', '5') AND SEQPOLICY IS NOT NULL UNION ALL SELECT VATSEQPOLICY FROM MM_INVPLY_TD I WHERE I.OPSTATUS NOT IN ('3', '4', '5') AND VATSEQPOLICY IS NOT NULL) TP ON P.FATHERNO = TP.SEQPOLICY WHERE P.DATATYPE IN ('132', '304', '313', '316', '323', '321', '330', '334', '338', '342', '346', '350', '351', '353', '355', '358', '359', '362', '365', '364', '516', '518', '524', '528', '532', '535', '538', '539', '542', 'Y32', 'C04', 'C70', 'C30', 'C74', 'C53', 'C55', 'C76', 'C58', 'C79', 'C59', 'C80', 'C62', 'C83', 'C65', 'C64', 'F16', 'F18', 'F24', 'F28', 'F32', 'F35', 'F38', 'F39', 'F42', 'C30', 'C28', 'C75', '367', '370') AND P.OPSTATUS IN ('0') AND P.SUBCOMPANY = '015100' AND BASEAMOUNT < BASEUSEDAMOUNT AND BASEAMOUNT < 0.00 AND TP.SEQPOLICY IS NULL /* AND NOT EXISTS (SELECT 1 FROM MM_INVPLY_TD I WHERE (I.SEQPOLICY = P.FATHERNO OR I.VATSEQPOLICY = P.FATHERNO) AND I.OPSTATUS NOT IN ('3', '4', '5')) */ AND EXISTS (SELECT 'X' FROM MM_PAYABLEMONEY_TD LEFT JOIN (SELECT SEQPOLICY FROM MM_INVPLY_TD I WHERE I.OPSTATUS NOT IN ('3', '4', '5') AND SEQPOLICY IS NOT NULL UNION ALL SELECT VATSEQPOLICY FROM MM_INVPLY_TD I WHERE I.OPSTATUS NOT IN ('3', '4', '5') AND VATSEQPOLICY IS NOT NULL) TD ON FATHERNO = TD.SEQPOLICY WHERE POLICYNO = P.POLICYNO AND UNITCODE = P.UNITCODE AND BASECURRENCYCODE = P.BASECURRENCYCODE AND DATATYPE IN ('122', '302', '311', '314', '319', '325', '328', '332', '336', '340', '344', '348', '352', '354', '356', '357', '360', '361', '363', '366', '502', '504', '506', '508', '512', '514', '522', '526', '534', '536', '537', '540', '541', 'Y22', 'C02', 'C68', 'C28', 'C72', 'C54', 'C56', 'C77', 'C57', 'C78', 'C60', 'C81', 'C61', 'C82', 'C63', 'C66', 'F02', 'F04', 'F06', 'F08', 'F12', 'F14', 'F22', 'F26', 'F34', 'F36', 'F37', 'F40', 'F41', 'C30', 'C28', 'C75', '367', '370') /* AND NOT EXISTS (SELECT 1 FROM MM_INVPLY_TD I WHERE (I.SEQPOLICY = P.FATHERNO OR I.VATSEQPOLICY = P.FATHERNO) AND I.OPSTATUS NOT IN ('3', '4', '5'))*/ AND TD.SEQPOLICY IS NULL AND OPSTATUS IN ('0') AND ((CUSTSEQ = P.OFFSETNO AND P.OFFSETNO IS NOT NULL) OR (OFFSETNO = P.CUSTSEQ AND P.OFFSETNO IS NULL)) AND AMOUNT = -P.AMOUNT AND TRIM(CUSTOMERCODE) = TRIM(P.CUSTOMERCODE) AND BASEAMOUNT > 0.00) AND NOT EXISTS (SELECT 1 FROM MM_BATCHINFO_TD WHERE POLICYNO = P.POLICYNO AND OPSTATUS <> '3' AND ((SERIALNO = P.CUSTSEQ) OR (SERIALNO = P.OFFSETNO AND P.OFFSETNO IS NOT NULL))) AND NOT EXISTS (SELECT 1 FROM MM_BATCHINFO_TI WHERE POLICYNO = P.POLICYNO AND STATUS <> '4' AND ((SERIALNO = P.CUSTSEQ) OR (SERIALNO = P.OFFSETNO AND P.OFFSETNO IS NOT NULL))) AND NOT EXISTS (SELECT 1 FROM MM_POLICY_EVENTS_TD2 E2, MM_APPLYFEE_TD A WHERE E2.FATHERNO = A.SEQFEELIST AND E2.FATHERNO = P.FATHERNO) 优化后的执行计划： Plan hash value: 783089741 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 897 | 442K (2)| 01:28:25 | |* 1 | FILTER | | | | | | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN OUTER | | 170 | 148K| 8528 (2)| 00:01:43 | |* 4 | HASH JOIN ANTI | | 170 | 146K| 2540 (3)| 00:00:31 | |* 5 | TABLE ACCESS BY INDEX ROWID | MM_PAYABLEMONEY_TD | 200 | 170K| 2189 (2)| 00:00:27 | |* 6 | INDEX RANGE SCAN | IDX_PAYABLEMONEY_02 | 273 | | 1966 (3)| 00:00:24 | | 7 | VIEW | VW_SQ_1 | 12620 | 160K| 350 (3)| 00:00:05 | |* 8 | HASH JOIN | | 12620 | 172K| 350 (3)| 00:00:05 | | 9 | TABLE ACCESS FULL | MM_POLICY_EVENTS_TD2 | 12620 | 88340 | 166 (1)| 00:00:02 | | 10 | INDEX FAST FULL SCAN | SYS_C00139261 | 372K| 2544K| 180 (3)| 00:00:03 | | 11 | VIEW | | 536K| 6808K| 5983 (2)| 00:01:12 | | 12 | UNION-ALL | | | | | | |* 13 | TABLE ACCESS FULL | MM_INVPLY_TD | 437K| 5124K| 2997 (3)| 00:00:36 | |* 14 | TABLE ACCESS FULL | MM_INVPLY_TD | 99039 | 773K| 2985 (2)| 00:00:36 | | 15 | CONCATENATION | | | | | | |* 16 | FILTER | | | | | | |* 17 | INDEX RANGE SCAN | IDX_BATCHINFO_TEST | 1 | 48 | 3 (0)| 00:00:01 | |* 18 | INDEX RANGE SCAN | IDX_BATCHINFO_TEST | 1 | 48 | 3 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | IDX_BATCHINFO_TI_TEST | 1 | 48 | 3 (0)| 00:00:01 | |* 20 | HASH JOIN ANTI | | 1 | 109 | 5995 (2)| 00:01:12 | |* 21 | TABLE ACCESS BY INDEX ROWID| MM_PAYABLEMONEY_TD | 1 | 96 | 7 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | IDX_PAYABLEMONEY_04 | 8 | | 3 (0)| 00:00:01 | | 23 | VIEW | | 536K| 6808K| 5983 (2)| 00:01:12 | | 24 | UNION-ALL | | | | | | |* 25 | TABLE ACCESS FULL | MM_INVPLY_TD | 437K| 5124K| 2997 (3)| 00:00:36 | |* 26 | TABLE ACCESS FULL | MM_INVPLY_TD | 99039 | 773K| 2985 (2)| 00:00:36 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "MM_BATCHINFO_TD" "MM_BATCHINFO_TD"???) 2 - filter("TP"."SEQPOLICY" IS NULL) 3 - access("P"."FATHERNO"="TP"."SEQPOLICY"(+)) 4 - access("FATHERNO"="P"."FATHERNO") 5 - filter("P"."BASEAMOUNT"<"P"."BASEUSEDAMOUNT") 6 - access("P"."SUBCOMPANY"='015100' AND "P"."OPSTATUS"='0' AND "P"."BASEAMOUNT"<0.00) filter("P"."BASEAMOUNT"<0.00 AND "P"."OPSTATUS"='0' AND ("P"."DATATYPE"='132' OR "P"."DATATYPE"='304' OR "P"."DATATYPE"='313' OR "P"."DATATYPE"='316' OR "P"."DATATYPE"='321' OR "P"."DATATYPE"='323' OR "P"."DATATYPE"='330' OR "P"."DATATYPE"='334' OR "P"."DATATYPE"='338' OR "P"."DATATYPE"='342' OR "P"."DATATYPE"='346' OR "P"."DATATYPE"='350' OR "P"."DATATYPE"='351' OR "P"."DATATYPE"='353' OR "P"."DATATYPE"='355' OR "P"."DATATYPE"='358' OR "P"."DATATYPE"='359' OR "P"."DATATYPE"='362' OR "P"."DATATYPE"='364' OR "P"."DATATYPE"='365' OR "P"."DATATYPE"='367' OR "P"."DATATYPE"='370' OR "P"."DATATYPE"='516' OR "P"."DATATYPE"='518' OR "P"."DATATYPE"='524' OR "P"."DATATYPE"='528' OR "P"."DATATYPE"='532' OR "P"."DATATYPE"='535' OR "P"."DATATYPE"='538' OR "P"."DATATYPE"='539' OR "P"."DATATYPE"='542' OR "P"."DATATYPE"='C04' OR "P"."DATATYPE"='C28' OR "P"."DATATYPE"='C30' OR "P"."DATATYPE"='C53' OR "P"."DATATYPE"='C55' OR "P"."DATATYPE"='C58' OR "P"."DATATYPE"='C59' OR "P"."DATATYPE"='C62' OR "P"."DATATYPE"='C64' OR "P"."DATATYPE"='C65' OR "P"."DATATYPE"='C70' OR "P"."DATATYPE"='C74' OR "P"."DATATYPE"='C75' OR "P"."DATATYPE"='C76' OR "P"."DATATYPE"='C79' OR "P"."DATATYPE"='C80' OR "P"."DATATYPE"='C83' OR "P"."DATATYPE"='F16' OR "P"."DATATYPE"='F18' OR "P"."DATATYPE"='F24' OR "P"."DATATYPE"='F28' OR "P"."DATATYPE"='F32' OR "P"."DATATYPE"='F35' OR "P"."DATATYPE"='F38' OR "P"."DATATYPE"='F39' OR "P"."DATATYPE"='F42' OR "P"."DATATYPE"='Y32')) 8 - access("E2"."FATHERNO"="A"."SEQFEELIST") 13 - filter("I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') 14 - filter("VATSEQPOLICY" IS NOT NULL AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') 16 - filter(:B1 IS NOT NULL) 17 - access("SERIALNO"=:B1 AND "POLICYNO"=:B2) filter("OPSTATUS"<>'3') 18 - access("SERIALNO"=:B1 AND "POLICYNO"=:B2) filter("OPSTATUS"<>'3' AND (LNNVL(:B1 IS NOT NULL) OR LNNVL("SERIALNO"=:B2))) 19 - access("POLICYNO"=:B1) filter("STATUS"<>'4' AND ("SERIALNO"=:B1 OR :B2 IS NOT NULL AND "SERIALNO"=:B3)) 20 - access("FATHERNO"="TD"."SEQPOLICY") 21 - filter((:B1 IS NULL AND "MM_PAYABLEMONEY_TD"."OFFSETNO"=:B2 OR :B3 IS NOT NULL AND "MM_PAYABLEMONEY_TD"."CUSTSEQ"=:B4) AND "MM_PAYABLEMONEY_TD"."BASECURRENCYCODE"=:B5 AND "MM_PAYABLEMONEY_TD"."UNITCODE"=:B6 AND "MM_PAYABLEMONEY_TD"."AMOUNT"=(-:B7) AND "MM_PAYABLEMONEY_TD"."OPSTATUS"='0' AND "MM_PAYABLEMONEY_TD"."BASEAMOUNT">0.00 AND TRIM("MM_PAYABLEMONEY_TD"."CUSTOMERCODE")=TRIM(:B8) AND ("MM_PAYABLEMONEY_TD"."DATATYPE"='122' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='302' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='311' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='314' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='319' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='325' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='328' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='332' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='336' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='340' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='344' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='348' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='352' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='354' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='356' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='357' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='360' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='361' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='363' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='366' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='367' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='370' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='502' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='504' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='506' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='508' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='512' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='514' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='522' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='526' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='534' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='536' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='537' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='540' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='541' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C02' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C28' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C30' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C54' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C56' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C57' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C60' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C61' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C63' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C66' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C68' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C72' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C75' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C77' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C78' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C81' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='C82' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F02' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F04' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F06' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F08' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F12' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F14' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F22' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F26' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F34' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F36' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F37' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F40' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='F41' OR "MM_PAYABLEMONEY_TD"."DATATYPE"='Y22')) 22 - access("MM_PAYABLEMONEY_TD"."POLICYNO"=:B1) 25 - filter("I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4') 26 - filter("VATSEQPOLICY" IS NOT NULL AND "I"."OPSTATUS"<>'3' AND "I"."OPSTATUS"<>'5' AND "I"."OPSTATUS"<>'4')执行时间：1s 返回：0
转载于:https://www.cnblogs.com/wanbin/p/9514669.html
相关资源：JAVA上百实例源码以及开源项目