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 |
-----------------------------------------------------------------------------------------
Но кажется, что второй индекс сортируется, даже если он уже есть ( "Если индекс существует, то база данных может избежать сортировки первого набора данных. Однако база данных всегда сортирует второй набор данных независимо от индексов" 1)
В принципе, я хочу, это запрос, который использует соединение 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 |
------------------------------------------------------------------------------------
