怎么不走索引

[orcher]

有两个表
user_notice(nid number primary key,user_id number,notice_info varchar2(10));
subscription(subscriber number,subscribed number,primary key(subscriber,subscribed ))

我在user_notice.user_id 上创建了一个索引
现在我要查
select * from user_notice s where exists (select null from subscription c where c.subscriber=1715 and s.user_id = c.subscribed )
为什么他对user_notice要全表扫描，而不走索引？
select * from user_notice s where exists (select null from subscription c where s.user_id = c.subscribed and c.subscriber=1715) 也是一样

[junon]

看错没

[orcher]

SELECT STATEMENT, GOAL = CHOOSE
FILTER
TABLE ACCESS FULL WestTo USER_NOTICE
INDEX UNIQUE SCAN WestTo SYS_C002420

[ziling2011]

字段里包含null好像不走索引吧

[orcher]

谢谢楼上，我改过了user_id不能为null,但是还不能走索引

[lnuwhy]

因为你的优化器是CBO的，对于你的查询，优化器认为全表扫描的方式更优，如果你的查询是select user_id from user_notice s where exists (select null from subscription c where s.user_id = c.subscribed and c.subscriber=1715)我想一定是走full index scan的，因为你原来的查询即使走索引了，还是要通过ROWID去查询其他字段的数据，效率还不如全表好

[orcher]

[Quote=引用 5 楼 lnuwhy 的回复:]

因为你的优化器是CBO的，对于你的查询，优化器认为全表扫描的方式更优，如果你的查询是select user_id from user_notice s where exists (select null from subscription c where s.user_id = c.subscribed and c.subscriber=1715)我想一定是走full index scan的，……
[/Quote]
谢谢，不过即使是按照你提供的查询，他还是全表

[lnuwhy]

[Quote=引用 6 楼 orcher 的回复:]
引用 5 楼 lnuwhy 的回复:

因为你的优化器是CBO的，对于你的查询，优化器认为全表扫描的方式更优，如果你的查询是select user_id from user_notice s where exists (select null from subscription c where s.user_id = c.subscribed and c.subscriber=1715)我想一……
[/Quote]
你没收集信息吧，运行下这个analyze table user_notice compute statistics for all indexes;
要是还不行，我也不知道了，我觉得应该走索引

[youshang444]

select * from user_notice s where s.user_id in (select c.subscribed from subscription c where c.subscriber=1715 and) 改成这样子吧，大概就会走索引了，用exists，这个肯定要走全表扫描的!