Oracle join elimination
Оптимизатор в Oracle может применять различные способы трансформации запросов для улучшения их производительности. Одним из таких способов является join elimination. В официальной документации Oracle Database SQL Tuning Guide об этом способе сказано достаточно мало, в отличие от других.Приглашаю читателей под кат, чтобы поговорить об этом способе поподробнее.Содержание: Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде. Он поддерживал только inner join, написанный в традиционном (не-ANSI) стиле Oracle. В версии 11.1 и 11.2 возможности join elimination были значительно расширены.В документации join elimination определяется как: Удаление лишних таблиц из запроса. Таблица считается лишней, если ее колонки используются только в условии соединения, и такое соединение гарантированно не фильтрует данные и не добавляет новые строки.На первый взгляд это может показаться странным — зачем кто-то будет писать такой бессмысленный запрос? Но такое может происходить, если мы используем генерированный запрос или обращаемся к представлениям (view).
Трансформация inner joinДавайте рассмотрим небольшой пример (скрипты выполнялись на Oracle 11.2).Для начала создадим несколько таблиц, одну родительскую и одну дочернюю (master-detail): create table parent ( id number not null, description varchar2(20) not null, constraint parent_pk primary key (id) );
insert into parent values (1, 'первый'); insert into parent values (2, 'второй'); commit;
create table child ( id number not null, parent_id number, description varchar2(20) not null );
insert into child values (1, 1, 'первый'); insert into child values (2, 1, 'второй'); insert into child values (3, 2, 'третий'); insert into child values (4, 2, 'четвертый'); commit; Теперь попробуем выполнить простой запрос и посмотрим на его план: explain plan for select c.id from child c join parent p on c.parent_id = p.id;
select * from table (dbms_xplan.display);
-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×36 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 4×36 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| CHILD | 4×24 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1×3 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------- 3 — access («C».«PARENT_ID»=«P».«ID») Несмотря на то, что мы запрашиваем колонку только из таблицы child, Oracle, тем не менее, выполняет честный inner join и впустую делает обращение к таблице parent.Получается, оптимизатор не понимает, что в этом запросе соединение этих двух таблиц не приводит к какой-либо фильтрации или размножению строк. Значит, нужно помочь ему это понять.
Свяжем эти таблицы с помощью foreign key из child на parent и посмотрим на то, как изменится план запроса:
alter table child add constraint child_parent_fk foreign key (parent_id) references parent (id);
explain plan for select c.id from child c join parent p on c.parent_id = p.id;
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×104 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CHILD | 4×104 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- 1 — filter («C».«PARENT_ID» IS NOT NULL) Как видно из плана запроса — этого оказалось достаточно.Чтобы Oracle смог удалить лишние таблицы из запроса, соединенные через inner join, нужно чтобы между ними существовала связь foreign key — primary key (или unique constraint).Трансформация outer join Для того, чтобы Oracle мог убрать лишние таблицы из запроса в случае outer join — достаточно на колонке внешней таблицы, участвующей в соединении, был первичный ключ (primary key) или ограничение уникальности (unique constraint).Добавим еще несколько родительских таблиц create table parent2 ( id number not null, description varchar2(20) not null, constraint parent2_pk primary key (id) );
insert into parent2 values (3, 'третий'); insert into parent2 values (4, 'четвертый'); commit;
create table parent3 ( id number not null, description varchar2(20) not null, constraint parent3_pk primary key (id) );
insert into parent3 values (5, 'пятый'); insert into parent3 values (6, 'шестой'); commit;
alter table child add (parent2_id number, parent3_id number); alter table child add constraint child_parent2_fk foreign key (parent2_id) references parent2(id);
merge into child c using ( select 1 id, 3 parent2_id, null parent3_id from dual union all select 2 id, 4 parent2_id, 5 from dual union all select 3 id, 3 parent2_id, 6 from dual union all select 4 id, 4 parent2_id, null from dual ) s on (c.id = s.id) when matched then update set c.parent2_id = s.parent2_id, c.parent3_id = s.parent3_id; commit; И попробуем выполнить следующий запрос: explain plan for select c.id, c.description from child c left join parent3 p on c.parent3_id = p.id;
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×100 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| CHILD | 4×100 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Как видно из плана запроса, в этом случае Oracle так же догадался, что таблица parent_3 лишняя и ее можно удалить.Число таблиц, которое может быть удалено из запроса, не ограничено. Join elimination удобно использовать, если существует дочерняя таблица, несколько родительских таблиц и результат их соединения выставлен в виде представления.
Создадим такое представление, которое объединит все наши таблицы и попробуем использовать его в запросе:
create or replace view child_parents_v as select c.id, c.parent_id, c.parent2_id, c.parent3_id, c.description, p1.description p1_desc, p2.description p2_desc, p3.description p3_desc from child c join parent p1 on c.parent_id = p1.id join parent2 p2 on c.parent2_id = p2.id left join parent3 p3 on c.parent3_id = p3.id;
explain plan for select id from child_parents_v;
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×156 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CHILD | 4×156 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- 1 — filter («C».«PARENT2_ID» IS NOT NULL AND «C».«PARENT_ID» IS NOT NULL) Как видно из плана, Oracle отлично справился и с таким запросом тоже.Трансформация semi join и anti join Для того, чтобы была возможность таких трансформаций: между таблицами должна быть связь foreign key — primary key, как и в случае inner join.Сначала рассмотрим пример semi join: explain plan for select * from child c where exists (select * from parent2 p where c.parent2_id = p.id);
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×256 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CHILD | 4×256 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- 1 — filter («C».«PARENT2_ID» IS NOT NULL) А теперь пример anti join: explain plan for select * from child c where c.parent_id not in (select p.id from parent p);
select * from table (dbms_xplan.display);
----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×308 | 5 (0)| 00:00:01 | |* 1 | HASH JOIN ANTI SNA | | 4×308 | 5 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | CHILD | 4×256 | 3 (0)| 00:00:01 | | 3 | INDEX FAST FULL SCAN| PARENT_PK | 2×26 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------- 1 — access («C».«PARENT_ID»=«P».«ID») Как видно, с такими типами запросов Oracle тоже научился работать.Трансформация self join Гораздо реже, но встречаются запросы с соединением одной и той же таблицы. К счастью, join elimination распространяется и на них, но с небольшим условием — нужно чтобы в условии соединения использовалась колонка с первичным ключом (primary key) или ограничением уникальности (unique constraint). create or replace view child_child_v as select c.id, c.description c_desc, c2.description c2_desc from child c join child c2 on c.id = c2.id;
alter table child add primary key (id); explain plan for select id, c2_desc from child_child_v;
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×100 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| CHILD | 4×100 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Такой запрос тоже с успехом трансформируется: explain plan for select c.id, c.description from child c where c.parent3_id is null and c.id in (select c2.id from child c2 where c2.id > 1);
select * from table (dbms_xplan.display);
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1×38 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| CHILD | 1×38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SYS_C0013028957×3 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- 1 — filter («PARENT3_ID» IS NULL) 2 — access («C2».«ID»>1) Rely disable и join elimination Есть еще одна интересная особенность join elimination — он продолжает работать даже в том случае, когда ограничения (foreign key и primary key) выключены (disable), но помечены как доверительные (rely).Для начала просто попробуем отключить ограничения и посмотрим на план запроса:
alter table child modify constraint child_parent_fk disable; alter table parent modify constraint parent_pk disable;
explain plan for select c.id, c.description from child c join parent p on c.parent_id = p.id;
select * from table (dbms_xplan.display);
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×204 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 4×204 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| PARENT | 2×26 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| CHILD | 4×152 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- 1 — access («C».«PARENT_ID»=«P».«ID») Вполне ожидаемо, что join elimination перестал работать. А теперь попробуем указать rely disable для обоих ограничений: alter table child modify constraint child_parent_fk rely disable; alter table parent modify constraint parent_pk rely disable;
explain plan for select c.id, c.description from child c join parent p on c.parent_id = p.id;
select * from table (dbms_xplan.display);
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×152 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CHILD | 4×152 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- 1 — filter («C».«PARENT_ID» IS NOT NULL) Как видно, join elimination заработал вновь.На самом деле, rely предназначен для немного другой трансформации запроса. В таких случаях требуется, чтобы параметр query_rewrite_integrity был установлен в «trusted» вместо стандартного «enforced», но, в нашем случае, он ни на что не влияет и все прекрасно работает и при значении «enforced».К сожалению, ограничения rely disable вызывают join elimination только с inner join. Стоит так же отметить, что несмотря на то, что мы можем указывать rely disable primary key или rely disable foreign key для представлений — работать для join elimination это, к сожалению, не будет.
Параметр _optimizer_join_elimination_enabled Вместе с таким замечательным способом трансформации запроса добавился еще и скрытый параметр _optimizer_join_elimination_enabled, который по умолчанию включен (true) и отвечает за использование этой трансформации.Если она вам надоест, то ее всегда можно выключить: alter session set »_optimizer_join_elimination_enabled» = false;
explain plan for select c.id, c.description from child c join parent p on c.parent_id = p.id;
select * from table (dbms_xplan.display);
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4×204 | 6 (0)| 00:00:01 | |* 1 | HASH JOIN | | 4×204 | 6 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| PARENT | 2×26 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| CHILD | 4×152 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- 1 — access («C».«PARENT_ID»=«P».«ID») Итог Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.Ну и, напоследок, скрипт удаления всех созданных объектов drop view child_parents_v; drop view child_child_v; drop table child; drop table parent; drop table parent2; drop table parent3;
