问题
t_ply_base是大表500万行,c_ply_no有索引
t_Relat_Car 为小表
Select
* From t_plY_base a
WHERE
c_ply_no in (select c_relat_no from t_Relat_Car where c_Ply_No =trim('1210103172006001082'))
or
c_ply_no in (select c_ply_no from t_Relat_Car where c_relat_No =trim('1210103172006001082'))
or
c_ply_no in (select '1210103172006001082' from dual);
执行计划
SELECT STATEMENT Optimizer Mode=CHOOSE
FILTER
PARTITION RANGE ALL
PARTITION LIST ALL
TABLE ACCESS FULL AISYS.T_PLY_BASE
INDEX RANGE SCAN AISYS.IDX_RELATCAR_PLYNO
INDEX RANGE SCAN AISYS.IDX_RELATCAR_PLYNO
FILTER
TABLE ACCESS FULL SYS.DUAL
运行慢
加上
/*+index(a IDX_PLY_BASE_PLYNO) */
也是相同的执行计划
将语句改造
Select /*+index(a IDX_PLY_BASE_PLYNO)*/
* From t_plY_base a
WHERE
c_ply_no in (select c_relat_no from t_Relat_Car where c_Ply_No =trim('1210103172006001082')
union all
select c_ply_no from t_Relat_Car where c_relat_No =trim('1210103172006001082')
union all
select trim('1210103172006001082') from dual
)
执行计划为
SELECT STATEMENT Optimizer Mode=CHOOSE
MERGE JOIN
TABLE ACCESS BY GLOBAL INDEX ROWID AISYS.T_PLY_BASE
INDEX FULL SCAN AISYS.IDX_PLY_BASE_PLYNO
SORT JOIN
VIEW SYS.VW_NSO_1
SORT UNIQUE
UNION-ALL
INDEX RANGE SCAN AISYS.IDX_RELATCAR_PLYNO
INDEX RANGE SCAN AISYS.IDX_RELATCAR_RELATNO
TABLE ACCESS FULL SYS.DUAL
也不行
如何写HINT使ORACLE能够使用恰当的执行计划,使AISYS.IDX_PLY_BASE_PLYNO能够使用的范围索引,如下
SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS BY INDEX ROWID AISYS.T_PLY_BASE
NESTED LOOPS
VIEW SYS.VW_NSO_1
SORT UNIQUE
INDEX RANGE SCAN AISYS.IDX_PLY_BASE_PLYNO