SQL> select sum(nvl(GetBusinessZongE('BusinessApprove', BA.SerialNo), 0)) as BusinessSum,
sum(nvl(GetBusinessChangKou('BusinessApprove', BA.SerialNo), 0)) as ApproveSum
from Business_Approve BA
where 1 = 1
and not exists (select 'x'
from Business_Contract
where RelativeSerialNo = BA.SerialNo)
and BA.StatusFlag is null
and exists
(select 'x'
from Customer_Info
where (EMcCustomerID = '' or CustomerID = '20131119000089')
and
(
(BA.CustomerID = CustomerID and BA.BusinessType <> '1020040') or
(BA.ThirdPartyID2 = CustomerID and BA.BusinessType = '1020040')
)
)
and (BA.BusinessType like '1%' or BA.BusinessType like '2%')
and BA.BusinessType not in
('1110008040', '1110008041', '1110008042', '4010', '4020'); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
BUSINESSSUM APPROVESUM
----------- ----------
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8kxqnmdm3qfk1, child number 0
-------------------------------------
select sum(nvl(GetBusinessZongE('BusinessApprove', BA.SerialNo), 0)) as
BusinessSum, sum(nvl(GetBusinessChangKou('BusinessApprove',
BA.SerialNo), 0)) as ApproveSum from Business_Approve BA where 1 = 1
and not exists (select 'x' from Business_Contract
where RelativeSerialNo = BA.SerialNo) and BA.StatusFlag is null
and exists (select 'x' from Customer_Info where
(EMcCustomerID = '' or CustomerID = '20131119000089') and
( (BA.CustomerID = CustomerID and BA.BusinessType <>
'1020040') or (BA.ThirdPartyID2 = CustomerID and
BA.BusinessType = '1020040') ) ) and (BA.BusinessType
like '1%' or BA.BusinessType like '2%') and BA.BusinessType not in
('1110008040', '1110008041', '1110008042', '4010', '4020')
Plan hash value: 1312284355
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.72 | 23998 | 23097 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.72 | 23998 | 23097 | | | |
|* 2 | FILTER | | 1 | | 0 |00:00:01.72 | 23998 | 23097 | | | |
|* 3 | HASH JOIN RIGHT ANTI | | 1 | 1457 | 139 |00:00:01.72 | 23849 | 23097 | 11M| 4048K| 13M (0)|
|* 4 | INDEX FAST FULL SCAN| PK_BC_RELATIVESERIALNO | 1 | 202K| 202K|00:00:00.06 | 744 | 0 | | | |
|* 5 | TABLE ACCESS FULL | BUSINESS_APPROVE | 1 | 145K| 160K|00:00:01.29 | 23105 | 23097 | | | |
|* 6 | INDEX UNIQUE SCAN | BIN$oTAOXbMjMHLgQwoCgBIwcg==$0 | 139 | 1 | 0 |00:00:00.01 | 149 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
3 - access("RELATIVESERIALNO"="BA"."SERIALNO")
4 - filter("RELATIVESERIALNO" IS NOT NULL)
5 - filter((("BA"."BUSINESSTYPE" LIKE '1%' OR "BA"."BUSINESSTYPE" LIKE '2%') AND "BA"."STATUSFLAG" IS NULL AND
"BA"."BUSINESSTYPE"<>'1110008040' AND "BA"."BUSINESSTYPE"<>'4010' AND "BA"."BUSINESSTYPE"<>'1110008042' AND "BA"."BUSINESSTYPE"<>'1110008041'
AND "BA"."BUSINESSTYPE"<>'4020'))
6 - access("CUSTOMERID"='20131119000089')
filter((("CUSTOMERID"=:B1 AND :B2<>'1020040') OR ("CUSTOMERID"=:B3 AND :B4='1020040')))
41 rows selected.
SQL> explain plan for select sum(nvl(GetBusinessZongE('BusinessApprove', BA.SerialNo), 0)) as BusinessSum,
sum(nvl(GetBusinessChangKou('BusinessApprove', BA.SerialNo), 0)) as ApproveSum
from Business_Approve BA
where 1 = 1
and not exists (select 'x'
from Business_Contract
where RelativeSerialNo = BA.SerialNo)
and BA.StatusFlag is null
and exists
(select 'x'
from Customer_Info
where (EMcCustomerID = '' or CustomerID = '20131119000089')
and
(
(BA.CustomerID = CustomerID and BA.BusinessType <> '1020040') or
(BA.ThirdPartyID2 = CustomerID and BA.BusinessType = '1020040')
)
)
and (BA.BusinessType like '1%' or BA.BusinessType like '2%')
and BA.BusinessType not in
('1110008040', '1110008041', '1110008042', '4010', '4020'); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1312284355
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | | 8626 (1)| 00:01:44 |
| 1 | SORT AGGREGATE | | 1 | 58 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN RIGHT ANTI | | 1457 | 84506 | 4936K| 7169 (1)| 00:01:27 |
|* 4 | INDEX FAST FULL SCAN| PK_BC_RELATIVESERIALNO | 202K| 2565K| | 201 (1)| 00:00:03 |
|* 5 | TABLE ACCESS FULL | BUSINESS_APPROVE | 145K| 6403K| | 6334 (1)| 00:01:17 |
|* 6 | INDEX UNIQUE SCAN | BIN$oTAOXbMjMHLgQwoCgBIwcg==$0 | 1 | 16 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "CUSTOMER_INFO" "CUSTOMER_INFO" WHERE "CUSTOMERID"='20131119000089'
AND ("CUSTOMERID"=:B1 AND :B2<>'1020040' OR "CUSTOMERID"=:B3 AND :B4='1020040')))
3 - access("RELATIVESERIALNO"="BA"."SERIALNO")
4 - filter("RELATIVESERIALNO" IS NOT NULL)
5 - filter(("BA"."BUSINESSTYPE" LIKE '1%' OR "BA"."BUSINESSTYPE" LIKE '2%') AND "BA"."STATUSFLAG" IS
NULL AND "BA"."BUSINESSTYPE"<>'1110008040' AND "BA"."BUSINESSTYPE"<>'4010' AND
"BA"."BUSINESSTYPE"<>'1110008042' AND "BA"."BUSINESSTYPE"<>'1110008041' AND "BA"."BUSINESSTYPE"<>'4020')
6 - access("CUSTOMERID"='20131119000089')
filter("CUSTOMERID"=:B1 AND :B2<>'1020040' OR "CUSTOMERID"=:B3 AND :B4='1020040')
26 rows selected.
改写成；
select sum(nvl(GetBusinessZongE('BusinessApprove', BA.SerialNo), 0)) as BusinessSum,
sum(nvl(GetBusinessChangKou('BusinessApprove', BA.SerialNo), 0)) as ApproveSum
from Business_Approve BA
inner join (select CustomerID from Customer_Info
where (EMcCustomerID = '' or CustomerID = '20131119000089')
group by CustomerID) Customer_Info
on ((case when BA.BusinessType= '1020040' then BA.ThirdPartyID2 else BA.CustomerID end))=Customer_Info.CustomerID
where 1 = 1
and not exists (select 'x'
from Business_Contract
where RelativeSerialNo = BA.SerialNo)
and BA.StatusFlag is null
and (BA.BusinessType like '1%' or BA.BusinessType like '2%')
and BA.BusinessType not in
('1110008040', '1110008041', '1110008042', '4010', '4020');
