ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
22 сообщений из 47, страница 2 из 2
  2  все
Пятнияная задачка: full equi join.
    #39375564
Nexxxt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nexxxt
Гость
Elic,
Код: 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.
with t1 as (
select 0 key, 'OO' value from dual
union all select 0 key, 'A1' value from dual
union all select -1 key, 'B1' value from dual
union all select -2 key, 'C1' value from dual
union all select -3 key, 'D1' value from dual
union all select -4 key, 'E1' value from dual
),
t2 as (
select 0 key, 'A2' value from dual
union all select 0 key, 'Z2' value from dual
union all select -1 key, 'B2' value from dual
union all select -5 key, 'F2' value from dual
union all select -10 key, 'X2' value from dual)
SELECT T1.KEY,T1.VALUE,
       T2.KEY,T2.VALUE
  FROM (SELECT MINV + LEVEL - 1 KEY 
          FROM (SELECT LEAST((SELECT NVL(MIN(T1.KEY),0) FROM T1),(SELECT NVL(MIN(T2.KEY),0) FROM T2)) MINV,
                       GREATEST((SELECT NVL(MAX(T1.KEY),0) FROM T1),(SELECT NVL(MAX(T2.KEY),0) FROM T2)) MAXV
                  FROM DUAL)
        CONNECT BY MINV + LEVEL - 1 <= MAXV) A
  LEFT JOIN T1 
    ON T1.KEY = A.KEY
  LEFT JOIN T2
    ON T2.KEY = A.KEY  
 WHERE NOT (T1.KEY IS NULL AND T2.KEY IS NULL)
...
Рейтинг: 0 / 0
26.12.2016, 19:38
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375567
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymxunion all detectedandreymx,

пропущено...
я могу убрать юнион и насвинячить прочей хрени, но это решение будет явно хужеЕсли говорить про перфоманс - то решение Винта предпочтительнее ибо универсальнее, а перфоманс такой же.

Код: plaintext
1.
2.
3.
create table t1 as select rownum key, lpad(' ', 100, ' ') value from dual connect by rownum <= 5e5;
create table t2 as select rownum+100 key, lpad(' ', 100, ' ') value from dual connect by rownum <= 5e5;
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
alter system flush buffer_cache;
alter session set statistics_level = all;
set timing on
create table t3 as
select key,
       max(case when t = 1 then value end) value_1,
       max(case when t = 2 then value end) value_2
  from (select key, value, 1 t from t1 union all select key, value, 2 t from t2)
 group by key;
select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));
select count(*) from t3;

Такой же тест выполнен для его запроса (без сортировки).

Код: 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.
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-----------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |      1 |        |      0 |00:00:08.88 |   33994 |  21908 |  21217 |
|   1 |  LOAD AS SELECT        |      |      1 |        |      0 |00:00:08.88 |   33994 |  21908 |  21217 |
|   2 |   HASH GROUP BY        |      |      1 |    500K|    500K|00:00:06.37 |   15394 |  21898 |   6510 |
|   3 |    VIEW                |      |      1 |   1000K|   1000K|00:00:02.98 |   15394 |  15388 |   0 |
|   4 |     UNION-ALL          |      |      1 |        |   1000K|00:00:02.77 |   15394 |  15388 |   0 |
|   5 |      TABLE ACCESS FULL | T1   |      1 |    500K|    500K|00:00:00.87 |    7697 |   7694 |   0 |
|   6 |      TABLE ACCESS FULL | T2   |      1 |    500K|    500K|00:00:01.48 |    7697 |   7694 |   0 |
-----------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT  |      |      1 |        |      0 |00:00:08.43 |   49389 |  30784 |  14707 |
|   1 |  LOAD AS SELECT         |      |      1 |        |      0 |00:00:08.43 |   49389 |  30784 |  14707 |
|*  2 |   HASH JOIN RIGHT OUTER |      |      1 |   1000K|    500K|00:00:06.31 |   30788 |  30774 |   0 |
|   3 |    TABLE ACCESS FULL    | T1   |      1 |    500K|    500K|00:00:00.66 |    7697 |   7694 |   0 |
|*  4 |    HASH JOIN OUTER      |      |      1 |   1000K|    500K|00:00:04.72 |   23091 |  23080 |   0 |
|   5 |     VIEW                |      |      1 |   1000K|    500K|00:00:02.12 |   15394 |  15387 |   0 |
|   6 |      SORT UNIQUE        |      |      1 |   1000K|    500K|00:00:02.04 |   15394 |  15387 |   0 |
|   7 |       UNION-ALL         |      |      1 |        |   1000K|00:00:01.40 |   15394 |  15387 |   0 |
|   8 |        TABLE ACCESS FULL| T1   |      1 |    500K|    500K|00:00:00.61 |    7697 |   7693 |   0 |
|   9 |        TABLE ACCESS FULL| T2   |      1 |    500K|    500K|00:00:00.44 |    7697 |   7694 |   0 |
|  10 |     TABLE ACCESS FULL   | T2   |      1 |    500K|    500K|00:00:01.83 |    7697 |   7693 |   0 |
------------------------------------------------------------------------------------------------------------



