Oracle 为什么不用我的索引呢？

Question

Table Device (
device_id number  PK,  //Sequence生成的long 型数字,但没不是 Number(10)，当初为什么这样也不知道，
re   number not null, // 0 , 1 标记删除。
name varchar ,
deviceZL varchar(2), // 种类.
makeDate date,
)Table Boiler (
  boiler_id number(10) PK, // 子表，与Device 通过 device_id = boiler_id 关联.
  power     number(7,2) ,
  model     varchar2(30).
  ...
)这2表还有些字段上有索引和外键，但是我的查询中不用。Device = 330000 条， 
Boiler = 5 0000 条记录。select * from device d,vessel v 
 where d.device_id=v.vessel_id  and d.re=0 
使用 first_rows 时，它显示使用 PK_BOILER 一个索引，select * from device d,vessel v 
 where d.device_id=v.vessel_id  and d.re=0 and model = 'A0304023M35' 这个时候   first_rows 它显示使用 PK_DEVICE 索引。我认为 DEVICE 数据量更大，如果用 PK_DEVICE 应该会更快些。
我不明白为什么它不优先使用  PK_DEVICE 而用 PK_BOILER ，跟 DEVICE_ID 和 BOILER_ID 类型不完全一致有关系么？好像在 BOILER 上多加条件时，PK_DEVICE 被使用的概率更高，不知道为什么，现在我知道 select count(*) from device 肯定 是 select count(*) from boiler 的 7 ~ 20 倍.我应该怎么让 Oracle 总是优先使用  PK_DEVICE ,  而且两个主键出现在关联中，好像也没有看到同时用上2 个主键?1: 
select * from device d,vessel v 
 where d.device_id=v.vessel_id  and d.re=0 SELECT STATEMENT, GOAL = FIRST_ROWS 113221 111327 65126295
 NESTED LOOPS                    113221 111327 65126295
  TABLE ACCESS FULL NEWSJJC DEVICE           1894 111327 45755397
  TABLE ACCESS BY INDEX ROWID NEWSJJC VESSEL 1 1 174
   INDEX UNIQUE SCAN NEWSJJC PK_VESSEL 1
2:
select * from device d,vessel v 
 where d.device_id=v.vessel_id  and d.re=0 and v.radius = 3SELECT STATEMENT, GOAL = FIRST_ROWS 399 83 48555
 NESTED LOOPS                   399 83 48555
  TABLE ACCESS FULL NEWSJJC VESSEL          316 83 14442
  TABLE ACCESS BY INDEX ROWID NEWSJJC DEVICE 1 1 411
   INDEX UNIQUE SCAN NEWSJJC PK_DEVICE 3

Answer 1

2:
select * from device d,vessel v
where d.device_id=v.vessel_id and d.re=0 and v.radius = 3SELECT STATEMENT, GOAL = FIRST_ROWS 399 83 48555
NESTED LOOPS 399 83 48555
TABLE ACCESS FULL NEWSJJC VESSEL 316 83 14442   //以VESSEL 做驱动表
TABLE ACCESS BY INDEX ROWID NEWSJJC DEVICE 1 1 411  //以DEVICE 做内表
INDEX UNIQUE SCAN NEWSJJC PK_DEVICE 3
---------------------------------------------------------
1. 以first_rows(最佳快速反应)都会选择nest loop
2. 系统总是选择返回结果集小的表作为驱动表.
   在计划1中,re=0使得device返回结果集小,因此device作为驱动表.
   在计划2中,在BOILER 上多加条件时使得boiler返回结果集更小,因此boiler作为驱动表.
3. 在表连接中总是在连接字段上选用走索引,所以在计划1中内表VESSEL走PK_VESSEL,在计划2中内表DEVICE走PK_DEVICE.
总之,不要看连接表的大小而是看表返回结果集的大小来决定谁做驱动表,谁做内表.

Answer 2

问题1：
    如果我总是加上  and rownum between 1 and 20 会不会有所不同呢？
问题2： 
    
如果我的查询绝大多数都是  first_rows(20) 只是有些JSP页面要做统计（这个用户认为 20~30 秒可以接受，因为用的也少) ,是否我应该把数据库配置改成：
OPTIMIZER_MODE=FIRST_ROWS_100 ,  然后在统计时使用单独的 Oracle session 并且获取 SQL connection 之后先设置 ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS 再查数据。

Answer 3

FIRST_ROWS_ n 优化会告诉查询优化器，让它选择一个能够把响应时间减到最小的查询执行计划，以产生查询结果的前 n 行, 但不保证消耗的所有计算资源最小.你可以在sql里写出优化提示,你可以不用ALTER SESSION SET... 
select /*+ first_rows(20) */ * from table,当然你也可以那么做.

Answer 4

再问这个问题就揭帖了。
语句 1 ：select * from (
 select rownum row_id, a.* from (  select d.* , b.* from device d , boiler b where d.re=0 and d.device_id =   b.boiler_id) a ) where row_id between 1 and 20 语句2：
 select * from d.*,b.* from device d, boiler b where d.device_id = b.boiler_id and rownum between 1 and 20 这两条语句我的 /*+ first_rows(20) */ 加在什么地方呢，有差别么？

Answer 5

语句 1 ：select * from (
select rownum row_id, a.* from (select /*+ first_rows(20) */ d.* , b.* from device d , boiler b where d.re=0 and d.device_id = b.boiler_id) a) where row_id between 1 and 20语句2：
 select /*+ first_rows(20) */ * from d.*,b.* from device d, boiler b where d.device_id = b.boiler_id and rownum between 1 and 20

Answer 6

语句2没对,rownum要在外面嵌套一层

Answer 7

Number(10) 和  Number 类型主键  一对一 关联， 类型不完全相同会不会在 Where 后存在多个索引列时影响索引的被使用的优先级?