Oracle mechanics

27.11.2008

Predicate pushdown for outer-joined views with GROUP BY

Filed under: CBO,Oracle new features — Игорь Усольцев @ 19:41
Tags: ,

Что стоит учитывать при использовании в запросах типа

select xo.object_id, xo_view.result_count
from xxx_objects xo
left join (select object_id, count(*) as result_count
           from xxx_objects group by object_id) xo_view
on xo_view.object_id = xo.object_id
where xo.object_name = 'XXX_OBJECTS'

использующих outer join с обзорами (view), содержащими group by.
Или  об ограничениях механизма join predicate push-down (JPPD) в версиях Oracle, предшествующих Oracle11g.
В Oracle whitepater Query Optimization in Oracle Database10g Release 2 объясняется механизм join predicate push-down (JPPD):

"Typically, when a query contains a view that is being joined to other tables,
the views can be merged in order to better optimize the query.
However, if a view is being joined using an outer join, then the view cannot
be merged. In this case, Oracle has specific predicate pushdown operations
which will allow the join predicate to pushed into the view;
this transformation allows for the possibility of executing the outer join
using an index on one of the tables within the view. This transformation is
cost-based because the index access may not be the most effective."

Коротко: если обзор используется во внешнем соединении (outer join), он не может быть объединён (merged) с другими таблицами запроса и в этом случае Oracle использует специальную операцию predicate pushdown, которая позволяет переместить условия запроса в тело обзора, что позволяет использовать индексы по таблицам обзора, если позволяет стоимостной подход.

Однако для операции predicate pushdown в Oracle 10g присутствовуют ограничения (правила). Рассмотрим пример:

SQL> create table xxx_objects as select * from all_objects;
SQL> create unique index ux_objects on xxx_objects(object_id);
SQL> create  index onx_objects on xxx_objects(object_name);
SQL> begin dbms_stats.gather_table_stats(ownname => user,
tabname => 'XXX_OBJECTS', cascade => true); end;
SQL> set autotrace traceonly exp
SQL> select xo.object_id, xo_view.result_count
  2  from xxx_objects xo
  3  left join (select object_id, count(*) as result_count
  4  from xxx_objects group by object_id) xo_view
  5  on xo_view.object_id = xo.object_id
  6  where xo.object_name = 'XXX_OBJECTS';

Execution Plan
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     2 |   112 |       |   109   (3)| 00:00:02 |
|*  1 |  HASH JOIN OUTER             |             |     2 |   112 |       |   109   (3)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| XXX_OBJECTS |     2 |    60 |       |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ONX_OBJECTS |     2 |       |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       |             | 49391 |  1254K|       |   106   (2)| 00:00:02 |
|   5 |    HASH GROUP BY             |             | 49391 |   241K|  1176K|   106   (2)| 00:00:02 |
|   6 |     INDEX FULL SCAN          | UX_OBJECTS  | 49391 |   241K|       |   106   (2)| 00:00:02 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("XO_VIEW"."OBJECT_ID"(+)="XO"."OBJECT_ID")
   3 - access("XO"."OBJECT_NAME"='XXX_OBJECTS')

Oracle 10g (до 10.2.0.4 включительно) упорно выбирает в качестве операции объединения таблицы XXX_OBJECTS с обзором XO_VIEW либо HASH JOIN OUTER -> VIEW, как показано на рисунке, либо NESTED LOOPS OUTER -> VIEW, т.е. механизм predicate pushdown не срабатывает, несмотря на явный выигрыш по стоимости оптимизатора и времени выполнения. Хинты типа /*+ PUSH_PRED(xo_view)*/ не помогают.

Объяснение можно найти в трейсе файле 10053 нашего запроса:

JPPD:   Checking validity of push-down from SEL$05EDCC0D (#1) to SEL$3 (#3)
JPPD:     JPPD bypassed: View contains a group by.

Механизм join predicate push-down (JPPD) не используется из-за того, что наш обзор содержит group by.

В версии Oracle 11.1.0.6 проблема решена, при аналогичных условиях оптимизатор вполне разумно использует операцию NESTED LOOPS OUTER -> VIEW PUSHED PREDICATE, которая оказывается действительно «дешевле» (хотя, понятно, что некорректно сравнивать стоимости оптимизатора Oracle разных версий):

Execution Plan
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     2 |    96 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |             |     2 |    96 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| XXX_OBJECTS |     2 |    70 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ONX_OBJECTS |     2 |       |     3   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      |             |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    FILTER                    |             |       |       |            |          |
|   6 |     SORT AGGREGATE           |             |     1 |     5 |            |          |
|*  7 |      INDEX UNIQUE SCAN       | UX_OBJECTS  |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("XO"."OBJECT_NAME"='XXX_OBJECTS')
   5 - filter(COUNT(*)>0)
   7 - access("OBJECT_ID"="XO"."OBJECT_ID")

В трейсе 10053 уже нет указаний на ограничения group by:

***********************************
Cost-Based Join Predicate Push-down
***********************************

JPPD:     Passed validity checks
JPPD: JPPD:   Pushdown from query block SEL$05EDCC0D (#1) passed validity checks.
Join-Predicate push-down on query block SEL$05EDCC0D (#1)

JPPD: Performing join predicate push-down (no transformation phase) from query block SEL$05EDCC0D (#1) to query block SEL$3 (#3)

Для оптимизации запрос можно целенаправленно видоизменить для использования индексного доступа:

SQL> select xo.object_id,
  2  (select count(*) from xxx_objects xo1
  3   where xo1.object_id = xo.object_id
  4   group by object_id)  as result_count
  5  from xxx_objects xo
  6  where xo.object_name = 'XXX_OBJECTS';

Execution Plan
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |    60 |     2   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT       |             |     1 |     5 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | UX_OBJECTS  |     1 |     5 |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| XXX_OBJECTS |     2 |    60 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN          | ONX_OBJECTS |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("XO1"."OBJECT_ID"=:B1)
   4 - access("XO"."OBJECT_NAME"='XXX_OBJECTS')

1 комментарий »

  1. В действительности механизм join predicate push-down (JPPD) не работает не только для OUTER JOIN

    Dion Cho http://dioncho.wordpress.com/2009/04/12/join-predicate-pushing-and-group-by/ на примере запроса с обычным INNER JOIN

    select t1.c1, t1.c2, v.*
    from t1, (select c1, sum(c2), sum(c3) from t2 group by c1) v
    where t1.c1 = v.c1(+) and t1.c2 in (1, 2)

    констатирует ту же ситуацию для запросов с GROUP BY, DISTINCT или ANTI/SEMI join:

    «Oracle не допускает push join predicate в случаях, когда обзор содержит GROUP BY, DISTINCT expression or ANTI/SEMI join, ввиду значительного усложнения использования механизма в этих случаях.

    К счастью, это ограничение снимается в 11g. В котором использование механизма расширяется — extended JPPD и контролируется параметром _optimizer_extend_jppd_view_types (default is TRUE)»

    комментарий от iusoltsev — 14.10.2009 @ 18:28 | Ответить


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 такие блоггеры, как: