oracle的独自形式和ansi形式,10条 oracle sql 最佳实践

1.使用 ANSI Joins 而不是 Oracle Joins

在大型查询中，很容易忘记添加WHERE子句来连接表，从而导致不必要的笛卡尔联接和不正确的结果;

WHERE子句应用于过滤记录，而不是用于将表连接在一起。一个微妙的区别，但它使查询更容易理解;

ANSI Joins 更容易阅读，因为您可以看到哪些部分用于联接，哪些部分用于筛选数据;

2.避免带函数的WHERE子句

如果在查询中应用了函数，则不会使用在列本身上创建的 任何索引，这会大大降低查询的速度。

如果确实需要在WHERE子句中的列上使用函数，请考虑在该列上创建一个基于函数的索引。这是一种根据应用于列的函数的结果创建的索引类型，可以在此查询中使用。

3.使用 CASE 代替 Multiple Unions

SELECT id, product_name

FROM product

WHERE status = ‘X’ AND created_date < TO_DATE(‘2017-01-01’, ‘YYYY-MM-DD’)

UNION ALL

SELECT id, product_name

FROM product

WHERE status = ‘A’ AND product_series = ‘WXT’;

改进：

SELECT id, product_name

FROM (

SELECT id, product_name,

CASE

WHEN status = ‘X’ AND created_date < TO_DATE(‘2017-01-01’, ‘YYYY-MM-DD’) THEN 1

WHEN status = ‘A’ AND product_series = ‘WXT’ THEN 1

ELSE 0 END AS prodcheck

FROM product

) sub

WHERE prodcheck = 1;

显示正确记录的逻辑在case语句中。有几行，每组标准一行，如果找到匹配，则返回1。此逻辑都在子查询中，外部查询筛选器只显示该情况为1的记录。

编写case语句有几种不同的方法，但其思想是只使用CASE语句中的主查询和几个条件，而不是单独的查询。但是，请确保测试两个版本的查询以提高性能，因为可能会有与UNION查询一起使用的索引，而这些索引不会与CASE查询一起运行。

4. 尽量减少使用DISTION

添加一个不同的关键字以确保不会得到重复的记录是很有诱惑力的。但是，添加一个单独的关键字可能会导致对查询执行昂贵的操作，从而减慢其速度。它会给你需要的结果，但它掩盖了其他地方的问题。它可能来自一个不完整的联接，或者表中的不正确数据，或者是一些你没有考虑的标准，这导致了重复的行。解决查询或数据中的问题是正确的解决方案。

5. 重新设计数据值列表以使用表

SELECT *

FROM product

WHERE status IN (‘A’, ‘P’, ‘C’, ‘S’);

此查询可能会给出所需的结果。如果状态值在将来的某个时候发生了变化，或者业务规则发生了变化，这意味着您需要调整这个列表，那么会发生什么呢？如果将此列表编码到查询中，则需要调整查询。这可能会导致应用程序代码和部署过程的更改。

另一种方法是将值存储在单独的表中并连接到此表。例如，您可以有一个Status_lookup表，其中包含值和类别，其中类别定义了所需的数据。

SELECT product.*

FROM product

INNER JOIN status_lookup ON product.status = status_lookup.status

WHERE status_lookup.category = ‘ACTIVE’;

6. UNION ALL 代替 UNION [集合运算符]

他们之间有一些细微的差别。UNION All显示两个结果集中的所有记录，而UNION显示不包括重复的所有记录。简单而言：UNION移除重复项，UNIONALL不删除。

这意味着，在Oracle中，当使用UNION在合并后从结果集中删除所有重复行时，将执行额外的步骤， 它与执行DISTINCT类似。

7. 使用表别名

8. 仅对聚合函数使用 HAVING

Oracle SQL中的HAVING子句用于过滤结果集中的记录,它与WHERE子句非常相似。但是，WHERE子句在应用聚合函数之前过滤行，HAVING子句在应用聚合函数之后过滤行。如果您正在使用聚合函数，那么将HAVING用于所有内容可能很诱人，但它们会在您的查询中执行不同的操作。

确保仅对聚合函数使用HAVING，并在聚合之前对要限制的结果使用WHERE。

9. 始终在 INSERT 语句中指定列

一个很好的习惯是在INSERT语句中指定列。这有几个好处。首先，它可以防止错误或数据进入错误的列。如果不指定列，就无法保证将这些列插入到哪个顺序中。这可能导致出现错误，或在错误的列中插入带有值的数据。

可以清楚地看到哪些列代表哪些值。当您查看一个没有列的语句时，您将不得不猜测值是什么。如果添加列，则准确地知道每个列的值。

10. 避免使用空格的对象名称

使用带有空格的表名可能更容易阅读。然而，它可能会引起几个问题。Oracle中的表名以大写形式存储，或者如果它们有引号，则在输入它们时存储它们。这意味着每当您引用此表时，都需要使用引号并在编写时指定它。这对您和其他开发人员都不方便。

另一个原因是在查询中很难引用此表。您必须用引号指定它，并且可能需要使用表别名来确保查询是正确的。最好指定没有空格的对象名称。您可以使用下划线代替