ORACLE NULL值谓词问题

原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2018/12/18/null-predicate/

人们不时会问我是否要写另一本关于“基于成本的优化器”的书，我想答案是否定的，因为产品一直在快速增长，不可能跟上发展的步伐，而且总是有越来越多的小细节可能已经存在多年，当有人问我关于一些我以前从未注意到的小细节的问题。
这些“小细节”的难点在于你能花多少时间去弄清楚它们是否重要，以及是否值得写些关于它们的文章。这里有一个小例子来说明我的意思——首先是构造数据集：

rem
rem     Script:         null_filter.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2018
rem     Purpose:
rem
rem     Last tested
rem             18.3.0.0
rem             12.1.0.2
rem

create table t1
nologging
as
select  *
from    all_objects
where   rownum <= 50000 -- > comment to avoid wordpress format issue
;

insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;

create index t1_i1 on t1(object_type, data_object_id, object_id, created);

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/

这是一个简单的数据集，只有一个索引。索引唯一有意义的地方是第二列（data_object_id）经常为空。这导致一对非常相似的语句在执行计划中出现了一点差异：

set serveroutput off
alter session set statistics_level = all;

select
        object_name, owner
from
        t1
where
        object_type = 'TABLE'
and     data_object_id = 20002
and     object_id = 20002
and     created > trunc(sysdate - 90)
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select
        object_name, owner
from
        t1
where
        object_type = 'TABLE'
and     data_object_id is null
and     object_id = 20002
and     created > trunc(sysdate - 90)
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

在这两个查询的执行计划中，会有多大的差异？当然，“ is null”谓词的副作用是禁用索引基于distinct_keys的“隐式列分组”，但在本例中，我在其中一列上有一个基于范围的谓词，因此Oracle无论如何都不会使用distinct_keys。
当然，有一点是不能使用带null的相等运算符，必须使用“is null”，这可能会产生影响，但如何做到呢？以下是两个执行计划：

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |      0 |00:00:00.01 |       3 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |      1 |      0 |00:00:00.01 |       3 |      1 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |      1 |      1 |      0 |00:00:00.01 |       3 |      1 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='TABLE' AND "DATA_OBJECT_ID"=20002 AND "OBJECT_ID"=20002 AND
              "CREATED">TRUNC(SYSDATE@!-90))

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |      0 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE"='TABLE' AND "DATA_OBJECT_ID" IS NULL AND "OBJECT_ID"=20002 AND
              "CREATED">TRUNC(SYSDATE@!-90))
       filter(("OBJECT_ID"=20002 AND "CREATED">TRUNC(SYSDATE@!-90)))

谓词“data_object_id is null”的查询将object_id和sysdate谓词重复作为access谓词和filter谓词。这似乎有点令人惊讶，而且是一个潜在的性能风险。在第一个查询中，查询引擎将非常有效地在正确的位置命中正确的索引叶块，然后顺序扫描，将每个rowid提供给父运算符，直到它到达范围的末尾。
使用“is null”的执行计划，查询引擎在扫描索引叶子节点时，将检查object_id和created的实际值，这将消耗额外的CPU开销，更重要的是，Oracle可能会从object_type='TABLE’和data_object_id is null的第一个索引项开始，并遍历所有为空的索引条目，检查object_id是否正确？
基于这一比较，我们如何证明Oracle并没有从第一个索引条目开始一直走下去，在第一个索引条目中，object_type='TABLE’和data_object_id is null，检查扫描的每个条目，或者换一种说法，Oracle是否真的准确的将索引范围扫描缩减到最小的“路径”去执行，即谓词“OBJECT_ID”=20002 AND “CREATED”>TRUNC(SYSDATE@!-90)作为access谓词？
让我们使用sys-op-lbid（）函数计算相关的叶子块数，Oracle在内部使用该函数计算索引中的叶块数。首先我们得到索引对象的id，然后扫描它，看看有多少叶块包含与我们的object_type and data_object_id谓词匹配的条目，并出现在我们的目标值20002之前的索引中：

column object_id new_value m_index_id
 
select
        object_id
from
        user_objects
where
        object_type = 'INDEX'
and     object_name = 'T1_I1'
;
 
select  distinct sys_op_lbid(&m_index_id, 'L', rowid)
from    t1
where   object_type    = 'TABLE'
and     data_object_id is null
and     object_id      < 20002
;
SYS_OP_LBID(159271
------------------
AAAm4nAAFAAACGDAAA
AAAm4nAAFAAACF9AAA
AAAm4nAAFAAACGCAAA
AAAm4nAAFAAACF/AAA
AAAm4nAAFAAACF+AAA
AAAm4nAAFAAACGFAAA
AAAm4nAAFAAACGEAAA
AAAm4nAAFAAACGGAAA
 
8 rows selected.

这告诉我们，在找到object_ID=20002之前，索引中有8个叶块，我们必须对其进行遍历，如果Oracle实际上没有使用access谓词并缩小它正在探测的索引的范围，那么我们将看到8个buffer get，而不是rowsource execution stats中的3个。
一句话：对于多列索引，当列是索引中较靠前的列时，“column is null”和“column=constant”之间的执行计划似乎有所不同，但即使“is null”选项导致某些访问谓词在索引范围内重新显示为filter谓词并产生额外的工作可能并不重要——Oracle仍然在索引范围扫描中使用最小数量的索引叶块。