SQL Tuning Guidelines for Oracle -Simple Yet Effective!

Author: Kathy Gleeson
In this article I will discuss some SQL tuning guidelines that are simple but effective. SQL is the heart of the Oracle system. You can use many different SQL statements to attain the same result. It is often the case that only one statement will be the most efficient option in a given state of affairs. The guidelines below include information about whether one form of the statement is always more efficient or whether each statement is an alternative and the efficiency will differ depending on your application.

>>Oracle processing of SQL:

Oracle processes SQL in two steps:

Parsing
Execution.
Tuning may speed up your SQL by reducing either parsing or execution or both. Tuning SQL should only be done after your code is working correctly. Beware that there is an inevitable tug-of-war between writing efficient SQL and understandable SQL.

>>Oracle SQL Tuning - Identical SQL:

SQL cannot be shared within Oracle unless it is absolutely identical. Statements must match exactly in case, white space and underlying schema objects to be shared within Oracle's memory. Oracle avoids the parsing step for each subsequent use of an identical statement.

sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 212; statement to match
sql> SELECT NAME FROM s_customer WHERE ID = 212; lower case
sql> SELECT NAME FROM S_CUSTOMER WHERE ID=212; white space
sql> SELECT NAME
FROM S_CUSTOMER
WHERE ID=212; white space


>>Oracle SQL Tuning - Standard SQL:

Use SQL standards within an application. Rules like the following are easy to implement and will allow more sharing within Oracle's memory.

- Use a single case for all SQL verbs
- Begin all SQL verbs on a new line
- Align the verbs right or left within the initial SQL verb
- Separate all words with a single space

>>Oracle SQL Tuning - Bind variables:

Use bind variables. The values of bind variables need not to be the same for two statements to be considered identical. Bind variables are not substituted until a statement has been successfully parsed.

Sharable SQL
Non-sharable SQL

SELECT * FROM emp
WHERE emp_no = :B1; Bind value: 123
SELECT * FROM emp
WHERE emp_no = 123;

SELECT * FROM emp
WHERE emp_no = :B1; Bind value: 987
SELECT * FROM emp
WHERE emp_no = 987;


>>Oracle SQL Tuning - Table Aliases:

Use table aliases and a standard approach to table aliases. If two identical SQL statements differ because an identical table has two different aliases, then the SQL is different and will not be shared.

>>Oracle SQL Tuning - Prefix column names by Aliases:

Prefix all column names by their aliases when more than one table is involved in a query. This decreases the parse time and prevents future syntax errors if someone adds a column to one of the tables with the same name as a column in another table. (ORA-00918: Column Ambiguously Defined)

>>Oracle SQL Tuning - WHERE Clause:

Beware of WHERE clauses which do not use indexes at all. Even if there is an index over a column that is referenced by a WHERE clause included in this section, Oracle will ignore the index. All of WHERE clauses can be re-written to use an index while returning the same values. In other words, don't perform operations on database objects referenced in the WHERE clause.

SELECT account_name, trans_date, amount
WHERE SUBSTR (account_name,1,7) = 'CAPITAL';
SELECT account_name, trans_date, amount
WHERE account_name LIKE 'CAPITAL%';

SELECT account_name, trans_date, amount
WHERE account_name || account_type = 'AMEXA';
SELECT account_name, trans_date, amount
WHERE account_name = 'AMEX'
AND account_type = 'A';

SELECT account_name, trans_date, amount
WHERE TRUNC (trans_date) = TRUNC ( SYSDATE );
SELECT account_name, trans_date, amount
WHERE trans_date BETWEEN TRUNC ( SYSDATE ) AND TRUNC ( SYSDATE ) + .99999;

SELECT account_name, trans_date, amount
WHERE account_name = NVL ( :acc_name, account_name);
SELECT account_name, trans_date, amount
WHERE account_name LIKE NVL ( :acc_name, '%');

SELECT account_name, trans_date, amount
WHERE amount + 3000 < 5000;
SELECT account_name, trans_date, amount
WHERE amount != 0;

SELECT account_name, trans_date, amount
WHERE amount < 2000;
SELECT account_name, trans_date, amount
WHERE amount > 0;

SELECT account_name, trans_date, amount
WHERE amount NOT = 0;
SELECT account_name, trans_date, amount
FROM TRANSACTION
WHERE amount > 0;


>>Oracle SQL Tuning - Tuning the views:

Don't forget to tune views. Views are SELECT statements and can be tuned in just the same way as any other type of SELECT statement can be. All tuning applicable to any SQL statement are equally applicable to views.

