存储过程SQL执行计划 [英] Stored procedure SQL execution plan

问题描述

我对执行缓慢的存储过程有些困惑.存储过程基本上包含一个使用传入参数(in_id)的查询，并将其放置在光标中，如下所示:

I'm a bit stuck with a stored procedure that is executing really slow. The stored procedure basically contains a query that uses an incoming parameter (in_id) and is put in a cursor like this:

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)"，则应用程序的性能将再次提高.

When I get an execution plan for the SQL query separately with predefined value, I get good results with the query using an index. However when I call the procedure from my application, I see that no index is being used and the table gets full scan, thus giving slow performance.
If I remove the first part of WHERE clause "(in_id is null)" the performance from the application is fast again.
How come the index isn't used during the call from my application (in_id is passed in)?

推荐答案

假定in_id是查询参数-不是列名:

Assuming that in_id is a query parameter - not a column name:

无论输入内容如何，​​查询都必须只有一个执行计划.因此，如果将参数in_id传递为NULL，则应该返回所有行.如果传递非NULL，则in_id应该仅返回单个PK值.

The query has to have only ONE exec plan, regardless of the input. So if you pass parameter in_id as NULL, then it is supposed to return ALL rows. If you pass non-NULL in_id is should return only a single PK value.

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

So Oracle chooses the "worst possible" exec. plan to deal with "worst possible" scenario. The "generic" queries are road to hell. Simply split the query into two.

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

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

This will use FULL table scan, which is the best way how to get all the rows.

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

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

This will use UNIQUE index scan, which is the best way how to get a single indexed row.

这篇关于存储过程SQL执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！