优化Oracle with全表扫描的问题(2)

模拟如下,使用Oracle的HR示例用户
 create table emp as select * from hr.employees;
 create index inx_hire_date on emp(hire_date);
 create index inx_emp_id on emp(employee_id);
 with t1 as
 (
 select user_id from
 (
 select employee_id user_id from emp order by hire_date desc
 ) where rownum<20
 )
 select first_name,last_name from emp t2,t1 where t2.employee_id=t1.user_id;
 

执行计划

解决方案如下,使用Oracle Hint
 with t1 as
 (
    select user_id from
    (
        select employee_id user_id from emp order by hire_date desc
    ) where rownum<20
 )
 select /*+ ordered use_nl(t1,t2) */ first_name,last_name from t1,emp t2 where t2.employee_id=t1.user_id;