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

Откуда: loopback
Сообщений: 49744
Влияет-ли на результат запроса порядок соедиений в плане, при условии если одно из них внешнее?
22 июн 10, 15:42    [8980873]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
ALocky
Member

Откуда: Понаехал из Подмосковья
Сообщений: 747
mayton,

На результат запроса влияет текст запроса. План запроса на результат запроса влиять не должен.
22 июн 10, 15:48    [8980957]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Изя Кацман
Member

Откуда: Великий Эксперимент
Сообщений: 2019
SELECT * 
FROM DEPT 
      LEFT OUTER JOIN 
     EMP ON (EMP.DEPTNO = DEPT.DEPTNO)
Может здесь в плане таблица EMP быть первой?
22 июн 10, 15:51    [8981003]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Изя Кацман
SELECT * 
FROM DEPT 
      LEFT OUTER JOIN 
     EMP ON (EMP.DEPTNO = DEPT.DEPTNO)
Может здесь в плане таблица EMP быть первой?
Конечно.
SELECT * 
  2  FROM DEPT 
  3        LEFT OUTER JOIN 
  4       EMP ON (EMP.DEPTNO = DEPT.DEPTNO);

Execution Plan
----------------------------------------------------------
Plan hash value: 425244018

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 10000 |   253K|     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      | 10000 |   253K|     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | EMP  |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | DEPT | 10000 |   126K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
22 июн 10, 16:00    [8981096]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Изя Кацман
Member

Откуда: Великий Эксперимент
Сообщений: 2019
wurdu
SELECT * 
  2  FROM DEPT 
  3        LEFT OUTER JOIN 
  4       EMP ON (EMP.DEPTNO = DEPT.DEPTNO);

Execution Plan
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 10000 |   253K|     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      | 10000 |   253K|     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | EMP  |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | DEPT | 10000 |   126K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Ух хитрый Oracle! Переставил таблы плейсами!)))
22 июн 10, 16:16    [8981299]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Серафимный Шестикрыл
Member [заблокирован]

Откуда: С луны свалился
Сообщений: 2922
mayton
Влияет-ли на результат запроса порядок соедиений в плане, при условии если одно из них внешнее?


Это в связи с чем такой вопрос ? Уж не нарвался ли ты на два OUTER JOIN-а с одной внутренней таблицей в ANSI SQL ?
22 июн 10, 16:16    [8981302]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mayton
Member

Откуда: loopback
Сообщений: 49744
Серафимный Шестикрыл
Это в связи с чем такой вопрос ? Уж не нарвался ли ты на два OUTER JOIN-а с одной внутренней таблицей в ANSI SQL ?

Нет. Есть соединение трёх таблиц. (t1, t2, t3). Вторая и третья соединяются через внешнее соединение. Результат - корректен.

Добавляю четвёртую таблицу-справочник (t4) обычным соединением. И из запроса исчезают строки, которые исчезать не должны.

Проблему решили с момощью inline-views но очень не красиво.

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

Т.е. соединение

t1 = t2 = (+) t3 = t4

не эквивалентно

(t1 = t2) = (+) (t3 = t4).
22 июн 10, 16:25    [8981427]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Серафимный Шестикрыл
Member [заблокирован]

Откуда: С луны свалился
Сообщений: 2922
mayton
Добавляю четвёртую таблицу-справочник (t4) обычным соединением. И из запроса исчезают строки, которые исчезать не должны.


А чего ты ожидал ?

mayton

Проблему решили с момощью inline-views но очень не красиво.

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

Т.е. соединение

t1 = t2 = (+) t3 = t4

не эквивалентно

(t1 = t2) = (+) (t3 = t4).


"Реляционная алгебра" здесь вообще перпендикулярна,
поскольку внешнее соединение не является "соединением" с точки зрения классической
реляционной теории.

А если ты слегка напряжешься, то поймешь в чем разница.
И почему твои примеры не эквивалентны.
22 июн 10, 16:51    [8981738]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Elic
Member

Откуда:
Сообщений: 29979
mayton
Т.е. соединение

t1 = t2 = (+) t3 = t4

не эквивалентно

(t1 = t2) = (+) (t3 = t4).
Ты попросил при отсутствии связи t2 с t3 возвращать null-ы в t3. Дальше ты эти null-ы просишь связать невнешне с t4. Дальше додумаешь?
22 июн 10, 16:55    [8981786]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
mayton


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


Не важен. Формально, сначала получаем декартово произведение всех таблиц. Затем применяем предикаты отбора нужных строк.

У тебя скорее всего получились пустые поля во внешне соединённых записях t3, естественно для пустого поля из t3 запрос не нашёл запись из t4 и отбросил её.

mayton

Т.е. соединение

t1 = t2 = (+) t3 = t4

не эквивалентно

(t1 = t2) = (+) (t3 = t4).


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

t1 = t2 = (+) t3 = (+) t4

Если неохота присоединять t4 внешне t4, используй nvl(t3.код, 0) = t4.код. Где 0 - некий словарный код для незаданных значений.
22 июн 10, 17:02    [8981867]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mayton
Member

Откуда: loopback
Сообщений: 49744
mcureenab

t1 = t2 = (+) t3 = (+) t4

Да. Похоже это именно то, что мне было надо. Просто нужно было теоретическое обоснование.
22 июн 10, 17:16    [8981996]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mmar
Member

Откуда: -)
Сообщений: 391
ансишный синтаксис сам "открывает" скобки, в отличие от ораклового..
22 июн 10, 17:21    [8982052]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mayton
Member

Откуда: loopback
Сообщений: 49744
mmar
ансишный синтаксис сам "открывает" скобки, в отличие от ораклового..

Тоесть вы хотите сказать, что если я запишу предложение WHERE c использованием ключевых слов LEFT/RIGHT JOIN то моя проблема будет решена/упрощена на этапе написания?
22 июн 10, 17:25    [8982083]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mmar
Member

Откуда: -)
Сообщений: 391
mayton
mmar
ансишный синтаксис сам "открывает" скобки, в отличие от ораклового..

Тоесть вы хотите сказать, что если я запишу предложение WHERE c использованием ключевых слов LEFT/RIGHT JOIN то моя проблема будет решена/упрощена на этапе написания?


приношу извинения, соврал - в любом случае придется использовать outer join
22 июн 10, 17:31    [8982141]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Серафимный Шестикрыл
Member [заблокирован]

Откуда: С луны свалился
Сообщений: 2922
mcureenab
(t1 = t2) = (+) (t3 = t4).


Раскрой скобки и получишь другое соединение (видимо то, которое ты хотел получить).

t1 = t2 = (+) t3 = (+) t4


Замечательная травка, замечательная.
Ты и правда думаешь, что в данном случае "раскрытие скобок"
происходит как в детской арифметике ?

SQL> create table t (x int);

Table created.

SQL> create table t1 (x int);

Table created.

SQL> create table t2 (x int);

Table created.

SQL> insert into t select rownum from dual connect by level <= 3;

3 rows created.

SQL> insert into t1 select rownum from dual connect by level <= 2;

2 rows created.

SQL> insert into t2 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select t.x x, t1.x x1, t2.x x2
  2  from t left join t1 on (t.x = t1.x) left join t2 on (t1.x = t2.x)
  3  order by 1,2,3;

         X         X1         X2
---------- ---------- ----------
         1          1          1
         2          2
         3

SQL> select t.x x, t1.x x1, t2.x x2
  2  from t left join (t1 join t2 on (t1.x = t2.x)) on (t.x = t1.x)
  3  order by 1,2,3
  4  /

         X         X1         X2
---------- ---------- ----------
         1          1          1
         2
         3
22 июн 10, 17:31    [8982142]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mayton
Member

Откуда: loopback
Сообщений: 49744
Ап. Дошли руки.

Дано: Стандартная схема SCOTT.

Дополнительно: Справочник JOBS-ов.

create table jobs(job varchar2(30) primary key,job_desc varchar2(60) unique);

insert into jobs values ('MANAGER','Manager');
insert into jobs values ('CLERK','Clerk');
insert into jobs values ('SALESMAN','Sales man');
insert into jobs values ('PRESIDENT','President of company');
insert into jobs values ('ANALYST','Analyst');
insert into jobs values ('DEVELOPER','C++ Developer');
commit;

Надо сформировать следующий отчёт:
    DEPTNO DNAME               EMPNO ENAME      JOB                            JOB_DESC
---------- -------------- ---------- ---------- ------------------------------ ------------------------------
        10 ACCOUNTING           7839 KING       PRESIDENT                      President of company
        10 ACCOUNTING           7782 CLARK      MANAGER                        Manager
        10 ACCOUNTING           7934 MILLER     CLERK                          Clerk
        20 RESEARCH             7566 JONES      MANAGER                        Manager
        20 RESEARCH             7902 FORD       ANALYST                        Analyst
        20 RESEARCH             7369 SMITH      CLERK                          Clerk
        30 SALES                7499 ALLEN      SALESMAN                       Sales man
        30 SALES                7521 WARD       SALESMAN                       Sales man
        30 SALES                7654 MARTIN     SALESMAN                       Sales man
        30 SALES                7900 JAMES      CLERK                          Clerk
        30 SALES                7698 BLAKE      MANAGER                        Manager
        30 SALES                7844 TURNER     SALESMAN                       Sales man
        40 OPERATIONS
                                                DEVELOPER                      C++ Developer

У меня к сожалению не получилось это сделать соединением 3-х таблиц с
использованием LEFT/RIGHT joins. Пришлось подключать подзапросы или
inline views или unions.
2 июл 10, 15:38    [9040398]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Elic
Member

Откуда:
Сообщений: 29979
mayton
с использованием LEFT/RIGHT joins.
FULL попробуй :)
2 июл 10, 15:41    [9040428]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
mayton
Member

Откуда: loopback
Сообщений: 49744
Кое чего осилил. 

explain plan for 
 select
     d.deptno,
     d.dname,
     a.empno,
     a.ename,
     a.job_desc     
   from
     dept d,
     (select deptno,empno,ename,job_desc from emp e,jobs j where e.job=j.job) a
   where     
     a.deptno(+)=d.deptno 
   order by 1;


Plan hash value: 1831221445

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |    12 |   816 |     6  (34)| 00:00:01 |
|   1 |  MERGE JOIN OUTER               |               |    12 |   816 |     6  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | DEPT          |     4 |    52 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN              | PK_DEPT       |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                     |               |    12 |   660 |     5  (40)| 00:00:01 |
|   5 |    VIEW                         |               |    12 |   660 |     4  (25)| 00:00:01 |
|   6 |     MERGE JOIN                  |               |    12 |   480 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| JOBS          |     6 |   114 |     1   (0)| 00:00:01 |
|   8 |       INDEX FULL SCAN           | SYS_C00140080 |     6 |       |     1   (0)| 00:00:01 |
|*  9 |      SORT JOIN                  |               |    12 |   252 |     3  (34)| 00:00:01 |
|  10 |       TABLE ACCESS FULL         | EMP           |    12 |   252 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   4 - access("A"."DEPTNO"(+)="D"."DEPTNO")
       filter("A"."DEPTNO"(+)="D"."DEPTNO")
   9 - access("E"."JOB"="J"."JOB")
       filter("E"."JOB"="J"."JOB")


explain plan for 
with a as (select deptno,empno,ename,job_desc from emp e,jobs j where e.job=j.job)
 select
     d.deptno,
     d.dname,
     a.empno,
     a.ename,
     a.job_desc     
 from
     dept d,a
 where     
     a.deptno(+)=d.deptno 
 order by 1;


-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |    12 |   576 |     8  (38)| 00:00:01 |
|   1 |  SORT ORDER BY                  |               |    12 |   576 |     8  (38)| 00:00:01 |
|*  2 |   HASH JOIN OUTER               |               |    12 |   576 |     7  (29)| 00:00:01 |
|   3 |    TABLE ACCESS FULL            | DEPT          |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |    VIEW                         |               |    12 |   420 |     4  (25)| 00:00:01 |
|   5 |     MERGE JOIN                  |               |    12 |   480 |     4  (25)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| JOBS          |     6 |   114 |     1   (0)| 00:00:01 |
|   7 |       INDEX FULL SCAN           | SYS_C00140080 |     6 |       |     1   (0)| 00:00:01 |
|*  8 |      SORT JOIN                  |               |    12 |   252 |     3  (34)| 00:00:01 |
|   9 |       TABLE ACCESS FULL         | EMP           |    12 |   252 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - access("A"."DEPTNO"(+)="D"."DEPTNO")
   8 - access("E"."JOB"="J"."JOB")
       filter("E"."JOB"="J"."JOB")


2 июл 10, 16:29    [9040989]     Ответить | Цитировать Сообщить модератору
 Re: Внешние соединения и эквивалентность результатов  [new]
Elic
Member

Откуда:
Сообщений: 29979
mayton
Кое чего осилил.
Разработчиков не потерял?
2 июл 10, 16:43    [9041178]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить