Oracle mechanics

07.07.2009

Использование DISTINCT vs EXISTS vs GROUP BY в подзапросах

Filed under: CBO,Oracle — Игорь Усольцев @ 00:27
Tags: ,

Интересное наблюдение за поведением Oracle CBO на примере трёх идентичных (по результату) запросов:

1
select b.comm from bonus b where b.ename in
(select distinct e.ename from emp e where e.mgr >0);
2
select b.comm from bonus b where exists
(select 1 from emp e where e.ename = b.ename and e.mgr >0);
3
select b.comm from bonus b where b.ename in
(select e.ename from emp e where e.mgr >0 group by e.ename);


У двух первых запросов планы выполнения одинаковые, а точнее в обоих случаях Oracle CBO использует механизм semi-join (операция HASH JOIN SEMI), предназначенный специально для подзапросов с конструкцией EXISTS:

«semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery«

что лишний раз подтверждает эквивалентность (в данном конкретном случае) запросов №1 (distinct) и №2 (exists) с точки зрения Oracle CBO:

SQL> explain plan for
2  select b.comm from bonus b where
3  b.ename in (select distinct e.ename from emp e where e.mgr >0);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 2159264696
-------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    30 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |       |     1 |    30 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| BONUS |     1 |    20 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP   |    13 |   130 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ENAME"="E"."ENAME")
3 - filter("E"."MGR">0)

План выполнения последнего варианта запроса (group by) отличается использованием механизмов HASH GROUP BY либо SORT GROUP BY в зависимости от статистики объектов и системы.

Важно, что при использовании group by в подзапросе CBO не использует механизм объединения запросов (unnest and merge) для поиска оптимального плана выполнения. Таким образом, с помощью изменения синтаксиса подзапроса (выбирая между distinct|exists и group by) можно однозначно определить будет ли использоваться объединение подзапросов (nested или unnested subquery) при выполнении  запроса Oracle:

SQL> explain plan for
2  select b.comm from bonus b where
3  b.ename in (select e.ename from emp e where e.mgr >0 group by e.ename);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 525582177
-------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |    20 |     6  (17)| 00:00:01 |
|*  1 |  FILTER              |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | BONUS |     1 |    20 |     2   (0)| 00:00:01 |
|*  3 |   FILTER             |       |       |       |            |          |
|   4 |    HASH GROUP BY     |       |     1 |    10 |     4  (25)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| EMP   |    13 |   130 |     3   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "EMP" "E" WHERE "E"."MGR">0
GROUP BY "E"."ENAME" HAVING "E"."ENAME"=:B1))
3 - filter("E"."ENAME"=:B1)
5 - filter("E"."MGR">0)
20 rows selected.

Кроме того, в последнем разделе плана можно увидеть замечательный фильтр:

filter( EXISTS (SELECT /*+ */ 0 FROM «EMP» «E» WHERE «E».»MGR»>0 GROUP BY «E».»ENAME» HAVING «E».»ENAME»=:B1))

, ещё раз напоминающий об эквивалентности [результатов] запросов :)

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: