针对存储过程的Oracle性能调整

Question

最近遇到的一个场景，我有很多select语句，如：

and  (UPPER(Table1.col1) LIKE UPPER(v_param1)||'%' or v_param1 is NULL)
and  (UPPER(Table2.col2) LIKE UPPER(v_param2)||'%' or v_param2 is NULL)

CCD_ 1&v_param2是存储过程的输入，我需要优化查询。是否有任何方法可以检查v_param1,v_param1是否为NULL，则无需检查Or之前的第一个条件。

Answer 1

删除该或并更改条件，如下面的

和鞋面(Table1.col1(类似鞋面(v_param1(||'%'和鞋面(Table2.col2(类似鞋面(v_param2(||'%'

注意：如果param值为null，则它将执行条件，如"upper(col1(like'%'">

Answer 2

如果使用存储过程，则可以在pl/sql中动态构建查询，绑定变量并使用dbms_sql执行查询。

...
sSQL varchar2(32767) := 'select col1, col2 from Table1';
iCurrId            number := DBMS_SQL.OPEN_CURSOR;
iCurRes            number;
begin
-- constant conditions
sSQL := sSQL||' where col3 = :column3_value';
-- add condition for v_param1 if it is not null  
if v_param1 is not null then
sSQL := sSQL||' and UPPER(Table1.col1) LIKE :v_param1_search_value';
end if;
-- add condition for v_param2 if it is not null  
if v_param2 is not null then
sSQL := sSQL||' and UPPER(Table1.col2) LIKE :v_param2_search_value';
end if;
-- parse cursor
DBMS_SQL.PARSE(
C             => iCurrId,
STATEMENT     => sSQL,
LANGUAGE_FLAG => DBMS_SQL.NATIVE
);
-- bind variables
DBMS_SQL.BIND_VARIABLE( C => iCurrId, NAME => 'column3_value', VALUE => 'abcd');
if v_param1 is not null then
DBMS_SQL.BIND_VARIABLE( C => iCurrId, NAME => 'v_param1_search_value', VALUE => UPPER(v_param1)||'%');
end if;
if v_param2 is not null then
DBMS_SQL.BIND_VARIABLE( C => iCurrId, NAME => 'v_param2_search_value', VALUE => UPPER(v_param2)||'%');
end if;
-- execute query
iCurRes := DBMS_SQL.EXECUTE(iCurrId);
...

Answer 3

您可以做的最简单的事情就是切换评估条件的顺序：

and  (v_param1 is NULL or UPPER(Table1.col1) LIKE UPPER(v_param1)||'%'  )
and  (v_param2 is NULL or UPPER(Table2.col2) LIKE UPPER(v_param2)||'%'  )

Oracle并不保证会缩短OR求值的时间，但在NULL测试中领先会让它朝着正确的方向前进。

Answer 4

尝试使用：

AND (
CASE
WHEN v_param2 IS NULL
THEN NULL
ELSE UPPER(Table2.col2)
END LIKE UPPER(v_param2)
||'%'
OR v_param2      IS NULL)