oracle - Oracle 绑定(bind)变量未正确使用索引的问题

Question

在我的场景中，以下查询运行速度很快(在有 7000 万行的表上运行 0.5 秒):

select * from Purchases
where (purchase_id = 1700656396)

而且，它甚至可以使用绑定(bind)变量快速运行:

var purchase_id number := 1700656396
select * from Purchases
where (purchase_id = :purchase_id)

这些运行得很快，因为我在 purchase_id 上有一个索引柱子。 (继续阅读...)

我需要创建一个允许对任意列进行“过滤”的查询。这意味着提供多个输入变量，并对每个变量进行过滤，除非它是 null。 .起初这很好用。

例如，以下查询运行速度也很快(0.5 秒):

select * from Purchases
where (1700656396 IS NULL OR purchase_id    = 1700656396)
and   (NULL       IS NULL OR purchase_name  = NULL)
and   (NULL       IS NULL OR purchase_price = NULL)

但是，当我尝试通过绑定(bind)变量或存储过程对查询进行参数化时，查询会显着减慢(1.5 分钟)，就好像它忽略了任何索引一样:

var purchase_id    number   := 1700656396
var purchase_name  varchar2 := NULL
var purchase_price number   := NULL
select * from Purchases
where (:purchase_id    IS NULL OR purchase_id    = :purchase_id)
and   (:purchase_name  IS NULL OR purchase_name  = :purchase_name)
and   (:purchase_price IS NULL OR purchase_price = :purchase_price)

现在，在我的应用程序中，我被迫在运行时动态构建查询以获得良好的性能。这意味着我失去了参数化查询的所有优势，并迫使我担心 SQL 注入(inject)。

是否可以在保持相同逻辑的同时避免动态构造的查询？

Answer 1

这确实是一个更大的话题，但这是我认为最容易实现并且效果很好的方法。诀窍是使用动态 SQL，但实现它以便始终传递相同数量的参数(需要)，并且当您没有参数值时允许 Oracle 短路(您缺少的您当前的方法)。例如:

set serveroutput on
create or replace procedure test_param(p1 in number default null, p2 in varchar2 default null) as
  l_sql varchar2(4000);
  l_cur sys_refcursor;
  l_rec my_table%rowtype;
  l_ctr number := 0;
begin

  l_sql := 'select * from my_table where 1=1';
  if (p1 is not null) then
    l_sql := l_sql || ' and my_num_col = :p1';
  else
    -- short circuit for optimizer (1=1)
    l_sql := l_sql || ' and (1=1 or :p1 is null)';
  end if;

  if (p2 is not null) then
    l_sql := l_sql || ' and name like :p2';
  else
    -- short circuit for optimizer (1=1)
    l_sql := l_sql || ' and (1=1 or :p2 is null)';
  end if;

  -- show what the SQL query will be
  dbms_output.put_line(l_sql);

  -- note always have same param list (using)
  open l_cur for l_sql using p1,p2;

  -- could return this cursor (function), or simply print out first 10 rows here for testing
  loop
    l_ctr := l_ctr + 1;
    fetch l_cur
    into l_rec;
    exit when l_cur%notfound OR l_ctr > 10;

    dbms_output.put_line('Name is: ' || l_rec.name || ', Address is: ' || l_rec.address1);
  end loop;
  close l_cur;
end;

要测试，只需运行它。例如:

set serveroutput on
-- using 0 param
exec test_param();
-- using 1 param
exec test_param(123456789);
-- using 2 params
exec test_param(123456789, 'ABC%');

在我的系统上，使用的表超过 100 毫米行，在数字字段和名称字段上有索引。几乎立即返回。另请注意，如果您不需要所有列，您可能不想执行 select *，但我有点懒惰并在此示例中使用 %rowtype。

希望有帮助