今天开发问我一个问题,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块慢没必然联系