Oracle mechanics

14.06.2014

Filter Push-Down и избыточные предикаты

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

Александр Шакура показал интересное: в простом запросе добавление очевидно избыточного условия (redundant predicate) включает partition pruning, заметно улучшая тем самым стоисмость (Cost) и скорость выполнения запроса к обзору, построенному на помесячно партиционированной таблице

SELECT
 *
  FROM (SELECT /* эта часть запроса станет view */
         TRUNC(a.DT, 'mm')                    BEG_MONTH,
         ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1 END_MONTH,
         SUM(a.SUM_RUR)                       SUM_RUR
          FROM MVIEW__DAILY_SALES a
          WHERE a.dt BETWEEN TRUNC (a.DT, 'mm') AND ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1 -- redundant predicate?
         GROUP BY TRUNC(a.DT, 'mm'), ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1)
 WHERE beg_month = DATE '2013-04-01'
   AND end_month = DATE '2013-04-30'

Без избыточного предиката запрос демонстрирует типичный PARTITION RANGE ALL как ожидаемое поведение:

11.2.0.3.@ SQL> explain plan for
  2  SELECT
  3   *
  4    FROM (SELECT /* this will be a view */
  5           TRUNC(a.DT, 'mm') BEG_MONTH,
  6           ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1 END_MONTH,
  7           SUM(a.SUM_RUR) SUM_RUR
  8            FROM MVIEW__DAILY_SALES a
  9           GROUP BY TRUNC(a.DT, 'mm'), ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1)
 10   WHERE beg_month = DATE '2013-04-01'
 11     AND end_month = DATE '2013-04-30'
 12  /

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |  3898 | 54572 |  1122K  (1)| 04:21:54 |       |       |
|   1 |  HASH GROUP BY         |                    |  3898 | 54572 |  1122K  (1)| 04:21:54 |       |       |
|   2 |   PARTITION RANGE ALL  |                    |  8899 |   121K|  1122K  (1)| 04:21:54 |     1 |1048575|
|*  3 |    MAT_VIEW ACCESS FULL| MVIEW__DAILY_SALES |  8899 |   121K|  1122K  (1)| 04:21:54 |     1 |1048575|
-------------------------------------------------------------------------------------------------------------

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

   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1=TO_DATE(' 2013-04-30
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

в полном соотвествии с документацией:

There are several cases when the optimizer cannot perform pruning. One common reasons is when an operator is used on top of a partitioning column. This could be an explicit operator (for example, a function) or even an implicit operator introduced by Oracle as part of the necessary data type conversion for executing the statement

, рекомендующей, например, использовать virtual column partitioning

Однако при добавлении «пустого» предиката:

SQL> explain plan for
  2  SELECT
  3   *
  4    FROM (SELECT /* this will be a view */
  5           TRUNC(a.DT, 'mm') BEG_MONTH,
  6           ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1 END_MONTH,
  7           SUM(a.SUM_RUR) SUM_RUR
  8            FROM MVIEW__DAILY_SALES a
  9            WHERE a.dt BETWEEN TRUNC (a.DT, 'mm') AND ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1 -- избыточное условие?
 10           GROUP BY TRUNC(a.DT, 'mm'), ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1)
 11   WHERE beg_month = DATE '2013-04-01'
 12     AND end_month = DATE '2013-04-30'
 13  /

--------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |     1 |    14 | 24852   (1)| 00:05:48 |       |       |
|   1 |  HASH GROUP BY          |                    |     1 |    14 | 24852   (1)| 00:05:48 |       |       |
|   2 |   PARTITION RANGE SINGLE|                    |     1 |    14 | 24851   (1)| 00:05:48 |   101 |   101 | -- прочие партиции не используются
|*  3 |    MAT_VIEW ACCESS FULL | MVIEW__DAILY_SALES |     1 |    14 | 24851   (1)| 00:05:48 |   101 |   101 |
--------------------------------------------------------------------------------------------------------------

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

   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "A"."DT">=TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm') AND
              TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm')<=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1=TO_DATE(' 2013-04-30 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "A"."DT"<=ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1 AND
              ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1>=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "A"."DT">=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND -- *
              "A"."DT"<=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))    -- **

— заметно умножаются условия FILTER-ации и, в частности, благодаря появлению двух последних условий (*,**) становится возможным использовать Partition Pruning

В свою очередь появление этих условий логично обусловлено преобразованием Filter Push-Down (FPD) вместе с другими, менее полезными условиями:

