存储过程SQL执行计划

Question

我对执行速度非常慢的存储过程有些困惑。该存储过程基本上包含一个使用传入参数（in_id）的查询，并将其放在游标中，如下所示：

open tmp_cursor for 
select col1, col2, col3
from table1 tab
where ((in_id is null) or (tab.id = in_id));  -- tab.id is the PK

当我获得带有预定义值的SQL查询的执行计划时，使用索引可以得到良好的查询结果。但是，当我从应用程序中调用该过程时，我看到没有索引在使用中，并且该表得到了完整扫描，从而降低了性能。
如果删除WHERE子句的第一部分“（in_id为null）”，则应用程序的性能将再次提高。
为什么在我的应用程序调用过程中不使用索引（传入了in_id）？

Answer 1

假设这in_id是一个查询参数-不是列名：

不管输入什么，查询都必须只有一个执行计划。因此，如果将参数传递 in_id为NULL，则应该返回所有行。如果传递非NULL
in_id，则应仅返回单个PK值。

因此，Oracle选择了“最糟糕的”执行程序。计划应对“最坏的情况”。“通用”查询是通向地狱的道路。只需将查询分为两个即可。

select col1, col2, col3
from table1 tab
where in_id is null or in_id is not null;

这将使用全表扫描，这是获取所有行的最佳方法。

select col1, col2, col3
from table1 tab
where tab.id = in_id;  -- tab.id is the PK

这将使用UNIQUE索引扫描，这是获取单个索引行的最佳方法。