Oracle加入-常规语法VS ANSI语法之间的比较

问题描述

序言

最近，我看到太多极客对Oracle问题发表评论，说不要使用(+)运算符，而要使用JOIN语法".

Of late, I see too many geeks commenting on Oracle questions saying that "Do not use (+) operator, rather use JOIN syntax".

问题

我确实看到两者都运作良好.但是使用它们之间的真正区别是什么并使您感觉使用它们呢?我欢迎您回答，更多来自经验.

I do see that both work well. But what is the real difference between using them and what makes you feel using them? I would welcome answers, more from experience.

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

 2. What would you suggest for me?

我确实阅读了 Oracle文档但还不足以使我理解或接受这些综合信息.

I did read something on Oracle documentation but not good enough to make me understand or feel comfortable with the comprehensive information.

注意:如果要使用关键字而不是(+)

Note: I am planning to migrate 200+ packages and procedures, if the Keyword should be used instead of (+)

  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 :阅读所有分组更新的答案摘要.

PS: Read the summary of answers for all updates grouped.

推荐答案

将答案分组在一起

1. 使用显式JOIN而不是隐式JOIN(无论它们是否为外部联接)是因为意外地使用隐式联接创建笛卡尔乘积要容易得多.使用显式JOIN，您不能偶然"创建一个.涉及的表越多，错过一个联接条件的风险就越高.
2. 与ANSI联接相比，基本上(+)受严格限制.此外，它仅在Oracle中可用，而所有主要的DBMS都支持ANSI连接语法
迁移到ANSI语法后，
3. SQL不会开始表现更好-它只是不同的语法.
4. Oracle强烈建议您使用前面示例中显示的更灵活的FROM子句连接语法.过去有一些ANSI语法错误，但是如果您使用的是最新的11.2或12.1，则应该已经修复.
5. 使用JOIN运算符可确保您的SQL代码符合ANSI，因此可以将前端应用程序更轻松地移植到其他数据库平台.
联接条件在每个表上具有非常低的选择性，而在理论叉积中对元组的选择性很高. where语句中的条件通常具有更高的选择性.
6. Oracle在内部将ANSI语法转换为(+)语法，您可以在执行计划的谓词信息"部分中看到这种情况.

1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

在12c引擎上使用ANSI语法的可能陷阱

包括12c中的JOIN错误的可能性.请参见此处

Including a possibility of bug in JOIN in 12c. See here

跟进:

Quest SQL optimizer tool将SQL重写为ANSI语法.

Quest SQL optimizer tool rewrites the SQL to ANSI syntax.

这篇关于Oracle加入-常规语法VS ANSI语法之间的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！