这句为什么执行特别慢

Question

SQL> select sum ( charges ) from inp_bill_detail where performed_by ='34' and rcpt_no >= '90031714' and rcpt_no <= '90032590' and item_class ='A' and ordered_by ='21'  ;Elapsed: 00:00:24.38Execution Plan
----------------------------------------------------------                                          
Plan hash value: 4038433501                                                                         
                                                                                                    
--------------------------------------------------------------------------------------              
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |              
--------------------------------------------------------------------------------------              
|   0 | SELECT STATEMENT   |                 |     1 |    33 | 49972   (3)| 00:10:00 |              
|   1 |  SORT AGGREGATE    |                 |     1 |    33 |            |          |              
|*  2 |   TABLE ACCESS FULL| INP_BILL_DETAIL |     1 |    33 | 49972   (3)| 00:10:00 |              
--------------------------------------------------------------------------------------              
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter("PERFORMED_BY"='34' AND "ITEM_CLASS"='A' AND "ORDERED_BY"='21'                        
              AND "RCPT_NO">='90031714' AND "RCPT_NO"<='90032590')                                  
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement                                                       
Statistics
----------------------------------------------------------                                          
          0  recursive calls                                                                        
          0  db block gets                                                                          
     219060  consistent gets                                                                        
     208771  physical reads                                                                         
          0  redo size                                                                              
        417  bytes sent via SQL*Net to client                                                       
        396  bytes received via SQL*Net from client                                                 
          2  SQL*Net roundtrips to/from client                                                      
          0  sorts (memory)                                                                         
          0  sorts (disk)                                                                           
          1  rows processed   

Answer 1

你这个表是不是曾经很大，但是现在数据没有那么多了！！如果是这样的话就是高水位的问题，你可以执行
alter table table_name move tatblespace tablespace_name;
这样是挪动表的表空间，你可以还写现在表的表空间，这样会回收高水位！！

Answer 2

SQL> SELECT /*+ INDEX(INP_BILL_DETAIL) */ SUM(charges)
  2    FROM inp_bill_detail
  3   WHERE performed_by = '34'
  4     AND rcpt_no >= NVL('90031714', UID)
  5     AND rcpt_no <= '90032590'
  6     AND item_class = 'A'
  7     AND ordered_by = '21';Elapsed: 00:00:00.15Execution Plan
----------------------------------------------------------                                          
Plan hash value: 2182654951                                                                         
                                                                                                    
----------------------------------------------------------------------------------------------------
---                                                                                                 
                                                                                                    
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time   
  |                                                                                                 
                                                                                                    
----------------------------------------------------------------------------------------------------
---                                                                                                 
                                                                                                    
|   0 | SELECT STATEMENT              |                       |     1 |    33 |   100K  (1)| 00:20:0
4 |                                                                                                 
                                                                                                    
|   1 |  SORT AGGREGATE               |                       |     1 |    33 |            |        
  |                                                                                                 
                                                                                                    
|*  2 |   FILTER                      |                       |       |       |            |        
  |                                                                                                 
                                                                                                    
|*  3 |    TABLE ACCESS BY INDEX ROWID| INP_BILL_DETAIL       |     1 |    33 |   100K  (1)| 00:20:0
4 |                                                                                                 
                                                                                                    
|*  4 |     INDEX RANGE SCAN          | IND_1_INP_BILL_DETAIL |   524K|       |  1835   (2)| 00:00:2
3 |                                                                                                 
                                                                                                    
----------------------------------------------------------------------------------------------------
---                                                                                                 
                                                                                                    
                                                                                                    
Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 
                                                                                                    
   2 - filter(NVL('90031714',TO_CHAR(UID@!))<='90032590')                                           
   3 - filter("PERFORMED_BY"='34' AND "ITEM_CLASS"='A' AND "ORDERED_BY"='21')                       
   4 - access("RCPT_NO">=NVL('90031714',TO_CHAR(UID@!)) AND "RCPT_NO"<='90032590')                  
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic sampling used for this statement                                                       
Statistics
----------------------------------------------------------                                          
          0  recursive calls                                                                        
          0  db block gets                                                                          
      39853  consistent gets                                                                        
          0  physical reads                                                                         
          0  redo size                                                                              
        417  bytes sent via SQL*Net to client                                                       
        396  bytes received via SQL*Net from client                                                 
          2  SQL*Net roundtrips to/from client                                                      
          0  sorts (memory)                                                                         
          0  sorts (disk)                                                                           
          1  rows processed                                                                         SQL> SPOOL OFF
刚才用toad优化看了一下，它加了一段/*+ INDEX(INP_BILL_DETAIL) */ 后 ，快乐很多，可/* */不是注释的意思的嘛。我就不太明白了，我是写在PB程序中的，在pb中这段/* */是直接注释掉的意思，请问他为什么不用索引呢？
我在PB 中的语句是这样的select /*+ INDEX(INP_BILL_DETAIL) */   sum(costs) into :aa 
from inp_bill_detail 
where performed_by='34' and  to_number(rcpt_no)>=:b and to_number(rcpt_no)<=:a and item_class='A'  and ordered_by='21' ;

Answer 3

请问怎么用 hit ， 我的表有索引呢，但是为什么这句不会用呢，非得全表扫，加个索引提示/* */，又会被PB当作注释。。

Answer 4

1.重新收集一下该表（inp_bill_detail ）的统计信息：analyze table inp_bill_detail compute statistics;2.然后创建索引
在，"PERFORMED_BY" AND "ITEM_CLASS" AND "ORDERED_BY"  上，
可以分别建立，也可以创建联合索引