sql - Oracle SQL 优化器在处理 OR 和与行无关的谓词时的行为(例如，无论行如何，函数都返回相同的值)

Question

听说OR s 不好，并且有多个 OR s 可能会显着影响性能。但是行独立的情况如何OR ?看一个例子:

SELECT
  *
FROM
  some_table t
WHERE
  (
    some_function('CONTEXT') = 'context of selecting by id'
    AND t.id = TO_NUMBER(another_function('ID'))
  )
  OR (
    some_function('CONTEXT') = 'context of filtering by name'
    AND t.name LIKE '%' || another_function('NAME') || '%'
  )
  OR (
    some_function('CONTEXT') = 'context of taking actual rows'
    AND TO_DATE(another_function('ACTUAL_DATE'), '...')
        BETWEEN t.start_date AND t.end_date
  )
  ...

这里 some_function('CONTEXT')无论行如何，都返回相同的值(它不使用任何与行相关的数据，例如列值作为其参数，并且在查询执行时它不会改变影响结果的内部状态)。它也可以只是一个包变量，如 some_package.context .
我认为，优化器应该计算 some_function('CONTEXT')先决定哪个 OR采取。
但实际上会发生什么呢？我如何确定这样的查询不会有性能泄漏？

附注:11.2

Answer 1

您需要使用未记录的提示 use_concat(or_predicates(1))或使用 UNION ALL 重写查询.无论函数如何，优化器在处理这些类型的谓词时都存在问题。

预期计划

你想要一个看起来像这样的计划:

------------------------------------------------------
| Id  | Operation                     | Name         |
------------------------------------------------------
|   0 | SELECT STATEMENT              |              |
|   1 |  CONCATENATION                |              |
|*  2 |   FILTER                      |              |
|*  3 |    TABLE ACCESS FULL          | SOME_TABLE   |
|*  4 |   FILTER                      |              |
|*  5 |    TABLE ACCESS FULL          | SOME_TABLE   |
|*  6 |   FILTER                      |              |
|*  7 |    TABLE ACCESS BY INDEX ROWID| SOME_TABLE   |
|*  8 |     INDEX UNIQUE SCAN         | SYS_C0010268 |
------------------------------------------------------

FILTER在 Operation与典型的 filter 大不相同在 Predicate Information解释计划的部分。这些 FILTER将
评估条件并决定在运行时使用执行计划的哪一部分。根据传递给函数的值，计划将
使用全表扫描(对于名称或日期的非选择性谓词)或使用唯一索引扫描(对于 id 的非常有选择性的谓词)。

对于像您这样的查询，这正是您想要的。如果查询只有少量 AND s 和 OR s，可能会有 FILTER .

实际计划

但实际上，有了一个复杂的谓词，计划看起来像这样:

----------------------------------------
| Id  | Operation         | Name       |
----------------------------------------
|   0 | SELECT STATEMENT  |            |
|*  1 |  TABLE ACCESS FULL| SOME_TABLE |
----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SOME_FUNCTION"('CONTEXT')='context of filtering by name' 
              AND "T"."NAME" LIKE '%'||"ANOTHER_FUNCTION"('NAME')||'%' OR 
              "SOME_FUNCTION"('CONTEXT')='context of taking actual rows' AND 
              "T"."START_DATE"<=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...') AND 
              "T"."END_DATE">=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...') OR 
              "SOME_FUNCTION"('CONTEXT')='context of selecting by id' AND 
              "T"."ID"=TO_NUMBER("ANOTHER_FUNCTION"('ID')))

全表扫描并不总是坏的。但是它们对于选择单个主键值来说非常糟糕。

示例架构

创建一个表和 100 万个示例行。一些色谱柱具有高度选择性，而另一些则非常没有选择性。他们都有直方图，所以优化器有很多
的好信息。

drop table some_table purge;

create table some_table
(
    id          number primary key,
    name        varchar2(100),
    start_date  date,
    end_date    date
);

begin
    for i in 1 .. 10 loop
        insert into some_table
        select 
            level+(i*100000),
            'Name '||mod(level, 5),
            date '2000-01-01' + mod(level, 10000),
            date '2010-01-01' + mod(level, 10000)
        from dual
        connect by level <= 100000;
    end loop;
end;
/
begin
    dbms_stats.gather_table_stats(user, 'SOME_TABLE'
        ,method_opt => 'for all columns size 254');
end;
/

示例函数

这些函数是非常静态的，优化器应该知道这一点。此示例使用 some_function以永远不会匹配任何东西的方式。
这是一种最好的情况； Oracle 应该很容易确定此查询将不返回任何内容。

--Static functions.
create or replace function some_function(p_context in varchar2) return varchar2 is
begin
    return p_context;
end;
/
--Btw, returning stringly-typed data is almost always a horrible idea.
--(Althogh if you're dealing with sys_context you may not have a choice.)
create or replace function another_function(p_type in varchar2) return varchar2 is
begin
    if p_type = 'ID' then
        return '1';
    elsif p_type = 'NAME' then
        return 'Name 1';
    elsif p_type = 'ACTUAL_DATE' then
        return '2000-01-01';
    end if;
end;
/

默认 - 没有 FILTER 操作的错误计划

默认计划很差。查询应该在几乎 0 秒内运行，但必须执行全表扫描。

explain plan for
SELECT * FROM some_table t
WHERE
  (
    some_function('CONTEXT') = 'context of selecting by id'
    AND t.id = TO_NUMBER(another_function('ID'))
  )
  OR (
    some_function('CONTEXT') = 'context of filtering by name'
    AND t.name LIKE '%' || another_function('NAME') || '%'
  )
  OR (
    some_function('CONTEXT') = 'context of taking actual rows'
    AND TO_DATE(another_function('ACTUAL_DATE'), '...')
        BETWEEN t.start_date AND t.end_date
  );

select * from table(dbms_xplan.display);

Plan hash value: 3038250352

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   525 | 14700 |  1504  (17)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SOME_TABLE |   525 | 14700 |  1504  (17)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SOME_FUNCTION"('CONTEXT')='context of filtering by name' 
              AND "T"."NAME" LIKE '%'||"ANOTHER_FUNCTION"('NAME')||'%' OR 
              "SOME_FUNCTION"('CONTEXT')='context of taking actual rows' AND 
              "T"."START_DATE"<=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...') AND 
              "T"."END_DATE">=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...') OR 
              "SOME_FUNCTION"('CONTEXT')='context of selecting by id' AND 
              "T"."ID"=TO_NUMBER("ANOTHER_FUNCTION"('ID')))

