Oracle SP执行计划应取决于输入参数

askTom 2018-09-27

355

问题描述

嗨，伙计们，
我有一个具有输入参数的SP，执行计划应取决于提供给该过程的参数。
例如: 过程GetData (数据类型int，数据值int)
如果数据类型 = 1，我希望这个过程在列n1中搜索数据值，如果数据类型 = 2，在列2中搜索数据值。
我写了这样的东西:

SELECT * FROM Data
WHERE ( DataType = 1 AND column1 = DataValue )
   OR ( DataType = 2 AND column2 = DataValue )复制

Column1和Column2被索引，但似乎Oracle没有选择正确的执行计划。
oracle是否将SP执行计划保留在缓存中？
如果是这种情况，是否有任何方法可以强制Oracle使用正确的执行计划。
我知道我可以把我的SP写成

if ( DataType = 1 ) THEN
ELSE ....复制

但是实际上，该过程要复杂得多，这将需要大量的代码重复，我想避免。
谢谢你的帮助

专家解答

执行计划确实取决于输入变量!

但是，如果您有许多或条件，例如:

SELECT * FROM Data
WHERE ( DataType = 1 AND column1 = DataValue )
OR ( DataType = 2 AND column2 = DataValue )
OR ( DataType = 3 AND column3 = DataValue )
OR ( DataType = 4 AND column4 = DataValue )
...复制

优化器努力想出一个好的计划。您可以在以下位置阅读更多信息:

https://blogs.oracle.com/sql/optimizing-the-plsql-challenge-iv:-more-or-condition-woes

如果可选参数太多，无法对每个参数进行静态SQL查询，请查看动态SQL。这里你在运行时构造where子句:

sql_where := ' ... ';

if ( DataType = 1 ) THEN
  sql_where := sql_where || ' AND column1 = :DataValue '; 
elsif ( DataType = 2 ) THEN
  sql_where := sql_where || ' AND column2 = :DataValue '; 
elsif ... etc. 
复制

在以下位置阅读有关动态SQL的更多信息:

https://dzone.com/articles/a-quick-guide-to-writing-dynamic-sql-in-plsql