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

Откуда: Москва
Сообщений: 368
Создадим таблицу MG_T с датой DT, измерениями DIM1, DIM2 и показателем VAL.
Заполним ее тестовыми данными:

create table mg_t as
select trunc(sysdate) + level dt,
       trunc(3 * dbms_random.value()) dim1,
       trunc(3 * dbms_random.value()) dim2,
       trunc(100 * dbms_random.value()) val
  from dual
connect by level <= 20;


Число различных возможных значений, которые могут принимать DIM1 и DIM2 невелико.
Необходимо быстро считать суммы с накопительным итогом по измерениям DIM1, DIM2 и паре (DIM1, DIM2):
В реальности таблица MG_T большая, на 3 миллиона строк, измерений DIMn побольше, чем 2.

select dt,
       dim1,
       dim2,
       val,
       sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
       sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
       sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
  from mg_t
 order by dt;


Пока таблица MG_T не проиндексирована, план получается плохой, с несколькими WINDOWS_SORTами, как на картинке:

Если создать битмап-индексы на DIM1, DIM2, (DIM1, DIM2) и пробовать хинтовать запрос по INDEX_COMBINE, то подхватывается только один индекс, и запрос выполняется тоже очень медленно.

Как эффективно проиндексировать таблицу и прохинтовать запрос по поиску нескольких сумм с накопительным итогом?
15 ноя 12, 22:46    [13480927]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
yon_brover
Member

Откуда: Москва
Сообщений: 368
картинка с планом без индексов:

К сообщению приложен файл. Размер - 19Kb
15 ноя 12, 22:48    [13480932]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
yon_brover,

И в реальности вам нужно накопительные суммы по всем трем миллионам? Думаю тут лучше выносить все это дело в pl/sql - достаточно одного прохода с сортиповкой по dt
15 ноя 12, 22:53    [13480945]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
pectopatop
Member

Откуда:
Сообщений: 765
А не поможет ли тут cube и rollup?
15 ноя 12, 23:03    [13480980]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
yon_brover
Member

Откуда: Москва
Сообщений: 368
Постановка задачи от бизнеса такова, что нужна сумма по всем 3 миллионам строк. Сделать PL/SQL-ный цикл можно, но нельзя ли изящно проиндексировать?
15 ноя 12, 23:06    [13480983]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
yon_brover,

изящно извращенно можно, но нужно ли? :)
Например, можно было бы создать индексы (dim1,dim2,dt), (dim2,dt) проходить по ним по отдельности и джойнить по rowid... Но это чисто гипотетически, на самом деле лучше так не делать, а просто создать индекс на dt и в pl/sql'е легко и просто отщелкать
15 ноя 12, 23:31    [13481055]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
yon_brover,

извращенный примерчик
+
create table mg_t as
select trunc(sysdate) + level dt,
       trunc(3 * dbms_random.value()) dim1,
       trunc(3 * dbms_random.value()) dim2,
       trunc(100 * dbms_random.value()) val
  from dual
connect by level <= 2000;
alter table mg_t modify dt not null;
create index ix_dim1 on mg_t(dim1,dt);
create index ix_dim2 on mg_t(dim2,dt);

with
   t1 as (
        select--+ index(mg_t)
               rowid rid
              ,sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum
        from mg_t
   ), 
   t2 as (
        select--+ index(mg_t)
               rowid rid
              ,sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum
        from mg_t
   )
select mg_t.*
      ,dim1_cumulative_sum
      ,dim2_cumulative_sum
from mg_t
     ,t1,t2
where t1.rid=t2.rid and mg_t.rowid=t1.rid;
...
2000 rows selected.

Elapsed: 00:00:01.12

sort trace:
---- Sort Parameters ------------------------------
sort_area_size                    88064
sort_area_retained_size           88064
sort_multiblock_read_count        1
max intermediate merge width      4
---- Sort Statistics ------------------------------
Input records                             2000
Output records                            2000
Total number of comparisons performed     0
Total amount of memory used               88064
Uses version 1 sort
---- End of Sort Statistics -----------------------
---- Sort Parameters ------------------------------
sort_area_size                    88064
sort_area_retained_size           88064
sort_multiblock_read_count        1
max intermediate merge width      4

*** 2012-11-15 23:35:56.611
---- Sort Statistics ------------------------------
Input records                             2000
Output records                            2000
Total number of comparisons performed     0
Total amount of memory used               88064
Uses version 1 sort
---- End of Sort Statistics -----------------------

План:
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |      1 |        |   2000 |00:00:00.06 |      61 |      7 |       |       |          |
|*  1 |  HASH JOIN                      |         |      1 |   2000 |   2000 |00:00:00.06 |      61 |      7 |   862K|   862K| 1266K (0)|
|   2 |   VIEW                          |         |      1 |   2000 |   2000 |00:00:00.01 |      26 |      0 |       |       |          |
|   3 |    WINDOW BUFFER                |         |      1 |   2000 |   2000 |00:00:00.01 |      26 |      0 | 99328 | 99328 |88064  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID | MG_T    |      1 |   2000 |   2000 |00:00:00.01 |      26 |      0 |       |       |          |
|   5 |      INDEX FULL SCAN            | IX_DIM2 |      1 |   2000 |   2000 |00:00:00.01 |       8 |      0 |       |       |          |
|*  6 |   HASH JOIN                     |         |      1 |   2000 |   2000 |00:00:00.05 |      35 |      7 |   791K|   791K| 1291K (0)|
|   7 |    TABLE ACCESS FULL            | MG_T    |      1 |   2000 |   2000 |00:00:00.03 |       9 |      0 |       |       |          |
|   8 |    VIEW                         |         |      1 |   2000 |   2000 |00:00:00.01 |      26 |      7 |       |       |          |
|   9 |     WINDOW BUFFER               |         |      1 |   2000 |   2000 |00:00:00.01 |      26 |      7 | 99328 | 99328 |88064  (0)|
|  10 |      TABLE ACCESS BY INDEX ROWID| MG_T    |      1 |   2000 |   2000 |00:00:00.01 |      26 |      7 |       |       |          |
|  11 |       INDEX FULL SCAN           | IX_DIM1 |      1 |   2000 |   2000 |00:00:00.01 |       8 |      7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."RID"="T2"."RID")
   6 - access("MG_T".ROWID="T1"."RID")


36 rows selected.

Elapsed: 00:00:00.13


15 ноя 12, 23:43    [13481088]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
Что-то я кривой пример наваял... только заметил. в индексы надо было val добавить чтоб лукапов в твблицу не было
15 ноя 12, 23:57    [13481123]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
yon_brover
Member

Откуда: Москва
Сообщений: 368
По rowid мне понравилось. Спасибо! Да, VAL надо в индексы добавить. Утром посмотрю, насколько ускорится по сравнению с фуллсканом.
16 ноя 12, 00:33    [13481231]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4237
yon_brover
Пока таблица MG_T не проиндексирована, план получается плохой, с несколькими WINDOWS_SORTами, как на картинке:
Я бы сказал, что план оптимальный. Селективных условий для использования индексов нет. Проблема в том, что нет PGA и хочется вместо сортировок использовать медленные одноблочные чтения по индексам? Если стандартная проблема с реализацией аналитики, то читаем Analytic Agony.
Для примера, поменял connect by level <= 20 на connect by level <= 3000000:
+ пример 11.2.0.3
SQL> set autotrace traceonly
SQL> set timing on
SQL> select count(1) from(
  2  select /*+ no_merge */  dt,
  3         dim1,
  4         dim2,
  5         val,
  6         sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
  7         sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
  8         sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
  9    from mg_t a
 10   order by dt);


Elapsed: 00:00:31.03

Execution Plan
----------------------------------------------------------
Plan hash value: 860268573

