Объединение Oracle - Сравнение между традиционным синтаксисом VS ANSI Syntax

Задержка, я вижу слишком много вундеркиндов, комментирующих вопросы Oracle, говорящие, что "Не используйте (+) оператор, скорее используйте синтаксис JOIN".

Я вижу, что оба работают хорошо. Но какова реальная разница в использовании их и что заставляет вас чувствовать их использование? Я бы приветствовал ответы, больше из опыта.

 1. Is there anything to do with limitations in application, performance, 
    etc. while using them?

 2. What would you suggest for me?

Я кое-что прочитал в документации Oracle, но недостаточно хорош, чтобы заставить меня понять или чувствовать себя комфортно с исчерпывающей информацией.

Примечание. Я планирую перенести более 200 пакетов и процедур, если вместо (+)

следует использовать ключевое слово,

  3. Also is there any freeware tools to do the rewrite?

Проводка образцов

+----------------------------------+------------------------------------------------+
|-INNER JOIN - CONVENTIONAL        |-INNER JOIN - ANSI SYNTAX                       |
|----------------------------------|------------------------------------------------|
|SELECT                            |SELECT                                          |
|      EMP.DEPTNO                  |       ENAME,                                   |
|FROM                              |       DNAME,                                   |
|      EMP,                        |       EMP.DEPTNO,                              |
|      DEPT                        |       DEPT.DEPTNO                              |
|WHERE                             | FROM                                           |
|      EMP.DEPTNO = DEPT.DEPTNO;   |       SCOTT.EMP INNER JOIN SCOTT.DEPT          |
|                                  |       ON EMP.DEPTNO = DEPT.DEPTNO;             |
|----------------------------------|------------------------------------------------|
|-LEFT OUTER JOIN - CONVENTIONAL   |-LEFT OUTER JOIN - ANSI SYNTAX                  |
|----------------------------------|------------------------------------------------|
|SELECT                            | SELECT                                         |
|      EMP.DEPTNO                  |       ENAME,                                   |
|FROM                              |       DNAME,                                   |
|      EMP,                        |       EMP.DEPTNO,                              |
|      DEPT                        |       DEPT.DEPTNO                              |
|WHERE                             | FROM                                           |
|      EMP.DEPTNO = DEPT.DEPTNO(+);|       SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT     |
|                                  |       ON EMP.DEPTNO = DEPT.DEPTNO;             |
|----------------------------------|------------------------------------------------|
|-RIGHT OUTER JOIN - CONVENTIONAL  |-RIGHT OUTER JOIN - ANSI SYNTAX                 |
|----------------------------------|------------------------------------------------|
|SELECT                            | SELECT                                         |
|      EMP.DEPTNO                  |       ENAME,                                   |
|FROM                              |       DNAME,                                   |
|      EMP,                        |       EMP.DEPTNO,                              |
|      DEPT                        |       DEPT.DEPTNO                              |
|WHERE                             | FROM                                           |
|      EMP.DEPTNO(+) = DEPT.DEPTNO;|       SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT    |
|                                  |       ON EMP.DEPTNO = DEPT.DEPTNO;             |
|----------------------------------|------------------------------------------------|
|-FULL OUTER JOIN - CONVENTIONAL   |-FULL OUTER JOIN - ANSI SYNTAX                  |
|----------------------------------|------------------------------------------------|
|                                  |                                                |
|SELECT                            | SELECT                                         |
|      *                           |       *                                        |
|FROM                              | FROM                                           |
|      EMP,                        |       SCOTT.EMP FULL OUTER JOIN SCOTT.DEPT     |
|      DEPT                        |       ON EMP.DEPTNO = DEPT.DEPTNO;             |
|WHERE                             |                                                |
|      EMP.DEPTNO = DEPT.DEPTNO(+) |                                                |
|UNION ALL                         |                                                |
|SELECT                            |                                                |
|      *                           |                                                |
|FROM                              |                                                |
|      EMP,                        |                                                |
|      DEPT                        |                                                |
|WHERE                             |                                                |
|      EMP.DEPTNO(+) = DEPT.DEPTNO |                                                |
|      AND EMP.DEPTNO IS NULL;     |                                                |
|__________________________________|________________________________________________|

PS: прочитайте сводку ответов для всех сгруппированных обновлений.

Ответы

Ответ 1

Группировка ответов вместе

Используйте явные JOIN, а не имплицитные (независимо от того, являются ли они внешними объединениями или нет) заключается в том, что намного проще создать декартовский продукт с неявными объединениями. С явным JOINs вы не можете "случайно" создать его. Чем больше таблиц задействовано, тем выше риск того, что вы пропустите одно условие соединения.
В принципе (+) сильно ограничен по сравнению с ANSI-соединениями. Кроме того, он доступен только в Oracle, тогда как синтаксис соединения ANSI поддерживается всеми основными СУБД
SQL не будет работать лучше после перехода на синтаксис ANSI - это просто другой синтаксис.
Oracle настоятельно рекомендует использовать более гибкий синтаксис соединения предложения FROM, показанный в предыдущем примере. Раньше были некоторые ошибки с синтаксисом ANSI, но если вы идете с последними 11.2 или 12.1, которые должны быть исправлены уже.
Использование операторов JOIN гарантирует, что ваш код SQL соответствует требованиям ANSI и, таким образом, позволит более легко переносить внешнее приложение для других платформ баз данных.
Условия соединения имеют очень низкую избирательность по каждой таблице и высокую селективность по кортежам в теоретическом кросс-продукте. Условия в заявлении where обычно имеют гораздо более высокую избирательность.
Oracle внутренне преобразует синтаксис ANSI в синтаксис (+), это можно увидеть в разделе "Информация о предикате" плана выполнения.

Возможный Pitfall при использовании синтаксиса ANSI на двигателе 12c

Включая возможность ошибки в JOIN в 12c. См. здесь

ПОСЛЕДУЮЩИЙ:

Quest SQL optimizer tool перезаписывает синтаксис SQL в ANSI.

Ответ 2

Если ваши 200+ пакетов работают по назначению с помощью "старомодного" синтаксиса, пусть это так. SQL не будет работать лучше после перехода на синтаксис ANSI - это просто другой синтаксис.

Все, что сказано, синтаксис ANSI чище - вы не собираетесь нормализовать соединение, если вы забудете (+) в некотором многоколоночном внешнем соединении.
В прошлом были некоторые ошибки с синтаксисом ANSI, но если вы идете с последними 11.2 или 12.1, которые должны быть исправлены уже.
Конечно, вы знаете свою среду и приоритеты лучше, как сказал SchmitzIT. Синтаксис ANSI является частью стандарта SQL, и это поможет при использовании другого продукта RDBMS.

Ответ 3

В 11g вы должны использовать синтаксис ANSI join. Он более гибкий (поддержка полных внешних соединений и секционированных соединений), а в документации указано:

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

Эта причина достаточно.

Ответ 4

Помимо причин, упомянутых другими, использование операторов JOIN гарантирует, что ваш SQL-код совместим с ANSI и, таким образом, позволит более легко переносить внешнее приложение для других платформ баз данных.

Ответ 5

Разделение предикатов на основе селективности

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

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

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

Подсказка для оптимизатора (человека!)

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

Неясно, использует ли оптимизатор Oracle реальное положение оператора SQL в качестве подсказки для оптимизации запроса. Я думаю, что он больше интересуется жесткими фактами в статистике таблицы (произошли некоторые изменения в том, как Oracle обрабатывает различные объединения в 11g R1, см. this post из команды оптимизатора Oracle для получения дополнительной информации).

По крайней мере, для меня, как для человека, очень полезно знать, имеет ли инструкция избирательность в одной таблице, когда я пытаюсь понять и оптимизировать запрос. Вы также должны рассмотреть это, когда хотите поместить несколько условий в предложение ON (например, ON (a.x=b.x AND a.y=b.y) и поставить одно из условий в кластере: просто проверьте, насколько выборочно условие.

Заключение

Для существующих запросов сохраните синтаксис как есть. Создавая новый запрос или рефакторинг существующего, попробуйте выполнить сортировку предикатов по селективности с использованием синтаксиса "JOIN ON": если он не очень доступен для выбора в одной таблице, поместите его в часть ON, иначе в части WHERE.

Ответ 6

Знак (+), используемый в Outer-соединениях, представляет собой синтаксис Oracle для соединений Outer

Из информации, полученной из надежных источников oracle, я мог видеть, что вы можете сохранить синтаксис внешнего соединения Oracle (+) для существующих пакетов, поскольку имеется 200+ пакетов, и поскольку внутренне Oracle все еще преобразует их из синтаксиса ANSI в синтаксис Oracle.

Пожалуйста, используйте синтаксис ANSI в будущем, где есть ограничения на использование оператора (+)

Пожалуйста, найдите ссылки для подробного объяснения на (+) знаке Внешние соединения, которые могут помочь вам решить вашу миграцию

Синтаксис Oracle Outer Join и синтаксис ANSI

Ограничения использования (+) Внешние соединения

Дополнительная информация о внешнем соединении Oracle

(+) Outer join Оператор рекомендуется в Java при использовании драйверов Oracle JDBC

Ответ 7

Я использовал оба подхода для разных проектов, и я предпочитаю синтаксис JOIN.

Существует четкое разделение для условий соединения в разделе ON и фильтрах условий в WHERE.
Легче читать и поддерживать большие запросов с большим количеством подключений.

Ответ 8

Говоря об опыте работы, использование JOIN, а не (+) проще, быстрее, лучше выглядит и лучше работать с решением, особенно когда вы работаете с несколькими базами данных (по синонимам), с большим количеством из таблиц в нем (например: 40+ таблиц) в большой базе данных (1000+ таблиц, некоторые таблицы с более чем 2 миллиардами строк), вы почувствуете большую разницу.

Ответ 9

Некоторые комментаторы заявили, что синтаксис (+) не допускает полного внешнего объединения. Он делает это, так что это не проблема: (ВЛЕВОЙ ВХОДНЫЙ СОЕДИНИТЕЛЬ) СОЮЗ ВСЕ (ПРАВИЛЬНЫЙ ВСТРОЕННЫЙ СОЕДИНЕНИЕ).

Другие сказали, что производительность является причиной того, чтобы сделать коммутатор. Это группа BS, особенно в SQL. Конечно, есть несколько рекомендаций, но каждый запрос и каждая база данных имеют свои особенности, вы должны настраиваться для конкретных не для общих ситуаций.

Причины перехода с (+), помимо стандартного, являются его ограничениями в отличие от нового явного синтаксиса: http://docs.oracle.com/cd/E16655_01/server.121/e17209/queries006.htm#SQLRF52354, Начните читать здесь: "Oracle рекомендует использовать синтаксис FROM FROM OUTER JOIN, а не оператор объединения Oracle".

Ответ 10

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

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