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("С"."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("С"."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("С"."PARENT2_ID" IS NOT NULL AND "С"."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("С"."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("С"."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("С"."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("С"."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("С"."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;



Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.