Oracle join elimination

в 8:01, , рубрики: join elimination, oracle, sql, Программирование

Оптимизатор в 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;

Автор: ApInvent

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js