oracle - 为什么 Oracle 的查询规划器要添加一个复制约束的过滤谓词？

Question

我有一个简单的 Oracle 查询，其中的计划没有意义。

SELECT
    u.custid AS "custid",
    l.listid AS "listid"
FROM
    users u
    INNER JOIN lists l ON u.custid = l.custid

下面是 autotrace explain 告诉我它有一个计划

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |  1468K|    29M|       | 11548   (1)| 00:00:01 |
|*  1 |  HASH JOIN            |          |  1468K|    29M|  7104K| 11548   (1)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| USERS_PK |   404K|  2367K|       |   266   (2)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL   | LISTS    |  1416K|    20M|       |  9110   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("U"."CUSTID"="L"."CUSTID")
   3 - filter(TRUNC("SORT_TYPE")>=1 AND TRUNC("SORT_TYPE")<=16)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 1 Sql Plan Directive used for this statement

令我担心的是谓词 3。sort_type 没有出现在查询中，也根本没有被索引。在我看来，sort_type 根本不应该参与此查询。

lists.sort_type 有一个约束:(是的，我知道我们可以让 sort_type 是一个 INTEGER 而不是一个 NUMBER)

sort_type   NUMBER DEFAULT 2 NOT NULL,
    CONSTRAINT lists_sort_type CHECK ( sort_type BETWEEN 1 AND 16 AND TRUNC(sort_type) = sort_type )

在我看来，sort_type 上的过滤器基本上是同义反复。由于该约束，lists 中的每一行都必须通过该过滤器。

如果我放弃约束，过滤器将不再出现在计划中，并且估计成本会下降一点点。如果我重新添加约束，计划将再次使用过滤器。一种或另一种方式的执行速度没有显着差异。

我很担心，因为我在一个更大、更复杂的查询中发现了这个过滤器，我试图从几分钟的运行时间开始对其进行优化。

Oracle 为什么要添加该过滤器，这是一个问题和/或指向另一个问题吗？

编辑:如果我将 sort_type 上的约束更改为没有 TRUNC 部分，过滤器就会消失。如果我将约束拆分为两个不同的约束，过滤器就会返回。

Answer 1

一般来说，Oracle 会根据您的CHECK 约束生成谓词，只要这样做可以为优化器提供更多信息以生成良好的计划。它并不总是足够聪明地识别出它们何时是多余的。这是 Oracle 12c 中使用您的表名的一个简短示例:

-- Create the CUSTS table
CREATE TABLE custs ( custid number not null );
CREATE INDEX custs_u1 on custs (custid);

-- Create the LISTS table
CREATE TABLE lists 
  ( listid number not null, 
    sort_type number not null, 
    custid number,
    constraint lists_c1 check ( sort_type between 1 and 16 and
          trunc(sort_type) = sort_Type )
);

-- Explain a join
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(u) */ 
       u.custid AS "custid",
       l.listid AS "listid"
FROM custs u
INNER JOIN lists l ON u.custid = l.custid;

-- Show the plan
select * from table(dbms_xplan.display);

Plan hash value: 2443150416

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |          |     1 |    39 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CUSTS_U1 |     1 |    13 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| LISTS    |     1 |    26 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("U"."CUSTID"="L"."CUSTID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

到目前为止，没有什么奇怪的。没有添加有问题的谓词。

现在，让我们告诉 Oracle 优化器TRUNC(sort_type) 上的数据分布可能很重要...

declare
  x varchar2(30);
begin
  x := dbms_stats.create_extended_stats ( user, 'LISTS', '(TRUNC(SORT_TYPE))');
  dbms_output.put_line('Extension name = ' || x);
end;

...现在，让我们再次解释同一个查询...

-- Re-explain the same join as before
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(u) */ 
       u.custid AS "custid",
       l.listid AS "listid"
FROM custs u
INNER JOIN lists l ON u.custid = l.custid;

-- Show the new plan
select * from table(dbms_xplan.display);

Plan hash value: 2443150416

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    52 |     3   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |          |     1 |    52 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CUSTS_U1 |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LISTS    |     1 |    39 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("U"."CUSTID"="L"."CUSTID")
   3 - filter(TRUNC("SORT_TYPE")>=1 AND TRUNC("SORT_TYPE")<=16)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

现在，Oracle 添加了谓词，因为 CBO 可能会从中受益。它真的从中受益吗？不，但 Oracle 只是足够聪明，知道它可能并且不会 (*) 伤害任何东西。

(*) 在以前的版本中存在许多错误，这些错误_已经_破坏了 CBO 估计的选择性。

扩展统计信息的存在只是 Oracle 认为它可以从该谓词中获益的原因之一。要查明这是否是您的原因，您可以像这样在数据库中查找扩展统计信息:

SELECT * FROM dba_stat_extensions where table_name = 'LISTS';

请记住，Oracle CBO 可以自行创建统计扩展。因此，可能会有您没有意识到的扩展统计数据。