SELECT
rof.remand_id,
'' SUB_ID,
rof.process_id,
rof.process_id root_process_id,
rof.name as PROCESS_NAME,
rip_get_user_name(rof.create_user)create_user_name ,
rof.create_time,
rof.put_department,
rof.createxqman ,
null plan_time,
rs.remand_id
FROM REMAND_ORDER_FINAL rof,REMAND_STAGE s, REMAND_STATUS rs
where rof.remand_id=rs.remand_id(+)
and rof.remand_id = s.remand_id(+)
--and s.remand_id(+) = rof.remand_id
AND rof.app_type not IN ('BUG票')
and (s.stage > 8 OR s.stage < 8)
and (rs.status IS NULL OR rs.status!=2)红色的部分的外联占的cost是42,整个查询cost最大就是这个,请问才能把这个外联的全表扫描干掉...
但是REMAND_ORDER_FINAL 的索引没有起作用...
FILTER
HASH JOIN RIGHT OUTER 52 3662 465074
TABLE ACCESS FULL RIPORCL REMAND_STAGE 8 3876 77520
FILTER
NESTED LOOPS OUTER 43 3662 391834
TABLE ACCESS FULL RIPORCL REMAND_ORDER_FINAL 42 3662 314932
TABLE ACCESS BY INDEX ROWID RIPORCL REMAND_STATUS 1 1 21
INDEX RANGE SCAN RIPORCL REMAND_ID_RSTATUS 0 1
还有就是如果条件里可能包含了null也不会走索引。where 中加上条件is not null把你几个表的索引和行数列出来看看吧。