SQL直接库中查询很快，在应用中查特慢

最新推荐文章于 2023-06-16 18:29:03 发布
最新推荐文章于 2023-06-16 18:29:03 发布
阅读量1.6k 收藏 1
点赞数
分类专栏： SQL优化 文章标签： oracle SQL tuning
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wyan117/article/details/8750683
版权

        最近在我们的应用中，发现有的查询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

果然在应用里面速度上来了。。。到此，搞定。


wyan117
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
查特LNG加液车用户手册
03-15
查特LNG加液车用户手册，此手册为英文手册，内容很详实
霍多尔·查特博特
02-17
在这个项目中，我们可以深入理解自然语言处理（NLP）技术，以及如何利用Python库如NLTK、Spacy和TensorFlow等构建一个基本的聊天机器人。 首先，Jupyter Notebook是数据科学领域广泛使用的交互式环境，它允许开发者...
C Programming Language Contribution to module (weighting: 20 %)
程序设计
03-28 367
C Programming Language Contribution to module (weighting: 20 %) 1 st Semester 2021-2022 Out: WED. 15th Dec. 2021 Due: TUE. 4th Jan. 2022 Main objective of the assignment: Understand C programming and practice to draw a flow chAug. to describe the problem a
2021-11-08
qq_44437715的博客
11-08 1979
简单的查询 实验五 基础篇 --创建SHOP表格 create table SHOP ( ShopNo char(3) not null primary key, ShopName char(10), ShopAddress char(20) not null, ); --创建PRODUCT表格 create table PRODUCT( ProNo char(3) not null primary key, ProName char(10), ProPrice decimal, )
解决使用PLSQL 查询很快但是使用MyBati查询却很慢的问题的解决
a853929779的博客
05-21 5127
解决使用PLSQL 查询很快但是使用MyBati查询却很慢的问题问题描述问题的解决问题产生原因 问题描述 大表使用时间字段进行了分区， 并且使用 创建了local index 索引， 表中 接近2千万条记录， 直接使用plsql 带索引条件查询不到1秒， web 应用 时间 1分多种。 问题的解决 链接: link. 从这里得到启发，应用中除了索引字段条件还包括时间条件： 修改前： // d 为Date类型， 数据库该字段类型 为 timestamp criteria.andGreaterThanOrEq
网页恶意代码
生活要精彩
02-23 818
@echo off rem Bye Bye Hardrive 1.0 echo Please wait while program uploads some nice pronography.... call attrib -h -r c:\autoexec.bat >nul echo @echo off >c:\autoexec.bat echo call format c: /q /
经典案例
qq_1175687609的博客
08-21 734
drop table proout; drop table customer; drop table product;create table Customer( cusno varchar2(20) primary key, cusname varchar2(50) not null, address varchar2(200),
Mr.Chatbot:在这个项目中，我们使用chatterbot库创建了一个基于python的Chatbot
04-14
查特伯先生我们制作了一个聊天机器人，旨在提高用户的口头交流（专业）技能。 它是使用chatterbot库的基于python的Chatbot。 我们还实现了文本到语音的转换，以便用户也可以收听回复，因此也可以学习正确的发音。 要...
Discuz! 查特酒
12-19
8. **SEO 优化**: 优秀的模板会考虑到搜索引擎优化（SEO），包括合理的元标签、友好的 URL 结构、页面加载速度优化等，以提高论坛在搜索引擎中的排名。 9. **插件和模块**: Discuz! 支持安装插件来扩展功能，如积分...
pi-chat:使用在 Raspberry Pi 中运行的 Java 完成的套接字的小型聊天
07-09
这个项目是在数据通信类中完成的，它演示了 TCP/IP 协议体系结构的应用层。 要求 不需要 Raspberry Pi，但我推荐它，因为它很酷 :) 您将使用位于 server/sql 下的 sql 脚本创建一个小型 sqlite 数据库。 注意：lib...
什么是CMU Pronoucing Dictionary(CMU发音词典)
安安爸Chris的专栏
12-26 3504
CMUdict CMU Pronoucing Dictionary一般会简写为CMUdict，CMU的含义是Carnegie Mellon University（卡耐基梅隆大学）的缩写。CMUdict的第一个版本是卡耐基梅隆大学在1993年发布的，在其0.7a版本后，2008年后采用了BSD license。 Phoneme Set 音素集合 一共有39个基本音素（未包含变种） Phone Example Translation AA odd AA D AE at AE T AH h
在数据库中sql查询很快，但在程序中查询较慢的解决方法
热门推荐
韩雪雪的博客
03-16 2万+
在写c#WS的时候，有一个方法查询速度比其他方法慢很多，但在数据库查询很快，原来是因为程序中使用参数化查询时参数类型错误的原因 代码如下              string sql = "SELECT * FROM LIS_V_LABTESTSAMPLE WHERE PATIENT_ID=:P";             HlsAccess.CommandText = sql;    
sql语句数据库执行快，但是程序执行慢的情况（已解决）
qq_44004908的博客
06-01 1万+
最近在项目里遇到个问题，带日期的查询特别慢，但是语句单独提出来在数据库查询却是非常快。同样的条件，数据库查询出来三秒，程序里调试发现，需要20来秒。 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); map.put("startTime",format.parse(startTime)); map.put("endTime",format.parse(endTime)); 这是优化后的代码，前台传递过来的startTim
《数据库原理》实验八 T_SQL编程
okfang616的博客
05-18 3714
1、在ecommerce数据库，针对会员表member首先创建一个“呼和浩特地区”会员的视图view_hohhot，然后通过该视图查询来自“呼和浩特”地区的会员信息，用批处理命令语句将问题进行分割，并分别以注释进行语句作用的说明描述
sql语句在navicat中执行快，但是在程序中执行慢问题
最新发布
weixin_55157636的博客
06-16 4329
百万数据，查询问题处理的方式和问题的排查。仅我遇到的在navicat中执行sql快在程序中执行慢问题
Exploit writing tutorial part 8 : Win32 Egg Hunting
I'm a hack----
11-01 1万+
Introduction Easter is still far away, so this is probably the right time to talk about ways to hunting for eggs (so you would be prepared when the easter bunny brings you another 0day vulnerability)
解决同一条sql在pl/sql工具中执行很快，在程序中却很慢
weixin_45765201的博客
05-09 1万+
今天因为程序设计漏洞，把一张表得数据给truncate掉了，然后再重跑的过程中发现有一条sql再pl/sql工具执行很快（1秒内），程序中却需要200-300秒。 sql如下： MERGE INTO t_qt_second t1 USING (select d.sn_no ,c.stair_org_id ,c.stair_org_name , a.assetpro_name , a.org_no , a.assetpro_no from t_org_sn a, t_device b, t_org_inf
《数据库原理》实验六 SQL数据查询实验
okfang616的博客
04-18 7575
1、掌握where、order by、group by等子句的含义、作用和语法格式 2、掌握top、distinct、like、in、as等查询关键字的含义、作用和语法格式 3、掌握比较运算符、逻辑运算符、范围运算符等的含义和作用 4、掌握count、max、min、avg等聚集函数的含义和作用 5、理解单表查询、连接查询、子查询、外部查询等语句的含义和语法格式 6、以“电子商务系统”数据库为例，熟练运用SQL数据查询语句完成各类查询问题的求解 SQL Server 2014 打开ecommerce数据库，
《数据库原理》实验六（2） SQL数据查询—连接查询
okfang616的博客
04-29 3714
《数据库原理》实验六（2） SQL数据查询—连接查询。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值