select min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG = :1 为什么不走索引？

显示全部楼层 · 2006-3-20 16:25:54

select min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG = :1
100万条记录中查询几条记录，走全表扫描。
select ID from TB_SMS_MTREPORT where DISPATCHEDFLAG = :1
100万条记录中查询几条记录，走索引。
统计信息正常。
如果要走索引是不是必须加索引提示 select /* + rule*/ min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG = :1

千问 · 2006-3-20 16:25:54

收集直方图啊,参考第一点
http://www.itpub.net/viewthread. ... 3%DE%BB%A8%CC%C7ONE
[ 本帖最后由棉花糖ONE 于 2008-10-22 11:15 编辑 ]

千问 · 2006-3-20 16:25:54

这语句直接建全索引好了,如果dispatchedflag的值只有几个而且比较倾斜的话,建议不要绑定了
[ 本帖最后由棉花糖ONE 于 2008-10-22 11:16 编辑 ]

千问 · 2006-3-20 16:25:54

建立统计直方图结果还是一样

千问 · 2006-3-20 16:25:54

原帖由 xiangqy 于 2008-10-22 11:37 发表
建立统计直方图结果还是一样
不要绑定变量来测试，把set autot trace的结果发出来

千问 · 2006-3-20 16:25:54

bind peeking

千问 · 2006-3-20 16:25:54

我怀疑和我上次一样，bug，lz看到的执行计划是假的，从v$sql_plan里面找执行计划，贴上来

千问 · 2006-3-20 16:25:54

SQL> var a number;
SQL> execute :a:=0;
PL/SQL 过程已成功完成。
已用时间:00: 00: 00.00
SQL> select min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG = :a
2/
MIN(ID)
----------

已用时间:00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 4285779785
-------------------------------------------------------------------------------
------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Tim
|
-------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT |
| 1 | 7 |3625 (3)| 00:
0:44 |
| 1 |SORT AGGREGATE|
| 1 | 7 |
|
|
|*2 | TABLE ACCESS FULL| TB_SMS_MTREPORT |1055K|7214K|3625 (3)| 00:
0:44 |
-------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DISPATCHEDFLAG"=TO_NUMBER(:A))

统计信息
----------------------------------------------------------
273recursive calls

0db block gets
60consistent gets

0physical reads

0redo size
329bytes sent via SQL*Net to client
338bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

1rows processed
SQL>

千问 · 2006-3-20 16:25:54

select min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG =0;
你这么测试看看

千问 · 2006-3-20 16:25:54

SQL> select /*+ rule*/ min(ID) from TB_SMS_MTREPORT where DISPATCHEDFLAG = :a
2;
MIN(ID)
----------

已用时间:00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 49589153
-----------------------------------------------------------------------
| Id| Operation
| Name
|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
|
| 1 |SORT AGGREGATE
|
|
| 2 | TABLE ACCESS BY INDEX ROWID| TB_SMS_MTREPORT
|
|*3 |INDEX RANGE SCAN
| IDX_SMS_MTREPORT_DISPATHEDFLAG |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DISPATCHEDFLAG"=TO_NUMBER(:A))
Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------

1recursive calls

0db block gets

4consistent gets

0physical reads

0redo size
329bytes sent via SQL*Net to client
338bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1rows processed