最近在我们的应用中,发现有的查询SQL在应用里面反映奇慢,但直接把语句丢进DB里面查又是相当快。对于变量给定的参数是一致的。今日就来记一下这样的案例处理方法。
原SQL:
select a.prodID PROID,a.prodNO PRONO,a.prodNAME PRONAME,a.prodMEMORYCODE PROMEMORYCODE,
nvl(a.PRODSPECIFICATION,'') PROSPECIFICATION,
nvl(a.PACKAGEUNIT,'') PACKAGEUNIT,
nvl(a.BIGPACKAGEQUANTITY,0) BIGPACKAGEQUANTITY,
nvl(a.MIDPACKAGEQUANTITY,0) MIDPACKAGEQUANTITY,
nvl(a.MANUFACTURE,'') MANUFACTURE,
nvl(a.CHINESEDRUGYIELDLY,'') CHINESEDRUGYIELDLY,
nvl(a.PRESCRIPTIONCLASS,'') PrescriptionClass,
nvl(b.RETAILPRICE,0) RetailPrice,
nvl(b.WholeSaleprice,0) TradePrice,
'N' isgift,'N' islmis ,'N' isgift,'N' islmis ,
decode(IS_DECIMAL,1,'是','否') IsDecimal,
nvl(b.minsellprice,0) MinSellPrice,
nvl(b.minsellpricelimit,0) minsellpricelimit,
nvl(b.SELLGUIDPRICE,0) selltaxprice,
decode(a.is_unpick,0,'N','Y') isUnpick,
decode(a.is_unpick,1,'是','否') isUnpickCN,
nvl(a.taxrate,0) taxrate,
nvl(a.busitype,'未维护') busitypeid,nvl(a.busitypetext,'') BusiTypeName,
nvl(a.prodSCOPENO,'') PROSCOPENO,
decode(a.IS_SPECIALDRUGS,0,'否','是') IsSpecialDrugs,
nvl(a.RespectiveLicense,'') RespectiveLicense,
nvl(a.purchaseid,'') purchaseid,
nvl(a.purchaser,'未维护') STAFFNAME,
decode(a.is_essentialdrugs,0,'否','是') is_essentialdrugs,
nvl((select max(invbalqty-preassignedqty-notavailableqty-nvl(n.preqty,0)) from TB_GOS_ACCOUNT_O_STOREINVEN
where prodid=a.prodid and branchid=a.branchid and (storeid=&StoreID or trim(&StoreID) is null)),0) storeqty,
0 StockMinLimit,nvl(e.costprice,0) CostPrice,
nvl(case when &BranchID='FWA' then e.costaccounting else h.costaccounting end,0) costaccounting,
nvl(h.invbalqty,0) centerInvQty,a.is_electronicmonitoringtext /*decode(a.is_electronicmonitoring,0,'否','是')*/ IsElectronicProd,a.approvalno,
nvl(b.minaccprice,0) minaccprice,nvl(A.selltype,'') as selltype,0 suppstoreqty,0 maststoreqty
from vw_common_prod a
left join TB_COMMON_PROCPRICE b on a.prodID=b.proID and a.branchid=b.branchid
left join TB_GOS_ACCOUNT_O_BRANINVEN e on a.prodid=e.prodid and a.branchid=e.branchid
left join tb_gos_account_upbranchinven h on a.prodno=h.prodno
left join vw_common_prodpreAssigned n on a.branchid=n.branchid and a.prodid=n.prodid and n.storeid=&StoreID
where a.branchid = &branchid
and a.deleteflag = 0
and (trim(&BusiTypeID)= 'CHC' or 1=1)
and (a.prodid in (
select prodid from tb_gos_sale_orddet where branchid=&branchid
and (billid in (select column_value from table(split(&BillID,','))))
union
select prodid from tb_gos_sale_salestockindet t where t.branchid=&branchid and billid=&BillID and branchid = &branchid
union
select proid as prodid from tb_gos_purchase_purstockindet t where t.branchid=&branchid and billid=&BillID
) or &BillID is null) ;
对应的库中执行计划:
已用时间: 00: 00: 00.43
执行计划
----------------------------------------------------------
Plan hash value: 3848047773
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33957 | 10M| | 7094 (1)| 00:01:26 |
| 1 | SORT AGGREGATE | | 1 | 37 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_O_STOREINVEN | 1 | 37 | | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TB_ACCOUNT_O_STOREINVEN_BPSID | 1 | | | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 33957 | 10M| | 7094 (1)| 00:01:26 |
|* 5 | TABLE ACCESS FULL | TB_GOS_ACCOUNT_O_BRANINVEN | 45424 | 1064K| | 419 (1)| 00:00:06 |
|* 6 | HASH JOIN RIGHT OUTER | | 33957 | 9782K| | 6674 (1)| 00:01:21 |
| 7 | VIEW | VW_COMMON_PRODPREASSIGNED | 3 | 69 | | 13 (8)| 00:00:01 |
| 8 | HASH GROUP BY | | 3 | 105 | | 13 (8)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | TB_GOS_STOCK_STOCKPREEMPTION | 5 | 175 | | 12 (0)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 33957 | 9019K| 3304K| 6661 (1)| 00:01:20 |
|* 11 | TABLE ACCESS FULL | TB_COMMON_PROCPRICE | 67609 | 2508K| | 800 (1)| 00:00:10 |
|* 12 | HASH JOIN RIGHT OUTER | | 33353 | 7621K| 2064K| 5311 (1)| 00:01:04 |
| 13 | TABLE ACCESS FULL | TB_GOS_ACCOUNT_UPBRANCHINVEN | 68164 | 1264K| | 243 (1)| 00:00:03 |
|* 14 | HASH JOIN | | 33353 | 7002K| | 4608 (1)| 00:00:56 |
| 15 | VIEW | VW_NSO_1 | 78276 | 687K| | 927 (1)| 00:00:12 |
| 16 | SORT UNIQUE | | 78276 | 2522K| 3080K| 927 (20)| 00:00:12 |
| 17 | UNION-ALL | | | | | | |
|* 18 | HASH JOIN | | 78272 | 2522K| | 54 (2)| 00:00:01 |
| 19 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 8168 | 16336 | | 29 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | TB_GOS_SALE_ORDDET | 2894 | 89714 | | 24 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_SALESTOCKINDET | 1 | 31 | | 165 (0)| 00:00:02 |
|* 22 | INDEX RANGE SCAN | IX_SALESTOCKINDET_BILLDATE | 1 | | | 164 (0)| 00:00:02 |
|* 23 | TABLE ACCESS BY INDEX ROWID | TB_GOS_PURCHASE_PURSTOCKINDET | 3 | 93 | | 6 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | IX_PURCHASE_PURSTKINDET_BILL | 6 | | | 3 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | VW_COMMON_PROD | 70295 | 13M| | 3680 (1)| 00:00:45 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BRANCHID"=:B1 AND "PRODID"=:B2 AND "STOREID"='CKI00000017')
4 - access("A"."BRANCHID"="E"."BRANCHID"(+) AND "A"."PRODID"="E"."PRODID"(+))
5 - filter("E"."BRANCHID"(+)='FD3')
6 - access("A"."PRODID"="N"."PRODID"(+) AND "A"."BRANCHID"="N"."BRANCHID"(+))
9 - filter("DELETEFLAG"=0 AND "STOREID"='CKI00000017' AND "BRANCHID"='FD3')
10 - access("A"."BRANCHID"="B"."BRANCHID"(+) AND "A"."PRODID"="B"."PROID"(+))
11 - filter("B"."BRANCHID"(+)='FD3')
12 - access("A"."PRODNO"="H"."PRODNO"(+))
14 - access("A"."PRODID"="PRODID")
18 - access("BILLID"=VALUE(KOKBF$))
20 - filter("BRANCHID"='FD3')
22 - access("T"."BRANCHID"='FD3' AND "BILLID"='FD3XTR00023856')
filter("BILLID"='FD3XTR00023856')
23 - filter("T"."BRANCHID"='FD3')
24 - access("BILLID"='FD3XTR00023856')
25 - filter("A"."BRANCHID"='FD3' AND "A"."DELETEFLAG"=0)
统计信息
----------------------------------------------------------
25 recursive calls
1 db block gets
18903 consistent gets
0 physical reads
144 redo size
2983 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
从上面的信息看:统计信息逻辑读近2万,估算的行数3万多,cost 7千多。 实际情况,查询出来的结果只有1条记录。
估算的行数不准,于是我把表的统计信息重新收集了一下,情况没有任何改善。
再细看下上面的执行计划,它是先对union那部分查询后,与vw_common_prod做hash join,最后和及其他几个表做连接后再在上面做过滤,显然这样速度慢了,因为当union那部分数据集直接与vw_common_prod做连接后就过滤数据,可以用到vw_common_prod上面的索引,比full table scan是要快很多的。总之一句话,将full table scan 、filter改成index、rowid方式来提速。
vw_common_prod --216810 records ,prodprice表数据量和vw_common_prod差不多。
接着分析执行计划,初步判断问题在于全表扫描那一块儿,尤其是表 vw_common_prod,分析了下逻辑,觉得此处在表vw_common_prod上可以利用基于(branchid,prodid)列建立的复合索引来加快速度。
于是做了一个10053事件,里面提供了一个cost最小的执行计划,如下:
============
Plan Table
============
-------------------------------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 321 | |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_O_STOREINVEN | 1 | 37 | 3 | 00:00:01 |
| 3 | INDEX UNIQUE SCAN | TB_ACCOUNT_O_STOREINVEN_BPSID| 1 | | 2 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 10 | 3190 | 321 | 00:00:04 |
| 5 | NESTED LOOPS OUTER | | 10 | 3000 | 301 | 00:00:04 |
| 6 | NESTED LOOPS OUTER | | 10 | 2620 | 282 | 00:00:04 |
| 7 | HASH JOIN OUTER | | 10 | 2380 | 267 | 00:00:04 |
| 8 | NESTED LOOPS | | | | | |
| 9 | NESTED LOOPS | | 10 | 2150 | 253 | 00:00:04 |
| 10 | VIEW | VW_NSO_1 | 23 | 207 | 207 | 00:00:03 |
| 11 | SORT UNIQUE | | 23 | 751 | 207 | 00:00:03 |
| 12 | UNION-ALL | | | | | |
| 13 | NESTED LOOPS | | | | | |
| 14 | NESTED LOOPS | | 19 | 627 | 33 | 00:00:01 |
| 15 | COLLECTION ITERATOR PICKLER FETCH | SPLIT | 2 | 4 | 29 | 00:00:01 |
| 16 | INDEX RANGE SCAN | IX_SALE_ORDDET_BILLID | 10 | | 1 | 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_ORDDET | 10 | 310 | 2 | 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_SALESTOCKINDET | 1 | 31 | 165 | 00:00:02 |
| 19 | INDEX RANGE SCAN | IX_SALESTOCKINDET_BILLDATE | 1 | | 164 | 00:00:02 |
| 20 | TABLE ACCESS BY INDEX ROWID | TB_GOS_PURCHASE_PURSTOCKINDET| 3 | 93 | 6 | 00:00:01 |
| 21 | INDEX RANGE SCAN | IX_PURCHASE_PURSTKINDET_BILL | 6 | | 3 | 00:00:01 |
| 22 | INDEX UNIQUE SCAN | IX_BPID | 1 | | 1 | 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | VW_COMMON_PROD | 1 | 206 | 2 | 00:00:01 |
| 24 | VIEW | VW_COMMON_PRODPREASSIGNED | 107 | 2461 | 13 | 00:00:01 |
| 25 | HASH GROUP BY | | 107 | 3745 | 13 | 00:00:01 |
| 26 | TABLE ACCESS FULL | TB_GOS_STOCK_STOCKPREEMPTION | 127 | 4445 | 12 | 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_O_BRANINVEN | 1 | 24 | 2 | 00:00:01 |
| 28 | INDEX UNIQUE SCAN | TB_ACCOUNT_O_BRANINVEN_BPID | 1 | | 1 | 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_PROCPRICE | 1 | 38 | 2 | 00:00:01 |
| 30 | INDEX RANGE SCAN | TB_PROCPRICE_BPID | 1 | | 1 | 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_UPBRANCHINVEN | 1 | 19 | 2 | 00:00:01 |
| 32 | INDEX RANGE SCAN | IX_UPBRANCHINVEN_PORDNO | 1 | | 1 | 00:00:01 |
-------------------------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("BRANCHID"=:B1 AND "PRODID"=:B2 AND "STOREID"='CKI00000017')
7 - access("A"."PRODID"="N"."PRODID" AND "A"."BRANCHID"="N"."BRANCHID")
16 - access("BRANCHID"='FD3' AND "BILLID"=VALUE(KOKBF$))
19 - access("T"."BRANCHID"='FD3' AND "BILLID"='FD3XTR00023856')
19 - filter("BILLID"='FD3XTR00023856')
20 - filter("T"."BRANCHID"='FD3')
21 - access("BILLID"='FD3XTR00023856')
22 - access("A"."BRANCHID"='FD3' AND "A"."PRODID"="PRODID")
23 - filter("A"."DELETEFLAG"=0)
26 - filter(("DELETEFLAG"=0 AND "STOREID"='CKI00000017' AND "BRANCHID"='FD3'))
28 - access("E"."BRANCHID"='FD3' AND "A"."PRODID"="E"."PRODID")
30 - access("B"."BRANCHID"='FD3' AND "A"."PRODID"="B"."PROID")
32 - access("A"."PRODNO"="H"."PRODNO")
从上面看出,在vw_common_prod与union 那块儿的数据集使用NL连接并利用了表vw_common_prod上基于列(branchid,prodid) 的复合索引,之后其他的表就结合数据量及表结构上的索引信息,得到了一个查询最优的方案。
调整后的SQL:
with t as
(
select distinct prodid,branchid from tb_gos_sale_orddet where branchid=&branchid
and (billid in (select column_value from table(split(&BillID,','))))
union all
select prodid,branchid from tb_gos_sale_salestockindet t where t.branchid=&branchid and billid=&BillID
union all
select proid as prodid,branchid from tb_gos_purchase_purstockindet t where t.branchid=&branchid and billid=&BillID
)
select a.prodID PROID,a.prodNO PRONO,a.prodNAME PRONAME,a.prodMEMORYCODE PROMEMORYCODE,
nvl(a.PRODSPECIFICATION,'') PROSPECIFICATION,
nvl(a.PACKAGEUNIT,'') PACKAGEUNIT,
nvl(a.BIGPACKAGEQUANTITY,0) BIGPACKAGEQUANTITY,
nvl(a.MIDPACKAGEQUANTITY,0) MIDPACKAGEQUANTITY,
nvl(a.MANUFACTURE,'') MANUFACTURE,
nvl(a.CHINESEDRUGYIELDLY,'') CHINESEDRUGYIELDLY,
nvl(a.PRESCRIPTIONCLASS,'') PrescriptionClass,
nvl(b.RETAILPRICE,0) RetailPrice,
nvl(b.WholeSaleprice,0) TradePrice,
'N' isgift,'N' islmis ,'N' isgift,'N' islmis ,
decode(IS_DECIMAL,1,'是','否') IsDecimal,
nvl(b.minsellprice,0) MinSellPrice,
nvl(b.minsellpricelimit,0) minsellpricelimit,
nvl(b.SELLGUIDPRICE,0) selltaxprice,
decode(a.is_unpick,0,'N','Y') isUnpick,
decode(a.is_unpick,1,'是','否') isUnpickCN,
nvl(a.taxrate,0) taxrate,
nvl(a.busitype,'未维护') busitypeid,nvl(a.busitypetext,'') BusiTypeName,
nvl(a.prodSCOPENO,'') PROSCOPENO,
decode(a.IS_SPECIALDRUGS,0,'否','是') IsSpecialDrugs,
nvl(a.RespectiveLicense,'') RespectiveLicense,
nvl(a.purchaseid,'') purchaseid,
nvl(a.purchaser,'未维护') STAFFNAME,
decode(a.is_essentialdrugs,0,'否','是') is_essentialdrugs,
nvl((select max(invbalqty-preassignedqty-notavailableqty-nvl(n.preqty,0)) from TB_GOS_ACCOUNT_O_STOREINVEN
where prodid=a.prodid and branchid=a.branchid and (storeid=&StoreID or trim(&StoreID) is null)),0) storeqty,
0 StockMinLimit,nvl(e.costprice,0) CostPrice,
nvl(case when &BranchID='FWA' then e.costaccounting else h.costaccounting end,0) costaccounting,
nvl(h.invbalqty,0) centerInvQty,a.is_electronicmonitoringtext /*decode(a.is_electronicmonitoring,0,'否','是')*/ IsElectronicProd,a.approvalno,
nvl(b.minaccprice,0) minaccprice,nvl(A.selltype,'') as selltype,0 suppstoreqty,0 maststoreqty
from vw_common_prod a
left join TB_COMMON_PROCPRICE b on a.branchid=b.branchid and a.prodID=b.proID
left join TB_GOS_ACCOUNT_O_BRANINVEN e on a.prodid=e.prodid and a.branchid=e.branchid
left join tb_gos_account_upbranchinven h on a.prodid=h.prodid and a.branchid=h.branchid
left join vw_common_prodpreAssigned n on a.branchid=n.branchid and a.prodid=n.prodid and n.storeid=&StoreID
inner join t on a.prodid=t.prodid and t.branchid=a.branchid
where a.branchid = &branchid
and a.deleteflag = 0
and (trim(&BusiTypeID)= 'CHC' or 1=1)
and (a.prodid in (
select prodid from t
) or &BillID is null);
调整后SQL对应的执行计划:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 335 | 254 (71)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 37 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_O_STOREINVEN | 1 | 37 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TB_ACCOUNT_O_STOREINVEN_BPSID | 1 | | 2 (0)| 00:00:01 |
| 4 | TEMP TABLE TRANSFORMATION | | | | | |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6716_CC2631DD | | | | |
| 6 | UNION-ALL | | | | | |
| 7 | HASH UNIQUE | | 262 | 8646 | 57 (8)| 00:00:01 |
|* 8 | HASH JOIN | | 78272 | 2522K| 54 (2)| 00:00:01 |
| 9 | COLLECTION ITERATOR PICKLER FETCH| SPLIT | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | TB_GOS_SALE_ORDDET | 2894 | 89714 | 24 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_SALESTOCKINDET | 1 | 31 | 165 (0)| 00:00:02 |
|* 12 | INDEX RANGE SCAN | IX_SALESTOCKINDET_BILLDATE | 1 | | 164 (0)| 00:00:02 |
|* 13 | TABLE ACCESS BY INDEX ROWID | TB_GOS_PURCHASE_PURSTOCKINDET | 3 | 93 | 6 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IX_PURCHASE_PURSTKINDET_BILL | 6 | | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 335 | 26 (12)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 1 | 297 | 24 (13)| 00:00:01 |
|* 17 | HASH JOIN | | 1 | 273 | 22 (14)| 00:00:01 |
|* 18 | HASH JOIN OUTER | | 1 | 261 | 20 (15)| 00:00:01 |
| 19 | NESTED LOOPS OUTER | | 1 | 238 | 6 (17)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 215 | 5 (20)| 00:00:01 |
| 21 | VIEW | VW_NSO_1 | 266 | 2394 | 2 (0)| 00:00:01 |
| 22 | HASH UNIQUE | | 1 | 2394 | | |
| 23 | VIEW | | 266 | 2394 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6716_CC2631DD | 266 | 3192 | 2 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | VW_COMMON_PROD | 1 | 206 | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | IX_BPID | 1 | | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_UPBRANCHINVEN | 1 | 23 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | UQ_UPBRANCHINVEN_BPPID | 1 | | 0 (0)| 00:00:01 |
| 29 | VIEW | VW_COMMON_PRODPREASSIGNED | 3 | 69 | 13 (8)| 00:00:01 |
| 30 | HASH GROUP BY | | 3 | 105 | 13 (8)| 00:00:01 |
|* 31 | TABLE ACCESS FULL | TB_GOS_STOCK_STOCKPREEMPTION | 5 | 175 | 12 (0)| 00:00:01 |
|* 32 | VIEW | | 266 | 3192 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6716_CC2631DD | 266 | 3192 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | TB_GOS_ACCOUNT_O_BRANINVEN | 1 | 24 | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | TB_ACCOUNT_O_BRANINVEN_BPID | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_PROCPRICE | 1 | 38 | 2 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | TB_PROCPRICE_BPID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BRANCHID"=:B1 AND "PRODID"=:B2 AND "STOREID"='CKI00000017')
8 - access("BILLID"=VALUE(KOKBF$))
10 - filter("BRANCHID"='FD3')
12 - access("T"."BRANCHID"='FD3' AND "BILLID"='FD3XTR00023856')
filter("BILLID"='FD3XTR00023856')
13 - filter("T"."BRANCHID"='FD3')
14 - access("BILLID"='FD3XTR00023856')
17 - access("A"."PRODID"="T"."PRODID" AND "T"."BRANCHID"="A"."BRANCHID")
18 - access("A"."PRODID"="N"."PRODID"(+) AND "A"."BRANCHID"="N"."BRANCHID"(+))
25 - filter("A"."DELETEFLAG"=0)
26 - access("A"."BRANCHID"='FD3' AND "A"."PRODID"="PRODID")
28 - access("H"."BRANCHID"(+)='FD3' AND "A"."PRODID"="H"."PRODID"(+))
31 - filter("DELETEFLAG"=0 AND "STOREID"='CKI00000017' AND "BRANCHID"='FD3')
32 - filter("T"."BRANCHID"='FD3')
35 - access("E"."BRANCHID"(+)='FD3' AND "A"."PRODID"="E"."PRODID"(+))
37 - access("B"."BRANCHID"(+)='FD3' AND "A"."PRODID"="B"."PROID"(+))
统计信息
----------------------------------------------------------
80 recursive calls
9 db block gets
392 consistent gets
2 physical reads
532 redo size
2981 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
果然在应用里面速度上来了。。。到此,搞定。