• PL/SQL 0.几秒出结果,SQL效率一定高吗?


    今天开发问我一个问题,PL/SQL很快出结果了,为什么应用还是很慢
    create index F_AGT_SAVB_ACCTINFO_H_idx4 on F_AGT_SAVB_ACCTINFO_H
    
    (agmt_id,last_trans_date,acct_bal,start_dt,end_dt,acct_status);
    
    SQL>  select  agmt_id, max(last_trans_date) last_trans_date, sum(acct_bal) acct_bal
                          from dwf.F_AGT_SAVB_ACCTINFO_H
                         where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')
                           and end_dt > to_date('2014-03-31', 'YYYY-MM-DD')
                           and acct_status <> '1'
                           group by  agmt_id;  2    3    4    5    6  
    
    596497 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1288393334
    
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation	      | Name			   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      | 			   |   698K|	34M|	   | 44078   (1)| 00:08:49 |
    |   1 |  HASH GROUP BY	      | 			   |   698K|	34M|	65M| 44078   (1)| 00:08:49 |
    |*  2 |   INDEX FAST FULL SCAN| F_AGT_SAVB_ACCTINFO_H_IDX4 |  1010K|	50M|	   | 33677   (1)| 00:06:45 |
    ------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    	      "ACCT_STATUS"<>'1' AND "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    
    Statistics
    ----------------------------------------------------------
    	  0  recursive calls
    	  0  db block gets
         124565  consistent gets
    	  0  physical reads
    	  0  redo size
       27208221  bytes sent via SQL*Net to client
         437946  bytes received via SQL*Net from client
          39768  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         596497  rows processed
    
    走INDEX FAST FULL SCAN 需要3S出结果
    
    
    走INDEX FULL SCAN :
    create index F_AGT_SAVB_ACCTINFO_H_idx4 on F_AGT_SAVB_ACCTINFO_H(agmt_id,last_trans_date,acct_bal);
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2644626439
    
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation		     | Name			  | Rows  | Bytes | Cost (%CPU)| Time	  |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT	     |				  |   698K|    34M|    14M  (1)| 47:22:11 |
    |   1 |  SORT GROUP BY NOSORT	     |				  |   698K|    34M|    14M  (1)| 47:22:11 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| F_AGT_SAVB_ACCTINFO_H	  |  1010K|    50M|    14M  (1)| 47:22:11 |
    |   3 |    INDEX FULL SCAN	     | F_AGT_SAVB_ACCTINFO_H_IDX4 |    14M|	  | 88373   (1)| 00:17:41 |
    -----------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("END_DT">TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
    	      "ACCT_STATUS"<>'1' AND "START_DT"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    
    
    Statistics
    ----------------------------------------------------------
    	  1  recursive calls
    	  0  db block gets
       14247288  consistent gets
        1189251  physical reads
    	  0  redo size
       28157922  bytes sent via SQL*Net to client
         437946  bytes received via SQL*Net from client
          39768  SQL*Net roundtrips to/from client
    	  0  sorts (memory)
    	  0  sorts (disk)
         596497  rows processed
    
    
     select /*+ index(F_AGT_SAVB_ACCTINFO_H F_AGT_SAVB_ACCTINFO_H_idx4)*/ agmt_id, max(last_trans_date) last_trans_date, 
    
    sum(acct_bal) acct_bal
                          from dwf.F_AGT_SAVB_ACCTINFO_H
                         where start_dt <= to_date('2014-03-31', 'YYYY-MM-DD')
                           and end_dt > to_date('2014-03-31', 'YYYY-MM-DD')
                           and acct_status <> '1'
                           group by  agmt_id;
    
    但是这个SQL 出结果确是0.几秒
    
    总结:PL/SQL这个工具坑爹,出结果快慢和SQL块慢没必然联系


    
    
    
  • 相关阅读:
    firefox配置
    安装gstreamer开发环境
    linux下批量替换文件内容(转)
    iptables详细教程:基础、架构、清空规则、追加规则、应用实例(转)
    iptables 使用
    如何用iptables实现NAT(转)
    Python 练习题
    Python unittest 参数化
    Python Logging模块
    Python 多进程
  • 原文地址:https://www.cnblogs.com/zhaoyangjian724/p/3797875.html
Copyright © 2020-2023  润新知