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

Откуда:
Сообщений: 19
Пытливый ум покоя не дает.

Oracle
CROSS JOIN : Specifies a join that produces the Cartesian product of two tables. It has no explicit join clause.

+ Тут всё логично
> with
t1 as (
select 1 as q1, 2 as q2 from dual union all
select 3, 4 from dual union all
select 5, 6 from dual
)
, t2 as (
select 1 as q1, 2 as q2 from dual union all
select 3, 4 from dual union all
select 5, 6 from dual
)
select *
from t1
cross join t2
Q1 Q2 Q1 Q2
---------- ---------- ---------- ----------
1 2 1 2
1 2 3 4
1 2 5 6
3 4 1 2
3 4 3 4
3 4 5 6
5 6 1 2
5 6 3 4
5 6 5 6

9 rows selected

But, wth? :
Shizick
> with
t1 as (
select 1 as q1, 2 as q2 from dual union all
select 3, 4 from dual union all
select 5, 6 from dual
)
, t2 as (
select 1 as q1, 2 as q2 from dual union all
select 3, 4 from dual union all
select 5, 6 from dual
)
select *
from t1
cross join t1
Q1 Q2 Q1 Q2
---------- ---------- ---------- ----------
1 2 1 2
3 4 3 4
5 6 5 6
1 2 1 2
3 4 3 4
5 6 5 6
1 2 1 2
3 4 3 4
5 6 5 6

9 rows selected

Почему мы не видим Декартова произведения множества строк когда делаем self-cj?
12 янв 15, 08:35    [17104167]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Elic
Member

Откуда:
Сообщений: 29976
Shizick
Почему мы не видим Декартова произведения множества строк когда делаем self-cj?
Тебе стоит протереть глаза.
А также радоваться отсутствию ORA-32035.
12 янв 15, 08:41    [17104179]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
Elic
Тебе стоит протереть глаза.
А также радоваться отсутствию ORA-32035.

Ну на счет Декартова я может и неправильно выразился.
Привел пример в такой вид:
Shizick
> with
t1 as (
select 1 as q1, 2 as q2 from dual union all
select 3, 4 from dual union all
select 5, 6 from dual
)
select *
from t1
cross join t1
order by 1
Q1 Q2 Q1 Q2
---------- ---------- ---------- ----------
1 2 1 2
1 2 1 2
1 2 1 2
3 4 3 4
3 4 3 4
3 4 3 4
5 6 5 6
5 6 5 6
5 6 5 6

9 rows selected

Почему происходит построковое соединение таблиц?
12 янв 15, 09:19    [17104235]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick,

без версии сервера - гадать бессмысленно. ибо у меня на 10.2.0.5 и на 11.2.0.4 работает как надо...
12 янв 15, 09:24    [17104242]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Elic
Member

Откуда:
Сообщений: 29976
Shizick
Почему происходит построковое соединение таблиц?
А понимаешь ли ты, что есть декартово произведение (cross join - Cartesian product)?
12 янв 15, 09:25    [17104246]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick,

а вообще, скорее всего, оптимизатор тупо "запутался" в названиях полей/таблиц.
попробуй добавить алиасы к каждой версии таблицы. возможно, результат изменится:
 with
    t1 as (
      select 1 as q1, 2 as q2 from dual union 
      select 3, 4 from dual union 
      select 5, 6 from dual
  )
select *
  from t1 t1
    cross join t1 t2
order by 1
12 янв 15, 09:28    [17104255]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
попробуй добавить алиасы к каждой версии таблицы.
На самом деле - достаточно добавить алиас к любой одной из версии таблиц...

З.Ы.
а на версии 10gR2 запрос должен был упасть с ошибкой ORA-00918, если что...
12 янв 15, 09:43    [17104313]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
Добрый Э - Эх
без версии сервера - гадать бессмысленно

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Elic
А понимаешь ли ты, что есть декартово произведение (cross join - Cartesian product)?

Примерно как перемножение всех членов разных множест, но в приведенном примере строки соеднияются только по rowid, а не как множество перебора всех пар.
Добрый Э - Эх
попробуй добавить алиасы к каждой версии таблицы. возможно, результат изменится

Изменился. Как желудок закончит свою работу, я обязательно вернусь к осмыслению этой ситуации :D
Спасибо.
12 янв 15, 09:46    [17104327]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Попробовал вместо cartesian nested loops - то же самое:
select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Затрач.время: 00:00:00.01

with
    t1 as (
      select 1 as q1, 2 as q2 from dual union all
      select 3, 4 from dual union all
      select 5, 6 from dual
  )
select /*+ gather_plan_statistics */ *
  from t1
    cross join t1
order by 1;

        Q1         Q2         Q1         Q2
---------- ---------- ---------- ----------
         1          2          1          2
         1          2          1          2
         1          2          1          2
         3          4          3          4
         3          4          3          4
         3          4          3          4
         5          6          5          6
         5          6          5          6
         5          6          5          6

9 строк выбрано.

Затрач.время: 00:00:00.01

select * from table(dbms_xplan.display_cursor(null,null,'allstats  last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  26f7y7caya9h8, child number 0
-------------------------------------
with     t1 as (       select 1 as q1, 2 as q2 from dual union all
 select 3, 4 from dual union all       select 5, 6 from dual   ) select
/*+ gather_plan_statistics */ *   from t1     cross join t1 order by 1

Plan hash value: 2605877701

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |      1 |        |      9 |00:00:00.01 |      14 |      1 |      1 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |      1 |        |      9 |00:00:00.01 |      14 |      1 |      1 |       |       |          |
|   2 |   LOAD AS SELECT           |                             |      1 |        |      0 |00:00:00.01 |       4 |      0 |      1 |   269K|   269K|  269K (0)|
|   3 |    UNION-ALL               |                             |      1 |        |      3 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   4 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   5 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   6 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   7 |   SORT ORDER BY            |                             |      1 |      9 |      9 |00:00:00.01 |       7 |      1 |      0 |  2048 |  2048 | 2048  (0)|
|   8 |    MERGE JOIN CARTESIAN    |                             |      1 |      9 |      9 |00:00:00.01 |       7 |      1 |      0 |       |       |          |
|   9 |     VIEW                   |                             |      1 |      3 |      3 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D69CC_DC22924A |      1 |      3 |      3 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  11 |     BUFFER SORT            |                             |      3 |      3 |      9 |00:00:00.01 |       2 |      0 |      0 |  2048 |  2048 | 2048  (0)|
|  12 |      VIEW                  |                             |      1 |      3 |      3 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
|  13 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D69CC_DC22924A |      1 |      3 |      3 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


31 строк выбрано.

Затрач.время: 00:00:00.04

with
    t1 as (
      select 1 as q1, 2 as q2 from dual union all
      select 3, 4 from dual union all
      select 5, 6 from dual
  )
select /*+ gather_plan_statistics no_use_merge(t1 t1) */ *
  from t1
    cross join t1
order by 1;

        Q1         Q2         Q1         Q2
---------- ---------- ---------- ----------
         1          2          1          2
         1          2          1          2
         1          2          1          2
         3          4          3          4
         3          4          3          4
         3          4          3          4
         5          6          5          6
         5          6          5          6
         5          6          5          6

9 строк выбрано.

Затрач.время: 00:00:00.01

select * from table(dbms_xplan.display_cursor(null,null,'allstats  last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  3dxrbn8ztuusb, child number 0
-------------------------------------
with     t1 as (       select 1 as q1, 2 as q2 from dual union all
 select 3, 4 from dual union all       select 5, 6 from dual   ) select
/*+ gather_plan_statistics no_use_merge(t1 t1) */ *   from t1     cross
join t1 order by 1

Plan hash value: 1488330927

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |      1 |        |      9 |00:00:00.01 |      18 |      1 |      1 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |      1 |        |      9 |00:00:00.01 |      18 |      1 |      1 |       |       |          |
|   2 |   LOAD AS SELECT           |                             |      1 |        |      0 |00:00:00.01 |       4 |      0 |      1 |   269K|   269K|  269K (0)|
|   3 |    UNION-ALL               |                             |      1 |        |      3 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   4 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   5 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   6 |     FAST DUAL              |                             |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   7 |   SORT ORDER BY            |                             |      1 |      9 |      9 |00:00:00.01 |      11 |      1 |      0 |  2048 |  2048 | 2048  (0)|
|   8 |    NESTED LOOPS            |                             |      1 |      9 |      9 |00:00:00.01 |      11 |      1 |      0 |       |       |          |
|   9 |     VIEW                   |                             |      1 |      3 |      3 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  10 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D69D8_DC22924A |      1 |      3 |      3 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
|  11 |     VIEW                   |                             |      3 |      3 |      9 |00:00:00.01 |       6 |      0 |      0 |       |       |          |
|  12 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D69D8_DC22924A |      3 |      3 |      9 |00:00:00.01 |       6 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


31 строк выбрано.

Затрач.время: 00:00:00.07
12 янв 15, 11:37    [17104921]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
AmKad,

потому что проблема не в способе соединения наборов данных, а в разрешении имен полей итогового датасета. грубо говоря, оптимизатор выполняет запрос вида Select t.*, t.*. И тут он тупо не понимает, что это две разные версии одной и той же таблицы, считая список полей - принадлежащей одной им той же таблице и просто дважды повторяет поля первой копии таблицы. в древних версия запрос бы упал с ошибкой : ORA-00918 - столбец определен неоднозначно. поэтому проблему и решает добавление алиаса на одну из версий таблицы....
12 янв 15, 11:46    [17104997]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
Добрый Э - Эх
принадлежащей одной им той же таблице и просто дважды повторяет поля первой копии таблицы

Мне кажется что это направильная логика для декартовых множеств...

self join "select * from t1, t1", что есть прямой аналог cross join, вылетает в 00918.
12 янв 15, 12:02    [17105132]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick,

я же говорю - на версиях 10gR2 оно так же благополучно выпадает по ORA-00918. в любом синтаксисе. хоть старообрядном запятушном, хоть в новомодном кросс-джойновом.
в 11g чего-то "перекрутили", стало работать немного иначе. Возможно, в 12c чего-то ещё подкрутили. нужно проверять
12 янв 15, 12:08    [17105161]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
Не смог удержаться и задал этот же вопрос на форуме оракла.
В 12с та же проблема...
Может кто на MsSql проверить результат? Инсталлить влом ))
13 янв 15, 05:12    [17108711]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick,

ставить в лом - иди на http://sqlfiddle.com/, выбирай интересуемую СУБД (доступны одна версия Оракла, по две версии MS SQL Server-а и SQLite-а, три версии MySQL и 5 версий PostgreSQL) и проверяй себе на здоровье...
13 янв 15, 06:30    [17108724]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick,

все остальные сервера банально ругаются на некорректность повторного использования одного и того же имени источника данных, требуя задавать алиас хотя бы для одной из копий таблицы, чтобы именя используемых в запросе источников данных были уникальны. посему, тут косяк оракла в том, что он не генерирует соответствующее сообщение об ошибке. в этом плане 10-ка работала более правильно, если сравнивать поведение с другими СУБД
13 янв 15, 06:41    [17108730]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
Добрый Э - Эх
проверяй себе на здоровье...

Проверяю ора11 - он даже с альясами обрабатывает как без них :D MsSql после этого чекать не стал...
Всем спасибо.
13 янв 15, 07:33    [17108762]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Shizick
Проверяю ора11 - он даже с альясами обрабатывает как без них :D MsSql после этого чекать не стал...


Не знаю что и как ты проверяешь, но с alias все как и должно быть:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> with
  2      t1 as (
  3        select 1 as q1, 2 as q2 from dual union all
  4        select 3, 4 from dual union all
  5        select 5, 6 from dual
  6    )
  7  select *
  8    from t1
  9      cross join t1
 10  order by 1;

        Q1         Q2         Q1         Q2
---------- ---------- ---------- ----------
         1          2          1          2
         1          2          1          2
         1          2          1          2
         3          4          3          4
         3          4          3          4
         3          4          3          4
         5          6          5          6
         5          6          5          6
         5          6          5          6

9 rows selected.

SQL> with
  2      t1 as (
  3        select 1 as q1, 2 as q2 from dual union all
  4        select 3, 4 from dual union all
  5        select 5, 6 from dual
  6    )
  7  select *
  8    from t1
  9      cross join t1 t2
 10  order by 1;

        Q1         Q2         Q1         Q2
---------- ---------- ---------- ----------
         1          2          1          2
         1          2          3          4
         1          2          5          6
         3          4          1          2
         3          4          3          4
         3          4          5          6
         5          6          1          2
         5          6          3          4
         5          6          5          6

9 rows selected.

SQL> 


SY.
13 янв 15, 15:30    [17111467]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Добрый Э - Эх
в древних версия запрос бы упал с ошибкой : ORA-00918 - столбец определен неоднозначно.


В древних версиях не было ANSI join. Native join синтакс как выдавал так и выдает:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> with
  2      t1 as (
  3        select 1 as q1, 2 as q2 from dual union all
  4        select 3, 4 from dual union all
  5        select 5, 6 from dual
  6    )
  7  select *
  8    from t1,t1
  9  /
select *
       *
ERROR at line 7:
ORA-00918: column ambiguously defined


SQL> 


Так-что дело в ANSI joins.

SY.
13 янв 15, 15:35    [17111502]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
имхо, смело можно добавлять сабж в копилку

баги ansy
13 янв 15, 15:43    [17111559]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
SY,
9-КА и 10-КА тоже уже древние, если что. И в них ansi-join уже вполне себе есть, насколько я помню...
13 янв 15, 16:13    [17111845]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Добрый Э - Эх
9-КА и 10-КА тоже уже древние, если что. И в них ansi-join уже вполне себе есть, насколько я помню...


И что, в 9i/10g ANSI CROSS JOIN выдавал ORA-00918? Проблема не в версии а в оракловской имплементации ANSI CROSS JOIN. SQL Server корректно выдет свой аналог ORA-00918 на:

with
    t1(q1, q2) as (
      select 1 as q1, 2 as q2 union all
      select 3, 4 union all
      select 5, 6
  )
select *
  from t1
    cross join t1
order by 1;
Msg 1011, Level 16, State 1, Line 1
The correlation name 't1' is specified multiple times in a FROM clause.


SY.
13 янв 15, 17:32    [17112371]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
stax..
Guest
SY
Добрый Э - Эх
в древних версия запрос бы упал с ошибкой : ORA-00918 - столбец определен неоднозначно.


В древних версиях не было ANSI join. Native join синтакс как выдавал так и выдает:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> with
  2      t1 as (
  3        select 1 as q1, 2 as q2 from dual union all
  4        select 3, 4 from dual union all
  5        select 5, 6 from dual
  6    )
  7  select *
  8    from t1,t1
  9  /
select *
       *
ERROR at line 7:
ORA-00918: column ambiguously defined


SQL> 


Так-что дело в ANSI joins.

SY.


ет понятно
не понятно почему ошибка указывает на *, ведь оракля может и одинаковые поля выводить при разных алиасах (таблицах)

напр select * from t1,t1 t2

как програмно узнать что второе поле q1 из т2
вопрос чисто поговорить, я * в таких случаях не пользовал

.....
stax
13 янв 15, 18:13    [17112589]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
SY
И что, в 9i/10g ANSI CROSS JOIN выдавал ORA-00918?
за 9-ку не скажу, нет её уже в доступе. а 10-ка (10.2.0.5 х64) именно с такой ошибкой и падает. в любом синтаксисе. хоть старообрядном запятушном, хоть в новомодном кросс-джойновом.
13 янв 15, 18:28    [17112661]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Shizick
Member

Откуда:
Сообщений: 19
SY
Не знаю что и как ты проверяешь, но с alias все как и должно быть:

Речь о:
Добрый Э - Эх
иди на http://sqlfiddle.com/, выбирай интересуемую СУБД
14 янв 15, 11:55    [17115531]     Ответить | Цитировать Сообщить модератору
 Re: Cross join таблицы саму на себя  [new]
Добрый Э - Эх
Guest
Shizick
Речь о:
ну да, там движок ещё более тупой, как выяснилось. если набор полей повторяется, то выводится лишь первое упоминание поля, второе - подавляется. поэтому там вообще невозможно получить набор данных с одинаковыми именами разных полей. и результат запроса в итоге содержит всего два поля, вместо 4-х ожидаемых. даже если в этих полях будут содержаться разные данные...
14 янв 15, 12:02    [17115602]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить