ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задача: Красное и черное
25 сообщений из 176, страница 3 из 8
  3  все
Пятничная задача: Красное и черное
    #40019075
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
graycode
НеофитSQL
Интересно, какого размера нужен дата сет, чтобы померять сравнительную производительность разных подходов?

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


"Невозможно сравнить скорость" это не не ответ для того, кто сказал что сделает быстрее :)
Если разница в разы в десятке замеров в разных ситуациях. я думаю понятно какое из решений быстрее.
А если разница проценты, то наверное одинаково.

П.С. Удивился длине процедуры, я почему-то думал что решение циклом будет намного короче.
...
Рейтинг: 0 / 0
16.11.2020, 19:09
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019107
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
НеофитSQL
"Невозможно сравнить скорость" это не не ответ для того, кто сказал что сделает быстрее :)
Если разница в разы в десятке замеров в разных ситуациях. я думаю понятно какое из решений быстрее.

Я к тому, что полученные циферки времени выполнения нужно рассматривать критически.

НеофитSQL
П.С. Удивился длине процедуры, я почему-то думал что решение циклом будет намного короче.

Перебрал варианты в лоб, можешь поискать закономерности и свернуть алгоритм в более короткий вид, главное чтобы в нем разобраться можно было))
...
Рейтинг: 0 / 0
16.11.2020, 19:46
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019114
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
НеофитSQL
Обилие вложенных селектов и оконных функций кажется неэффективным
На самом деле это вариант очень приличный!
Группировка пивотом позволила обойтись одной и той же сортировкой в аналитике на всех уровнях (order by x) ,
в результате чего у тебя только один WINDOW SORT и два WINDOW BUFFER.
Мелкий баг - decode(s,'-',t,s) может быть null если диапазоны начинаются с target.
...
Рейтинг: 0 / 0
16.11.2020, 19:58
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019115
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
Stax
цветов много, поетому пересечений может быть больше двух
Диапазоны source не пересекаются и не соприкасаются. То же самое для target.
...
Рейтинг: 0 / 0
16.11.2020, 19:59
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019123
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode
Вроде нигде с условиями не накосячил
Ну респект за усердие.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
create table t(x1 int, x2 int, c varchar2(10), flag varchar2(3));

exec dbms_random.seed(111);

declare
  strt   constant int := 0;
  offset constant int := 100;
  len    constant int := 100;
  colors constant int := 9;
  qty    constant int := 1e6;
  x1 int;
  x2 int;
  c  varchar2(4000);
begin
  for r in (select 'src' flag from dual union all select 'tgt' from dual) loop
    x2 := strt;
    for i in 1 .. qty loop
      x1 := 1 + x2 + trunc(dbms_random.value(1, offset + 1));
      x2 := x1 + trunc(dbms_random.value(1, len + 1));
      c  := 'COL' || trunc(dbms_random.value(1, colors + 1));
      insert into t values (x1, x2, c, r.flag);
    end loop;
  end loop;
end;
/

commit;



 
ctest.sql
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
set timing off
column hash format 999999999999999999999999999999

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2147483647;

set timing on

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

prompt ==========> graycode

with t1 (x, c_src, c_tgt, gr_src, gr_tgt) as
(
select x1 as x
     , decode(flag, 'src', c, ''), decode(flag, 'tgt', c, '')
     , decode(flag, 'src', 1, 0), decode(flag, 'tgt', 1, 0)
from t
union all
select x2 + 1 as x
     , '', ''
     , decode(flag, 'src', 1, 0), decode(flag, 'tgt', 1, 0)
  from t
)
, t2 (x, c_src, c_tgt, gr_src, gr_tgt) as
(
select x, c_src, c_tgt
     , sum(gr_src) over (order by x range unbounded preceding)
     , sum(gr_tgt) over (order by x range unbounded preceding)
  from t1
)
, t3 (x, c) as
(
select x
     , coalesce(min(c_src) keep (dense_rank first order by x) over (partition by gr_src),
                min(c_tgt) keep (dense_rank first order by x) over (partition by gr_tgt))
  from t2
)
, t4 (x, c) as
(
select min(x), min(c) from (
    select x, c, sum(sog) over (order by x) as gr from (
        select x, c
             , case nvl(c, 'none') when nvl(lag(c) over (order by x), 'none') then 0 else 1 end as sog
          from t3))
group by gr
)
, t5 (x1, x2, c) as
(
select x
     , lead(x) over (order by x) - 1
     , c
  from t4
)
select sum(x1*x2) hash from t5;

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

