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

Откуда:
Сообщений: 2704
Доброй день уважаемые господа !
Имею ситуация

Есть ряд запросов - они оптимизированы, обрабатываються досточно быстро
Слепливаю их помощью union all - производительность падает при этом план оптимизации не меняеться
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as andrewbond
 
SQL> 
 
Explained
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3802835251
--------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     2 |    44 |     3
|   1 |  SORT GROUP BY NOSORT          |               |     2 |    44 |     3
|*  2 |   COUNT STOPKEY                |               |       |       |
|   3 |    VIEW                        |               |     2 |    44 |     3
|   4 |     SORT GROUP BY NOSORT       |               |     2 |    16 |     3
|   5 |      INDEX FULL SCAN DESCENDING| PK_KP_AM_OVER |     2 |    16 |     3
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<3)
 
17 rows selected
 
Explained
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1622212879
--------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     2 |    44 |     4   (0
|   1 |  SORT GROUP BY NOSORT          |            |     2 |    44 |     4   (0
|*  2 |   COUNT STOPKEY                |            |       |       |
|   3 |    VIEW                        |            |     2 |    44 |     4   (0
|   4 |     SORT GROUP BY NOSORT       |            |     2 |    16 |     4   (0
|   5 |      INDEX FULL SCAN DESCENDING| PK_SAP_DEP |     2 |    16 |     4   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<3)
 
17 rows selected
 
Explained
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2612801736
--------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     4 |    88 |   463K
|   1 |  UNION-ALL                      |               |       |       |
|   2 |   SORT GROUP BY NOSORT          |               |     2 |    44 |   458K
|*  3 |    COUNT STOPKEY                |               |       |       |
|   4 |     VIEW                        |               |  1449 | 31878 |   458K
|*  5 |      SORT GROUP BY STOPKEY      |               |  1449 | 11592 |   458K
|   6 |       INDEX FULL SCAN DESCENDING| PK_SAP_DEP    |   153M|  1173M|   458K
|   7 |   SORT GROUP BY NOSORT          |               |     2 |    44 |  5050
|*  8 |    COUNT STOPKEY                |               |       |       |
|   9 |     VIEW                        |               |   794 | 17468 |  5050
|* 10 |      SORT GROUP BY STOPKEY      |               |   794 |  6352 |  5050
|  11 |       INDEX FULL SCAN DESCENDING| PK_KP_AM_OVER |  1092K|  8537K|  5050
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   3 - filter(ROWNUM<3)
   5 - filter(ROWNUM<3)
   8 - filter(ROWNUM<3)
  10 - filter(ROWNUM<3)
 
26 rows selected
 
SQL>

Не МЕНЯЕТЬСЯ структура плана (по весу не определишь) - но база добавлены еще пару условий
который не было в составляющих частях

|* 5 | SORT GROUP BY STOPKEY | | 1449 | 11592 | 458K
|* 10 | SORT GROUP BY STOPKEY | | 794 | 6352 | 5050

5 - filter(ROWNUM<3)
10 - filter(ROWNUM<3)

Предполагаю что они привели к снижению скорость
А как можно их обойти подскажите пожалуйста

Благодарен !
4 июл 11, 17:37    [10920054]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

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

explain plan for
select 'A1' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A1 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A1
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1;
select * from table (dbms_xplan.display);
explain plan for
select 'A2' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A2 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A2
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1; 
select * from table (dbms_xplan.display);
explain plan for
select 'A1' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A1 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A1
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1 
 union all
select 'A2' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A2 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A2
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1; 
select * from table (dbms_xplan.display);


4 июл 11, 17:41    [10920089]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
HOME_X,

Давай планы через GATHER_PLAN_STATISTICS + DISPLAY_CURSOR
4 июл 11, 17:45    [10920107]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Vint
Member

Откуда: Москва
Сообщений: 4576
абсолютно всё равно какие 2 записи выберутся?)
истранные у вас планы. 17 + 17 != 26 покажите нормальные запросы, и к ним нормальные планы.
4 июл 11, 17:49    [10920130]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

Откуда:
Сообщений: 2704
HOME_X,
аналогичный эффект - ТОРМОЗ имею когда
поверху составляющего куска пишу еще один SELECT

select * from (


select A1 KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A1 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A1
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1

) ;


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as andrewbond
 
SQL> 
 
Explained
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1153460427
--------------------------------------------------------------------------------
| Id  | Operation                       | Name       | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |     2 |   134 |     4   (
|   1 |  VIEW                           |            |     2 |   134 |     4   (
|   2 |   SORT GROUP BY NOSORT          |            |     2 |    44 |     4   (
|*  3 |    COUNT STOPKEY                |            |       |       |
|   4 |     VIEW                        |            |     2 |    44 |     4   (
|*  5 |      SORT GROUP BY STOPKEY      |            |     2 |    16 |     4   (
|   6 |       INDEX FULL SCAN DESCENDING| PK_SAP_DEP |     2 |    16 |     4   (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(ROWNUM<3)
   5 - filter(ROWNUM<3)
 
19 rows selected
 
SQL> 

Второе условие на
|* 5 | SORT GROUP BY STOPKEY | | 2 | 16 | 4 (
5 - filter(ROWNUM<3)
4 июл 11, 17:49    [10920140]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

Откуда:
Сообщений: 2704
Vint
абсолютно всё равно какие 2 записи выберутся?)


Работа идет исключительно по индексу (без таблицы)
Индекс активизирован в обратном порядке
Осуществляеться выбор последних двух дней


Vint
истранные у вас планы. 17 + 17 != 26 покажите нормальные запросы, и к ним нормальные планы.


Показал работы двух составляющих запросов

explain plan for
select 'A1' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A1 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A1
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1;
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3802835251
--------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     2 |    44 |     3
|   1 |  SORT GROUP BY NOSORT          |               |     2 |    44 |     3
|*  2 |   COUNT STOPKEY                |               |       |       |
|   3 |    VIEW                        |               |     2 |    44 |     3
|   4 |     SORT GROUP BY NOSORT       |               |     2 |    16 |     3
|   5 |      INDEX FULL SCAN DESCENDING| PK_KP_AM_OVER |     2 |    16 |     3
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<3)
 
17 rows selected


И один результатирующий
explain plan for
select 'A1' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A1 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A1
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1 
 union all
select 'A2' KIND,
       Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
       Min(COL)      keep(Dense_Rank first order by DATE_REP) CMIN,
       Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP) DMAX,
       Max(COL)      keep(Dense_Rank last  order by DATE_REP) CMAX,
       Case when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP)
              <> Max(DATE_REP) keep(Dense_Rank last  order by DATE_REP)
             and Max(COL) keep(Dense_Rank last  order by DATE_REP) between 
                 Min(COL) keep(Dense_Rank first order by DATE_REP) *0.9
             and Min(COL) keep(Dense_Rank first order by DATE_REP) *1.1
            then 1 else 0 end EXEC     
  from (select /*+
                  Index_Desc(A2 PK_F1) 
               */ 
               DATE_REP,
               Count(*) COL
          from A2
         group by DATE_REP
       ) 
 where RowNum<3
 group by 1; 
select * from table (dbms_xplan.display);


Explained
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2612801736
--------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     4 |    88 |   463K
|   1 |  UNION-ALL                      |               |       |       |
|   2 |   SORT GROUP BY NOSORT          |               |     2 |    44 |   458K
|*  3 |    COUNT STOPKEY                |               |       |       |
|   4 |     VIEW                        |               |  1449 | 31878 |   458K
|*  5 |      SORT GROUP BY STOPKEY      |               |  1449 | 11592 |   458K
|   6 |       INDEX FULL SCAN DESCENDING| PK_SAP_DEP    |   153M|  1173M|   458K
|   7 |   SORT GROUP BY NOSORT          |               |     2 |    44 |  5050
|*  8 |    COUNT STOPKEY                |               |       |       |
|   9 |     VIEW                        |               |   794 | 17468 |  5050
|* 10 |      SORT GROUP BY STOPKEY      |               |   794 |  6352 |  5050
|  11 |       INDEX FULL SCAN DESCENDING| PK_KP_AM_OVER |  1092K|  8537K|  5050
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   3 - filter(ROWNUM<3)
   5 - filter(ROWNUM<3)
   8 - filter(ROWNUM<3)
  10 - filter(ROWNUM<3)
 
26 rows selected
 


Почему должно быть 17 + 17 = 34 ?
4 июл 11, 17:56    [10920181]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

Откуда:
Сообщений: 2704
_Nikotin
HOME_X,

Давай планы через GATHER_PLAN_STATISTICS + DISPLAY_CURSOR


Извините уважаемый _Nikotin !
Я у меня пользовательские права
select /*+ gather_plan_statistics */ To_Number('9')  F1
  from DUAL;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 
Connected as andrewbond
 
SQL> 
 
        F1
----------
         9
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
 
SQL> 

Если сумеете догадаться !!!!! буду признателен, но стат. привести не могу
4 июл 11, 18:05    [10920238]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 524
HOME_X
Не МЕНЯЕТЬСЯ структура плана (по весу не определишь) - но база добавлены еще пару условий
который не было в составляющих частях

|*  5 |      SORT GROUP BY STOPKEY      |               |  1449 | 11592 |   458K
|* 10 |      SORT GROUP BY STOPKEY      |               |   794 |  6352 |  5050 

   5 - filter(ROWNUM<3)
  10 - filter(ROWNUM<3)

Предполагаю что они привели к снижению скорость
А как можно их обойти подскажите пожалуйста

Благодарен !


NOSORT в операции SORT GROUP BY NOSORT означает, что Оракл сможет избежать сорировки, т.к. дочерний rowsource уже отсортирован.
Во втором случае SORT GROUP BY STOPKEY означает, что строки будут отсортированы, сгруппированы и потом будут взяты top-N строк.

SQL> create table test as select * from all_objects;
 
Table created
 
SQL> create index test_idx on test(created);
 
Index created

select *
  from (select /*+ index_desc(a1 test_idx) */
               created DATE_REP, 
               Count(*) COL
          from test A1
         group by created) 
 where rownum < 3

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |      2 |00:00:00.01 |       2 |
|*  1 |  COUNT STOPKEY                |          |      1 |        |      2 |00:00:00.01 |       2 |
|   2 |   VIEW                        |          |      1 |  67192 |      2 |00:00:00.01 |       2 |
|   3 |    SORT GROUP BY NOSORT       |          |      1 |  67192 |      2 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  67192 |      5 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<3)
В этом случае читается индекс, полученные строки группируется без сортировки, по достижении 2 строк (после группировки) они выдаются родителю.


select * from
(
    select *
      from (select /*+ index_desc(a1 test_idx) */
                   created DATE_REP, 
                   Count(*) COL
              from test A1
             group by created) 
     where rownum < 3
) 

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |      2 |00:00:00.02 |     167 |       |       |          |
|   1 |  VIEW                          |          |      1 |      2 |      2 |00:00:00.02 |     167 |       |       |          |
|*  2 |   COUNT STOPKEY                |          |      1 |        |      2 |00:00:00.02 |     167 |       |       |          |
|   3 |    VIEW                        |          |      1 |  67192 |      2 |00:00:00.02 |     167 |       |       |          |
|*  4 |     SORT GROUP BY STOPKEY      |          |      1 |  67192 |      2 |00:00:00.02 |     167 |  2048 |  2048 | 2048  (0)|
|   5 |      INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  67192 |  62479 |00:00:00.01 |     167 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<3)
   4 - filter(ROWNUM<3)
В этом случае читается весь индекс, потом все строки сортируются/группируются, затем берутся 2 топовые строки, выдаются родителю.
Очевидно, что первый вариант предпочтительнее, второй более ресурсоемкий.



В твоем случае можно воспользоваться таким вариантом:
select *
  from (
        
        select 'A1' KIND,
                Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
                Min(COL) keep(Dense_Rank first order by DATE_REP) CMIN,
                Max(DATE_REP) keep(Dense_Rank last order by DATE_REP) DMAX,
                Max(COL) keep(Dense_Rank last order by DATE_REP) CMAX,
                Case
                  when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) <>
                       Max(DATE_REP)
                   keep(Dense_Rank last order by DATE_REP) and Max(COL)
                   keep(Dense_Rank last order by DATE_REP) between Min(COL)
                   keep(Dense_Rank first order by DATE_REP) * 0.9 and
                       Min(COL)
                   keep(Dense_Rank first order by DATE_REP) * 1.1 then
                   1
                  else
                   0
                end EXEC
          from (
                 
                 select t.*, row_number() over(order by DATE_REP desc) rn
                   from (select /*+ index_desc(a1 test_idx) */
                                created DATE_REP, 
                                сount(*) COL
                           from test A1
                          group by created) t
                 
                 )
         where rn < 3
        )

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  VIEW                           |          |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   2 |   SORT AGGREGATE                |          |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    VIEW                         |          |      1 |  67192 |      2 |00:00:00.01 |       2 |       |       |          |
|*  4 |     WINDOW NOSORT STOPKEY       |          |      1 |  67192 |      3 |00:00:00.01 |       2 |   663K|   481K|          |
|   5 |      SORT GROUP BY NOSORT       |          |      1 |  67192 |      4 |00:00:00.01 |       2 |       |       |          |
|   6 |       INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  67192 |     12 |00:00:00.01 |       2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("RN"<3)
   4 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED") DESC )<3)

С точки зрения производительности он не эквивалентен предыдущиму с NOSORT, из-за того что стопает на один шаг позже.
Если в оригинальном варианте будут зафетчены n строк с 2-мя уникальными DATE_REP + 1 строка (итого 5 строк в моем примере), то в последнем n строк c 3-мя уникальными DATE_REP + 1 строка (12 строк в моем случае).
5 июл 11, 01:57    [10921676]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Vladimir_
Member

Откуда: Санкт-Петербург
Сообщений: 771
HOME_X
Почему должно быть 17 + 17 = 34 ?
насколько я понял, Вы используете union all и как следствие количество строк возвращаемых должно совпадать. 2 запроса по 17 строк дадут именно 34 строчки а не 26 как в листинге. это должно навести на мысль, что запросы разные.
5 июл 11, 09:54    [10922300]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Vladimir_
Member

Откуда: Санкт-Петербург
Сообщений: 771
забыл пояснить для автора вопроса, что 17 строк - это строки в запросе плана выполнения и не имеют отношения к строкам самого селекта. Что-то я сегодня никак не проснусь :(
5 июл 11, 09:58    [10922327]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 524
Alexander Anokhin
С точки зрения производительности он не эквивалентен предыдущиму с NOSORT, из-за того что стопает на один шаг позже.
Если в оригинальном варианте будут зафетчены n строк с 2-мя уникальными DATE_REP + 1 строка (итого 5 строк в моем примере), то в последнем n строк c 3-мя уникальными DATE_REP + 1 строка (12 строк в моем случае).

Точнее даже не 3 DATE_REP + 1 строка, а 4 DATE_REP + 1 строка.

На упрощенном примере
select 
       *
  from (select created, 
               row_number() over(order by created desc) rn
          from (select /*+ index_desc(t test_idx) */
                       created 
                  from test t) 
       )
 where rn < 3

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |      2 |00:00:00.01 |       2 |    |          |          |
|*  1 |  VIEW                        |          |      1 |  67192 |      2 |00:00:00.01 |       2 |    |          |          |
|*  2 |   WINDOW NOSORT STOPKEY      |          |      1 |  67192 |      3 |00:00:00.01 |       2 |   663K|   481K|          |
|   3 |    INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  67192 |      4 |00:00:00.01 |       2 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN"<3)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED") DESC )<3)
Стоит отметить:
1. WINDOW NOSORT STOPKEY выдает родительской операции 3 строки (когда достаточно было бы 2)
2. делает это когда получает от дочернего rowsource на одну строку больше. В данном случае 4 строки. В случае автора там SORT GROUP BY NOSORT, который чтобы вернуть 4 строки должен зафетчить из индекса n строк с 4мя уникальными DATE_REP + 1 строка.

Интересно, что первое решается, если немного изменить условие
select *
  from (select created, 
               row_number() over(order by created desc) rn
          from (select /*+ index_desc(t test_idx) */
                       created 
                  from test t) 
       )
 where rn <= 2


------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |      2 |00:00:00.01 |       2 |    |          |          |
|*  1 |  VIEW                        |          |      1 |  67192 |      2 |00:00:00.01 |       2 |    |          |          |
|*  2 |   WINDOW NOSORT STOPKEY      |          |      1 |  67192 |      2 |00:00:00.01 |       2 |   663K|   481K|          |
|   3 |    INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  67192 |      3 |00:00:00.01 |       2 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED") DESC )<=2)


Таким образом, автор, если решишь пользоваться запросом, который я приводил выше, то стоит условие подправить
select *
  from (
        
        select 'A1' KIND,
                Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) DMIN,
                Min(COL) keep(Dense_Rank first order by DATE_REP) CMIN,
                Max(DATE_REP) keep(Dense_Rank last order by DATE_REP) DMAX,
                Max(COL) keep(Dense_Rank last order by DATE_REP) CMAX,
                Case
                  when Min(DATE_REP) keep(Dense_Rank first order by DATE_REP) <>
                       Max(DATE_REP)
                   keep(Dense_Rank last order by DATE_REP) and Max(COL)
                   keep(Dense_Rank last order by DATE_REP) between Min(COL)
                   keep(Dense_Rank first order by DATE_REP) * 0.9 and
                       Min(COL)
                   keep(Dense_Rank first order by DATE_REP) * 1.1 then
                   1
                  else
                   0
                end EXEC
          from (
                 
                 select t.*, row_number() over(order by DATE_REP desc) rn
                   from (select /*+ index_desc(a1 test_idx) */
                                created DATE_REP, 
                                count(*) COL
                           from test A1
                          group by created) t
                 
                 )
         where rn <= 2
        )

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  VIEW                           |          |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   2 |   SORT AGGREGATE                |          |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    VIEW                         |          |      1 |  76314 |      2 |00:00:00.01 |       2 |       |       |          |
|*  4 |     WINDOW NOSORT STOPKEY       |          |      1 |  76314 |      2 |00:00:00.01 |       2 |   753K|   497K|          |
|   5 |      SORT GROUP BY NOSORT       |          |      1 |  76314 |      3 |00:00:00.01 |       2 |       |       |          |
|   6 |       INDEX FULL SCAN DESCENDING| TEST_IDX |      1 |  76314 |      9 |00:00:00.01 |       2 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("RN"<=2)
   4 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED") DESC )<=2)