----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |       |       | 74439   (2)| 00:06:41 |
|   1 |  SORT AGGREGATE         |      |     1 |       |       |            |          |
|   2 |   VIEW                  |      |  2883K|       |       | 74439   (2)| 00:06:41 |
|   3 |    SORT ORDER BY        |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   4 |     WINDOW SORT         |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   5 |      WINDOW SORT        |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   6 |       WINDOW SORT       |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   7 |        TABLE ACCESS FULL| MG_T |  2883K|    46M|       |  2722   (3)| 00:00:15 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        288  recursive calls
         22  db block gets
       8847  consistent gets
      77379  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          3  sorts (disk)
          1  rows processed

SQL> alter session set "_smm_isort_cap" = 2048000;

SQL> select count(1) from(
  2  select /*+ no_merge */  dt,
  3         dim1,
  4         dim2,
  5         val,
  6         sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
  7         sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
  8         sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
  9    from mg_t a
 10   order by dt);

Elapsed: 00:00:17.22

Execution Plan
----------------------------------------------------------
Plan hash value: 860268573

----------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |       |       | 74439   (2)| 00:06:41 |
|   1 |  SORT AGGREGATE         |      |     1 |       |       |            |          |
|   2 |   VIEW                  |      |  2883K|       |       | 74439   (2)| 00:06:41 |
|   3 |    SORT ORDER BY        |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   4 |     WINDOW SORT         |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   5 |      WINDOW SORT        |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   6 |       WINDOW SORT       |      |  2883K|    46M|    88M| 74439   (2)| 00:06:41 |
|   7 |        TABLE ACCESS FULL| MG_T |  2883K|    46M|       |  2722   (3)| 00:00:15 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       8847  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(1) from(
  2  select /*+ no_merge parallel(a 8)*/  dt,
  3         dim1,
  4         dim2,
  5         val,
  6         sum(val) over(partition by dim1 order by dt) dim1_cumulative_sum,
  7         sum(val) over(partition by dim2 order by dt) dim2_cumulative_sum,
  8         sum(val) over(partition by dim1, dim2 order by dt) dim1_dim2_cumulative_sum
  9    from mg_t a
 10   order by dt);

Elapsed: 00:00:05.60

Execution Plan
----------------------------------------------------------
Plan hash value: 195863231

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |       |       |   494  (26)| 00:00:03 |        |      |            |
|   1 |  SORT AGGREGATE                   |          |     1 |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                  |          |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (ORDER)             | :TQ10003 |     1 |       |       |            |          |  Q1,03 | P->S | QC (ORDER) |
|   4 |     SORT AGGREGATE                |          |     1 |       |       |            |          |  Q1,03 | PCWP |            |
|   5 |      VIEW                         |          |  2883K|       |       |   494  (26)| 00:00:03 |  Q1,03 | PCWP |            |
|   6 |       SORT ORDER BY               |          |  2883K|    46M|    88M|   494  (26)| 00:00:03 |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE                 |          |  2883K|    46M|       |   494  (26)| 00:00:03 |  Q1,03 | PCWP |            |
|   8 |         PX SEND RANGE             | :TQ10002 |  2883K|    46M|       |   494  (26)| 00:00:03 |  Q1,02 | P->P | RANGE      |
|   9 |          WINDOW SORT              |          |  2883K|    46M|    88M|   494  (26)| 00:00:03 |  Q1,02 | PCWP |            |
|  10 |           PX RECEIVE              |          |  2883K|    46M|       |   494  (26)| 00:00:03 |  Q1,02 | PCWP |            |
|  11 |            PX SEND HASH           | :TQ10001 |  2883K|    46M|       |   494  (26)| 00:00:03 |  Q1,01 | P->P | HASH       |
|  12 |             WINDOW SORT           |          |  2883K|    46M|    88M|   494  (26)| 00:00:03 |  Q1,01 | PCWP |            |
|  13 |              WINDOW SORT          |          |  2883K|    46M|    88M|   494  (26)| 00:00:03 |  Q1,01 | PCWP |            |
|  14 |               PX RECEIVE          |          |  2883K|    46M|       |   377   (2)| 00:00:03 |  Q1,01 | PCWP |            |
|  15 |                PX SEND HASH       | :TQ10000 |  2883K|    46M|       |   377   (2)| 00:00:03 |  Q1,00 | P->P | HASH       |
|  16 |                 PX BLOCK ITERATOR |          |  2883K|    46M|       |   377   (2)| 00:00:03 |  Q1,00 | PCWC |            |
|  17 |                  TABLE ACCESS FULL| MG_T     |  2883K|    46M|       |   377   (2)| 00:00:03 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
       9056  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
          1  rows processed