Аналог твоего без union, очевидно, будет похуже оригинала.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select nvl(x.key, t2.key) key,
       max(x.value_1) value_1,
       max(t2.value) value_2
  from (select x.type, t1.key, t1.value value_1
          from (select rownum type from (select * from dual group by rollup(dummy))) x, t1
         where nvl2(t1.key(+), 1, 1) = x.type) x, t2
 where nvl2(t2.key(+), 2, 2) = x.type
 group by nvl(x.key, t2.key)
 order by 1, 2, 3


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT      |      |      1 |        |      0 |00:00:12.82 |   33995 |  30507 |  29815 |
|   1 |  LOAD AS SELECT             |      |      1 |        |      0 |00:00:12.82 |   33995 |  30507 |  29815 |
|   2 |   HASH GROUP BY             |      |      1 |     25M|    500K|00:00:10.30 |   15397 |  30498 |  15108 |
|*  3 |    HASH JOIN OUTER          |      |      1 |     25M|   1000K|00:00:07.10 |   15397 |  22965 |   7575 |
|*  4 |     HASH JOIN OUTER         |      |      1 |   5000 |    500K|00:00:00.71 |    7700 |   7696 |      0 |
|   5 |      VIEW                   |      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   6 |       COUNT                 |      |      1 |        |      2 |00:00:00.01 |       3 |      2 |      0 |
|   7 |        VIEW                 |      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   8 |         SORT GROUP BY ROLLUP|      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   9 |          TABLE ACCESS FULL  | DUAL |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |      0 |
|  10 |      TABLE ACCESS FULL      | T1   |      1 |    500K|    500K|00:00:00.44 |    7697 |   7694 |      0 |
|  11 |     TABLE ACCESS FULL       | T2   |      1 |    500K|    500K|00:00:01.93 |    7697 |   7694 |      0 |
----------------------------------------------------------------------------------------------------------------


PS. Если вспомнить про перфоманс вариантов, где в условии соединения есть or - там вообще все печально.
Зато какое поле для творчества.
...
Рейтинг: 0 / 0
26.12.2016, 19:41
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375572
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender
 
уровень 2 - на иннер джойнах
Код: 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.
with 
  mkey as (select greatest(
                     (select nvl(max(key),0) from t1)
                    ,(select nvl(max(key),0) from t2)
                 ) max_key
           from dual
          )
,g as (select level - 1 as key from mkey connect by level<= max_key+1)
,v1 as (select key, row_number()over(partition by key order by value) rn1, value as value_1 from t1)
,v2 as (select key, row_number()over(partition by key order by value) rn2, value as value_2 from t2)
,gg as (select key
               ,nvl((select max(rn1) from v1 where v1.key=g.key),1) cnt1 
               ,nvl((select max(rn2) from v2 where v2.key=g.key),1) cnt2 
         from g
        )