prompt ==========> kaban analyt

with tt as
(
select x1,
       nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c)) result,
       lag(nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c))) over(order by x1) prev_result
  from (select x1,
               sum(decode(flag, 'src', sign)) over(order by x1, type) src_active,
               sum(decode(flag, 'tgt', sign)) over(order by x1, type) tgt_active,
               last_value(decode(flag, 'src', c) ignore nulls) over(order by x1, type) src_c,
               last_value(decode(flag, 'tgt', c) ignore nulls) over(order by x1, type) tgt_c
          from (select c,
                       flag,
                       type,
                       x + decode(type, 'X2', 1, 0) x1,
                       decode(type, 'X1', 1, 'X2', -1) sign
                  from t unpivot(x for type in(x1, x2)))) t
)
select sum(x1*x2) hash
  from (select min(x1) x1,
               lead(min(x1)) over(order by grp) - 1 x2,
               min(result) result
          from (select tt.*, sum(decode(result, prev_result, 0, 1)) over(order by x1) grp from tt)
         group by grp)
 where x2 is not null;

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

prompt ==========> kaban pattern matching

select sum(x1*x2) hash
from
(
select t.*,
       nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c)) result
  from (select x1,
               sum(decode(flag, 'src', sign)) over(order by x1, type) src_active,
               sum(decode(flag, 'tgt', sign)) over(order by x1, type) tgt_active,
               last_value(decode(flag, 'src', c) ignore nulls) over(order by x1, type) src_c,
               last_value(decode(flag, 'tgt', c) ignore nulls) over(order by x1, type) tgt_c
          from (select c,
                       flag,
                       type,
                       x + decode(type, 'X2', 1, 0) x1,
                       decode(type, 'X1', 1, 'X2', -1) sign
                  from t unpivot(x for type in(x1, x2)))) t
)
match_recognize
(
  order by x1
  measures
    first(x.x1) x1,
    next(x.x1) - 1 x2,
    x.result result
  pattern (x+)
  define
    x as first(nvl(result, '~')) = nvl(result, '~') and next(x1) is not null
);

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

prompt ==========> neofit

with p as (
select * from (
  select x, flag, decode(z,'X1',c,'-') c, z
    from (select x1, x2+1 x2, c, flag from t) tt
 unpivot (x for z in (x1,x2))) 
 pivot (max(c) for flag in ('src' s,'tgt' t))
),
q as (
select x x1, lead(x) over (order by x) -1 x2, c from (
  select x, c, lag(c) over (order by x) cp from (
    select x, decode(nvl(s,'-'),'-',t,s) c from (
      select x,
             last_value(s ignore nulls) over (order by x) s,
             last_value(t ignore nulls) over (order by x) t
        from p
      )
    )
  )
 where c != nvl(cp,' ')
)
select sum(x1*x2) hash from q
 where x1 <= x2;

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

prompt ==========> graycode plsql

select sum(x1*x2) hash
  from table(f_superimpose(cursor (select src_rec_t(x1, x2, c, flag)
                              from t
                             order by x1, flag)));



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
SQL>@ctest.sql

Session altered.


Session altered.

==========> graycode

                           HASH
-------------------------------
         9910778652543263460375

Elapsed: 00:00:21.78
==========> kaban analyt

                           HASH
-------------------------------
         9910778652543263460375

Elapsed: 00:00:17.14
==========> kaban pattern matching

                           HASH
-------------------------------
         9910778652543263460375

Elapsed: 00:00:12.04
==========> neofit

                           HASH
-------------------------------
         9910778652543263460375

Elapsed: 00:00:12.48
==========> graycode plsql

                           HASH
-------------------------------
         9910778652543263460375

Elapsed: 00:00:17.53


То есть у неофита та же производительность что у моего финального.
У pl/sql та же производительность что у моего с аналитикой.
Твой с аналитикой самый медленный - ну там слишком дофига сортировок.
...
Рейтинг: 0 / 0
16.11.2020, 20:22
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019126
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
Там еще в начале теста можно было вставить
Код: plsql
1.
2.
3.
4.
5.
6.
7.
SQL> select count(*) cnt from t;

       CNT
----------
   2000000

Elapsed: 00:00:00.01

Чтоб показать что на ввод-вывод ресурсы не тратятся.
...
Рейтинг: 0 / 0
16.11.2020, 20:25
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019137
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

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

PS: pl/sql вариант конечно нужно переписать без объектной обертки, не знаю сколько она отъедает.
...
Рейтинг: 0 / 0
16.11.2020, 21:15
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019143
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
o! o!

я тоже хочу PL/SQL сделать!

Спасибо за тесты, очень образовательно.
...
Рейтинг: 0 / 0
16.11.2020, 21:41
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019144
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode,

Поставь ровнее, я ж только за.
...
Рейтинг: 0 / 0
16.11.2020, 21:43
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019147
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

Ты сам взялся за тесты производительности))

Проводить тесты нужно независимо, т.е. не друг за другом, состояние системы перед каждым тестом нужно приводить в исходное состояние и очень желательно нивелировать влияние физических чтений исходной таблицы с диска, кроме того то что ты продемонстрировал вообще одного порядка, т.е. реально оно все выполнилось за одинаковое время, нужно было довести время выполнения хотя бы до трех минут (как известно чем больше N тем сильнее расходятся асимптоты).

И ой, прошу прощения, не все вычистил, там кое что совсем лишнее, flag в сортировке не нужен для работы функции))
Код: plsql
1.
2.
3.
4.
select sum(x1*x2) hash
  from table(f_superimpose(cursor (select src_rec_t(x1, x2, c, flag)
                              from t
                             order by x1, flag)));
...
Рейтинг: 0 / 0
16.11.2020, 21:51
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019151
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode,

В тесте важно избежать физических чтений таблицы и ухода в темп на сортировках.
Можно было еще чуть минимизировать расходы на получения хеша.
Всё остальное - лирика.

В реальных данных все намного сложнее и это была лишь примитивная симуляция.

Не та эта задача где PL/SQL блещет как ты ни крути. :)
...
Рейтинг: 0 / 0
16.11.2020, 22:01
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019154
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

Еще раз, ты своим экспериментом продемонстрировал ... ничего, т.е. получил одинаковое время (одного порядка) и даже мой самый медленный и корявый вариант по твоему эксперименту не отличается от остальных))

Хочешь реально оценить, поставь эксперимент грамотно.
...
Рейтинг: 0 / 0
16.11.2020, 22:05
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019157
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode,

А кто-то говорил про отличия на порядки?
Если хочешь увидеть на порядки - потести варианты с коррелированными подзапросами.

Ты волен делать какие угодно выводы на основании таймингов или показать свой тест, но подобная демагогия это слив.

Можешь еще увеличивать число до 3e6, 5e6 и так далее пока не начнет вылезать в темп. И строить асимптоты.
...
Рейтинг: 0 / 0
16.11.2020, 22:28
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019159
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode,

Пример когда PL/SQL рулит - 11567017 .
Но там альтернатива была только модель.
С графиками. Думаю тебе должно понравиться. :)
...
Рейтинг: 0 / 0
16.11.2020, 22:40
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019167
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

Слив, это методика эксперимента))

Вот с графиками красиво и ... PL/SQL рулит ...

Переписал в виде пакета, без объектной обертки должно быть быстрее.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
create or replace package dropme_pkg as
    type res_rec_t is record (x1 t.x1%type, x2 t.x2%type, res t.c%type);
    type res_tab_t is table of res_rec_t;
    type refcur_t is ref cursor return t%rowtype;
    function f_superimpose return res_tab_t pipelined;
