1. exists is more effective than 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 MERs mers who have orders. your query has to be based on two tables: customer and cust_order. using distinct, your query wocould be written as follows: Method 1: Use distinct select distinct C. cust_nbr, C. name from customer C, cust_order O where c. cust_nbr = O. cust_nbr; analysis: first join the two tables and then sort them. Remove duplicate records. Method 2: Use 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); analysis: a correlation subquery is used to retrieve a record from the customer table and compare the value of cust_nbr with the value of cust_nbr for each record in the order table, if the same record exists, the corresponding fields of the record in the customer table are directly returned. Comparison: When method 2 is used, the correlated subquery is used to avoid inner join between the two tables, and the existence judgment of exists is used, returning 1 or no record is much faster than sorting and removing duplicate records after using inner join. The efficiency of method 2 is more than nine times faster than that of method 1. 2. exists and 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 MERs in Region 5: Method 1: Use in Delete from cust_order where cust_nbr in (select cust_nbr from customer where region_id = 5); analysis: use non-correlated subqueries to first form a temporary record set, and then perform full table scan and comparison. Method 2: Use exists Delete from cust_order where exists (select cust_nbr from customer where cust_order.cust_nbr = customer. cust_nbr and region_id = 5); analysis: The correlation subquery is used to retrieve a record from the cust_order table and compare it with the customer record, if the same record exists, delete the comparison of corresponding records in the cust_order table: When the exists clause is used, the query execution plan is determined by the External table, on the contrary, when the in clause is used, its execution plan is determined by the table in the subquery, And the exists query is almost much faster than the in query, unless there are only a few records in the query. where and havingwith 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 encounter 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 and Union allunion 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. avoid unnecessary conversion process 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 saved med (not necessarily in the sequence shown ):
SQL
Statement conversion process:
• 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
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 ownersIf 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. use variables to bind 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 cocould be rewritten to use bind variables. in that case, the SQL statement in question cocould be as follows:
Select * from customer where cust_nbr =: X;Oracle needs to parse this statement only once. the actual customer numbers wocould be supplied after parsing for each execution of the statement. multiple, concurrently executing programs cocould share the same copy of this SQL statement while at the same time supplying different customer number values. 7. use the alias 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. 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
