Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 PUSH_PRED и группировка  [new]
HOME_X
Member

Откуда:
Сообщений: 2544
Доброго дня господа !

Подскажите как за -PUSH_PRED-иться c группировкой
(и возможно ли это вообще)

Запрос

 
                         select /*+ 
                                    No_Merge(B) 
                                    PUSH_PRED(B)
                                */
                                A.CEEIB_S_UA_DEAL
                           from T_BW_ATL_DEALS A   
                           left join (select /*+
                                                 Index(SYS_C0055843)
                                             */
                                             CTX_AC,
                                             DATEFROM ACTIVE,
                                             CTX_CRPR
--                                             Min(DATEFROM)    ACTIVE,
--                                             Min(CTX_CRPR) keep(Dense_rank first order by DATEFROM) CTX_CRPR
                                        from ADBUS.V_CFA_CTX_CONTRACT
                                       where S_STATUS='CTX00'
--                                       group by CTX_AC
                                     ) B
                             on (    B.CTX_AC=A.CEEIB_S_UA_DEAL
                                )

Часть продуктивного плана
---------------------------------------------------------------------------------------------------------------------------------------------------
|* 75 |          TABLE ACCESS FULL                         | T_BW_ATL_DEALS               |     1 |    42 |    23   (0)| 00:00:01 |     4 |     4 |
|  77 |        VIEW PUSHED PREDICATE                       |                              |     2 |    60 |    12   (0)| 00:00:01 |       |       |
|* 78 |         FILTER                                     |                              |       |       |            |          |       |       |
|* 79 |          TABLE ACCESS BY INDEX ROWID BATCHED       | T_CFA_CTX_CONTRACT           |     2 |    72 |    13   (0)| 00:00:01 |       |       |
|* 80 |           INDEX RANGE SCAN                         | SYS_C0055843                 |    10 |       |     3   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------------


Делаю группировку
Запрос
                         select /*+ 
                                    No_Merge(B) 
                                    PUSH_PRED(B)
                                */
                                A.CEEIB_S_UA_DEAL
                           from T_BW_ATL_DEALS A   
                           left join (select /*+
                                                 Index(SYS_C0055843)
                                             */
                                             CTX_AC,
--                                             DATEFROM ACTIVE,
--                                             CTX_CRPR
                                             Min(DATEFROM)    ACTIVE,
                                             Min(CTX_CRPR) keep(Dense_rank first order by DATEFROM) CTX_CRPR
                                        from ADBUS.V_CFA_CTX_CONTRACT
                                       where S_STATUS='CTX00'
                                       group by CTX_AC
                                     ) B
                             on (    B.CTX_AC=A.CEEIB_S_UA_DEAL
                                )
Часть продуктивного плана
---------------------------------------------------------------------------------------------------------------------------------------------------
|  75 |          VIEW                                     |                              |   164K|  4985K|       |  6932   (1)| 00:00:01 |       |       |
|* 76 |           FILTER                                  |                              |       |       |       |            |          |       |       |
|  77 |            SORT GROUP BY                          |                              |   164K|  5789K|    23M|  6932   (1)| 00:00:01 |       |       |
|* 78 |             TABLE ACCESS FULL                     | T_CFA_CTX_CONTRACT           |   516K|    17M|       |  4841   (1)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------------


Возможно ли так использовать передачу параметров во внутрь предсталения

Заранее благодарен !
29 мар 18, 17:18    [21297137]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
HOME_X,

Да.
29 мар 18, 17:22    [21297150]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
HOME_X
Member

Откуда:
Сообщений: 2544
dbms_photoshop,

А где собака порылась ?
Почему при группировке отрабатывает FULL ACCESS
29 мар 18, 21:23    [21297616]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
HOME_X,

21297292
Читаем вдумчиво, каждое предложение.

Если понимание не приходит, сделай трассу 10053 и глянь в ней "Final query after transformations".
29 мар 18, 21:56    [21297665]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
Elic
Member

Откуда:
Сообщений: 29987
HOME_X
 Index(SYS_C0055843)
Говнограбли.
30 мар 18, 07:26    [21298031]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
HOME_X
Member

Откуда:
Сообщений: 2544
dbms_photoshop,

Inline view b не существует после трансформаций запросов (в частности view merging) во втором случае.
Соотвественно хинт push_pred(b) более невалидный, ибо inline view b как таковой больше нет.

Инкапсулируя логику в представления и присыпая их хинтами крайне наивно пологать,
что хинты типа push_pred останутся валидными, при включении представления в другой запрос.
Не говоря уже про то, что конвертация ANSI синтаксиса в native может приводить созданию дополнительных inline view,
тоже делая хинты невалидными


Здесь идет речь о трансформации кода и ПЕРЕМЕШИВАНИИ с внешними запросами
у меня код в который передаеться внешние параметры без изменений в двух вариантах (см. ниже)
|*  5 |    FILTER                               |                    |       |       |            |          |
|   6 |     SORT GROUP BY                       |                    |     1 |    36 |    14   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_CTX_CONTRACT |     3 |   108 |    14   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | SYS_C0055843       |    11 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Изменение и слияние условий пытался удержать и стабилизировать hint-om No_Merge

По сути получаеться
При подключении RCC_LISA=(INLINE VIEW c Materialize !!!!????) VIEW PUSHED PREDICATE не просматриваеться - план 1
При подключении RCC_LISB=(физическая таблица в базе ) VIEW PUSHED PREDICATE просматриваеться - план 2
Hinta-ми заставить не получаеться, т.е. все пропало ШЕФ, все пропало!!!! (вариантов решения нет ?)


Обратите внимание на тип связи (ПОЯСНИТЕ пожалуйста)
1 план HASH JOIN OUTER
2 план NESTED LOOPS OUTER