end dropme_pkg;
/

create or replace package body dropme_pkg as
    function f_superimpose return res_tab_t pipelined is
        c_refcur_t   refcur_t;
        l_res        res_rec_t;
        l_res_tmp    res_rec_t;
        l_src        t%rowtype;
        l_curr_flag  t.flag%type;
    begin
        open c_refcur_t for select * from t order by x1;
    
        fetch c_refcur_t into l_src;
        if c_refcur_t%NOTFOUND then 
            return;
        end if;
        
        l_res := res_rec_t(l_src.x1, l_src.x2, l_src.c);
        l_curr_flag := l_src.flag;

        loop
            fetch c_refcur_t into l_src;
            exit when c_refcur_t%NOTFOUND;
            
            -- разрыв
            if l_src.x1 - l_res.x2 > 1 then
                pipe row (l_res);
                l_res.x1 := l_res.x2 + 1;
                l_res.x2 := l_src.x1 - 1;
                l_res.res := '';
                pipe row (l_res);
                l_res.x1  := l_src.x1;
                l_res.x2  := l_src.x2;
                l_res.res := l_src.c;
                l_curr_flag := l_src.flag;
                continue;
            end if;
            
            -- касание
            if l_src.x1 - l_res.x2 = 1 then
                if l_src.c = l_res.res then
                    l_res.x2  := l_src.x2;
                    l_curr_flag := l_src.flag;
                else
                    pipe row (l_res);
                    l_res.x1  := l_src.x1;
                    l_res.x2  := l_src.x2;
                    l_res.res := l_src.c;
                    l_curr_flag := l_src.flag;
                end if;
                continue;
            end if;

            -- пересечение
            if l_src.x1 - l_res.x2 < 1 then
                if l_curr_flag = 'src' then
                    if l_src.x2 > l_res.x2 then
                        if l_src.c = l_res.res then
                            l_res.x2  := l_src.x2;
                            l_curr_flag := l_src.flag;
                        else
                            pipe row (l_res);
                            l_res.x1  := l_res.x2 + 1;
                            l_res.x2  := l_src.x2;
                            l_res.res := l_src.c;
                            l_curr_flag := l_src.flag;
                        end if;
                    end if;
                elsif l_curr_flag = 'tgt' then
                    if l_src.x2 >= l_res.x2 then
                        if l_src.c = l_res.res then
                            l_res.x2  := l_src.x2;
                            l_curr_flag := l_src.flag;
                        else
                            if l_src.x1 > l_res.x1 then
                                l_res.x2 := l_src.x1 - 1;
                                pipe row (l_res);
                            end if;
                            l_res.x1  := l_src.x1;
                            l_res.x2  := l_src.x2;
                            l_res.res := l_src.c;
                            l_curr_flag := l_src.flag;
                        end if;
                    else
                        if l_src.c != l_res.res then
                            if l_src.x1 > l_res.x1 then
                                l_res_tmp.x1  := l_res.x1;
                                l_res_tmp.x2  := l_src.x1 - 1;
                                l_res_tmp.res := l_res.res;
                                pipe row (l_res_tmp);
                            end if;
                            l_res_tmp.x1  := l_src.x1;
                            l_res_tmp.x2  := l_src.x2;
                            l_res_tmp.res := l_src.c;
                            pipe row (l_res_tmp);
                            l_res.x1 := l_src.x2 + 1;
                        end if;
                    end if;
                end if;
            end if;
        end loop;

        pipe row (l_res);
        close c_refcur_t;
    end;
end dropme_pkg;
/

select * from table(dropme_pkg.f_superimpose)

...
Рейтинг: 0 / 0
16.11.2020, 23:14
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019175
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
graycode
Переписал в виде пакета
В этом не было особой необходимости, Оракл всё равно под капотом для таких функций создает типы SYS_PLSQL_%
Пакет бы пригодился когда понадобилось бы параллелить функцию и объявлять strong ref cursor. ;)
graycode
без объектной обертки должно быть быстрее
А это улучшение, да.
Теперь на уровне ведущих эскуэльных подходов.
Мир, равенство, братсво.
...
Рейтинг: 0 / 0
16.11.2020, 23:35
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019179
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

