Оптимизатор в Oracle может применять различные способы трансформации запросов для улучшения их производительности. Одним из таких способов является join elimination. В официальной документации Oracle Database SQL Tuning Guide об этом способе сказано достаточно мало, в отличие от других.
Приглашаю читателей под кат, чтобы поговорить об этом способе поподробнее.
Содержание:
Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде — он поддерживал только inner join. В версии 11.1 и 11.2 возможности join elimination были значительно расширены.
В документации join elimination определяется как: Удаление лишних таблиц из запроса. Таблица считается лишней, если ее колонки используются только в условии соединения, и такое соединение гарантированно не фильтрует данные и не добавляет новые строки.
На первый взгляд это может показаться странным — зачем кто-то будет писать такой бессмысленный запрос? Но такое может происходить, если мы используем генерированный запрос или обращаемся к представлениям (view).
Давайте рассмотрим небольшой пример (скрипты выполнялись на Oracle 11.2).
Теперь попробуем выполнить простой запрос и посмотрим на его план:
Несмотря на то, что мы запрашиваем колонку только из таблицы child, Oracle, тем не менее, выполняет честный inner join и впустую делает обращение к таблице parent.
Получается, оптимизатор не понимает, что в этом запросе соединение этих двух таблиц не приводит к какой-либо фильтрации или размножению строк. Значит, нужно помочь ему это понять.
Свяжем эти таблицы с помощью foreign key из child на parent и посмотрим на то, как изменится план запроса:
Как видно из плана запроса — этого оказалось достаточно.
Чтобы Oracle смог удалить лишние таблицы из запроса, соединенные через inner join, нужно чтобы между ними существовала связь foreign key — primary key (или unique constraint).
Для того, чтобы Oracle мог убрать лишние таблицы из запроса в случае outer join — достаточно на колонке внешней таблицы, участвующей в соединении, был первичный ключ (primary key) или ограничение уникальности (unique constraint).
И попробуем выполнить следующий запрос:
Как видно из плана запроса, в этом случае Oracle так же догадался, что таблица parent_3 лишняя и ее можно удалить.
Число таблиц, которое может быть удалено из запроса, не ограничено. Join elimination удобно использовать, если существует дочерняя таблица, несколько родительских таблиц и результат их соединения выставлен в виде представления.
Создадим такое представление, которое объединит все наши таблицы и попробуем использовать его в запросе:
Как видно из плана, Oracle отлично справился и с таким запросом тоже.
Для того, чтобы была возможность таких трансформаций: между таблицами должна быть связь foreign key — primary key, как и в случае inner join.
Сначала рассмотрим пример semi join:
А теперь пример anti join:
Как видно, с такими типами запросов Oracle тоже научился работать.
Гораздо реже, но встречаются запросы с соединением одной и той же таблицы. К счастью, join elimination распространяется и на них, но с небольшим условием — нужно чтобы в условии соединения использовалась колонка с первичным ключом (primary key) или ограничением уникальности (unique constraint).
Такой запрос тоже с успехом трансформируется:
Есть еще одна интересная особенность join elimination — он продолжает работать даже в том случае, когда ограничения (foreign key и primary key) выключены (disable), но помечены как доверительные (rely).
Для начала просто попробуем отключить ограничения и посмотрим на план запроса:
Вполне ожидаемо, что join elimination перестал работать. А теперь попробуем указать rely disable для обоих ограничений:
Как видно, 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, который по умолчанию включен (true) и отвечает за использование этой трансформации.
Если она вам надоест, то ее всегда можно выключить:
Добавлено после комментария xtender.
Так же, чтобы контролировать эту трансформацию, можно применять подсказки оптимизатора.
Для того, чтобы включить трансформацию, используют подсказку ELIMINATE_JOIN:
Для того, чтобы выключить трансформацию, используют подсказку NO_ELIMINATE_JOIN:
В комментариях ниже xtender дал ссылку на свой интересный пример, в котором показывается, что join elimination может ухудшать план выполнения запроса. А так же дал некоторые пояснения в дальнейших комментариях.
Есть еще один вариант трансформации — удаление одинаковых соединений из запроса:
Эта трансформация так же отлично работает и с подзапросами, которые превращаются в соединения (subquery unnesting):
Но, такой вариант трансформации имеет некоторые отличия.
1) Для него необязательно иметь связь foreign key — primary key (или unique constraint):
2) На него не влияет отключение параметра _optimizer_join_elimination_enabled:
Но хотя бы действуют подсказки:
Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.
Приглашаю читателей под кат, чтобы поговорить об этом способе поподробнее.
Содержание:
- Трансформация inner join
- Трансформация outer join
- Трансформация semi join и anti join
- Трансформация self join
- Rely disable и join elimination
- Параметр _optimizer_join_elimination_enabled
- Подсказки ELIMINATE_JOIN и NO_ELIMINATE_JOIN
- Когда join elimination плохо
- Трансформация одинаковых соединений
- Итог
Этот способ трансформации запроса впервые появился в Oracle 10.2, но в достаточно ограниченном виде — он поддерживал только inner join. В версии 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")
Подсказки ELIMINATE_JOIN и NO_ELIMINATE_JOIN
Добавлено после комментария xtender.
Так же, чтобы контролировать эту трансформацию, можно применять подсказки оптимизатора.
Для того, чтобы включить трансформацию, используют подсказку ELIMINATE_JOIN:
alter session set "_optimizer_join_elimination_enabled" = false;
explain plan for
select /*+ ELIMINATE_JOIN(p) */ 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 | 84 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CHILD | 4 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
1 - filter("C"."PARENT_ID" IS NOT NULL)
Для того, чтобы выключить трансформацию, используют подсказку NO_ELIMINATE_JOIN:
alter session set "_optimizer_join_elimination_enabled" = true;
explain plan for
select /*+ NO_ELIMINATE_JOIN(p) */ 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 | 96 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 96 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CHILD | 4 | 84 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1 | 3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
3 - access("C"."PARENT_ID"="P"."ID")
Когда join elimination плохло
В комментариях ниже xtender дал ссылку на свой интересный пример, в котором показывается, что join elimination может ухудшать план выполнения запроса. А так же дал некоторые пояснения в дальнейших комментариях.
Трансформация одинаковых соединений
Есть еще один вариант трансформации — удаление одинаковых соединений из запроса:
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
Эта трансформация так же отлично работает и с подзапросами, которые превращаются в соединения (subquery unnesting):
select c.id
from child c
where
parent_id in (select /*+ qb_name(query_1) */ id from parent where description = 'первый') and
parent_id in (select /*+ qb_name(query_2) */id from parent where description = 'первый') and
parent_id in (select /*+ qb_name(query_3) */id from parent where description = 'первый')
/
select * from table(dbms_xplan.display_cursor(null, null, 'outline'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("PARENT_ID"="ID")
2 - filter("DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_3")
ELIMINATE_JOIN(@"SEL$45781D08" "PARENT"@"QUERY_2")
...
UNNEST(@"QUERY_3")
UNNEST(@"QUERY_2")
UNNEST(@"QUERY_1")
...
Но, такой вариант трансформации имеет некоторые отличия.
1) Для него необязательно иметь связь foreign key — primary key (или unique constraint):
alter table child drop constraint child_parent_fk
/
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'LAST OUTLINE'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
2) На него не влияет отключение параметра _optimizer_join_elimination_enabled:
alter session set "_optimizer_join_elimination_enabled" = false
/
select c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'))
/
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| CHILD | 4 | 104 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
1 - access("P3"."ID"="C"."PARENT_ID")
2 - filter("P3"."DESCRIPTION"='первый')
Outline Data
-------------
...
ELIMINATE_JOIN(@"SEL$EE94F965" "P"@"SEL$1")
ELIMINATE_JOIN(@"SEL$EE94F965" "P2"@"SEL$2")
...
Но хотя бы действуют подсказки:
select /*+ no_eliminate_join(p) no_eliminate_join(p2) no_eliminate_join(p3) */ c.id
from child c
join parent p on p.id = c.parent_id
join parent p2 on p2.id = c.parent_id
join parent p3 on p3.id = c.parent_id
where
p.description = 'первый' and
p2.description = 'первый' and
p3.description = 'первый'
/
select * from table(dbms_xplan.display_cursor())
/
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | NESTED LOOPS | | 1 | 101 | 8 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 101 | 8 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 76 | 8 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 2 | 102 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | PARENT | 1 | 25 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | CHILD | 4 | 104 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PARENT | 1 | 25 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PARENT_PK | 1 | | 0 (0)| |
|* 9 | INDEX UNIQUE SCAN | PARENT_PK | 1 | | 0 (0)| |
|* 10 | TABLE ACCESS BY INDEX ROWID | PARENT | 1 | 25 | 0 (0)| |
--------------------------------------------------------------------------------------------
4 - access("P"."ID"="C"."PARENT_ID")
5 - filter("P"."DESCRIPTION"='первый')
7 - filter("P2"."DESCRIPTION"='первый')
8 - access("P2"."ID"="C"."PARENT_ID")
9 - access("P3"."ID"="C"."PARENT_ID")
10 - filter("P3"."DESCRIPTION"='первый')
Итог
Подводя краткий итог, хочется сказать, что такой способ трансформации может быть действительно полезен в ряде случаев. Но полагаться на него надо тоже с умом. Если внутри вашего представления что-то поменяется и Oracle больше не сможет гарантированно определять то, что связь с таким представлением не фильтрует или не умножает строки, вы получите неожиданную потерю скорости выполнения запроса.
Ну и, напоследок, скрипт удаления всех созданных объектов
drop view child_parents_v;
drop view child_child_v;
drop table child;
drop table parent;
drop table parent2;
drop table parent3;
