oracle - PL 变量可以在 SQL 查询期间更改吗(为什么 Oracle 不优化我的简单查询)？

Question

今天我偶然发现了一个案例，我很容易假设一个简单的优化毫无疑问。

假设您有一个名为 my_table 的表，其中有一列名为 my_column 并且你写一个这样的存储过程:

procedure my_proc(a_value my_table.my_column%type := null) is
begin
    for i in (
        select another_column from my_table
        where  a_value is null or my_column = a_value)
    loop
      -- do something
    end loop;
end;

我一直假设表达式 a_value is null 对于由于 select 语句，或任何纯粹由 PL 变量和其他常量组成的表达式事情。换句话说，可以在执行之前安全地对其进行评估查询并替换为常量。例如，在这段代码中，当a_value 未传递，查询等同于

select another_column from my_table

相反，当传递值时，查询将等同于

select another_column from my_table
where my_column = a_value

令我惊讶的是，这个简单的优化并没有进行。 a_value 为 null表达式似乎对表中的每条记录都执行了，并且带有足够大的 table ，即使没有任何特殊之处，差异也很明显工具。我正在使用版本 10，并将此优化视为 2.0 或 3.0很久以前就会完成的功能。

这显然是有原因的。也许我假设 PL变量在 SQL 查询眼中是常量是不正确的。也许PL变量可以在执行 SQL 查询期间发生变化。你知道任何这样的情况？

Answer 1

当 Oracle 需要编译和优化 SQL 查询时，它必须创建一个查询计划，无论绑定(bind)变量的值是什么，该计划都将起作用，因为该计划可以在以后的相同查询但不同的值中重复使用。

在查询中，从 my_table 中选择 another_column，其中 a_value 为 null 或 my_column = a_value a_value 是绑定(bind)变量。查询只会硬解析成查询计划一次。它不能折叠到 select another_column from my_table 因为下一次调用存储过程可能会传入一个非空的 a_value。

编辑添加示例。

问汤姆有一个posting处理此问题的更复杂版本。根据他的回答。

示例数据:

create table my_table(my_column varchar2(10) null
    , another_column varchar2(10) null)
/
insert into my_table values('1', 'a');
insert into my_table values('2', 'b');
insert into my_table values('3', 'c');
insert into my_table values('4', 'd');
insert into my_table values('5', 'e');
insert into my_table values('6', 'f');
insert into my_table values('7', 'g');
insert into my_table values('8', 'h');
insert into my_table values('9', 'i');
insert into my_table values('10', 'j');
commit;

过程:

create or replace procedure my_proc(p_value in my_table.my_column%TYPE default null) is
    type l_rec_type is record(another_column my_table.another_column%TYPE);
    l_sql varchar2(32767) := 'select another_column from my_table where ';
    l_cursor sys_refcursor;
    l_record l_rec_type;
begin
    if p_value is null then 
        l_sql := l_sql || '(1=1 or :my_column is null)';
    else 
        l_sql := l_sql || '(my_column = :my_column)';
    end if;
    open l_cursor for l_sql using p_value;
    loop
        fetch l_cursor into l_record;
        exit when l_cursor%NOTFOUND;
        -- do something
        dbms_output.put_line(l_record.another_column);
    end loop;
    close l_cursor;
end my_proc;
/

观看它运行:

SQL> exec my_proc()
a
b
c
d
e
f
g
h
i
j

PL/SQL procedure successfully completed.

SQL> exec my_proc('2')
b

PL/SQL procedure successfully completed.