执行计划（索引为啥没有使用）

Question

索引oracle

Answer 1

除了在顶楼的问题，还有first_rows（200000）就走全表扫描了。而first_rows（190000）就走的索引。
到底这个first_rows有何作用，针对我这张表的这个查询，是否可以调整优化器策略修改为first_rows可以使性能得到提高呢？

Answer 2

是个好问题，推荐一下，大家一起看看
从1和2的比较中，table access full以cost更低的优势胜出
之所以使用索引会有这么大的cost，是因为根据in后面的参数，出现多次的跳转到3的时候，指定first rows(100), 执行计划中的基数变成了101. 此时，in里面的参数可能只要找到其中一个就能满足条件，走索引的成本就变得很低
到190000的时候，接近全表扫描和走索引的临界点。到了200000，和all_rows已经没有区别

Answer 3

in 的本质是OR，量多的时候需要产生的候选执行计划就非常多了。

Answer 4

这种情况，加firstrows（100）会不会提高效率呢？数据库层面还可以通过调整哪些来优化？

Answer 5

不能这么说。first_rows hint会影响cbo的判断，改变执行计划，但不意味着这种改变就是好的
first_rows一般用在分页中，会加快前几页的查询效率。在其他时候，可能反而会导致效率更低做个试验
建一个100万条记录的表，a字段的num_distinct是1/1000，在这个字段上建索引
SQL> create table test_opp_tab as
  2  select trunc(rownum/1000) a,'test' b,'testtest' c
  3  from dual
  4  connect by rownum<=1000000
  5  /
 
Table created
 
SQL> create index idx_test_opp_tab_a on test_opp_tab(a)
  2  /
 
Index created以下是测试语句，正常情况下会走全表扫描，但如果给first_rows hint，则将走索引
select c
from test_opp_tab
where a between 100 and 500但是真实的效率如何呢
加hint使其走索引的时候
SQL> select /*+ first_rows(100)*/c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:36.43Execution Plan
----------------------------------------------------------
Plan hash value: 1393809933--------------------------------------------------------------------------------------------------| Id  | Operation                   | Name               | Rows  | Bytes | Cost(%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                    |   434K|  9756K|     4   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OPP_TAB       |   434K|  9756K|     4   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_TEST_OPP_TAB_A |       |       |     3   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      55521  consistent gets
          0  physical reads
          0  redo size
   11570850  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed不加hint的时候
SQL> select c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:34.14Execution Plan
----------------------------------------------------------
Plan hash value: 3495120256----------------------------------------------------------------------------------| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |              |   434K|  9756K|   736   (4)| 00:00:09 ||*  1 |  TABLE ACCESS FULL| TEST_OPP_TAB |   434K|  9756K|   736   (4)| 00:00:09 |----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29842  consistent gets
          0  physical reads
          0  redo size
    4304387  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed可以看到，加了first_rows hint时候，逻辑读更高，执行时间更长

Answer 6

检查一下你的环境中，xxx_task_id是否是单列索引，碎片是否较多。压力较轻的时候，可以rebuild一下

Answer 7

把in 换成exists 可以提高速度吗？顺风车问问

Answer 8

不能这么说。first_rows hint会影响cbo的判断，改变执行计划，但不意味着这种改变就是好的
first_rows一般用在分页中，会加快前几页的查询效率。在其他时候，可能反而会导致效率更低做个试验
建一个100万条记录的表，a字段的num_distinct是1/1000，在这个字段上建索引
SQL> create table test_opp_tab as
  2  select trunc(rownum/1000) a,'test' b,'testtest' c
  3  from dual
  4  connect by rownum<=1000000
  5  /
 
Table created
 
SQL> create index idx_test_opp_tab_a on test_opp_tab(a)
  2  /
 
Index created以下是测试语句，正常情况下会走全表扫描，但如果给first_rows hint，则将走索引
select c
from test_opp_tab
where a between 100 and 500但是真实的效率如何呢
加hint使其走索引的时候
SQL> select /*+ first_rows(100)*/c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:36.43Execution Plan
----------------------------------------------------------
Plan hash value: 1393809933--------------------------------------------------------------------------------------------------| Id  | Operation                   | Name               | Rows  | Bytes | Cost(%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                    |   434K|  9756K|     4   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OPP_TAB       |   434K|  9756K|     4   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_TEST_OPP_TAB_A |       |       |     3   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      55521  consistent gets
          0  physical reads
          0  redo size
   11570850  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed不加hint的时候
SQL> select c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:34.14Execution Plan
----------------------------------------------------------
Plan hash value: 3495120256----------------------------------------------------------------------------------| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |              |   434K|  9756K|   736   (4)| 00:00:09 ||*  1 |  TABLE ACCESS FULL| TEST_OPP_TAB |   434K|  9756K|   736   (4)| 00:00:09 |----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29842  consistent gets
          0  physical reads
          0  redo size
    4304387  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed可以看到，加了first_rows hint时候，逻辑读更高，执行时间更长
全表扫描可以使用多块读取，所以在这里逻辑读的次数更少。

Answer 9

不能这么说。first_rows hint会影响cbo的判断，改变执行计划，但不意味着这种改变就是好的
first_rows一般用在分页中，会加快前几页的查询效率。在其他时候，可能反而会导致效率更低做个试验
建一个100万条记录的表，a字段的num_distinct是1/1000，在这个字段上建索引
SQL> create table test_opp_tab as
  2  select trunc(rownum/1000) a,'test' b,'testtest' c
  3  from dual
  4  connect by rownum<=1000000
  5  /
 
Table created
 
SQL> create index idx_test_opp_tab_a on test_opp_tab(a)
  2  /
 
Index created以下是测试语句，正常情况下会走全表扫描，但如果给first_rows hint，则将走索引
select c
from test_opp_tab
where a between 100 and 500但是真实的效率如何呢
加hint使其走索引的时候
SQL> select /*+ first_rows(100)*/c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:36.43Execution Plan
----------------------------------------------------------
Plan hash value: 1393809933--------------------------------------------------------------------------------------------------| Id  | Operation                   | Name               | Rows  | Bytes | Cost(%CPU)| Time     |--------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                    |   434K|  9756K|     4   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OPP_TAB       |   434K|  9756K|     4   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_TEST_OPP_TAB_A |       |       |     3   (0)| 00:00:01 |--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      55521  consistent gets
          0  physical reads
          0  redo size
   11570850  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed不加hint的时候
SQL> select c
  2  from test_opp_tab
  3  where a between 100 and 500
  4  /401000 rows selected.Elapsed: 00:00:34.14Execution Plan
----------------------------------------------------------
Plan hash value: 3495120256----------------------------------------------------------------------------------| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |              |   434K|  9756K|   736   (4)| 00:00:09 ||*  1 |  TABLE ACCESS FULL| TEST_OPP_TAB |   434K|  9756K|   736   (4)| 00:00:09 |----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("A">=100 AND "A"<=500)Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29842  consistent gets
          0  physical reads
          0  redo size
    4304387  bytes sent via SQL*Net to client
     294401  bytes received via SQL*Net from client
      26735  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     401000  rows processed可以看到，加了first_rows hint时候，逻辑读更高，执行时间更长
感谢版主耐心指导。一下子就理解了。多谢多谢

Answer 10

重新收集统计信息了。用dbms_stats包收集的，需要把哪个视图里面的信息发出来呢？

Answer 11

重新收集统计信息了。用dbms_stats包收集的，需要把哪个视图里面的信息发出来呢？
USER_TAB_COL_STATISTICS以及USER_IND_STATISTICS

Answer 12

用union all呢？会是什么情况？

Answer 13

Oracle 11G在查询时使用智能扫描（Smart Scan），在Exadata平台尤其这样，我们项目组之前做过类似的测试。当要查询或删除的数据小于总数据的5%时，走索引比较快，反之全表扫描更快！