主页 / dba / 问题 / 248884
Accepted
eckes
Asked: 2019-09-17 07:23:26 +0800 CST

具有 NULLable 列的 Oracle DISTINCT

  • 772

Oracle 存在一个长期存在的（？）问题（？），如果您想对具有 B*-Tree 索引但可为空的列使用 DISTINCT 选择，则不使用该索引。（正如其他答案所暗示的那样，如果事后添加了一个受约束的检查（不存在空值），这甚至是正确的）。

有多种解决方法（包括使用 BITMAP 索引或向索引添加第二个 NON NULL 或常量列）。但是我刚刚注意到，如果我使用 WHERE NOT NULL 执行 SELECT DISTINCT，Oracle 能够使用索引（索引快速全扫描）。

我的问题：由于该行为存在于哪个 Oracle 版本，它是否可靠（何时首选两列索引）以及为什么没有更频繁地提及它（例如，这个原本很好的答案没有提及）？

小复制器

drop table SCOTT.T;
-- will not work with short rows (SELECT OWNER,SUBOBJECT_NAME ...)
create table SCOTT.T AS SELECT * FROM ALL_OBJECTS;
create index SCOTT.IDX_T_OWNER on SCOTT.T(OWNER); -- NOT NULL
-- (subobject_name,1) or (subobject_name,namespace) is NULL, NOT NULL
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(subobject_name); -- NULL
exec  dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'T', cascade=>true );
desc SCOTT.T;

set autotrace on explain
-- fast index scan:
select distinct OWNER from SCOTT.T;
-- full table scan:
select distinct SUBOBJECT_NAME from SCOTT.T;
-- fast index scan:
select distinct SUBOBJECT_NAME from SCOTT.T where SUBOBJECT_NAME IS NOT NULL;

看起来（在 18c 上）类似于：

select distinct subobject_name from T;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 81426 |   159K|   428   (1)| 00:00:01 |
---------------------------------------------------------------------------


select distinct subobject_name from T where SUBOBJECT_NAME is not null;

------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   624 |  1248 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE          |              |   624 |  1248 |     5  (20)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_SUBOBJ |  1459 |  2918 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------
oracle index

1 个回答

  1. Best Answer

    从哪个 Oracle 版本开始出现这种行为？

    我不知道，但我希望它会存在很长时间。

    可靠吗？

    100% 可靠

    什么时候首选两列索引？

    当您也想使用空记录时。

    select distinct SUBOBJECT_NAME from SCOTT.T;可以使用以下索引

    create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(1, subobject_name);

    为什么没有更频繁地提到它（例如这个原本很好的答案没有提到它）？

    一旦您知道索引不包含全空记录并且优化器不使用约束（看起来就是这种情况），整个逻辑就很清楚了。

    如果您的索引不包含空值并且您要求在可空列上区分，您会看到，该索引不能使用（空值丢失）。如果选择，可以where col is not null使用索引（所有非空值都在那里）。

    • 1

相关问题