3,491
社区成员
发帖
与我相关
我的任务
分享执行计划（索引为啥没有使用）
首先说下这个是单表查询，1000万数据量，检索的是20万数据量，只有一个where条件，并且这个where条件是建了索引的。
直接查看执行计划是走的全表扫描、20万/1000万=2%。这么少的数据量为啥也走全表扫描，是没有建对索引，还是其他情况？
1、
2、
3、
4、
5、
6、
直接查看执行计划是走的全表扫描、20万/1000万=2%。这么少的数据量为啥也走全表扫描，是没有建对索引，还是其他情况？
1、
2、
3、
4、
5、
6、
...全文
请发表友善的回复…
发表回复
Waiting_ora 2014-09-05
- 打赏
- 举报
很有可能是统计信息不准确导致的问题
Dekey_1314 2014-09-04
- 打赏
- 举报
Oracle 11G在查询时使用智能扫描（Smart Scan），在Exadata平台尤其这样，我们项目组之前做过类似的测试。当要查询或删除的数据小于总数据的5%时，走索引比较快，反之全表扫描更快！
dbaer 2014-09-03
- 打赏
- 举报
用union all呢？会是什么情况？
FireBurn 2014-09-02
- 打赏
- 举报
重新收集统计信息了。用dbms_stats包收集的，需要把哪个视图里面的信息发出来呢？[/quote]
USER_TAB_COL_STATISTICS以及USER_IND_STATISTICS
xixi_168 2014-09-01
- 打赏
- 举报
重新收集统计信息了。用dbms_stats包收集的，需要把哪个视图里面的信息发出来呢？
xixi_168 2014-09-01
- 打赏
- 举报
不能这么说。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 createdselect c
from test_opp_tab
where a between 100 and 500SQL> 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.43
Execution 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
SQL> select c
2 from test_opp_tab
3 where a between 100 and 500
4 /
401000 rows selected.
Elapsed: 00:00:34.14
Execution 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 processedhalukua 2014-09-01
- 打赏
- 举报
完全看不懂。只是为了刷分。本人
国营养猪场 2014-08-31
- 打赏
- 举报
这个跟统计信息里的直方图有关,请检查一下直方图信息
FireBurn 2014-08-30
- 打赏
- 举报
在讨论执行计划的时候，如果没有参考对象的统计信息，那么基本都是瞎猜。
楼主应该重新收集一下统计信息，再把相关信息发上来看看。
FireBurn 2014-08-30
- 打赏
- 举报
不能这么说。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 createdselect c
from test_opp_tab
where a between 100 and 500SQL> 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.43
Execution 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
SQL> select c
2 from test_opp_tab
3 where a between 100 and 500
4 /
401000 rows selected.
Elapsed: 00:00:34.14
Execution 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 processedxiaoxiangqing 2014-08-30
- 打赏
- 举报
返回的数据太多了，全表反而还快些.索引只适合返回少量的数据.
小灰狼W 2014-08-29
- 打赏
- 举报
检查一下你的环境中，xxx_task_id是否是单列索引，碎片是否较多。压力较轻的时候，可以rebuild一下
小灰狼W 2014-08-29
- 打赏
- 举报
不能这么说。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 createdselect c
from test_opp_tab
where a between 100 and 500SQL> 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.43
Execution 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
SQL> select c
2 from test_opp_tab
3 where a between 100 and 500
4 /
401000 rows selected.
Elapsed: 00:00:34.14
Execution 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 processedxixi_168 2014-08-29
- 打赏
- 举报
这种情况，加firstrows（100）会不会提高效率呢？数据库层面还可以通过调整哪些来优化？
發糞塗牆 2014-08-29
- 打赏
- 举报
in 的本质是OR，量多的时候需要产生的候选执行计划就非常多了。
CodeC 2014-08-29
- 打赏
- 举报
持续关注
不写代码的钦 2014-08-29
- 打赏
- 举报
个人看法：
既然走全表扫描，说明走索引效率低或者走索引和全表扫描差不多。
感觉这里不是露珠索引问题，而是露珠这种in()写法的问题，如果想提高效率可以改变写法。
小小都不懂 2014-08-29
- 打赏
- 举报
把in 换成exists 可以提高速度吗？顺风车问问
小灰狼W 2014-08-28
- 打赏
- 举报
是个好问题，推荐一下，大家一起看看
从1和2的比较中，table access full以cost更低的优势胜出
之所以使用索引会有这么大的cost，是因为根据in后面的参数，出现多次的跳转
到3的时候，指定first rows(100), 执行计划中的基数变成了101. 此时，in里面的参数可能只要找到其中一个就能满足条件，走索引的成本就变得很低
到190000的时候，接近全表扫描和走索引的临界点。到了200000，和all_rows已经没有区别
xixi_168 2014-08-28
- 打赏
- 举报
除了在顶楼的问题，还有first_rows（200000）就走全表扫描了。而first_rows（190000）就走的索引。
到底这个first_rows有何作用，针对我这张表的这个查询，是否可以调整优化器策略修改为first_rows可以使性能得到提高呢？