with PARAMETER as (select /*+ Materialize */
                                To_Date('01-02-2018','DD-MM-YYYY') DBEG,
                                To_Date('28-02-2018','DD-MM-YYYY') DEND,
                                1 TYPE,
                                2 SHOW,
                                Null LIST,
                                USER,
                                Mdsys.SdorIdTab('CT0040','CT0041','CT0042','CT0043','CT0047','CT0049','CT0052','CT0054','CT2011','CT2013','CT2031','CT2033','CT2083','CT2111','CT2113','CT2131','CT2133','CT2161','CT2163','CT2181','CT2183') ATM,
                                Mdsys.SdorIdTab('CT0044','CT0045','CT0048','CT0053','CT0065','CT0066','CT2021','CT2071','CT2073','CT2121','CT2123','CT2173')                                                                                  POS,
                                Mdsys.SdorIdTab('CT0057','CT0058','CT8016','CT8017')                                                                                                                                                          RKO
                           from DUAL
                        ),
     PARAMETER_LIST(LIST,KIND,DBEG,DEND,TYPE,SHOW,KEYS) as
                        (select /*+ Materialize */
                                LIST,  KIND,  DBEG,  DEND,  TYPE, SHOW,
                                Max(KEYS) over(partition by KIND) KEYS
                           from (
                                 select Nvl(RegExp_Substr(LIST,'[^,]+',1,LEVEL),'DATE')                LIST,
                                        Case when LEVEL=Nvl(RegExp_Count(LIST,','),-1)+2          then 'RCC'
                                             when LEVEL=Nvl(RegExp_Count(LIST,','),-1)+3          then 'POS'
                                             when RegExp_Substr(LIST,'[^,]+',1,LEVEL) like 'CTX%' then 'RCC'
                                                                                                  else 'POS'
                                        end KIND,
                                        DBEG,
                                        DEND,
                                        TYPE,
                                        SHOW,
                                        Nvl2(RegExp_Substr(LIST,'[^,]+',1,LEVEL),1,0)                  KEYS
                                   from PARAMETER
                                connect by LEVEL<=Nvl(RegExp_Count(LIST,','),-1)+3
                                  union all
                                 select /*+ Opt_Estimate(Table A Rows=1) */
                                        A.*,'ATM',Cast(Null as Date) DBEG,Cast(Null as Date) DEND,0,0,0
                                   from table(select ATM from PARAMETER) A
                                  union all
                                 select /*+ Opt_Estimate(Table A Rows=1) */
                                        A.*,'POS',Cast(Null as Date) DBEG,Cast(Null as Date) DEND,0,0,0
                                   from table(select POS from PARAMETER) A
                                  union all
                                 select /*+ Opt_Estimate(Table A Rows=1) */
                                        A.*,'RKO',Cast(Null as Date) DBEG,Cast(Null as Date) DEND,0,0,0
                                   from table(select RKO from PARAMETER) A
                                )
                        ),
           RCC_LISA as (
                         select /*+
                                    Materialize
                                */
                                *
                           from (
                                 select A.CEEIB_S_UA_DEAL,
                                        Max(A.TB_TRNTYPE) TB_TRNTYPE,
                                        Sum(Case when F.KIND in('ATM','POS') then A.DEB_CRE_LC
                                                 else 0
                                            end
                                           )                                 TOTAL_PRICE,
                                        Sum(Case when A.TB_CHANNEL='CTX3'    then A.DEB_CRE_LC
                                                 else 0
                                                 end
                                           )                                 OPENING_RATE,
                                        Sum(Case when A.TB_CHANNEL='CTX2'    then A.DEB_CRE_LC
                                                 else 0
                                            end
                                           )                                 COMIS_RKO,
                                        Sum(Case when F.KIND='POS'           then 2*A.DEB_CRE_LC
                                                 else 0
                                            end
                                           )                                 GRACE,
                                        Max(S.DBEG)                          DBEG,
                                        Max(Cast(S.SHOW as varchar2(1)))     MODES
                                   from ADBUS.V_CFA_PAYM A
                                  inner join PARAMETER P
                                     on (    A.PSTNG_DATE between P.DBEG and P.DEND
                                         and A.CEEIB_S_UA_DEAL like 'CTX2625%'
                                         and A.ACCDEBCRED='D'
                                         and P.TYPE in (1,2)
                                        )
                                  inner join PARAMETER_LIST F
                                     on (    F.LIST=A.TB_TRNTYPE
                                         and F.KIND in ('ATM','POS','RKO')
                                        )
                                  inner join PARAMETER_LIST S
                                     on (    S.KIND ='RCC'
                                         and S.LIST ='DATE'
                                         and S.KEYS =0
                                          or S.KIND ='RCC'
                                         and S.LIST = A.CEEIB_S_UA_DEAL
                                         and S.KEYS =1
                                        )
                                  group by
                                        A.CEEIB_S_UA_DEAL
                                ) A
                        ),
            RCC_LISB as ( select *
                            from sv_rcc_list 
                        ),
            RCC_LIST as (
                         select /*+
                                    No_Merge(A)
                                    No_Merge(B)
                                    PUSH_PRED(B)
                                */
                                A.*,B.*
                           from --RCC_LISA A
                                RCC_LISB A
                           left join (select /*+
                                                 Index(A SYS_C0055843)
                                             */
                                             A.CTX_AC,
--                                             A.DATEFROM ACTIVE,
--                                             A.CTX_CRPR
                                             Min(A.DATEFROM)    ACTIVE,
                                             Min(A.CTX_CRPR) keep(Dense_rank first order by DATEFROM) CTX_CRPR
                                        from ADBUS.V_CFA_CTX_CONTRACT A
                                       where A.S_STATUS='CTX00'
                                       group by A.CTX_AC
                                     ) B
                             on (    B.CTX_AC=A.CEEIB_S_UA_DEAL
                                )
                        )
      select *
        from RCC_LIST;