>>Oracle SQL Tuning - HAVING Clause:

Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.

Using HAVING Clause
Not Using HAVING Clause

SELECT region, AVG (loc_size)
FROM location
GROUP BY region
HAVING region != ' SYDNEY '
AND region != ' PERTH ';
SELECT region, AVG (loc_size)
FROM location
WHERE region != ' SYDNEY '
AND region != ' PERTH ';
GROUP BY region;


>>Oracle SQL Tuning - Table Lookups:

Minimize the number of table lookups (subquery blocks) in queries, particularly if your statements include subquery SELECTs or multicolumn UPDATEs.

Separate Subqueries
Combined Subqueries

SELECT emp_name
FROM emp
WHERE emp_cat = ( SELECT MAX (category)
FROM emp_categories)
AND emp_range = ( SELECT MAX (sal_range)
FROM emp_categories)
AND emp_dept = 0020;
SELECT emp_name
FROM emp
WHERE (emp_cat, sal_range)
= ( SELECT MAX (category), MAX (sal_range)
FROM emp_categories)
AND emp_dept = 0020;


>>Oracle SQL Tuning - Multiple Table Joins:

Consider the alternatives like EXISTS, IN and table joins when doing multiple table joins. None of these are consistently faster; it depends on your data. If there is a poor performer here, it's likely the IN clause. This query returns the employee names from each department in department category 'A'.

EXISTS
IN
Table Joins

SELECT emp_name
FROM emp E
WHERE EXISTS (
SELECT 'X' FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');
SELECT emp_name
FROM emp E
WHERE dept_no IN
( SELECT dept_no FROM dept
WHERE dept_no = E.dept_no
AND dept_cat = 'A');
SELECT emp_name
FROM dept D, emp E
WHERE E.dept_no = D.dept_no
AND D.dept_cat = 'A';


>>Oracle SQL Tuning - DISTINCT vs. EXISTS:

Avoid joins that require the DISTINCT qualifier on the SELECT list in queries which are used to determine information at the owner end of a one-to-many relationship. The DISTINCT operator causes Oracle to fetch all rows satisfying the table join and then sort and filter out duplicate values. EXISTS is a faster alternative, because the Oracle optimizer realizes when the subquery has been satisfied once, there is no need to proceed further and the next matching row can be fetched.
Below query returns all department numbers and names which have at least one employee.

SELECT DISTINCT dept_no, dept_name
FROM dept D,
emp E
WHERE D.dept_no = E.dept_no; SELECT dept_no, dept_name
FROM dept D
WHERE EXISTS (
SELECT 'X'
FROM emp E
WHERE E.dept_no = D.dept_no);


>>Oracle SQL Tuning - UNION ALL:

Consider whether a UNION ALL will be adequate in place of a UNION . The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicate to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables include no duplicate records, or you don't care if duplicates are returned, the UNION ALL is much more efficient.

UNION
UNION ALL

SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = '31-DEC-95'
UNION
SELECT acct_num, balance_amt
FROM credit_transactions
WHERE tran_date = '31-DEC-95';
SELECT acct_num, balance_amt
FROM debit_transactions
WHERE tran_date = '31-DEC-95'
UNION ALL
SELECT acct_num, balance_amt
FROM credit_transactions
WHERE tran_date = '31-DEC-95';


>>Oracle SQL Tuning - DECODE:

Consider using DECODE to avoid having to scan the same rows repetitively or join the same table repetitively. DECODE is not necessarily faster as it depends on your data and the complexity of the resulting query. Also, using DECODE requires you to change your code when new values are allowed in the field.

SELECT COUNT (*)
FROM emp
WHERE status = 'Y'
AND emp_name LIKE 'SMITH%';
SELECT COUNT (*)
FROM emp
WHERE status = 'N'
AND emp_name LIKE 'SMITH%';
SELECT COUNT ( DECODE (status, 'Y', 'X', NULL )) Y_count,
COUNT ( DECODE (status, 'N', 'X', NULL )) N_count
FROM emp
WHERE emp_name LIKE 'SMITH%';


>>Oracle SQL Tuning - Casting:

Oracle automatically performs casting or simple column type conversions when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.


Posted by Anantha Narayanan



0 comments:
Post a Comment

Links to this post
SQL Tuning Guidelines for Oracle - Simple yet Effective!
In this article I will discuss some SQL tuning guidelines that are simple but effective. SQL is the heart of the Oracle system. You can use many different SQL statements to attain the same result. It is often the case that only one ...
Posted by atOctober 6, 2007 12:47 AM
Create a Link