I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all.
SQL Tuning Tips
Oracle Tips Session #6
3/31/98
Kathy Gleeson
________________________________________
SQL is the heart of the Oracle system. You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation. The tips below include information about whether one form of the statement is always more efficient or whether each statement is an alternative and the efficency will vary depending on your application.
Oracle processes SQL in two steps: parsing and execution. Tuning may speed up your SQL by reducing either parsing, execution or both. Note, tuning SQL should only be done after your code is working correctly. Be aware that there is an inevitable tug-of-war between writing efficient SQL and understandable SQL.
TIP 1 (Best Tip): SQL cannot be shared within Oracle unless it is absolutely identical. Statements must have 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
o Use SQL standards within an application. Rules like the following are easy to implement and will allow more sharing within Oracle's memory.
- Using a single case for all SQL verbs
- Beginning all SQL verbs on a new line
- Right or left aligning verbs within the initial SQL verb
- Separating all words with a single space
o Use bind variables. The values of bind variables do not need 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
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 123
SELECT * FROM emp WHERE emp_no = :B1; Bind value: 987
Non-sharable SQL
SELECT * FROM emp WHERE emp_no = 123;
SELECT * FROM emp WHERE emp_no = 987;
o Use a standard approach to table aliases. If two identical SQL statements vary because an identical table has two different aliases, then the SQL is different and will not be shared.
o Use table aliases and prefix all column names by their aliases when more than one table is involved in a query. This reduces 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)
TIP 2: 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 these 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.
Do Not Use Use
Expand|Select|Wrap|Line Numbers
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE SUBSTR(account_name,1,7) = 'CAPITAL';
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE account_name LIKE 'CAPITAL%';
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE account_name = NVL ( :acc_name, account_name);
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE account_name LIKE NVL ( :acc_name, '%');
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE TRUNC (trans_date) = TRUNC (SYSDATE);
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE trans_date BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE) + .99999;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE account_name || account_type = 'AMEXA';
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE account_name = 'AMEX'
- AND account_type = 'A';
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount + 3000 < 5000;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount < 2000;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount != 0;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount > 0;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount NOT = 0;
- SELECT account_name, trans_date, amount
- FROM transaction
- WHERE amount > 0;
TIP 4: 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.
Do Not Use Use
Expand|Select|Wrap|Line Numbers
- 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;
Separate Subqueries
Expand|Select|Wrap|Line Numbers
- 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;
Expand|Select|Wrap|Line Numbers
- SELECT emp_name
- FROM emp
- WHERE (emp_cat, sal_range)
- = (SELECT MAX (category), MAX (sal_range)
- FROM emp_categories)
- AND emp_dept = 0020;
(Note, this query returns the employee names from each department in department category 'A'.)
Expand|Select|Wrap|Line Numbers
- 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';
(Note: This query returns all department numbers and names which have at least one employee.)
Expand|Select|Wrap|Line Numbers
- Do Not Use Use
- 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);
UNION UNION ALL
Expand|Select|Wrap|Line Numbers
- 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';
Expand|Select|Wrap|Line Numbers
- 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%';
Datatype
of
field in
where
clause Your Query After Implicit Conversion Index
Used?
emp_no
indexed
numeric SELECT ...
FROM emp
WHERE emp_no = '123'; SELECT ...
FROM emp
WHERE emp_no = TO_NUMBER('123'); YES
emp_type
indexed
varchar2 SELECT ...
FROM emp
WHERE emp_type = 123; SELECT ...
FROM emp
WHERE TO_NUMBER (emp_type) = 123; NO!
________________________________________