use_concat(or_predicates(1)) - 使用过滤器的好计划

USE_CONCAT 提示会将查询转换为单独的 UNION ALL脚步。
然后每个谓词都很简单并且有一个 FILTER手术。不幸的是 USE_CONCAT有一些奇怪的限制。有时它只有在索引时才有效
使用(请参阅 My Oracle Support 文档 259741.1)。有时它根本不起作用，变通方法不起作用，而且它仍然没有在 12c 中修复(文档 14545269.8)。

添加 or_predicates(1)使它工作，但它完全没有记录。

explain plan for
SELECT --+ use_concat(or_predicates(1))
  *
FROM some_table t
WHERE
  (
    some_function('CONTEXT') = 'context of selecting by id'
    AND t.id = TO_NUMBER(another_function('ID'))
  )
  OR (
    some_function('CONTEXT') = 'context of filtering by name'
    AND t.name LIKE '%' || another_function('NAME') || '%'
  )
  OR (
    some_function('CONTEXT') = 'context of taking actual rows'
    AND TO_DATE(another_function('ACTUAL_DATE'), '...')
        BETWEEN t.start_date AND t.end_date
  );

select * from table(dbms_xplan.display);

Plan hash value: 1618041905

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 52500 |  1435K|  2721   (8)| 00:00:01 |
|   1 |  CONCATENATION                |              |       |       |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | SOME_TABLE   |  2500 | 70000 |  1362   (8)| 00:00:01 |
|*  4 |   FILTER                      |              |       |       |            |          |
|*  5 |    TABLE ACCESS FULL          | SOME_TABLE   | 49999 |  1367K|  1356   (7)| 00:00:01 |
|*  6 |   FILTER                      |              |       |       |            |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID| SOME_TABLE   |     1 |    28 |     3   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN         | SYS_C0010269 |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SOME_FUNCTION"('CONTEXT')='context of taking actual rows')
   3 - filter("T"."START_DATE"<=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...') AND 
              "T"."END_DATE">=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...'))
   4 - filter("SOME_FUNCTION"('CONTEXT')='context of filtering by name')
   5 - filter("T"."NAME" LIKE '%'||"ANOTHER_FUNCTION"('NAME')||'%' AND 
              (LNNVL("SOME_FUNCTION"('CONTEXT')='context of taking actual rows') OR 
              LNNVL("T"."START_DATE"<=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...')) OR 
              LNNVL("T"."END_DATE">=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...'))))
   6 - filter("SOME_FUNCTION"('CONTEXT')='context of selecting by id')
   7 - filter((LNNVL("SOME_FUNCTION"('CONTEXT')='context of filtering by name') OR 
              LNNVL("T"."NAME" LIKE '%'||"ANOTHER_FUNCTION"('NAME')||'%')) AND 
              (LNNVL("SOME_FUNCTION"('CONTEXT')='context of taking actual rows') OR 
              LNNVL("T"."START_DATE"<=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...')) OR 
              LNNVL("T"."END_DATE">=TO_DATE("ANOTHER_FUNCTION"('ACTUAL_DATE'),'...'))))
   8 - access("T"."ID"=TO_NUMBER("ANOTHER_FUNCTION"('ID')))

UNION ALL - 使用过滤器的好计划

手动扩展查询可能是一种更安全的方法。但是根据您的查询的复杂程度，它可能会变得非常难看。

explain plan for
SELECT * FROM some_table t
WHERE some_function('CONTEXT') = 'context of selecting by id' AND t.id = TO_NUMBER(another_function('ID'))
union all
SELECT * FROM some_table t
WHERE some_function('CONTEXT') = 'context of filtering by name' AND t.name LIKE '%' || another_function('NAME') || '%'
union all
SELECT * FROM some_table t
WHERE some_function('CONTEXT') = 'context of taking actual rows' AND TO_DATE(another_function('ACTUAL_DATE'), '...') BETWEEN t.start_date AND t.end_date

select * from table(dbms_xplan.display);

(Plan not shown - it's basically the same as the `USE_CONCAT` version.)

案例 - 没有过滤器的糟糕计划

将谓词重写为单个 CASE是个好主意，但在这里似乎行不通。虽然这可能只是我的具体例子的问题。

explain plan for
SELECT *
FROM some_table t
WHERE
    case
    when some_function('CONTEXT') = 'context of selecting by id'
        AND t.id = TO_NUMBER(another_function('ID')) then 1
    when some_function('CONTEXT') = 'context of filtering by name'
        AND t.name LIKE '%' || another_function('NAME') || '%' then 1
    when some_function('CONTEXT') = 'context of taking actual rows'
        AND TO_DATE(another_function('ACTUAL_DATE'), '...') BETWEEN t.start_date AND t.end_date then 1
    else 0 end
    = 1;

select * from table(dbms_xplan.display);

(Plan not shown - it's basically the same as the default version with the full table scan.)