SQL直接库中查询很快，在应用中查特慢
来源：互联网 发布：淘宝隐形降权怎么办 编辑：程序博客网 时间：2024/05/16 10:38
最近在我们的应用中，发现有的查询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 maststoreqtyfrom 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=&StoreIDwhere 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 maststoreqtyfrom 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.branchidwhere 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果然在应用里面速度上来了。。。到此，搞定。
- SQL直接库中查询很快，在应用中查特慢
- 在数据库中sql查询很快，但在程序中查询较慢的解决方法
- 直接SQL查询在Magento
- 奇怪的问题，存储过程查询分析器执行很快，在.NET中执行却很慢（超时）
- 存储过程查询分析器执行很快，在ASP.NET中执行却很慢（超时）
- 在SQL中直接使用存储过程查询返回的结果集
- 直接在PHP中写数据库查询
- 同样的SQL语句在查询分析器执行很快，但是网站上执行超时的诡异问题
- 在应用中直接打开QQ聊天
- 在IE中直接连接SQL数据库
- 在IE中直接连接SQL数据库
- 在IE中直接连接SQL数据库
- 直接在sql server中更新数据
- 在Magento中直接使用SQL语句
- 在Yii中直接使用sql
- WMS-直接SQL 查询
- 直接执行SQL查询
- sql server如何在查询结果页面直接编辑数据？
- iOS 合并.a文件，制作通用静态库
- c#里的checkbox
- Redis的PHP客户端
- android 手势操作GestureDetector
- C#中bool类型不能和其它整数类型转换
- SQL直接库中查询很快，在应用中查特慢
- erlang & php 操作mysql的效率测试
- Parallel scan in HBase
- 黑马程序员-------Java继承上
- eclipse启动无响应，停留在Loading workbench状态
- IOS 理解iPhone项目的BaseSDK和DeploymentTarget含义
- UNIX IO小结
- C++ 调试技巧
- 字符转义