Я твой тест из другого топика скоммуниздил и переделал под нашу задачу и что то не получаются твои результаты, ты бы позапускал и не один раз и на разном количестве записей ...

 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
declare
    l_timestamp timestamp;
    l_i         interval day to second;
    res         number;
begin
    l_timestamp := localtimestamp;

    select sum(x1*x2) into res
    from
    (
    select t.*,
           nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c)) result
      from (select x1,
                   sum(decode(flag, 'src', sign)) over(order by x1, type) src_active,
                   sum(decode(flag, 'tgt', sign)) over(order by x1, type) tgt_active,
                   last_value(decode(flag, 'src', c) ignore nulls) over(order by x1, type) src_c,
                   last_value(decode(flag, 'tgt', c) ignore nulls) over(order by x1, type) tgt_c
              from (select c,
                           flag,
                           type,
                           x + decode(type, 'X2', 1, 0) x1,
                           decode(type, 'X1', 1, 'X2', -1) sign
                      from t unpivot(x for type in(x1, x2)))) t
    )
    match_recognize
    (
      order by x1
      measures
        first(x.x1) x1,
        next(x.x1) - 1 x2,
        x.result result
      pattern (x+)
      define
        x as first(nvl(result, '~')) = nvl(result, '~') and next(x1) is not null
    );

    l_i := localtimestamp - l_timestamp;
    dbms_output.put_line('kaban pattern matching - ' || res || ' - ' ||
                         to_char(extract(second from l_i) * 1000 +
                                 extract(minute from l_i) * 60000));

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

    l_timestamp := localtimestamp;

    with tt as
    (
    select x1,
           nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c)) result,
           lag(nvl(decode(src_active, 1, src_c), decode(tgt_active, 1, tgt_c))) over(order by x1) prev_result
      from (select x1,
                   sum(decode(flag, 'src', sign)) over(order by x1, type) src_active,
                   sum(decode(flag, 'tgt', sign)) over(order by x1, type) tgt_active,
                   last_value(decode(flag, 'src', c) ignore nulls) over(order by x1, type) src_c,
                   last_value(decode(flag, 'tgt', c) ignore nulls) over(order by x1, type) tgt_c
              from (select c,
                           flag,
                           type,
                           x + decode(type, 'X2', 1, 0) x1,
                           decode(type, 'X1', 1, 'X2', -1) sign
                      from t unpivot(x for type in(x1, x2)))) t
    )
    select sum(x1*x2) into res
      from (select min(x1) x1,
                   lead(min(x1)) over(order by grp) - 1 x2,
                   min(result) result
              from (select tt.*, sum(decode(result, prev_result, 0, 1)) over(order by x1) grp from tt)
             group by grp)
     where x2 is not null;

    l_i := localtimestamp - l_timestamp;
    dbms_output.put_line('kaban analyt - ' || res || ' - ' ||
                         to_char(extract(second from l_i) * 1000 +
                                 extract(minute from l_i) * 60000));
  

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

    l_timestamp := localtimestamp;

    with p as (
    select * from (
      select x, flag, decode(z,'X1',c,'-') c, z
        from (select x1, x2+1 x2, c, flag from t) tt
     unpivot (x for z in (x1,x2))) 
     pivot (max(c) for flag in ('src' s,'tgt' t))
    ),
    q as (
    select x x1, lead(x) over (order by x) -1 x2, c from (
      select x, c, lag(c) over (order by x) cp from (
        select x, decode(nvl(s,'-'),'-',t,s) c from (
          select x,
                 last_value(s ignore nulls) over (order by x) s,
                 last_value(t ignore nulls) over (order by x) t
            from p
          )
        )
      )
     where c != nvl(cp,' ')
    )
    select sum(x1*x2) into res from q
     where x1 <= x2;

    l_i := localtimestamp - l_timestamp;
    dbms_output.put_line('neofit - ' || res || ' - ' ||
                         to_char(extract(second from l_i) * 1000 +
                                 extract(minute from l_i) * 60000));

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

    l_timestamp := localtimestamp;

    select sum(x1*x2) into res from table(dropme_pkg.f_superimpose);

    l_i := localtimestamp - l_timestamp;
    dbms_output.put_line('graycode plsql - ' || res || ' - ' ||
                         to_char(extract(second from l_i) * 1000 +
                                 extract(minute from l_i) * 60000));

end;

...
Рейтинг: 0 / 0
16.11.2020, 23:47
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019184
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

У тебя в том топике еще и bulk collect ...

НеофитSQL
я тоже хочу PL/SQL сделать!

Сделай не пайплайнед, а как у Кобанчег , в топике на который он ссылку дал.
...
Рейтинг: 0 / 0
17.11.2020, 00:08
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019222
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
graycode
Кобанчег,

У тебя в том топике еще и bulk collect ...

НеофитSQL
я тоже хочу PL/SQL сделать!

Сделай не пайплайнед, а как у Кобанчег , в топике на который он ссылку дал.


Я уже написал пайплайном, на вашем примере.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
create or replace function f_overlay(cur sys_refcursor) return res_tab_t pipelined is
  l_src     src_rec_t;
  l_back    src_rec_t := src_rec_t(0,0,null,null);
  l_last    res_rec_t;
  r         res_rec_t;
  function RangeUntil( x in number, c in varchar2 ) return res_rec_t is
    ret res_rec_t;
  begin
    if x <= l_last.x2 then return null; end if;
    
    if nvl(c,'clear') = nvl(l_last.res,'clear') then
      l_last.x2 := x;
      return null;
    end if;

    ret := res_rec_t(l_last.x1,l_last.x2-1,l_last.res);
    l_last.x1 := l_last.x2;
    l_last.x2 := x;
    l_last.res  := c;
    return ret;
  end;
begin
  loop
    fetch cur into l_src;
    exit when cur%NOTFOUND;
    l_src.x2 := l_src.x2+1;
    
    if l_last is null then 
      l_last := res_rec_t(l_src.x1, l_src.x1, l_src.c);
    end if;

    if l_src.flag = 'src' then
      r := RangeUntil( least(l_back.x2, l_src.x1), l_back.c );   if r is not null then pipe row (r); end if;
      r := RangeUntil( l_src.x1, null );                         if r is not null then pipe row (r); end if;
      r := RangeUntil( l_src.x2, l_src.c );                      if r is not null then pipe row (r); end if;
    else
      r := RangeUntil( l_back.x2, l_back.c );                    if r is not null then pipe row (r); end if;
      r := RangeUntil( l_src.x1, null );                         if r is not null then pipe row (r); end if;
      l_back := l_src;
    end if;
  end loop;
  close cur;

  r := RangeUntil( l_src.x2, l_src.c );  if r is not null then pipe row (r); end if;
  r := RangeUntil( l_src.x2+1, null );   if r is not null then pipe row (r); end if;
end;


Долго ломал голову как сделать условный pipe row (чтобы пустые строчки не плевало), но так и не смог.
Поэтому в правой части много повторяющегося кода.
...
Рейтинг: 0 / 0
17.11.2020, 02:08
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019224
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
Без пайплайна возможно быстрее. Надеюсь Кобанчег или другие бенчмаркнут.

 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
create or replace function f_overlay(cur sys_refcursor) return res_tab_t is
  l_src     src_rec_t;
  l_back    src_rec_t := src_rec_t(0,0,null,null);
  l_last    res_rec_t;
  rett      res_tab_t := res_tab_t();

  procedure RangeUntil( x in number, c in varchar2 ) is
  begin
    if x <= l_last.x2 then return; end if;
    
    if nvl(c,'clear') = nvl(l_last.res,'clear') then
      l_last.x2 := x;
      return;
    end if;

    rett.extend;
    rett(rett.last) := res_rec_t(l_last.x1,l_last.x2-1,l_last.res);
    l_last.x1 := l_last.x2;
    l_last.x2 := x;
    l_last.res  := c;
  end;

begin
  loop
    fetch cur into l_src;
    exit when cur%NOTFOUND;
    l_src.x2 := l_src.x2+1;
    
    if l_last is null then 
      l_last := res_rec_t(l_src.x1, l_src.x1, l_src.c);
    end if;

    if l_src.flag = 'src' then
      RangeUntil( least(l_back.x2, l_src.x1), l_back.c );
      RangeUntil( l_src.x1, null );
      RangeUntil( l_src.x2, l_src.c );
    else
      RangeUntil( l_back.x2, l_back.c );
      RangeUntil( l_src.x1, null );
      l_back := l_src;
    end if;
  end loop;
  close cur;

  RangeUntil( l_src.x2, l_src.c );
  RangeUntil( l_src.x2+1, null );
  return rett;
end;

...
Рейтинг: 0 / 0
17.11.2020, 02:25
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019364
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
НеофитSQL
Без пайплайна возможно быстрее. Надеюсь Кобанчег или другие бенчмаркнут.

С пайплайном мы по одной строчке перегоняем входящий отсортированный набор данных в выходной набор данных по которому потом еще идет суммирование, если весь входной набор отправить в коллекцию bulk collect-ом, обработать эту коллекцию не создавая новую и отдать ее в качестве результата, должно получиться быстрее.

PS: в реальных задачах нужно учитывать что коллекция будет содержать все данные таблицы и находиться она будет в UGA.
...
Рейтинг: 0 / 0
17.11.2020, 12:47
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019373
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
graycode
пайплайном мы по одной строчке перегоняем входящий отсортированный набор данных в выходной набор данных

Точно? :)
...
Рейтинг: 0 / 0
17.11.2020, 13:03
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019430
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
andrey_anonymous
Точно? :)

Мы точно не ползаем по исходному набору и не модифицируем его, мы читаем по строке, формируем новую в PL/SQL и отдаем обратно в SQL, где поверх еще идет суммирование, в SQL решениях одно исполняющее ядро SQL и возможность по максимуму использовать исходный набор данных не перекачивая его через сторонние структуры построчно.
...
Рейтинг: 0 / 0
17.11.2020, 14:17
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019437
НеофитSQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
НеофитSQL
Гость
graycode
НеофитSQL
Без пайплайна возможно быстрее. Надеюсь Кобанчег или другие бенчмаркнут.

С пайплайном мы по одной строчке перегоняем входящий отсортированный набор данных в выходной набор данных по которому потом еще идет суммирование, если весь входной набор отправить в коллекцию bulk collect-ом, обработать эту коллекцию не создавая новую и отдать ее в качестве результата, должно получиться быстрее.

PS: в реальных задачах нужно учитывать что коллекция будет содержать все данные таблицы и находиться она будет в UGA.


Если набор данных ужЕ в памяти, быстрее вызвать внешнюю функцию на сях :)

по поводу обработки коллекции in-place, там довольно неочевидная (для меня) оценка размера результата.
Вроде бы худший случай это 2N если нужно выводить незакрашенные интервалы, и 2N-1 если их можно пропускать.
(N- число строк в исходной таблице)
...
Рейтинг: 0 / 0
17.11.2020, 14:25
| Ответить | Цитировать | Написать  
Пятничная задача: Красное и черное
    #40019444
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
graycode
Гость
Кобанчег,

Погонял на таблице разного размера и генерации, однозначного победителя из трех вариантов kaban pattern matching, neofit, graycode plsql не выявил, лидером оказывается то один то другой то третий, причем pattern matching всегда очень близко к pl/sql варианту, neofit уходит то в плюс то в минус процентов на двадцать. Честно говоря от pl/sql варианта ожидал лучших результатов.
...
Рейтинг: 0 / 0
17.11.2020, 14:28
| Ответить | Цитировать | Написать  
25 сообщений из 176, страница 3 из 8
  3  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задача: Красное и черное
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40019137&tid=1880676]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 89ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 12ms
total:  187ms
созд. / загр.: 187ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]