我建了索引，却不起作用？why???

[duanhui]

table1 有两个Index
index1 for pri_key
index2 for colID

select * from table1 order by pri_key ----so faster

select * from table2 order by colID 和不建索引效果一样
index2没起作用？？？？？


why

100分不够，只管开口要。

[ATCG]

试试
select * from table2 where colID>0 order by colID;

[colacoca]

索引不能这样用吧？？

应该是

select * from table1 where pri_key = '0'

索引对order by好像不起作用

[colacoca]

ATCG(ORF)

select * from table2 where colID>0 order by colID;

colID>0
如果这样写，索引也没有作用
原因有二：

1最好写成colID=1 and colID=2 .....这种方式
不要在索引里用in,这样速度会很慢
2你建得index1是 pri_key ，要是前面没有pri_key ='0'这样的条件，直接用colID应该也不起作用

[more_zou]

做个试验看看就知道了
[e-mail]>select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
IDX_TEST_VAL －－唯一索引（val）
PK_TEST －－主键的索引（id）
SYS_C002419 －－唯一非空索引（ddd）

[e-mail]>set autotrace on
[e-mail]>select id,val from test order by id;

ID VAL
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (FULL SCAN) OF 'PK_TEST' (UNIQUE)

使用到主键的索引


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

[e-mail]>select id,val from test order by val;

ID VAL
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 TABLE ACCESS (FULL) OF 'TEST'

唯一索引（sal）没用到


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
826 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

[e-mail]>select id,val,ddd from test order by ddd;

ID VAL DDD
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
10 10 10

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (FULL SCAN) OF 'SYS_C002419' (UNIQUE)


唯一非空索引（ddd）用到

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
943 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

结论就是只有当该索引是唯一非空的时，order by才会用到

[hrb_qiuyb]

说几点吧：
1、看一个语句走不走索引不能只凭感觉，建议你用pl/sql developer或toad看一下语句的执行计划，看是不是走索引。
2、如果有索引起，但不走索引大体有以下几点:
a、其实sql语句走不走索引取决于optimizer,共用rule、cost、choose、firt row几种方式，默认是choose,即如果一个表有统计信息(statistics)那么走基于cost的方式，如果没有statistics则走基于rule的方式，这时你可以查一下user_tables看一下num_rows等列看是否为空.
b、表中的行比较少，优化器认为不值得走索引。
c、你的索引建立在一个i/o特别繁忙设备上。