ORACLE的组合列问题

原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2018/10/22/column-groups-5/

当某种特殊情况时，好的特性也会变成坏的——今天的帖子就是几年前出现在Oracle-L listserver上的一个例子，其中有一个问题是优化器在做什么。我将通过创建一些模拟数据来重现问题场景：

rem
rem     Script:         distinct_key_prob.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2016
rem     Purpose:
rem
rem     Last tested
rem             19.1.0.0  (Live SQL, with some edits)
rem             18.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0.4
rem

drop table t1 purge;

create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > commment to avoid wordpress format issue
)
select
        cast(mod(rownum-1,10) as number(8,0))   non_null,
        cast(null as number(8,0))               null_col,
        cast(lpad(rownum,10) as varchar2(10))   small_vc,
        cast(rpad('x',100) as varchar2(100))    padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > commment to avoid wordpress format issue
;

create index t1_i1 on t1(null_col, non_null);

begin

/*
        dbms_output.put_line(
                dbms_stats.create_extended_stats(user,'t1','(non_null, null_col)')
        );
*/

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

我有一个有1,000,000行的表；其中一列总是空的，另一列有非常少的不同值，并且从不为空（尽管它没有被声明为非空）。我已经创建了一个以“always null”列为首列的索引（在实际生产环境中，我们会看到一个“几乎总是”null的列，并且有一些列不为null的特殊行，所以这样的索引是有意义的）。
这里有几行代码，注释掉了，用来在组合列（non_null，null_col）上创建多列统计信息，因为任何与处理多列索引中不同列的唯一值数量相关的异常也可能与组合列相关。我可以执行这段代码的两个分支，一个有索引，一个没有索引，但有组合列统计信息，并且在这两种情况下都会出现相同的cardinality问题。
因此，在带入两个绑定变量之后，让我们执行两个查询——并从内存中获取它们的执行计划：

variable b_null    number
variable b_nonnull number

exec :b_null    := 5
exec :b_nonnull := 5

set serveroutput off

prompt  ===================
prompt  Query null_col only
prompt  ===================

select  count(small_vc)
from    t1
where
        null_col = :b_null
;

select * from table(dbms_xplan.display_cursor(null,null,'-plan_hash'));

prompt  =========================
prompt  Query (null_col,non_null)
prompt  =========================

select  count(small_vc)
from    t1
where
        null_col = :b_null
and     non_null = :b_nonnull
;

select * from table(dbms_xplan.display_cursor(null,null,'-plan_hash'));

优化器的统计信息可知null_col总是空的，所以它对null_col=5的行的估算是零（将四舍五入为1）；并且我们有一个以null_col开头的索引，所以我们可能希望优化器对这些查询使用该索引的索引范围扫描。以下是实际的执行计划：

SQL_ID  danj9r6rq3c7g, child number 0
-------------------------------------
select count(small_vc) from t1 where  null_col = :b_null

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    24 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    24 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("NULL_COL"=:B_NULL)



SQL_ID  d8kbtq594bsp0, child number 0
-------------------------------------
select count(small_vc) from t1 where  null_col = :b_null and non_null =
:b_nonnull

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  2189 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    27 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   100K|  2636K|  2189   (4)| 00:00:11 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("NULL_COL"=:B_NULL AND "NON_NULL"=:B_NONNULL))

仔细看看我们得到了什么：第二个查询必须访问与第一个查询所标识的谓词完全相同的数据行，然后应用第二个谓词，这个谓词可能会丢弃其中的一些行，但是优化器已经将访问路径从低成本的索引访问更改为高成本的全表扫描。这显然是愚蠢的——在这种情况下，优化器逻辑必定存在缺陷。
这个缺陷围绕着处理组合列时的不一致性展开，不管它们是显式创建的，还是通过引用user_indexes.distinct_keys推断出的. 通过显式创建组合列、收集统计信息和从user_tab_cols反馈，可以很容易地看到相应异常。

select
        column_name, sample_size, num_distinct, num_nulls, density, histogram, data_default
from
        user_tab_cols
where
        table_name = upper('T1')
order by
        column_id

;

OLUMN_NAME                            Sample     Distinct  NUM_NULLS    DENSITY HISTOGRAM       DATA_DEFAULT
-------------------------------- ------------ ------------ ---------- ---------- --------------- --------------------------------------------
NON_NULL                            1,000,000           10          0         .1 NONE
NULL_COL                                                 0    1000000          0 NONE
SMALL_VC                            1,000,000      995,008          0 .000001005 NONE
PADDING                             1,000,000            1          0          1 NONE
SYS_STULC#01EE$DE1QB7UY1K4$PBI      1,000,000           10          0         .1 NONE            SYS_OP_COMBINED_HASH("NON_NULL","NULL_COL")

正如所见，优化器可以注意到“null_col”总是空的，所以“null_col=：bind1”将产生非常小的cardinality估算；另一方面，当优化器看到“null_col = :bind1 and non_null = :bind2”时，它将把它转换成单个谓词“SYS_STULC#01EE$DE1QB7UY1K4$PBI = sys_op_combined_hash(null_col, non_null)”，并且统计数据表明这个（虚拟）列有10个不同的唯一值，没有空值，因此cardinality估算倾向于全表扫描。
我在上面提到的处理过程中的“轻微不一致”是，如果您使用“null_col is null and non_null = :bind2″这样的谓词，优化器将不会使用到组合列，尽管“is null”条件这正是多列统计信息所适用的情况。（在本例中，我通过忽略组合列构造了优化器的估算值，这实际上是正确的，并且与使用组合列得到的估算值相同，因为每一行的列都是空的。）

tl;dr
如果组合列的任何列有相当大比例的空值，组合列可能会给您一些非常糟糕的估计值，并且会出现违反常理的行为；这是因为组合列会使优化器忽略基础数据集中的空值数量。