CVM: CBQT Marking query block SEL$2 (#0) as valid for CVM.
CVM:   Merging complex view SEL$2 (#0) into SEL$1 (#0).
...
CVM: result SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT TRUNC("A"."DT", 'mm') "BEG_MONTH",                                                                                     -- запрос после Complex View Merging ***
       ADD_MONTHS(TRUNC("A"."DT", 'mm'), 1) - 1 "END_MONTH",
       SUM("A"."SUM_RUR") "SUM_RUR"
  FROM "MVIEW__DAILY_SALES" "A"
 WHERE TRUNC("A"."DT", 'mm') =
       TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
   AND ADD_MONTHS(TRUNC("A"."DT", 'mm'), 1) - 1 =
       TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
   AND "A"."DT" >= TRUNC("A"."DT", 'mm')
   AND "A"."DT" <= ADD_MONTHS(TRUNC("A"."DT", 'mm'), 1) - 1
 GROUP BY TRUNC("A"."DT", 'mm'), ADD_MONTHS(TRUNC("A"."DT", 'mm'), 1) - 1
Registered qb: SEL$F5BB74E1 0x8195bd68 (VIEW MERGE SEL$1; SEL$2)
...
FPD:   transitive predicates are generated in query block SEL$F5BB74E1 (#1)
TRUNC("A"."DT",'fmmm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
ADD_MONTHS(TRUNC("A"."DT",'fmmm'),1)-1=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."DT">=TRUNC("A"."DT",'fmmm') AND "A"."DT"<=ADD_MONTHS(TRUNC("A"."DT"
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT TRUNC("A"."DT", 'fmmm') "BEG_MONTH",
       ADD_MONTHS(TRUNC("A"."DT", 'fmmm'), 1) - 1 "END_MONTH",
       SUM("A"."SUM_RUR") "SUM_RUR"
  FROM "MVIEW__DAILY_SALES" "A"
 WHERE TRUNC("A"."DT", 'fmmm') = TO_DATE('2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                                     -- условия, появившиеся в запросе
   AND ADD_MONTHS(TRUNC("A"."DT", 'fmmm'), 1) - 1 = TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                 -- после Complex View Merging
   AND "A"."DT" >= TRUNC("A"."DT", 'fmmm')                                                                                    -- в комбинации с избыточными
   AND "A"."DT" <= ADD_MONTHS(TRUNC("A"."DT", 'fmmm'), 1) - 1                                                                 -- предикатами
   AND "A"."DT" >= TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                                                  -- в результате FPD генерируют как используемые
   AND "A"."DT" <= TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                                                  -- для Partition Pruning условия,
   AND TRUNC("A"."DT", 'fmmm') <= TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                                   -- так и логически непротиворечивые,
   AND ADD_MONTHS(TRUNC("A"."DT", 'fmmm'), 1) - 1 >= TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')                -- но неиспользуемые
   AND TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') <= TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') -- предикаты
 GROUP BY TRUNC("A"."DT", 'fmmm'),
          ADD_MONTHS(TRUNC("A"."DT", 'fmmm'), 1) - 1

Тест:

SQL> explain plan for
  2  SELECT --+ OPT_PARAM('_optimizer_filter_pushdown' 'false')                                 -- после отключения трансформации Filter Push-Down
  3   *
  4    FROM (SELECT /* this will be a view */
  5           TRUNC(a.DT, 'mm') BEG_MONTH,
  6           ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1 END_MONTH,
  7           SUM(a.SUM_RUR) SUM_RUR
  8            FROM MVIEW__DAILY_SALES a
  9            WHERE a.dt BETWEEN TRUNC (a.DT, 'mm') AND ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1 -- условие становится действительно избыточным
 10           GROUP BY TRUNC(a.DT, 'mm'), ADD_MONTHS(TRUNC(a.DT, 'mm'), 1) - 1)
 11   WHERE beg_month = DATE '2013-04-01'
 12     AND end_month = DATE '2013-04-30'
 13  /

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |    22 |   308 |  1122K  (1)| 04:21:54 |       |       |
|   1 |  HASH GROUP BY         |                    |    22 |   308 |  1122K  (1)| 04:21:54 |       |       |
|   2 |   PARTITION RANGE ALL  |                    |    22 |   308 |  1122K  (1)| 04:21:54 |     1 |1048575| -- Partition Pruning не используется, сканинуем все партиции
|*  3 |    MAT_VIEW ACCESS FULL| MVIEW__DAILY_SALES |    22 |   308 |  1122K  (1)| 04:21:54 |     1 |1048575|
-------------------------------------------------------------------------------------------------------------

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

   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd          -- фильтр возвращается к исходному 
              hh24:mi:ss') AND "A"."DT">=TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm') AND
              ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1=TO_DATE(' 2013-04-30 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "A"."DT"<=ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1)

***) Стоимостное преобразование (CBQT) вида Complex View Merging для выполнения операции Inline View Merge при этом необязателен, например:

SQL> explain plan for
  2  SELECT--+ NO_MERGE(V)                                             -- при отключении CVM подсказкой
  3   *
  4    FROM (  SELECT /* this will be a view */
  5                  TRUNC (a.DT, 'mm') BEG_MONTH,
  6                   ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1 END_MONTH,
  7                   SUM (a.SUM_RUR) SUM_RUR
  8              FROM MVIEW__DAILY_SALES a
  9              WHERE a.dt BETWEEN TRUNC (a.DT, 'mm') AND ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1
 10          GROUP BY TRUNC (a.DT, 'mm'), ADD_MONTHS (TRUNC (a.DT, 'mm'), 1) - 1
 11         ) V
 12   WHERE beg_month = DATE '2013-04-01' AND end_month = DATE '2013-04-30'
 13  /

SQL> select * from table(dbms_xplan.display(format => '+alias'));

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |     1 |    25 | 24852   (1)| 00:05:48 |       |       |
|   1 |  VIEW                    |                    |     1 |    25 | 24852   (1)| 00:05:48 |       |       | -- View Merging не работает,
|   2 |   HASH GROUP BY          |                    |     1 |    14 | 24852   (1)| 00:05:48 |       |       |
|   3 |    PARTITION RANGE SINGLE|                    |     1 |    14 | 24851   (1)| 00:05:48 |   101 |   101 | -- в отличие от Partition Pruning, кот.на месте
|*  4 |     MAT_VIEW ACCESS FULL | MVIEW__DAILY_SALES |     1 |    14 | 24851   (1)| 00:05:48 |   101 |   101 |
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / V@SEL$1
   2 - SEL$2
   4 - SEL$2 / A@SEL$2

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

   4 - filter(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "A"."DT">=TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm') AND
              ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "A"."DT"<=ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1 AND
              ADD_MONTHS(TRUNC(INTERNAL_FUNCTION("A"."DT"),'fmmm'),1)-1>=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "A"."DT">=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND                           -- благодаря этому замечательному фильтру
              "A"."DT"<=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

— FPD выполняет Simple Filter Push через создание transitive predicate для получения того же замечательно ускоряющего фильтра:

FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD:  Current where clause predicates "V"."BEG_MONTH"=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "V"."END_MONTH"=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "V"."BEG_MONTH"=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "V"."END_MONTH"=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

FPD:   Following are pushed to where clause of query block SEL$2 (#0)
TRUNC("A"."DT",'mm')=TO_DATE(' 2013-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ADD_MONTHS(TRUNC("A"."DT",'mm'),1)-1=TO_DATE(' 2013-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

7 комментариев »

  1. Хороший и простой пример, Игорь! Кстати, в тему будет эта тема: http://www.freelists.org/post/oracle-l/Functionbased-indexes-and-trunc

    комментарий от Sayan Malakshinov — 14.06.2014 @ 13:33 | Ответить

    • Спасибо за ссылку, Саян!

      Однако в моём примере извне стимулируется (добавлением избыточных предикатов в обзор) использование Oracle преобразования Filter Push-Down, как следствие — становится доступен partition pruning, получается быстро и хорошо. Без такой искусственной стимуляции — грустный PARTITION RANGE ALL, т.е. чтобы сработало требуется некая активность со стороны обслуживающего персонала :)

      По твоей ссылке Oracle самостоятельно, как показал Михаил Великих, с помощью доступного с 11.2.0.2 багфикса 9263333 generate transitive predicates for virtual генерирует дополнительные (transitive) предикаты для возможности использования FBI в запросе с простыми (в смысле нефункциональными) условиями, FPD не используется. Фича работает сама и активностей не требует ;)

      комментарий от Игорь Усольцев — 14.06.2014 @ 19:40 | Ответить

      • Игорь, я понял пример :) я к тому, что partition pruning сработал как раз из-за появившегося условия по trunc к дате, ограничивая диапазон. условия секцинирования то по dt

        комментарий от Sayan Malakshinov — 14.06.2014 @ 20:40 | Ответить

      • То есть нужно не забывать, что оракл может делать транзитивные преобразования и транком, да и использовать другие функции для преобразований, типа substr

        комментарий от Sayan Malakshinov — 14.06.2014 @ 20:44 | Ответить

        • ни разу не сомневался, что ты всё прекрасно понял, сорри, Саян :)
          ответил только для освещения разных реализаций транзитных предикатов, штука хорошая, agreed

          комментарий от Игорь Усольцев — 15.06.2014 @ 21:15 | Ответить

  2. У нас такой кейс тоже был. Таблица SUBS_SERV_HISTORY (Игорь, ты должен помнить:))) секционирована помесячно (ETIME), тем не менее запрос WHERE SH.SUBS_ID=:B1 AND SH.SERV_ID=:B2 AND STIME:B3 вызывал PARTITION RANGE ALL. В предикатах — тот же INTERNAL FUNCTION. Оказалось, запрос дергается не как обычно из PL/SQL, а из внешнего Java приложения — кодировщика профилей. А там тип подставляемой переменной объявлен не как DATE а как TIMESTAMP.

    комментарий от jimroll — 17.06.2014 @ 19:16 | Ответить

    • как можно забыть IN@Voice?)))

      комментарий от Игорь Усольцев — 18.06.2014 @ 00:05 | Ответить


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