"_smm_isort_cap" надо выставлять при автоматическом управлении памятью. Степень параллелизма - по возможностям оборудования.
16 ноя 12, 07:31    [13481586]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
wurdu,

да все равно в pl/sql легче и быстрее будет. у меня, например, раза в 3 быстрее такой вариант c pipelined на тех же 3 миллионах:
+
create table mg_t as
select trunc(sysdate) + level/1440 dt,
       trunc(3 * dbms_random.value()) dim1,
       trunc(3 * dbms_random.value()) dim2,
       trunc(100 * dbms_random.value()) val
  from dual
connect by level <= 3e6;

create type xt1_to_drop is object(
       dt                  date
      ,dim1                number
      ,dim2                number
      ,val                 number
      ,dim1_cumulative_sum number
      ,dim2_cumulative_sum number
      ,dim1_dim2_cumulative_sum number
);
create type xt2_to_drop as table of xt1_to_drop;

create or replace function xf_to_drop return xt2_to_drop pipelined
is
   type tt is table of number index by pls_integer;
   type tt2 is table of tt index by pls_integer;
   dim1_c tt;
   dim2_c tt;
   dim12_c tt2;
begin
   for r in (
            select dt,
                   dim1,
                   dim2,
                   val
              from mg_t
             order by dt
   )
   loop
      dim1_c(r.dim1):=case when dim1_c.exists(r.dim1) then dim1_c(r.dim1) else 0 end + r.val;
      dim2_c(r.dim1):=case when dim2_c.exists(r.dim1) then dim2_c(r.dim1) else 0 end + r.val;
      dim12_c(r.dim1)(r.dim2):=case
                                  when dim12_c.exists(r.dim1)
                                   and dim12_c(r.dim1).exists(r.dim2)
                                  then dim12_c(r.dim1)(r.dim2)
                                  else 0
                               end + r.val;
      pipe row (xt1_to_drop( r.dt,r.dim1,r.dim2,r.val,dim1_c(r.dim1),dim1_c(r.dim1),dim12_c(r.dim1)(r.dim2)));
   end loop;
end;
/
exec for r in (select * from table(xf_to_drop)) loop null; end loop;
16 ноя 12, 12:54    [13483198]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
а как же...
Guest
xtender,

а как же известная фраза - "если можешь обойтись одним селектом без pl/sql, то делай одним селектом" :)
16 ноя 12, 12:58    [13483223]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
xtender
Member

Откуда: Мск
Сообщений: 3241
а как же...,

Кайт просто упрощал для широких масс
16 ноя 12, 13:24    [13483463]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4237
xtender
wurdu,

да все равно в pl/sql легче и быстрее будет. у меня, например, раза в 3 быстрее такой вариант c pipelined на тех же 3 миллионах:
Не проверял, но впечатляет :) Реализация аналитики в Оракле просто курит в сторонке :)
16 ноя 12, 13:57    [13483826]     Ответить | Цитировать Сообщить модератору
 Re: Хинтовка запроса по поиску сумм с накопительным итогом  [new]
yon_brover
Member

Откуда: Москва
Сообщений: 368
xtender,

спасибо!

Индексирование предложенным вами способом улучшило время выполнения запроса в пару раз, а ваш pl/sql-ный вариант раз в 10.
16 ноя 12, 16:23    [13485271]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить
 
Лучший учебный центр Microsoft!
Новейшие курсы Microsoft SQL Server 2014!
ОЧЕНЬ привлекательные цены на курсы Oracle — от 26 тыс.руб.!
Все курсы по базам данных: Microsoft SQL Server 2014, Oracle, IBM DB2, Access, MySql