MERGE JOIN по двум индексам, все еще вызывающим СОРТ?

Это вопрос производительности, упрощенный для объединения двух индексов. Возьмите следующую настройку:

CREATE TABLE ZZ_BASE AS SELECT dbms_random.random AS ID, DBMS_RANDOM.STRING('U',10) AS STR FROM DUAL CONNECT BY LEVEL <=1000000;
CREATE INDEX ZZ_B_I ON ZZ_BASE(ID ASC); 
CREATE TABLE ZZ_CHILD AS SELECT dbms_random.random AS ID, DBMS_RANDOM.STRING('U',10) AS STR FROM DUAL CONNECT BY LEVEL <=1000000;
CREATE INDEX ZZ_C_I ON ZZ_CHILD(ID ASC);

-- As @Flado pointed out, the following is required so index scanning can be done
ALTER TABLE ZZ_BASE MODIFY (ID CONSTRAINT NN_B NOT NULL); 
ALTER TABLE ZZ_CHILD MODIFY (ID CONSTRAINT NN_C NOT NULL); -- given the join below not mandatory.

Теперь я хочу ВЕРНУТЬ ВНЕШНЮЮ СОЕДИНЕНИЕ этих двух таблиц и только вывести уже проиндексированное поле идентификатора.

SELECT  ZZ_BASE.ID 
FROM ZZ_BASE 
LEFT OUTER JOIN ZZ_CHILD ON (ZZ_BASE.ID = ZZ_CHILD.ID);
----------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  1000K|  9765K|       |  4894   (2)| 00:00:30 |
|*  1 |  HASH JOIN OUTER      |        |  1000K|  9765K|    16M|  4894   (2)| 00:00:30 |
|   2 |   INDEX FAST FULL SCAN| ZZ_B_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
|   3 |   INDEX FAST FULL SCAN| ZZ_C_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
----------------------------------------------------------------------------------------

Как вы видите, нет необходимости в доступе к таблице, только доступ к индексу. Но, по здравому смыслу, HASH-соединение не является самым оптимальным способом объединения этих двух индексов. Если эти две таблицы, где намного больше, нужно будет создать очень большую хэш-таблицу.

Более эффективным способом было бы СОРТИРОВАТЬ два индекса.

SELECT /*+ USE_MERGE(ZZ_BASE ZZ_CHILD) */ ZZ_BASE.ID 
FROM ZZ_BASE 
LEFT OUTER JOIN ZZ_CHILD ON (ZZ_BASE.ID = ZZ_CHILD.ID);
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |  1000K|  9765K|       |  6931   (3)| 00:00:42 |
|   1 |  MERGE JOIN OUTER      |        |  1000K|  9765K|       |  6931   (3)| 00:00:42 |
|   2 |   INDEX FULL SCAN      | ZZ_B_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
|*  3 |   SORT JOIN            |        |  1000K|  4882K|    22M|  4673   (4)| 00:00:29 |
|   4 |    INDEX FAST FULL SCAN| ZZ_C_I |  1000K|  4882K|       |   948   (3)| 00:00:06 |
-----------------------------------------------------------------------------------------

Но кажется, что второй индекс сортируется, даже если он уже есть ( "Если индекс существует, то база данных может избежать сортировки первого набора данных. Однако база данных всегда сортирует второй набор данных независимо от индексов" ¹)

В принципе, я хочу, это запрос, который использует соединение SORT-MERGE и мгновенно запускает вывод записей, то есть:

нет соединения HASH, потому что сначала нужно сделать хеш-таблицу (накладные расходы IO, если они хранятся на диске) и, таким образом, не выводится мгновенно.
нет NESTED LOOP, который, хотя и выводит мгновенно, имеет логарифмическую (N) сложность в индексе pokes и большие служебные данные ввода-вывода при несекретных чтениях индекса в случае большого индекса.

Ответ 1

INDEX_ASC (или просто INDEX) - это подсказка, которую вы можете попробовать, чтобы сравнить производительность с реальными данными.

Я немного удивлен, что вы получаете какой-либо индексный просмотр для внешнего источника строки, так как индексы B * Tree не могут найти NULL-ключи, а ZZ_BASE не имеет ограничения NOT NULL. Добавив это и немного намекая, вы получите полное сканирование в индексном порядке индекса ZZ_C_I. Это, к сожалению, не сэкономит вам шаг SORT JOIN, но по крайней мере он должен быть намного быстрее - O (n) - поскольку данные уже отсортированы.

alter table zz_base modify (id not null);
SELECT 
  /*+ leading(zz_base) USE_MERGE(ZZ_CHILD) 
      index_asc(zz_base (id)) index(zz_child (id)) */ ZZ_BASE.ID 
FROM ZZ_BASE left outer join ZZ_CHILD on zz_base.id=zz_child.id;

В этом запросе используется следующий план выполнения:

------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 |
|   1 |  MERGE JOIN OUTER |        |  1000K|  9765K|       |  8241   (3)| 00:00:50 |
|   2 |   INDEX FULL SCAN | ZZ_B_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
|*  3 |   SORT JOIN       |        |  1000K|  4882K|    22M|  5983   (3)| 00:00:36 |
|   4 |    INDEX FULL SCAN| ZZ_C_I |  1000K|  4882K|       |  2258   (2)| 00:00:14 |
------------------------------------------------------------------------------------