-----------------------------------------------------------------------------------------------------------------------------------
План 1
Plan hash value: 931396718

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                             |     1 |   117 |       |  2174K  (1)| 00:02:50 |
|   1 |  VIEW                                       | SV_AA11                     |     1 |   117 |       |  2174K  (1)| 00:02:50 |
|   2 |   TEMP TABLE TRANSFORMATION                 |                             |       |       |       |            |          |
|   3 |    LOAD AS SELECT                           | SYS_TEMP_0FD9D83B0_FE49FB4E |       |       |       |            |          |
|   4 |     WINDOW SORT                             |                             |     4 |   184 |       |   117   (1)| 00:00:01 |
|   5 |      VIEW                                   |                             |     4 |   184 |       |   116   (0)| 00:00:01 |
|   6 |       UNION-ALL                             |                             |       |       |       |            |          |
|*  7 |        CONNECT BY WITHOUT FILTERING         |                             |       |       |       |            |          |
|   8 |         FAST DUAL                           |                             |     1 |       |       |     2   (0)| 00:00:01 |
|   9 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|                             |     1 |     2 |       |    38   (0)| 00:00:01 |
|  10 |         FAST DUAL                           |                             |     1 |       |       |     2   (0)| 00:00:01 |
|  11 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|                             |     1 |     2 |       |    38   (0)| 00:00:01 |
|  12 |         FAST DUAL                           |                             |     1 |       |       |     2   (0)| 00:00:01 |
|  13 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|                             |     1 |     2 |       |    38   (0)| 00:00:01 |
|  14 |         FAST DUAL                           |                             |     1 |       |       |     2   (0)| 00:00:01 |
|  15 |    LOAD AS SELECT                           | SYS_TEMP_0FD9D83B1_FE49FB4E |       |       |       |            |          |
|* 16 |     FILTER                                  |                             |       |       |       |            |          |
|  17 |      HASH GROUP BY                          |                             |   145K|    13M|    15M| 62028   (1)| 00:00:05 |
|* 18 |       HASH JOIN                             |                             |   145K|    13M|       | 59980   (1)| 00:00:05 |
|  19 |        MERGE JOIN CARTESIAN                 |                             |    16 |   848 |       |    10   (0)| 00:00:01 |
|* 20 |         VIEW                                |                             |     4 |    76 |       |     2   (0)| 00:00:01 |
|  21 |          TABLE ACCESS FULL                  | SYS_TEMP_0FD9D83B0_FE49FB4E |     4 |   184 |       |     2   (0)| 00:00:01 |
|  22 |         BUFFER SORT                         |                             |     4 |   136 |       |    10   (0)| 00:00:01 |
|* 23 |          VIEW                               |                             |     4 |   136 |       |     2   (0)| 00:00:01 |
|  24 |           TABLE ACCESS FULL                 | SYS_TEMP_0FD9D83B0_FE49FB4E |     4 |   184 |       |     2   (0)| 00:00:01 |
|* 25 |        TABLE ACCESS BY INDEX ROWID BATCHED  | T_CFA_PAYM                  |   458K|    18M|       | 59949   (1)| 00:00:05 |
|* 26 |         INDEX RANGE SCAN                    | I01_CFA_PAYM                |   497K|       |       | 46615   (1)| 00:00:04 |
|* 27 |    HASH JOIN OUTER                          |                             |     1 |   117 |       |  2112K  (1)| 00:02:46 |
|  28 |     VIEW                                    |                             |     1 |    86 |       |     2   (0)| 00:00:01 |
|  29 |      TABLE ACCESS FULL                      | SYS_TEMP_0FD9D83B1_FE49FB4E |     1 |    86 |       |     2   (0)| 00:00:01 |
|  30 |     VIEW                                    |                             |   190K|  5770K|       |  2112K  (1)| 00:02:46 |
|* 31 |      FILTER                                 |                             |       |       |       |            |          |
|  32 |       SORT GROUP BY                         |                             |   190K|  6701K|       |  2112K  (1)| 00:02:46 |
|* 33 |        TABLE ACCESS BY INDEX ROWID BATCHED  | T_CFA_CTX_CONTRACT          |   506K|    17M|       |  2112K  (1)| 00:02:46 |
|  34 |         INDEX FULL SCAN                     | SYS_C0055843                |  2133K|       |       |  7194   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter(LEVEL<=2)
  16 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_PAYM','S_CFA_PAYM',3) IS NULL)
  18 - access("F"."LIST"="TB_TRNTYPE")
       filter("S"."LIST"='DATE' AND "S"."KEYS"=0 OR "S"."LIST"="CEEIB_S_UA_DEAL" AND "S"."KEYS"=1)
  20 - filter("F"."KIND"='ATM' OR "F"."KIND"='POS' OR "F"."KIND"='RKO')
  23 - filter("S"."KIND"='RCC')
  25 - filter("ACCDEBCRED"='D' AND "TB_TRNTYPE" IS NOT NULL)
  26 - access("PSTNG_DATE">=TO_DATE(' 2018-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CEEIB_S_UA_DEAL" LIKE 
              'CTX2625%' AND "PSTNG_DATE"<=TO_DATE(' 2018-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("CEEIB_S_UA_DEAL" LIKE 'CTX2625%')
  27 - access("B"."CTX_AC"(+)="A"."CEEIB_S_UA_DEAL")
  31 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CTX_CONTRACT','S_CFA_CTX_CONTRACT',3) IS NULL)
  33 - filter("S_STATUS"='CTX00')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement

[/SRC ]

[SRC PLSQL]
--------------------------------------------------------------------------------------------------------------
План 2
Plan hash value: 3884045316
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    | 20934 |   919K|   293K  (1)| 00:00:23 |
|   1 |  NESTED LOOPS OUTER                     |                    | 20934 |   919K|   293K  (1)| 00:00:23 |
|   2 |   VIEW                                  |                    | 20934 |   286K|    15   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                    | SV_RCC_LIST        | 20934 |   367K|    15   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE                 |                    |     1 |    31 |    14   (0)| 00:00:01 |
|*  5 |    FILTER                               |                    |       |       |            |          |
|   6 |     SORT GROUP BY                       |                    |     1 |    36 |    14   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_CTX_CONTRACT |     3 |   108 |    14   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | SYS_C0055843       |    11 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CTX_CONTRACT','S_CFA_CTX_CONTRACT',3) IS NULL)
   7 - filter("S_STATUS"='CTX00')
   8 - access("CTX_AC"="A"."CEEIB_S_UA_DEAL")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 1 Sql Plan Directive used for this statement


Спасибо за уделенное время
30 мар 18, 15:48    [21299985]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Читал по диагонали и вникать особого интереса нет. Возможно поможет пару наводящих мыслей.

HOME_X
Hinta-ми заставить не получаеться, т.е. все пропало ШЕФ, все пропало!!!! (вариантов решения нет ?)
Если я правильно понял, план запроса перестает устраивать при добавлении группировки в одну из inline view. Больше никаких изменений не делается.
Фундаментальных причин почему push pred должен перестать работать я не вижу.
В таком случае, можно поробовать взять из хорошего плана аутлайн
select * from table(dbms_xplan.display_cursor(format => 'ADVANCED'));
и влепить его на самый верхний уровень запроса с группировкой выбросив все свои хинты.
Если предикат стал пушится - то можно теперь попробовать упростить аутлайн до читабельного вида.
Если не вышло... ну тогда тебе таки придется откидывать лишнее из запроса чтоб понять причину.

HOME_X
Обратите внимание на тип связи (ПОЯСНИТЕ пожалуйста)
1 план HASH JOIN OUTER
2 план NESTED LOOPS OUTER
Предикат может пушится только если тип связи - NL.
Если HASH, то данные до группировки могут быть отфильтрованы [по условию соединения/корреляции] только если задействуется bloom filter.
30 мар 18, 17:23    [21300357]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
HOME_X,

если чесно то я не могу понять, как Вы предлагаете " использовать передачу параметров во внутрь предсталения"

сначала соеденить, а потом group by CTX_AC?
імхо, так ж нельзя, можно получіть другой результат

....
stax
30 мар 18, 17:36    [21300388]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
HOME_X
Member

Откуда:
Сообщений: 2544
dbms_photoshop
Если я правильно понял, план запроса перестает устраивать при добавлении группировки в одну из inline view. Больше никаких изменений не делается.

Совершенно верно.

dbms_photoshop
В таком случае, можно поробовать взять из хорошего плана аутлайн
select * from table(dbms_xplan.display_cursor(format => 'ADVANCED'));

и влепить его на самый верхний уровень запроса с группировкой выбросив все свои хинты.

Принято - буду пробывать.

dbms_photoshop
Предикат может пушится только если тип связи - NL.

Вот это кажеться истинная причина, так как в inline view RCC_LISA есть группировка и весь набор
данных в кеше, вероятно HASH связь в этом случае более оптимальная
Хотя я умышленно материализовал view RCC_LISA as (select /*+ Materialize */

Спасибо
30 мар 18, 17:43    [21300402]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
HOME_X
Member

Откуда:
Сообщений: 2544
Stax,

Результат получиться идентичный надо будет верно сагрегировать поля.

Я ХОТЕЛ !!!!!???? предположить что, передача параметров кєша в представление,
более єффективная чем множественная связь и агрегирования.
30 мар 18, 17:48    [21300414]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
HOME_X
Stax,

Результат получиться идентичный надо будет верно сагрегировать поля.

Я ХОТЕЛ !!!!!???? предположить что, передача параметров кєша в представление,
более єффективная чем множественная связь и агрегирования.


SQL> with T_BW_ATL_DEALS as(
  2  select 1 id,100 CEEIB_S_UA_DEAL from dual union all
  3  select 2 id,200 CEEIB_S_UA_DEAL from dual union all
  4  select 3 id,200 CEEIB_S_UA_DEAL from dual union all
  5  select 4 id,400 CEEIB_S_UA_DEAL from dual
  6  )
  7  ,V_CFA_CTX_CONTRACT as (
  8  select 200 CTX_AC,10 CTX_CRPR, 'CTX00' S_STATUS from dual union all
  9  select 200 CTX_AC,20 CTX_CRPR, 'CTX00' S_STATUS from dual )
 10                           select /*+
 11                                      No_Merge(B)
 12                                      PUSH_PRED(B)
 13                                  */
 14  id,
 15                                  A.CEEIB_S_UA_DEAL,sum_CTX_CRPR
 16                             from T_BW_ATL_DEALS A
 17                             left join (select /*+
 18                                                   Index(SYS_C0055843)
 19                                               */
 20                                               CTX_AC,
 21  --                                             DATEFROM ACTIVE
 22                                               sum(CTX_CRPR) sum_CTX_CRPR
 23  --                                             Min(DATEFROM)    ACTIVE,
 24  --                                             Min(CTX_CRPR) keep(Dense_rank first order by DATEFROM) CTX_CRPR
 25                                          from V_CFA_CTX_CONTRACT
 26                                         where S_STATUS='CTX00'
 27                                         group by CTX_AC
 28                                       ) B
 29                               on (    B.CTX_AC=A.CEEIB_S_UA_DEAL
 30                                  )
 31  /

        ID CEEIB_S_UA_DEAL SUM_CTX_CRPR
---------- --------------- ------------
         1             100
         2             200           30
         3             200           30
         4             400


A.CEEIB_S_UA_DEAL не уникально, как Вы предлагаете сгруппировать (протолкнуть B.CTX_AC=A.CEEIB_S_UA_DEAL)?

получится аля латерал, чего 11-ка почти не умеет

.....
stax
2 апр 18, 10:13    [21303970]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Stax,

Если посмотреть план для твоего запроса - там как раз и будет VIEW PUSHED PREDICATE.
2 апр 18, 14:06    [21305223]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
dbms_photoshop,

каким то образом оракля понимает что ето "константы" (201, 200, 200)

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

12 - filter(201="A"."CEEIB_S_UA_DEAL")
14 - filter(200="A"."CEEIB_S_UA_DEAL")
16 - filter(200="A"."CEEIB_S_UA_DEAL")


если поменять на табличку, то и для NL не пушится почему-то
with T_BW_ATL_DEALS as(
select 1 id,100 CEEIB_S_UA_DEAL from dual union all
select 2 id,200 CEEIB_S_UA_DEAL from dual union all
select 3 id,200 CEEIB_S_UA_DEAL from dual union all
select 4 id,400 CEEIB_S_UA_DEAL from dual
)
,t as (
select 201 CTX_AC,10 CTX_CRPR, 'CTX00' S_STATUS,sysdate datefrom from dual union all
select 200 CTX_AC,10 CTX_CRPR, 'CTX00' S_STATUS,sysdate datefrom from dual union all
select 200 CTX_AC,20 CTX_CRPR, 'CTX00' S_STATUS,sysdate from dual )
,V_CFA_CTX_CONTRACT as
(select 200 CTX_AC,10 CTX_CRPR, 'CTX00' S_STATUS,sysdate datefrom from dept)
                         select /*+
                                    use_nl(a b)
                                    PUSH_PRED(B)
                                */
id,
                                A.CEEIB_S_UA_DEAL,sum_CTX_CRPR
                           from T_BW_ATL_DEALS A
                           left join (select /*+
                                                 Index(SYS_C0055843)
                                             */
                                             CTX_AC,
--                                             DATEFROM ACTIVE
                                             sum(CTX_CRPR) sum_CTX_CRPR,
--                                             Min(DATEFROM)    ACTIVE,
                                             Min(CTX_CRPR) keep(Dense_rank first order by DATEFROM) CTX_CRPR
                                        from V_CFA_CTX_CONTRACT
                                       where S_STATUS='CTX00'
                                       group by CTX_AC
                                     ) B
                             on (    B.CTX_AC=A.CEEIB_S_UA_DEAL
                                )
/


        ID CEEIB_S_UA_DEAL SUM_CTX_CRPR
---------- --------------- ------------
         1             100
         2             200           40
         3             200           40
         4             400


Execution Plan
----------------------------------------------------------
Plan hash value: 1611096655

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |    88 |    20   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     4 |    88 |    20   (0)| 00:00:01 |
|   2 |   VIEW               |      |     4 |    24 |     8   (0)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|*  8 |   VIEW               |      |     1 |    16 |     3   (0)| 00:00:01 |
|   9 |    SORT GROUP BY     |      |     4 |       |     3   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL| DEPT |     4 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   8 - filter("B"."CTX_AC"(+)="A"."CEEIB_S_UA_DEAL")
2 апр 18, 17:34    [21306191]     Ответить | Цитировать Сообщить модератору
 Re: PUSH_PRED и группировка  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Stax,

В 12 пушится как надо, в 11 нет.
Но недоступность латералов для программистов не должна этому препятствовать имхо.

11 часто для соединений создает латералы под капотом, но в этом случае не допилили.
Кстати, без группировки тоже не пушится для твоего примера. Что не есть случай автора.

Может поведение меняется для различных версий 11.
+
create table V_CFA_CTX_CONTRACT as (
select 200 CTX_AC,10 CTX_CRPR, 'CTX00' S_STATUS, sysdate datefrom from dual
 union all
select 200 CTX_AC,20 CTX_CRPR, 'CTX00' S_STATUS, sysdate datefrom from dual )

-- + GROUP
with t_bw_atl_deals as
 (select 1 id, 100 ceeib_s_ua_deal
    from dual
  union all
  select 2 id, 200 ceeib_s_ua_deal
    from dual
  union all
  select 3 id, 200 ceeib_s_ua_deal
    from dual
  union all
  select 4 id, 400 ceeib_s_ua_deal from dual)
select --+ use_nl(a b) PUSH_PRED(B)
 id, a.ceeib_s_ua_deal, ctx_crpr
  from t_bw_atl_deals a,
       (select --+ no_merge
         ctx_ac,
         max(ctx_crpr) keep(dense_rank first order by datefrom) ctx_crpr
          from v_cfa_ctx_contract
         where s_status = 'CTX00'
         group by ctx_ac) b
 where (b.ctx_ac(+) = a.ceeib_s_ua_deal);

-- - GROUP
with t_bw_atl_deals as
 (select 1 id, 100 ceeib_s_ua_deal
    from dual
  union all
  select 2 id, 200 ceeib_s_ua_deal
    from dual
  union all
  select 3 id, 200 ceeib_s_ua_deal
    from dual
  union all
  select 4 id, 400 ceeib_s_ua_deal from dual)
select --+ use_nl(a b) PUSH_PRED(B)
 id, a.ceeib_s_ua_deal, ctx_crpr
  from t_bw_atl_deals a,
       (select --+ no_merge
         ctx_ac,
         /*max(ctx_crpr) keep(dense_rank first order by datefrom)*/ ctx_crpr
          from v_cfa_ctx_contract
         where s_status = 'CTX00'
         /*group by ctx_ac*/) b
 where (b.ctx_ac(+) = a.ceeib_s_ua_deal);
2 апр 18, 18:51    [21306367]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить