MERGE JOIN по двум индексам все еще вызывает сортировку?

Question

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

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, потому что сначала нужно создать хеш-таблицу (накладные расходы ввода-вывода, если они хранятся на диске) и, следовательно, не выводится мгновенно.
• нет соединения NESTED LOOP, которое, хотя оно и выводило бы мгновенно, имеет сложность log(N) при работе с индексами и большие издержки ввода-вывода при непоследовательных операциях чтения индекса в случае большого индекса.

Answer 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 |
------------------------------------------------------------------------------------