9- 最佳实践

1． Exists比Distinct更加有效：

The DISTINCT keyword used in a SELECT clause eliminates duplicate rows in the result set. To eliminate those duplicates, Oracle performs a sort, and that sort requires time and disk space. Therefore, avoid using DISTINCT if you can tolerate having duplicate rows returned by a query. If you can't tolerate the duplicate rows, or your application can't handle them, use EXISTS in place of DISTINCT.

 

For example, assume you are trying to find the names of customers who have orders. Your query has to be based on two tables: CUSTOMER and CUST_ORDER. Using DISTINCT, your query would be written as follows:

 

方法一：使用 Distinct

    SELECT DISTINCT C.CUST_NBR, C.NAME

      FROM CUSTOMER C, CUST_ORDER O

     WHERE C.CUST_NBR = O.CUST_NBR;

 

分析：先对两个表进行内连接，再排序，剔除重复记录

 

 

方法二：使用 Exists

    SELECT C.CUST_NBR, C.NAME

      FROM CUSTOMER C

     WHERE EXISTS (SELECT 1 FROM CUST_ORDER O WHERE C.CUST_NBR = O.CUST_NBR);

 

分析：使用相关性子查询，每次从 CUSTOMER表中取出一条记录，将其CUST_NBR值和ORDER表中的每一条记录的CUST_NBR值进行比较，如果有相同的记录则直接返回CUSTOMER表中该记录的相应字段。

 

比较：使用方法二的时候，由于使用了相关性子查询，避免了对两个表进行内连接，同时由于采用了 Exists的存在性判断，返回1或没有记录返回比起对采用内连接后进行排序和剔除重复记录要快得多。经实验方法二的效率比方法一快九倍以上。

 

2． Exists和In:

We've found that EXISTS often performs better than IN. Let's look at an example demonstrates this. The following query uses IN to delete the orders for customers in region 5:

 

方法一：使用 In

    DELETE FROM CUST_ORDER

     WHERE CUST_NBR IN (SELECT CUST_NBR FROM CUSTOMER WHERE REGION_ID = 5);

 

分析：使用非相关性子查询先形成临时记录集，再对 CUST_ORDER进行全表扫描比较

 

方法二：使用 Exists

    DELETE FROM CUST_ORDER WHERE EXISTS (

        SELECT CUST_NBR FROM CUSTOMER

         WHERE CUST_ORDER.CUST_NBR = CUSTOMER.CUST_NBR

           AND REGION_ID = 5);

 

分析：使用相关性子查询，每次从 CUST_ORDER表中取出一条记录和CUSTOMER的记录进行比较，如果存在相同记录则删除CUST_ORDER表中对应的记录

 

比较：当使用 Exists子句的时候，查询的执行计划由外部表决定，相反使用In子句的时候其执行计划是由子查询中的表决定，Exists查询几乎会比In查询要快得多，除非那些查询中的记录只有很少几条的情况下

 

3． Where和Having

With the HAVING clause, the query performs the group operation first, and then filters the groups for the condition specified. The WHERE clause version of the query filters the rows before performing the group operation. The result of filtering with the WHERE clause is that there are fewer rows to summarize, and consequently the query performs better

 

4． Union和Union All

UNION ALL combines the results of two SELECT statements. UNION combines the results of two SELECT statements, and then returns only distinct rows from the combination; duplicates are eliminated. It is, therefore, obvious that to remove the duplicates, UNION performs one extra step than UNION ALL. This extra step is a sort, which is costly in terms of performance. Therefore, whenever your application can handle duplicates or you are certain that no duplicates will result, consider using UNION ALL instead of UNION.

 

5．避免不必要的转换过程

Before your SQL can be executed by Oracle, it needs to be parsed. The importance of parsing when it comes to tuning SQL lies in the fact that no matter how many times a given SQL statement is executed, it needs to be parsed only once. During parsing, the following steps are performed (not necessarily in the sequence shown):

 

SQL 语句的转换过程：

•The syntax of the SQL statement is verified.

•The data dictionary is searched to verify table and column definitions.

•The data dictionary is searched to verify security privileges on relevant objects.

•Parse locks are acquired on the relevant objects.

•The optimal execution plan is determined.
•The statement is loaded into the shared SQL area (also known as the library cache) in the shared pool of 
  the system global area (SGA). The   execution plan and parse information are saved here in case the same
  statement is executed once again.

 

However, a statement is parsed only if Oracle doesn't find an identical SQL statement already in the shared SQL area (library cache) of the SGA. Before parsing a SQL statement, Oracle searches the library cache for an identical SQL statement. If Oracle finds an exact match, there is no need to parse the statement again. However, if an identical SQL statement is not found, Oracle goes through all the aforementioned steps to parse the statement.

 

The most important keyword in the previous paragraph is "identical." To share the same SQL area, two statements need to be truly identical. Two statements that look similar, or that return the same result, need not be identical. To be truly identical, the statements must:

 

·Have the same uppercase and lowercase characters.

·Have the same whitespace and newline characters.

·Reference the same objects using the same names, which must in turn have the same owners

 

If there is a possibility that your application executes the same (or similar) SQL statements multiple times, by all means try to avoid unnecessary parsing. This will improve the overall performance of your applications. The following techniques can help you reduce SQL parsing:

Use bind variables.

Use table aliases.

 

6．使用变量绑定

When multiple users use an application, they actually execute the same set of SQL statements over and over, but with different data values. For example, one customer service representative may be executing the following statement:

 

SELECT * FROM CUSTOMER WHERE CUST_NBR = 121;

while another customer service representative will be executing:

 

SELECT * FROM CUSTOMER WHERE CUST_NBR = 328;

These two statements are similar, but not "identical"—the customer ID numbers are different, therefore Oracle has to parse twice.

 

Because the only difference between these statements is the value used for the customer number, this application could be rewritten to use bind variables. In that case, the SQL statement in question could be as follows:

 

SELECT * FROM CUSTOMER WHERE CUST_NBR = :X;

 

Oracle needs to parse this statement only once. The actual customer numbers would be supplied after parsing for each execution of the statement. Multiple, concurrently executing programs could share the same copy of this SQL statement while at the same time supplying different customer number values.

 

7．使用别名

An important thing to remember while using table aliases is that if you define aliases in the FROM clause, you must use only those aliases, and not the actual table names, in the rest of the query.

 

This is where the performance aspect of using table aliases comes into play. Since the query doesn't qualify the columns NAME and ORDER_NBR, Oracle has to search both the CUSTOMER and CUST_ORDER tables while parsing this statement to find which table each of these columns belongs to. The time required for this search may be negligible for one query, but it does add up if you have a number of such queries to parse. It's good programming practice to qualify all columns in a query with table aliases, even those that are not ambiguous, so that Oracle can avoid this extra search when parsing the statement