具有 NULLable 列的 Oracle DISTINCT

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 版本開始出現這種行為？

我不知道，但我希望它會存在很長時間。

可靠嗎？

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使用索引（所有非空值都在那裡）。

引用自：https://dba.stackexchange.com/questions/248884