select *
from (
   select gg.key, 
          (select v1.value_1 from v1 where v1.key=gg.key and v1.rn1 = g1.key) as value_1, 
          (select v2.value_2 from v2 where v2.key=gg.key and v2.rn2 = g2.key) as value_2
   from gg, g g1, g g2
   where 
         g1.key between 1 and gg.cnt1
     and g2.key between 1 and gg.cnt2
)
where value_1 is not null or value_2 is not null
order by 1, 2, 3
/

connect by - это уж для слишком вырожденных случаев, но такой подход через коррелированные скалярчики допиливается и без оного.
...
Рейтинг: 0 / 0
26.12.2016, 19:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375574
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopбез оного.а смысл? задача-то извращенческая и явно не на оптимизацию..
...
Рейтинг: 0 / 0
26.12.2016, 19:56
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375576
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Интересно, являются ли min/max операциями над множеством
...
Рейтинг: 0 / 0
26.12.2016, 19:59
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375577
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopПризнаться в моей заготовке тоже не был учтен случай пустоты одного из наборовНе туда глянул. :))
Версия "Без учета уникальности" - работает для всех случаев.
...
Рейтинг: 0 / 0
26.12.2016, 20:00
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375582
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
dbms_photoshopУровень 2. Можно использовать только inner joins.


Неясно можно ли использовать CONNECT BY. Если да:

Код: 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.
with x1 as (
            select  rownum rn,
                    t1.*
              from  t1
           ),
     x2 as (
            select  rownum rn,
                    t2.*
              from  t2
           ),
     c1 as (
            select  greatest(count(*),1) cnt
              from  t1
           ),
     c2 as (
            select  greatest(count(*),1) cnt
              from  t2
           ),
     l1 as (
            select  ceil(level / c2.cnt) t1_rn,
                    mod(level - 1,c2.cnt) + 1 t2_rn
              from  c1,
                    c2
              connect by level <= c1.cnt * c2.cnt
           ),
     l2 as (
            select  t1_rn,
                    t2_rn,
                    (
                     select  key
                       from  x1
                       where rn = t1_rn
                    ) t1_key,
                    (
                     select  key
                       from  x2
                       where rn = t2_rn
                    ) t2_key
              from  l1
           )
select  case
          when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
          else t2_key
        end key,
        case
          when t1_key = t2_key or t2_rn = 1 then (
                                                  select  value
                                                    from  x1
                                                    where rn = t1_rn
                                                 )
        end value_1,
        case
          when t1_key = t2_key or t1_rn = 1 then (
                                                  select  value
                                                    from  x2
                                                    where rn = t2_rn
                                                 )
        end value_2
  from  l2
  where t1_key = t2_key
     or (
             l2.t2_rn = 1
         and
              not exists(
                         select  1
                           from  l2 l22
                           where l2.t1_rn = l22.t1_rn
                             and l2.t1_key = l22.t2_key
                        )
        )
     or (
             l2.t1_rn = 1
         and
              not exists(
                         select  1
                           from  l2 l22
                           where l2.t2_rn = l22.t2_rn
                             and l2.t2_key = l22.t1_key
                        )
        )
  order by key,
           value_1,
           value_2
/



 
Результат
Код: 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.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0 A1 A2
         0 A1 Z2
         0 OO A2
         0 OO Z2
         1 B1 B2
         2 C1
         3 D1
         4 E1
         5    F2
        10    X2

10 rows selected.

SQL> delete t1
  2  /

6 rows deleted.

SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0    A2
         0    Z2
         1    B2
         5    F2
        10    X2

SQL> rollback
  2  /

Rollback complete.

SQL> delete t2
  2  /

5 rows deleted.

SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0 A1
         0 OO
         1 B1
         2 C1
         3 D1
         4 E1

6 rows selected.

SQL>




SY.
...
Рейтинг: 0 / 0
26.12.2016, 20:03
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375583
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderdbms_photoshopбез оного.а смысл? задача-то извращенческая и явно не на оптимизацию..Если любое упражнение на смекалку для тебя извращение - не совсем понятно зачем ты это решал.
Мог бы просто брызнуть желчью как Элик и всё.

Смысл
...
Рейтинг: 0 / 0
26.12.2016, 20:05
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375588
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymxИнтересно, являются ли min/max операциями над множествомВидимо я нечетко сформулировал.
Не операции, а операторы.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm]The UNION [ALL], INTERSECT, MINUS Operators
...
Рейтинг: 0 / 0
26.12.2016, 20:11
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375590
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

так я гольфил пошагово, а не оптимизировал, т.к. условия изначально так поставлены, что нужно ухудшить стандартный FOJ
...
Рейтинг: 0 / 0
26.12.2016, 20:12
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375594
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

Всё, всё, больше не задеваю твое эго. Спасибо за участие в любом случае.
...
Рейтинг: 0 / 0
26.12.2016, 20:14
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375609
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshopandreymxИнтересно, являются ли min/max операциями над множествомВидимо я нечетко сформулировал.
Не операции, а операторы.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm]The UNION [ALL], INTERSECT, MINUS Operators ведь джойн - это тоже операция над множествами :))
...
Рейтинг: 0 / 0
26.12.2016, 20:43
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375610
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SYdbms_photoshopУровень 2. Можно использовать только inner joins.


Неясно можно ли использовать CONNECT BY.Весьма универсально. connect by не завязан на тип данных ключа.
...
Рейтинг: 0 / 0
26.12.2016, 20:45
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375621
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopЭто не фулл джойнORA__SQL, магия данных

Код: plsql
1.
2.
delete from t1;
select * from t1 full join t2 on t1.key = t2.key;

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

 
Если ключ уникален
Код: 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.
select key, max(value_1) value1, max(value_2) value_2
  from (select rownum rn,
               t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               nvl(t1.key, t2.key) key
          from (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t1
                 where nvl2(t1.key(+), 1, 1) = x.type) t1,
               (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t2
                 where nvl2(t2.key(+), 1, 1) = x.type) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1))
 group by decode(type_1, type_2, rn, null), key
having count(*) = 1 or decode(type_1, type_2, rn, null) is not null
 order by 1, 2, 3


 
Без учета уникальностиПодход аналогичный ORA__SQL, вместо exists используется аналитика.
Код: 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.
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.type, t2.type, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.type, t2.type, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t1
                 where nvl2(t1.key(+), 1, 1) = x.type) t1,
               (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t2
                 where nvl2(t2.key(+), 1, 1) = x.type) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1)
         order by type_1, type_2, nvl(key_1, key_2))
 where type_1 = type_2
    or key_1 is not null
   and c1 = 0
    or key_2 is not null
   and c2 = 0
 order by 1, 2, 3


 
Всего одно внутреннее соединение
Код: 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.
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.type, t2.type, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.type, t2.type, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from (select grouping_id(rowid) + 1 as type,
                       decode(grouping_id(rowid), 0, max(key)) key,
                       decode(grouping_id(rowid), 0, max(value)) value
                  from (select * from t1)
                 group by grouping sets(rowid,())) t1,
               (select grouping_id(rowid) + 1 as type,
                       decode(grouping_id(rowid), 0, max(key)) key,
                       decode(grouping_id(rowid), 0, max(value)) value
                  from (select * from t2)
                 group by grouping sets(rowid,())) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1)
         order by type_1, type_2, nvl(key_1, key_2))
 where type_1 = type_2
    or key_1 is not null and c1 = 0
    or key_2 is not null and c2 = 0
 order by 1, 2, 3

Надо еще включить фикс для пустоты. :)Один inner join с фиксом для пустоты.
Код: 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.
with
tt1 as
(
select key, value
from t1
where key is not null
model unique single reference dimension by (key) measures (value) rules (value[null]=null)
),
tt2 as
(
select key, value
from t2
where key is not null
model unique single reference dimension by (key) measures (value) rules (value[null]=null)
)
-- ^^^^^ ugly fix ^^^^^
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.key key_1,
               t1.value value_1,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.key, t2.key, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.key, t2.key, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from tt1 t1, tt2 t2
         where t1.key = t2.key
            or (t1.key is not null and t2.key is null)
            or (t2.key is not null and t1.key is null))
 where key_1 = key_2
    or key_1 is not null and c1 = 0
    or key_2 is not null and c2 = 0
 order by 1, 2, 3
...
Рейтинг: 0 / 0
26.12.2016, 20:56
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375645
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopElicты слегка оторван от жизниСоединения, работа с наборами данныхВ работе нужно максимально эффективно (не только с точки зрения узколобой производительности) использовать подходящие к месту декларативные возможности языка, а не убегать от них.
dbms_photoshopи реляционная алгебра уже не в тренде?А вот тут ты точно оторвался. Эта сухая теоретичка в практической жизни не нужна. Одно деление чего только не стоит.

dbms_photoshopПризнаться в моей заготовке тоже не был учтен случай пустоты одного из наборов.
Код: plsql
1.
2.
model
-- ^^^^^ ugly fix ^^^^^

Стоило ли банить full с union-ом, чтобы притащить за уши уродца?
...
Рейтинг: 0 / 0
26.12.2016, 21:42
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375649
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Elic,

Да сколько же можно объяснять.
Упражнение было, чтоб размять мозги.

PS. Что не так с "учтен"? Ожидается ё?
...
Рейтинг: 0 / 0
26.12.2016, 21:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375652
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopОжидается ё?Почему-то носители забыли, что буквы не зубы.
...
Рейтинг: 0 / 0
26.12.2016, 21:55
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375788
NVL2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVL2
Гость
Фотошоп, большое человечье спасибо тебе за трюк с nvl2 20042086
...
Рейтинг: 0 / 0
27.12.2016, 09:18
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381788
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshopЕще для уровня 1 можно решить за один проход каждой из таблиц, но при условии, что ключи уникальны .

union all detectedandreymx,

авторбез использования union (и любых других операций над множествами)
andreymxя могу убрать юнион и насвинячить прочей хрени, но это решение будет явно хуже

сильно хуже не стало
Код: 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.
WITH t1 AS
 (
  SELECT 1 r, 0 KEY, 'A1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 1 KEY, 'B1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 2 KEY, 'C1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 3 KEY, 'D1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 4 KEY, 'E1' VALUE FROM dual
  ),
T2 AS
 (
  SELECT 2 r,  0 KEY, 'Z2' VALUE FROM dual  UNION ALL
  SELECT 2 r,  1 KEY, 'B2' VALUE FROM dual  UNION ALL
  SELECT 2 r,  5 KEY, 'F2' VALUE FROM dual  UNION ALL
  SELECT 2 r, 10 KEY, 'X2' VALUE FROM dual)
SELECT NVL(t1.KEY, T2.KEY) KEY,
       MAX(T1.VALUE) value_1,
       MAX(T2.VALUE) value_2
  FROM t1,
       T2,
       (SELECT ROWNUM rn FROM dual connect BY ROWNUM < 3) D
 WHERE t1.r(+) = D.rn
   AND T2.r(+) = D.rn
 GROUP BY NVL(t1.KEY, T2.KEY)
 order by key

KEYVALUE_1VALUE_20A1Z21B1B22C13D14E15F210X2
...
Рейтинг: 0 / 0
10.01.2017, 18:53
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381827
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymx,

Да это же самый настоящий аналог аналога. :)

Аналог твоего без union, очевидно, будет похуже оригинала.
...
Рейтинг: 0 / 0
10.01.2017, 20:01
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381829
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshop,

там был юнион!
...
Рейтинг: 0 / 0
10.01.2017, 20:07
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381849
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymx,

Ты прочитал мой пост по приведенной ссылке? Полностью?
Интересно узнать где там union после процитированных слов.
...
Рейтинг: 0 / 0
10.01.2017, 20:38
| Ответить | Цитировать | Написать  
22 сообщений из 47, страница 2 из 2
  2  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39375576&tid=1886689]:
 0ms
get settings:
 9ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 166ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 251ms
total:  505ms
созд. / загр.: 505ms
x
x
Закрыть


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