5 июл 11, 10:11    [10922413]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

Откуда:
Сообщений: 2704
Спасибо за подробное пояснение уважаемый Alexander Anokhin !
Эффект получен - скорость увеличилась !

Скажите а данную задачу нельзя ли решить НЕ используя
оконных функций ?
(они достаточно тяжелые и старался по мере возможности их избегать)

Может Hintами - но в HELPе подходящих не нашел .....


Благодарен и признателен !
5 июл 11, 11:39    [10923145]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
Alexander Anokhin
Member

Откуда: Хабаровск
Сообщений: 524
HOME_X
Скажите а данную задачу нельзя ли решить НЕ используя
оконных функций ?

У меня других вариантов нет на данный момент.

HOME_X
(они достаточно тяжелые и старался по мере возможности их избегать)

Это суеверие. Лучше избегать суеверий, а не оконные функции.

Разница между первоначальным вариантом с rownum и последним вариантом с row_number в том, что первоначальный фетчит из индекса 2 дня, тогда как последний 3 дня.

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

Эта разница может стать значительной, если третьи по счету сутки будут включать много строк или благодаря filter access предикатам надо будет найти и отфильтровать много строк, чтобы добраться до третьих суток. Что будет вести к большому количеству чтений из индекса.
5 июл 11, 14:08    [10924739]     Ответить | Цитировать Сообщить модератору
 Re: Влияние Union all на скорость выполнения запроса  [new]
HOME_X
Member

Откуда:
Сообщений: 2704
Alexander Anokhin,

Принято - применено !
Спасибо !
5 июл 11, 16:42    [10926495]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить