Oracle 条件逻辑的PLSQL性能调优

askTom 2018-07-25

476

问题描述

我写了一个验证解析器，它使用webservice调用 (意味着需要高性能代码才能运行)，
我已经在Live sql中添加了示例链接
其中我已经添加了条件子句 (用于null检查几个输入参数并相应地查询) 到sql部分的where子句 (认为sql优化器将使条件代码更快)，而不是使用传统的IF条件来处理sql子句。

但是，代码审阅者认为，这样的和条件子句会降低sql查询的性能，而我认为这样的子句会降低PLSQL的性能，(相信SQL优化器可以非常有效地优化这些条件子句)

SELECT COUNT (*)
FROM
MTL_MANUFACTURERS WHERE ( :P_MANUFACTURER_ID IS NOT NULL AND :P_MANUFACTURER_NAME IS NULL AND
MANUFACTURER_ID = :P_MANUFACTURER_ID ) OR ( :P_MANUFACTURER_ID IS NULL AND :P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = :P_MANUFACTURER_NAME ) OR ( :P_MANUFACTURER_ID IS NOT NULL AND :P_MANUFACTURER_NAME IS NOT NULL AND
MANUFACTURER_NAME = :P_MANUFACTURER_NAME AND MANUFACTURER_ID = :P_MANUFACTURER_ID );复制

还有其他方法可以提高此查询的性能吗？

专家解答

“视情况而定”。

您可以在IF子句中选择单独的语句的优点是您可以手动调整每个语句。缺点是，当您添加更多可选参数时，这变得不切实际。

拥有单个SQL语句更容易管理。但是，在一个SQL语句中拥有所有条件测试的缺点是，优化器将难以在所有情况下提出一个好的解决方案。无论您创建哪个索引，它都将趋向于全表扫描。

但是还有另一种技术可能会有所帮助。

在所有可搜索列上创建单列索引。然后写一个查询，其中:

-为匹配相应参数的每一列有一个单独的选择，返回rowid
-把所有这些罗威人联合在一起
-检查每个rowid返回的次数是否与非null参数的数量匹配

这对你来说看起来像:

select * from mtl_manufacturers
where  rowid in (
  select rid from (
    select rowid rid from mtl_manufacturers 
    where  manufacturer_id = p_manufacturer_id
    union all
    select rowid rid from mtl_manufacturers 
    where  manufacturer_name = p_manufacturer_name
  )
  group by rid
  having count(*) = nvl2(p_manufacturer_id,1,0) + 
                    nvl2(p_manufacturer_name,1,0)
);复制

有关此方法的更多详细信息，请阅读Oren Nakdimon提出此方法的帖子:

http://db-oriented.com/2018/04/14/a-single-query-with-many-filter-combinations/

现在，有no保证这样会更好。最终，只有一种方法可以让你知道:

测试!

用现实的生产数据加载表。并比较你不同的方法。看看哪个最好。