分页查询遇到的奇怪问题

本人某表数据300万条，分页语句如下:
 select * from ( select * from FES.FES_CONTACTHISTORY this_ where
  this_.PHONE1='111'  //全表都为'111'
 and 
  this_.PARTY_TYPE='6' //在0～9内随机生成
 and 
  this_.VECTOR_ID='28' //在0～100内随机生成
 and 
  this_.VERSION_ID='11' //在0～100内随机生成
 and
  this_.CREATEDBY IN    //在0～120内随机生成
('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'63')
   and this_.STARTTIME>=to_date
   (
      '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
   ) and this_.STARTTIME<=to_date
   (
      '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
   )order by this_.STARTTIME DESC
   )where rownum <= 200 
以上出现的各个字段都已经建立索引。
奇怪之处：
当VECTOR_ID='28'，搜索时间大概70秒，所中数据14条
当VECTOR_ID='24'，搜索时间为2秒以内，所中数据16条
用其他某些字符进行搜索，时间要么大概2秒，要么就是大概70秒。而且所中数据量相差只在30条以内。
有无高人明白是怎么回事？？？

解决方案 »

  1.   

    CREATEDBY IN   (1...63)可以改成  1<=CREATEDBY <=63
      

  2.   


    lpc19598188 对优化的东西好象比较有经验,学习ing
      

  3.   

    具体要看系统生成的执行计划，根据执行计划来调整你的SQL。至于不执行就能说多执行计划，我想，这是瞎扯，或正好猜对了执行计划。因为执行计划的生成与很多原因有关系，比如系统中是否刚好有人执行过该SQL，系统初始化参数的配置等等。如果某人不小心说对了一个执行计划， 那可能是SQL太简单了，比如select * from all_objects.毫无疑问将执行全表扫描。 某个SQL的执行计划可能会因为DBA调整了一个参数就变掉了。此外直接使用提示也会改变执行计划。
      

  4.   

    上面的select * from all_objects,应为select * from 某表.
      

  5.   

    Plan hash value: 3508107676;
     ;
    -----------------------------------------------------------------------------------------------------;
    | Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |;
    -----------------------------------------------------------------------------------------------------;
    |   0 | SELECT STATEMENT               |                    |     1 | 11018 |   108   (1)| 00:00:02 |;
    |*  1 |  COUNT STOPKEY                 |                    |       |       |            |          |;
    |   2 |   VIEW                         |                    |     1 | 11018 |   108   (1)| 00:00:02 |;
    |*  3 |    SORT ORDER BY STOPKEY       |                    |     1 | 11018 |   108   (1)| 00:00:02 |;
    |*  4 |     TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY |     1 | 11018 |   107   (0)| 00:00:02 |;
    |*  5 |      INDEX RANGE SCAN          | CH_VERSIONID       | 31675 |       |    99   (0)| 00:00:02 |;
    -----------------------------------------------------------------------------------------------------;
     ;
    Predicate Information (identified by operation id):;
    ---------------------------------------------------;
     ;
       1 - filter(ROWNUM<=200);
       3 - filter(ROWNUM<=200);
       4 - filter("THIS_"."PHONE1"='111' AND "THIS_"."PARTY_TYPE"='6' AND ;
                  "THIS_"."VECTOR_ID"='14' AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."CREATEDBY"='10' OR ;
                  "THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"."CREATEDBY"='13' OR ;
                  "THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."CREATEDBY"='16' OR ;
                  "THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."CREATEDBY"='19' OR ;
                  "THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CREATEDBY"='21' OR ;
                  "THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."CREATEDBY"='24' OR ;
                  "THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."CREATEDBY"='27' OR ;
                  "THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."CREATEDBY"='3' OR ;
                  "THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."CREATEDBY"='32' OR ;
                  "THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."CREATEDBY"='35' OR ;
                  "THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."CREATEDBY"='38' OR ;
                  "THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CREATEDBY"='40' OR ;
                  "THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."CREATEDBY"='43' OR ;
                  "THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."CREATEDBY"='46' OR ;
                  "THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."CREATEDBY"='49' OR ;
                  "THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CREATEDBY"='51' OR ;
                  "THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."CREATEDBY"='54' OR ;
                  "THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."CREATEDBY"='57' OR ;
                  "THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."CREATEDBY"='6' OR ;
                  "THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."CREATEDBY"='62' OR ;
                  "THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR ;
                  "THIS_"."CREATEDBY"='9') AND "THIS_"."STARTTIME">=TIMESTAMP'2008-11-01 00:00:00' AND ;
                  "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59');
       5 - access("THIS_"."VERSION_ID"='11');
     ;
    Note;
    -----;
       - dynamic sampling used for this statement;
    这个是执行计划。本人是菜鸟，不知道怎么看
      

  6.   

    从上面这个执行计划来看，你没有收集过统计信息。
    先收集表和索引的统计信息，
    再把执行2秒和执行70秒的SQL的执行计划分别贴出来。
      

  7.   

    统计信息，数据分布改变会影响执行计划。
    可能是统计信息旧了，VECTOR_ID列倾斜等等。
    还是LZ收集后再把两个执行计划贴出来对比一下。
      

  8.   


    呵呵, dinya2003老大, 您看, 即使不出统计信息,
    DBA对自己sql语句的执行计划还是有一定程度期望的, 
    知道什么计划是可能的什么计划是不正常的.如果是一个长期维护自己对情况有充分了解的数据库, 执行计划不是那么难以预测的吧 
     
      

  9.   

    看dbms_stats还有lZ,看你的语句最后是：
    ...)order by this_.STARTTIME DESC
    )where rownum <= 200 
    如果你的意思是要取按STARTTIME降序的前200条记录，那么你的这个SQL是错误的。
      

  10.   


    按STARTTIME降序的前200条记录－就是这样的意思。怎么会有错呢？我的查出来的结果就是我想要的啊
      

  11.   

    恩。是我把括号看错了。
    你先收集统计信息：
    exec dbms_stats.gather_table_stats('FES','FES_CONTACTHISTORY',cascade=>true)
      

  12.   

    看dbms_stats －可否讲得详细点，我是完全的菜鸟。GOOGLE出来的东西也看不懂
      

  13.   

    Plan hash value: 2300518666;
     ;
    ----------------------------------------------------------------------------------------------------------;
    | Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |;
    ----------------------------------------------------------------------------------------------------------;
    |   0 | SELECT STATEMENT                    |                    |    17 |   182K|   384   (2)| 00:00:05 |;
    |*  1 |  COUNT STOPKEY                      |                    |       |       |            |          |;
    |   2 |   VIEW                              |                    |    17 |   182K|   384   (2)| 00:00:05 |;
    |*  3 |    SORT ORDER BY STOPKEY            |                    |    17 | 17765 |   384   (2)| 00:00:05 |;
    |*  4 |     TABLE ACCESS BY INDEX ROWID     | FES_CONTACTHISTORY |    17 | 17765 |   383   (2)| 00:00:05 |;
    |   5 |      BITMAP CONVERSION TO ROWIDS    |                    |       |       |            |          |;
    |   6 |       BITMAP AND                    |                    |       |       |            |          |;
    |   7 |        BITMAP CONVERSION FROM ROWIDS|                    |       |       |            |          |;
    |*  8 |         INDEX RANGE SCAN            | CH_VECID           | 29997 |       |   105   (1)| 00:00:02 |;
    |   9 |        BITMAP CONVERSION FROM ROWIDS|                    |       |       |            |          |;
    |* 10 |         INDEX RANGE SCAN            | CH_VERSIONID       | 29997 |       |   108   (1)| 00:00:02 |;
    ----------------------------------------------------------------------------------------------------------;
     ;
    Predicate Information (identified by operation id):;
    ---------------------------------------------------;
     ;
       1 - filter(ROWNUM<=200);
       3 - filter(ROWNUM<=200);
       4 - filter("THIS_"."PARTY_TYPE"='6' AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."CREATEDBY"='10' ;
                  OR "THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"."CREATEDBY"='13' OR ;
                  "THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."CREATEDBY"='16' OR ;
                  "THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."CREATEDBY"='19' OR ;
                  "THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CREATEDBY"='21' OR ;
                  "THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."CREATEDBY"='24' OR ;
                  "THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."CREATEDBY"='27' OR ;
                  "THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."CREATEDBY"='3' OR ;
                  "THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."CREATEDBY"='32' OR ;
                  "THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."CREATEDBY"='35' OR ;
                  "THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."CREATEDBY"='38' OR ;
                  "THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CREATEDBY"='40' OR ;
                  "THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."CREATEDBY"='43' OR ;
                  "THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."CREATEDBY"='46' OR ;
                  "THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."CREATEDBY"='49' OR ;
                  "THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CREATEDBY"='51' OR ;
                  "THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."CREATEDBY"='54' OR ;
                  "THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."CREATEDBY"='57' OR ;
                  "THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."CREATEDBY"='6' OR ;
                  "THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."CREATEDBY"='62' OR ;
                  "THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR ;
                  "THIS_"."CREATEDBY"='9') AND "THIS_"."PHONE1"='111' AND "THIS_"."STARTTIME">=TIMESTAMP'2008-11-01 ;
                  00:00:00' AND "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59');
       8 - access("THIS_"."VECTOR_ID"='8');
      10 - access("THIS_"."VERSION_ID"='11');
    现在的执行计划
      

  14.   

    现在这个SQL和你收集统计信息前哪个成本高？LZ在VECTOR_ID列和VERSION_ID上建联合索引试试。
    还有你贴执行计划的时候把下面的统计信息也贴出来，这样才能看出哪个SQL成本高。
      

  15.   


    SQL> set time on;
    16:58:13 SQL> set   autotrace   traceonly; 
    16:58:36 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
    16:58:43   2    this_.PHONE1='111'  
    16:58:43   3   and 
    16:58:43   4    this_.PARTY_TYPE='6' 
    16:58:43   5   and
    16:58:43   6    this_.VECTOR_ID='28'
    16:58:43   7   and 
    16:58:43   8    this_.VERSION_ID='27'
    16:58:43   9   and
    16:58:43  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' 
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60
    63')
    16:58:43  11     and this_.STARTTIME>=to_date
    16:58:43  12     (
    16:58:43  13        '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
    16:58:43  14     ) and this_.STARTTIME<=to_date
    16:58:43  15     (
    16:58:43  16        '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
    16:58:43  17     )order by this_.STARTTIME DESC
    16:58:43  18     )where rownum <= 200 ;已选择20行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 510175305--------------------------------------------------------------------------------
    --------------------------| Id  | Operation                           | Name               | Rows  | Bytes
     | Cost (%CPU)| Time     |--------------------------------------------------------------------------------
    --------------------------|   0 | SELECT STATEMENT                    |                    |    17 |   182
    K|   378   (2)| 00:00:05 ||*  1 |  COUNT STOPKEY                      |                    |       |
     |            |          ||   2 |   VIEW                              |                    |    17 |   182
    K|   378   (2)| 00:00:05 ||*  3 |    SORT ORDER BY STOPKEY            |                    |    17 | 17765
     |   378   (2)| 00:00:05 ||*  4 |     TABLE ACCESS BY INDEX ROWID     | FES_CONTACTHISTORY |    17 | 17765
     |   377   (2)| 00:00:05 ||   5 |      BITMAP CONVERSION TO ROWIDS    |                    |       |
     |            |          ||   6 |       BITMAP AND                    |                    |       |
     |            |          ||   7 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||*  8 |         INDEX RANGE SCAN            | CH_VERSIONID       | 27824 |
     |    98   (2)| 00:00:02 ||   9 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||* 10 |         INDEX RANGE SCAN            | CH_VECID           | 27824 |
     |   113   (1)| 00:00:02 |--------------------------------------------------------------------------------
    --------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_TYPE"='6' AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."
    CREATEDBY"='10'              OR "THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"
    ."CREATEDBY"='13' OR              "THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."C
    REATEDBY"='16' OR              "THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."C
    REATEDBY"='19' OR              "THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CR
    EATEDBY"='21' OR              "THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."C
    REATEDBY"='24' OR              "THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."C
    REATEDBY"='27' OR              "THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."C
    REATEDBY"='3' OR              "THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."C
    REATEDBY"='32' OR              "THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."C
    REATEDBY"='35' OR              "THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."C
    REATEDBY"='38' OR              "THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CR
    EATEDBY"='40' OR              "THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."C
    REATEDBY"='43' OR              "THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."C
    REATEDBY"='46' OR              "THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."C
    REATEDBY"='49' OR              "THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CR
    EATEDBY"='51' OR              "THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."C
    REATEDBY"='54' OR              "THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."C
    REATEDBY"='57' OR              "THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."C
    REATEDBY"='6' OR              "THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."C
    REATEDBY"='62' OR              "THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CR
    EATEDBY"='8' OR              "THIS_"."CREATEDBY"='9') AND "THIS_"."PHONE1"='111' AND "THIS_"."S
    TARTTIME">=TIMESTAMP'2008-11-01              00:00:00' AND "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')
       8 - access("THIS_"."VERSION_ID"='27')
      10 - access("THIS_"."VECTOR_ID"='28')
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            508  consistent gets
              0  physical reads
              0  redo size
          16920  bytes sent via SQL*Net to client
            396  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             20  rows processed
      

  16.   


    16:58:44 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
    17:03:01   2    this_.PHONE1='111'  
    17:03:01   3   and 
    17:03:01   4    this_.PARTY_TYPE='6' 
    17:03:01   5   and
    17:03:01   6    this_.VECTOR_ID='8'
    17:03:01   7   and 
    17:03:01   8    this_.VERSION_ID='8'
    17:03:01   9   and
    17:03:01  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' 
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
    63')
    17:03:01  11     and this_.STARTTIME>=to_date
    17:03:01  12     (
    17:03:01  13        '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
    17:03:01  14     ) and this_.STARTTIME<=to_date
    17:03:01  15     (
    17:03:01  16        '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
    17:03:01  17     )order by this_.STARTTIME DESC
    17:03:01  18     )where rownum <= 200 ;已选择9行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 2300518666--------------------------------------------------------------------------------
    --------------------------| Id  | Operation                           | Name               | Rows  | Bytes
     | Cost (%CPU)| Time     |--------------------------------------------------------------------------------
    --------------------------|   0 | SELECT STATEMENT                    |                    |    19 |   204
    K|   413   (2)| 00:00:05 ||*  1 |  COUNT STOPKEY                      |                    |       |
     |            |          ||   2 |   VIEW                              |                    |    19 |   204
    K|   413   (2)| 00:00:05 ||*  3 |    SORT ORDER BY STOPKEY            |                    |    19 | 19855
     |   413   (2)| 00:00:05 ||*  4 |     TABLE ACCESS BY INDEX ROWID     | FES_CONTACTHISTORY |    19 | 19855
     |   412   (1)| 00:00:05 ||   5 |      BITMAP CONVERSION TO ROWIDS    |                    |       |
     |            |          ||   6 |       BITMAP AND                    |                    |       |
     |            |          ||   7 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||*  8 |         INDEX RANGE SCAN            | CH_VECID           | 29997 |
     |   105   (1)| 00:00:02 ||   9 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||* 10 |         INDEX RANGE SCAN            | CH_VERSIONID       | 29997 |
     |   119   (1)| 00:00:02 |--------------------------------------------------------------------------------
    --------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_TYPE"='6' AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."
    CREATEDBY"='10'              OR "THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"
    ."CREATEDBY"='13' OR              "THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."C
    REATEDBY"='16' OR              "THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."C
    REATEDBY"='19' OR              "THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CR
    EATEDBY"='21' OR              "THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."C
    REATEDBY"='24' OR              "THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."C
    REATEDBY"='27' OR              "THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."C
    REATEDBY"='3' OR              "THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."C
    REATEDBY"='32' OR              "THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."C
    REATEDBY"='35' OR              "THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."C
    REATEDBY"='38' OR              "THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CR
    EATEDBY"='40' OR              "THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."C
    REATEDBY"='43' OR              "THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."C
    REATEDBY"='46' OR              "THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."C
    REATEDBY"='49' OR              "THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CR
    EATEDBY"='51' OR              "THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."C
    REATEDBY"='54' OR              "THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."C
    REATEDBY"='57' OR              "THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."C
    REATEDBY"='6' OR              "THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."C
    REATEDBY"='62' OR              "THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CR
    EATEDBY"='8' OR              "THIS_"."CREATEDBY"='9') AND "THIS_"."PHONE1"='111' AND "THIS_"."S
    TARTTIME">=TIMESTAMP'2008-11-01              00:00:00' AND "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')
       8 - access("THIS_"."VECTOR_ID"='8')
      10 - access("THIS_"."VERSION_ID"='8')
    统计信息
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            421  consistent gets
              0  physical reads
              0  redo size
          14358  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              9  rows processed
      

  17.   

    楼主每个字段建索引，不用把这些单个索引都删除，建一个组合索引create index idx_test_123 on FES.FES_CONTACTHISTORY(STARTTIME, VECTOR_ID, VERSION_ID);begin dbms_stats.gather_table_stats('FES', 'FES_CONTACTHISTORY', cascade=>true) end;--执行上面的sql之后，再看看下面的执行计划select * from ( select /*+ index(this_, idx_test_123) */ ...--这个是你的sql,加了hint
      

  18.   

    在starttime建立索引，假设索引名为IX_XX_STARTTIME，如果是分区表，最好加上Local属性,相关SQL的部分改为：SELECT *
    FROM   (SELECT /*+index(this_ IX_XX_STARTTIME)*/ *
            FROM   FES.FES_CONTACTHISTORY this_
    ......作用是强制使用IX_XX_STARTTIME这个索引。当然，将该索引改为在 starttime,version_id,vector_id上建复合索引效果更好，主要看你这个索引的使用频率值不值得这么做。
      

  19.   

    具体根据数据分布不同和你的查询条件的不同，如果建联合索引会有不确定影响的。就你上面贴的最后那个执行计划来看，如果在(VECTOR_ID,VERSION_ID)上建索引，可能会降低逻辑读。
    但是在starttime,version_id,vector_id这三个列上建联合索引，就不好说了。
      

  20.   

    怎么通过各个参数反映查询性能？例如 physical reads 多大视为查询性能差？？
      

  21.   


    14:41:58 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
    15:16:53   2    this_.PHONE1='111'  
    15:16:53   3   and 
    15:16:53   4    this_.PARTY_TYPE='6' 
    15:16:53   5   and
    15:16:53   6    this_.VECTOR_ID='11'
    15:16:53   7   and
    15:16:53   8    this_.VERSION_ID='44'
    15:16:53   9   and
    15:16:53  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' 
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
    63')
    15:16:54  11     and this_.STARTTIME>=to_date
    15:16:54  12     (
    15:16:54  13        '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
    15:16:54  14     ) and this_.STARTTIME<=to_date
    15:16:54  15     (
    15:16:54  16        '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
    15:16:54  17     )order by this_.STARTTIME DESC
    15:16:54  18     )where rownum <= 200 ;已选择7行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 584285649--------------------------------------------------------------------------------
    ---------------------| Id  | Operation                      | Name               | Rows  | Bytes | Co
    st (%CPU)| Time     |--------------------------------------------------------------------------------
    ---------------------|   0 | SELECT STATEMENT               |                    |     1 | 11018 |
     55   (2)| 00:00:01 ||*  1 |  COUNT STOPKEY                 |                    |       |       |
             |          ||   2 |   VIEW                         |                    |     1 | 11018 |
     55   (2)| 00:00:01 ||*  3 |    SORT ORDER BY STOPKEY       |                    |     1 | 11018 |
     55   (2)| 00:00:01 ||*  4 |     TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY |     1 | 11018 |
     54   (0)| 00:00:01 ||*  5 |      INDEX RANGE SCAN          | CH_VECID           | 15837 |       |
     50   (0)| 00:00:01 |--------------------------------------------------------------------------------
    ---------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PHONE1"='111' AND "THIS_"."PARTY_TYPE"='6' AND
                  "THIS_"."VERSION_ID"='44' AND ("THIS_"."CREATEDBY"='1' OR "THIS_".
    "CREATEDBY"='10' OR              "THIS_"."CREATEDBY"='11' OR "THIS_"."CREATEDBY"='12' OR "THIS_"."C
    REATEDBY"='13' OR              "THIS_"."CREATEDBY"='14' OR "THIS_"."CREATEDBY"='15' OR "THIS_"."C
    REATEDBY"='16' OR              "THIS_"."CREATEDBY"='17' OR "THIS_"."CREATEDBY"='18' OR "THIS_"."C
    REATEDBY"='19' OR              "THIS_"."CREATEDBY"='2' OR "THIS_"."CREATEDBY"='20' OR "THIS_"."CR
    EATEDBY"='21' OR              "THIS_"."CREATEDBY"='22' OR "THIS_"."CREATEDBY"='23' OR "THIS_"."C
    REATEDBY"='24' OR              "THIS_"."CREATEDBY"='25' OR "THIS_"."CREATEDBY"='26' OR "THIS_"."C
    REATEDBY"='27' OR              "THIS_"."CREATEDBY"='28' OR "THIS_"."CREATEDBY"='29' OR "THIS_"."C
    REATEDBY"='3' OR              "THIS_"."CREATEDBY"='30' OR "THIS_"."CREATEDBY"='31' OR "THIS_"."C
    REATEDBY"='32' OR              "THIS_"."CREATEDBY"='33' OR "THIS_"."CREATEDBY"='34' OR "THIS_"."C
    REATEDBY"='35' OR              "THIS_"."CREATEDBY"='36' OR "THIS_"."CREATEDBY"='37' OR "THIS_"."C
    REATEDBY"='38' OR              "THIS_"."CREATEDBY"='39' OR "THIS_"."CREATEDBY"='4' OR "THIS_"."CR
    EATEDBY"='40' OR              "THIS_"."CREATEDBY"='41' OR "THIS_"."CREATEDBY"='42' OR "THIS_"."C
    REATEDBY"='43' OR              "THIS_"."CREATEDBY"='44' OR "THIS_"."CREATEDBY"='45' OR "THIS_"."C
    REATEDBY"='46' OR              "THIS_"."CREATEDBY"='47' OR "THIS_"."CREATEDBY"='48' OR "THIS_"."C
    REATEDBY"='49' OR              "THIS_"."CREATEDBY"='5' OR "THIS_"."CREATEDBY"='50' OR "THIS_"."CR
    EATEDBY"='51' OR              "THIS_"."CREATEDBY"='52' OR "THIS_"."CREATEDBY"='53' OR "THIS_"."C
    REATEDBY"='54' OR              "THIS_"."CREATEDBY"='55' OR "THIS_"."CREATEDBY"='56' OR "THIS_"."C
    REATEDBY"='57' OR              "THIS_"."CREATEDBY"='58' OR "THIS_"."CREATEDBY"='59' OR "THIS_"."C
    REATEDBY"='6' OR              "THIS_"."CREATEDBY"='60' OR "THIS_"."CREATEDBY"='61' OR "THIS_"."C
    REATEDBY"='62' OR              "THIS_"."CREATEDBY"='63' OR "THIS_"."CREATEDBY"='7' OR "THIS_"."CR
    EATEDBY"='8' OR              "THIS_"."CREATEDBY"='9') AND "THIS_"."STARTTIME">=TIMESTAMP'2008-1
    1-01 00:00:00' AND              "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')
       5 - access("THIS_"."VECTOR_ID"='11')Note
    -----
       - dynamic sampling used for this statement
    统计信息
    ----------------------------------------------------------
           3705  recursive calls
              0  db block gets
          28551  consistent gets
          27026  physical reads
              0  redo size
          13912  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              3  sorts (memory)
              0  sorts (disk)
              7  rows processed
    这是刚刚查询出来的执行计划。能否帮忙具体分析下？？非常地谢谢vc555 
      

  22.   


    我认为应该扫描starttime, 因为他是以这个字段order by分页，后面加的2个version_id、vector_id只是辅助，并且我认为LZ表中的数据在version_id,vector_id这2个字段上的分布是不均匀的。
      

  23.   

    主要是看这个：
    28551  consistent gets 这个数值越小起好。LZ为什么不试试的说的方法，并且把我的方法的执行计划帖出来？
      

  24.   

    并且我认为LZ表中的数据在version_id,vector_id这2个字段上的分布是不均匀－》”分布不均匀“是什么意思呢？？？
      

  25.   

    我主要是看LZ前面贴的执行计划，LZ本来每个字段都有索引，但是执行计划中并没用到starttime索引，
    而是出现了BITMAP CONVERSION TO ROWIDS，并且是在 version_id、vector_id上。所以据此判断要在version_id、vector_id上联合索引。
    LZ这个表如果经常更新数据，那么starttime便宜得更厉害。
      

  26.   


    均匀，比方说吧， this_.VECTOR_ID='28' //在0～100内随机生成 
    vector_id这个字段，假设有100条记录，由于值是1..100, 那么平均下来值为1的记录有1条，值为2的记录有1条，值为3的记录有1条。现在不均匀，就是说是值为1的记录有90条，值为2的记录有9条，值有3的记录有1条，值为4的记录没有了。4楼说的： 可能和列数据分布直方图有关,用字面常量进行这类查询,获得的执行计划不一定一样.
    也就是这个意思。
      

  27.   

    各位大侠。到底该怎么建索引呢。刚刚我试了下。将全部索引删了，只留下starttime索引。居然执行全表扫描18:30:32 SQL>  select * from ( select * from FES.FES_CONTACTHISTORY this_ where
    18:33:27   2    this_.PARTY_ID='V0000001'
    18:33:27   3   and
    18:33:27   4    this_.STARTTIME>=to_date
    18:33:27   5     (
    18:33:27   6        '2008-11-01,00:00:00', 'YYYY-MM-DD,HH24:MI:SS'
    18:33:27   7     ) and this_.STARTTIME<=to_date
    18:33:27   8     (
    18:33:27   9        '2008-12-04,23:59:59', 'YYYY-MM-DD,HH24:MI:SS'
    18:33:27  10     )order by this_.STARTTIME DESC
    18:33:27  11     )where rownum <= 200 ;未选定行
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1483632479--------------------------------------------------------------------------------
    --------------| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CP
    U)| Time     |--------------------------------------------------------------------------------
    --------------|   0 | SELECT STATEMENT        |                    |    81 |   871K| 95720   (
    3)| 00:19:09 ||*  1 |  COUNT STOPKEY          |                    |       |       |
      |          ||   2 |   VIEW                  |                    |    81 |   871K| 95720   (
    3)| 00:19:09 ||*  3 |    SORT ORDER BY STOPKEY|                    |    81 | 90477 | 95720   (
    3)| 00:19:09 ||*  4 |     TABLE ACCESS FULL   | FES_CONTACTHISTORY |    81 | 90477 | 95719   (
    3)| 00:19:09 |--------------------------------------------------------------------------------
    --------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_ID"='V0000001' AND
                  "THIS_"."STARTTIME">=TIMESTAMP'2008-11-01 00:00:00' AND
                  "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-04 23:59:59')
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
         427921  consistent gets
         403991  physical reads
              0  redo size
          11367  bytes sent via SQL*Net to client
            374  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              0  rows processed
      

  28.   

    Oracle 10G以上有自动统计信息的功能，到底有什么功能呢。如果我不手动统计信息。是不是优化器会依靠自动统计信息？
      

  29.   

    你没收集统计信息呀，你才看看，CBO认为表中只有81行的记录10g自动统计，是由job来自动统计的，这有个时间点的
      

  30.   


    09:33:56 SQL> select * from ( select /*+ index(this_, idx_test_123) */ * from FES.FES_CONTACTHISTORY
     this_ where
    09:36:24   2    this_.PHONE1='111'  
    09:36:24   3   and 
    09:36:24   4    this_.PARTY_TYPE='8' 
    09:36:24   5   and
    09:36:24   6    this_.VECTOR_ID='28'
    09:36:24   7   and
    09:36:24   8    this_.VERSION_ID='38'
    09:36:24   9   and
    09:36:24  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' 
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
    63','64')
    09:36:25  11   and
    09:36:25  12    this_.STARTTIME>=to_date
    09:36:25  13     (
    09:36:25  14        '2008-11-05,06:50:30', 'YYYY-MM-DD,HH24:MI:SS'
    09:36:25  15     ) and this_.STARTTIME<=to_date
    09:36:25  16     (
    09:36:25  17        '2008-12-05,22:59:59', 'YYYY-MM-DD,HH24:MI:SS'
    09:36:25  18     )order by this_.STARTTIME DESC
    09:36:25  19     )where rownum <= 200 ;已选择9行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1645311555--------------------------------------------------------------------------------
    ---------------------| Id  | Operation                      | Name               | Rows  | Bytes | Co
    st (%CPU)| Time     |--------------------------------------------------------------------------------
    ---------------------|   0 | SELECT STATEMENT               |                    |    14 |   150K| 10
    917   (1)| 00:02:12 ||*  1 |  COUNT STOPKEY                 |                    |       |       |
             |          ||   2 |   VIEW                         |                    |    14 |   150K| 10
    917   (1)| 00:02:12 ||*  3 |    SORT ORDER BY STOPKEY       |                    |    14 | 14630 | 10
    917   (1)| 00:02:12 ||*  4 |     TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY |    14 | 14630 | 10
    916   (1)| 00:02:11 ||*  5 |      INDEX RANGE SCAN          | IDX_TEST_123       |   247 |       | 10
    669   (1)| 00:02:09 |--------------------------------------------------------------------------------
    ---------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_TYPE"='8' AND ("THIS_"."CREATEDBY"='1' OR
                  "THIS_"."CREATEDBY"='10' OR "THIS_"."CREATEDBY"='11' OR "THIS_"."C
    REATEDBY"='12' OR              "THIS_"."CREATEDBY"='13' OR "THIS_"."CREATEDBY"='14' OR "THIS_"."C
    REATEDBY"='15' OR              "THIS_"."CREATEDBY"='16' OR "THIS_"."CREATEDBY"='17' OR "THIS_"."C
    REATEDBY"='18' OR              "THIS_"."CREATEDBY"='19' OR "THIS_"."CREATEDBY"='2' OR "THIS_"."CR
    EATEDBY"='20' OR              "THIS_"."CREATEDBY"='21' OR "THIS_"."CREATEDBY"='22' OR "THIS_"."C
    REATEDBY"='23' OR              "THIS_"."CREATEDBY"='24' OR "THIS_"."CREATEDBY"='25' OR "THIS_"."C
    REATEDBY"='26' OR              "THIS_"."CREATEDBY"='27' OR "THIS_"."CREATEDBY"='28' OR "THIS_"."C
    REATEDBY"='29' OR              "THIS_"."CREATEDBY"='3' OR "THIS_"."CREATEDBY"='30' OR "THIS_"."CR
    EATEDBY"='31' OR              "THIS_"."CREATEDBY"='32' OR "THIS_"."CREATEDBY"='33' OR "THIS_"."C
    REATEDBY"='34' OR              "THIS_"."CREATEDBY"='35' OR "THIS_"."CREATEDBY"='36' OR "THIS_"."C
    REATEDBY"='37' OR              "THIS_"."CREATEDBY"='38' OR "THIS_"."CREATEDBY"='39' OR "THIS_"."C
    REATEDBY"='4' OR              "THIS_"."CREATEDBY"='40' OR "THIS_"."CREATEDBY"='41' OR "THIS_"."C
    REATEDBY"='42' OR              "THIS_"."CREATEDBY"='43' OR "THIS_"."CREATEDBY"='44' OR "THIS_"."C
    REATEDBY"='45' OR              "THIS_"."CREATEDBY"='46' OR "THIS_"."CREATEDBY"='47' OR "THIS_"."C
    REATEDBY"='48' OR              "THIS_"."CREATEDBY"='49' OR "THIS_"."CREATEDBY"='5' OR "THIS_"."CR
    EATEDBY"='50' OR              "THIS_"."CREATEDBY"='51' OR "THIS_"."CREATEDBY"='52' OR "THIS_"."C
    REATEDBY"='53' OR              "THIS_"."CREATEDBY"='54' OR "THIS_"."CREATEDBY"='55' OR "THIS_"."C
    REATEDBY"='56' OR              "THIS_"."CREATEDBY"='57' OR "THIS_"."CREATEDBY"='58' OR "THIS_"."C
    REATEDBY"='59' OR              "THIS_"."CREATEDBY"='6' OR "THIS_"."CREATEDBY"='60' OR "THIS_"."CR
    EATEDBY"='61' OR              "THIS_"."CREATEDBY"='62' OR "THIS_"."CREATEDBY"='63' OR "THIS_"."C
    REATEDBY"='64' OR              "THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
    ATEDBY"='9') AND              "THIS_"."PHONE1"='111')
       5 - access("THIS_"."STARTTIME">=TIMESTAMP'2008-11-05 06:50:30' AND
                  "THIS_"."VECTOR_ID"='28' AND "THIS_"."VERSION_ID"='38' AND
                  "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-05 22:59:59')
           filter("THIS_"."VECTOR_ID"='28' AND "THIS_"."VERSION_ID"='38')
    统计信息
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
          10733  consistent gets
            129  physical reads
              0  redo size
          14365  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              9  rows processed
      

  31.   


    09:39:38 SQL> select * from ( select /*+ index(this_, idx_test_123) */ * from FES.FES_CONTACTHISTORY
     this_ where
    09:41:17   2    this_.PHONE1='111'  
    09:41:17   3   and 
    09:41:17   4    this_.PARTY_TYPE='6' 
    09:41:17   5   and
    09:41:17   6    this_.VECTOR_ID='58'
    09:41:17   7   and
    09:41:17   8    this_.VERSION_ID='74'
    09:41:17   9   and
    09:41:17  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' 
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
    63','64')
    09:41:17  11   and
    09:41:17  12    this_.STARTTIME>=to_date
    09:41:17  13     (
    09:41:17  14        '2008-11-05,01:50:30', 'YYYY-MM-DD,HH24:MI:SS'
    09:41:17  15     ) and this_.STARTTIME<=to_date
    09:41:17  16     (
    09:41:17  17        '2008-12-05,23:59:50', 'YYYY-MM-DD,HH24:MI:SS'
    09:41:17  18     )order by this_.STARTTIME DESC
    09:41:17  19     )where rownum <= 200 ;已选择8行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1645311555--------------------------------------------------------------------------------
    ---------------------| Id  | Operation                      | Name               | Rows  | Bytes | Co
    st (%CPU)| Time     |--------------------------------------------------------------------------------
    ---------------------|   0 | SELECT STATEMENT               |                    |    13 |   139K| 10
    963   (1)| 00:02:12 ||*  1 |  COUNT STOPKEY                 |                    |       |       |
             |          ||   2 |   VIEW                         |                    |    13 |   139K| 10
    963   (1)| 00:02:12 ||*  3 |    SORT ORDER BY STOPKEY       |                    |    13 | 13585 | 10
    963   (1)| 00:02:12 ||*  4 |     TABLE ACCESS BY INDEX ROWID| FES_CONTACTHISTORY |    13 | 13585 | 10
    962   (1)| 00:02:12 ||*  5 |      INDEX RANGE SCAN          | IDX_TEST_123       |   208 |       | 10
    753   (1)| 00:02:10 |--------------------------------------------------------------------------------
    ---------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_TYPE"='6' AND ("THIS_"."CREATEDBY"='1' OR
                  "THIS_"."CREATEDBY"='10' OR "THIS_"."CREATEDBY"='11' OR "THIS_"."C
    REATEDBY"='12' OR              "THIS_"."CREATEDBY"='13' OR "THIS_"."CREATEDBY"='14' OR "THIS_"."C
    REATEDBY"='15' OR              "THIS_"."CREATEDBY"='16' OR "THIS_"."CREATEDBY"='17' OR "THIS_"."C
    REATEDBY"='18' OR              "THIS_"."CREATEDBY"='19' OR "THIS_"."CREATEDBY"='2' OR "THIS_"."CR
    EATEDBY"='20' OR              "THIS_"."CREATEDBY"='21' OR "THIS_"."CREATEDBY"='22' OR "THIS_"."C
    REATEDBY"='23' OR              "THIS_"."CREATEDBY"='24' OR "THIS_"."CREATEDBY"='25' OR "THIS_"."C
    REATEDBY"='26' OR              "THIS_"."CREATEDBY"='27' OR "THIS_"."CREATEDBY"='28' OR "THIS_"."C
    REATEDBY"='29' OR              "THIS_"."CREATEDBY"='3' OR "THIS_"."CREATEDBY"='30' OR "THIS_"."CR
    EATEDBY"='31' OR              "THIS_"."CREATEDBY"='32' OR "THIS_"."CREATEDBY"='33' OR "THIS_"."C
    REATEDBY"='34' OR              "THIS_"."CREATEDBY"='35' OR "THIS_"."CREATEDBY"='36' OR "THIS_"."C
    REATEDBY"='37' OR              "THIS_"."CREATEDBY"='38' OR "THIS_"."CREATEDBY"='39' OR "THIS_"."C
    REATEDBY"='4' OR              "THIS_"."CREATEDBY"='40' OR "THIS_"."CREATEDBY"='41' OR "THIS_"."C
    REATEDBY"='42' OR              "THIS_"."CREATEDBY"='43' OR "THIS_"."CREATEDBY"='44' OR "THIS_"."C
    REATEDBY"='45' OR              "THIS_"."CREATEDBY"='46' OR "THIS_"."CREATEDBY"='47' OR "THIS_"."C
    REATEDBY"='48' OR              "THIS_"."CREATEDBY"='49' OR "THIS_"."CREATEDBY"='5' OR "THIS_"."CR
    EATEDBY"='50' OR              "THIS_"."CREATEDBY"='51' OR "THIS_"."CREATEDBY"='52' OR "THIS_"."C
    REATEDBY"='53' OR              "THIS_"."CREATEDBY"='54' OR "THIS_"."CREATEDBY"='55' OR "THIS_"."C
    REATEDBY"='56' OR              "THIS_"."CREATEDBY"='57' OR "THIS_"."CREATEDBY"='58' OR "THIS_"."C
    REATEDBY"='59' OR              "THIS_"."CREATEDBY"='6' OR "THIS_"."CREATEDBY"='60' OR "THIS_"."CR
    EATEDBY"='61' OR              "THIS_"."CREATEDBY"='62' OR "THIS_"."CREATEDBY"='63' OR "THIS_"."C
    REATEDBY"='64' OR              "THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
    ATEDBY"='9') AND              "THIS_"."PHONE1"='111')
       5 - access("THIS_"."STARTTIME">=TIMESTAMP'2008-11-05 01:50:30' AND
                  "THIS_"."VECTOR_ID"='58' AND "THIS_"."VERSION_ID"='74' AND
                  "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-05 23:59:50')
           filter("THIS_"."VECTOR_ID"='58' AND "THIS_"."VERSION_ID"='74')
    统计信息
    ----------------------------------------------------------
            525  recursive calls
              0  db block gets
          10983  consistent gets
            183  physical reads
              0  redo size
          14133  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
              8  rows processed
      

  32.   


    10:59:35 SQL> select * from ( select * from FES.FES_CONTACTHISTORY this_ where
    11:01:32   2    this_.PHONE1='111'  
    11:01:32   3   and 
    11:01:32   4    this_.PARTY_TYPE='5' 
    11:01:32   5   and
    11:01:32   6    this_.VECTOR_ID='9'
    11:01:32   7   and
    11:01:32   8    this_.VERSION_ID='23'
    11:01:32   9   and
    11:01:32  10    this_.CREATEDBY IN ('1' ,'2' ,'3' ,'4' ,'5' ,'6' ,'7' ,'8' ,'9' ,'10' ,'11' ,'12' ,'
    13' ,'14' ,'15' ,'16' ,'17' ,'18' ,'19' ,'20' ,'21' ,'22' ,'23' ,'24' ,'25' ,'26' ,'27' ,'28' ,'29' 
    ,'30' ,'31' ,'32' ,'33' ,'34' ,'35' ,'36' ,'37' ,'38' ,'39' ,'40' ,'41' ,'42' ,'43' ,'44' ,'45' ,'46
    ' ,'47' ,'48' ,'49' ,'50' ,'51' ,'52' ,'53' ,'54' ,'55' ,'56' ,'57' ,'58' ,'59' ,'60' ,'61' ,'62' ,'
    63')
    11:01:32  11   and
    11:01:32  12    this_.STARTTIME>=to_date
    11:01:32  13     (
    11:01:32  14        '2008-11-05,03:39:55', 'YYYY-MM-DD,HH24:MI:SS'
    11:01:32  15     ) and this_.STARTTIME<=to_date
    11:01:32  16     (
    11:01:32  17        '2008-12-05,23:17:17', 'YYYY-MM-DD,HH24:MI:SS'
    11:01:32  18     )order by this_.STARTTIME DESC
    11:01:32  19     )where rownum <= 200 ;已选择12行。
    执行计划
    ----------------------------------------------------------
    Plan hash value: 2300518666--------------------------------------------------------------------------------
    --------------------------| Id  | Operation                           | Name               | Rows  | Bytes
     | Cost (%CPU)| Time     |--------------------------------------------------------------------------------
    --------------------------|   0 | SELECT STATEMENT                    |                    |    14 |   150
    K|   368   (2)| 00:00:05 ||*  1 |  COUNT STOPKEY                      |                    |       |
     |            |          ||   2 |   VIEW                              |                    |    14 |   150
    K|   368   (2)| 00:00:05 ||*  3 |    SORT ORDER BY STOPKEY            |                    |    14 | 14630
     |   368   (2)| 00:00:05 ||*  4 |     TABLE ACCESS BY INDEX ROWID     | FES_CONTACTHISTORY |    14 | 14630
     |   367   (2)| 00:00:05 ||   5 |      BITMAP CONVERSION TO ROWIDS    |                    |       |
     |            |          ||   6 |       BITMAP AND                    |                    |       |
     |            |          ||   7 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||*  8 |         INDEX RANGE SCAN            | CH_VECID           | 25721 |
     |    91   (2)| 00:00:02 ||   9 |        BITMAP CONVERSION FROM ROWIDS|                    |       |
     |            |          ||* 10 |         INDEX RANGE SCAN            | CH_VERSIONID       | 25721 |
     |   116   (1)| 00:00:02 |--------------------------------------------------------------------------------
    --------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   1 - filter(ROWNUM<=200)
       3 - filter(ROWNUM<=200)
       4 - filter("THIS_"."PARTY_TYPE"='5' AND "THIS_"."STARTTIME">=TIMESTAMP'2008-1
    1-05 03:39:55'              AND ("THIS_"."CREATEDBY"='1' OR "THIS_"."CREATEDBY"='10' OR "THIS_
    "."CREATEDBY"='11' OR              "THIS_"."CREATEDBY"='12' OR "THIS_"."CREATEDBY"='13' OR "THIS_"."C
    REATEDBY"='14' OR              "THIS_"."CREATEDBY"='15' OR "THIS_"."CREATEDBY"='16' OR "THIS_"."C
    REATEDBY"='17' OR              "THIS_"."CREATEDBY"='18' OR "THIS_"."CREATEDBY"='19' OR "THIS_"."C
    REATEDBY"='2' OR              "THIS_"."CREATEDBY"='20' OR "THIS_"."CREATEDBY"='21' OR "THIS_"."C
    REATEDBY"='22' OR              "THIS_"."CREATEDBY"='23' OR "THIS_"."CREATEDBY"='24' OR "THIS_"."C
    REATEDBY"='25' OR              "THIS_"."CREATEDBY"='26' OR "THIS_"."CREATEDBY"='27' OR "THIS_"."C
    REATEDBY"='28' OR              "THIS_"."CREATEDBY"='29' OR "THIS_"."CREATEDBY"='3' OR "THIS_"."CR
    EATEDBY"='30' OR              "THIS_"."CREATEDBY"='31' OR "THIS_"."CREATEDBY"='32' OR "THIS_"."C
    REATEDBY"='33' OR              "THIS_"."CREATEDBY"='34' OR "THIS_"."CREATEDBY"='35' OR "THIS_"."C
    REATEDBY"='36' OR              "THIS_"."CREATEDBY"='37' OR "THIS_"."CREATEDBY"='38' OR "THIS_"."C
    REATEDBY"='39' OR              "THIS_"."CREATEDBY"='4' OR "THIS_"."CREATEDBY"='40' OR "THIS_"."CR
    EATEDBY"='41' OR              "THIS_"."CREATEDBY"='42' OR "THIS_"."CREATEDBY"='43' OR "THIS_"."C
    REATEDBY"='44' OR              "THIS_"."CREATEDBY"='45' OR "THIS_"."CREATEDBY"='46' OR "THIS_"."C
    REATEDBY"='47' OR              "THIS_"."CREATEDBY"='48' OR "THIS_"."CREATEDBY"='49' OR "THIS_"."C
    REATEDBY"='5' OR              "THIS_"."CREATEDBY"='50' OR "THIS_"."CREATEDBY"='51' OR "THIS_"."C
    REATEDBY"='52' OR              "THIS_"."CREATEDBY"='53' OR "THIS_"."CREATEDBY"='54' OR "THIS_"."C
    REATEDBY"='55' OR              "THIS_"."CREATEDBY"='56' OR "THIS_"."CREATEDBY"='57' OR "THIS_"."C
    REATEDBY"='58' OR              "THIS_"."CREATEDBY"='59' OR "THIS_"."CREATEDBY"='6' OR "THIS_"."CR
    EATEDBY"='60' OR              "THIS_"."CREATEDBY"='61' OR "THIS_"."CREATEDBY"='62' OR "THIS_"."C
    REATEDBY"='63' OR              "THIS_"."CREATEDBY"='7' OR "THIS_"."CREATEDBY"='8' OR "THIS_"."CRE
    ATEDBY"='9') AND              "THIS_"."PHONE1"='111' AND "THIS_"."STARTTIME"<=TIMESTAMP'2008-12-
    05 23:17:17')   8 - access("THIS_"."VECTOR_ID"='9')
      10 - access("THIS_"."VERSION_ID"='23')
    统计信息
    ----------------------------------------------------------
            468  recursive calls
              0  db block gets
            592  consistent gets
            452  physical reads
              0  redo size
          15028  bytes sent via SQL*Net to client
            385  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
             12  rows processed
    前两个是用了联合索引的，这个没有用联合索引的。从统计信息上看到底那个查询性能要好呢？
      

  33.   

    592  consistent gets 
    这个值小是表示这个执行计划好。你都用同样的参数试试，别改参数啊。
      

  34.   

    自动统计信息提供给Oracle的优化器作为计算执行计划成本的依据.