Tuning in SQl
Oracle’s SQL is a very flexible language. We can make use of many different SQL statements to accomplish the same task. Even though there are several queries and retrieval statements producing similar results, one should make use of the most efficient choice . A SQL choice is optimized only if it produces the accurate results in a short span of time, without overriding the performance of other system resources. Whenever an SQL statement is executed, Oracle first looks for identical statements matching in the context (SGA) area as it holds the SQL statements in memory after parsing. Thus use of Bind variables is advisable when ever possible in place of Literals.
SQL tuning can be done by:
Making use of ROWID where ever possible
Reason:
Every record that is added to the database will have an unique ROWID and will not change until the delete statement is issued on that record. In case a record block or location has been changed for some reason, the original ROWID would still point to the new location or the new ROWID and so on. Thus as far as possible use ROWID to optimize the code for retrievals of a record.
Avoid making use of UNION and instead use UNION ALL
Reason:
UNION ALL includes duplicate rows and does not require a sort. Whereas the UNION operation sorts the result set to eliminate any rows,which are within the sub-queries. As the SORT operation is very expensive in terms of CPU consumption try as far as possible avoiding its use. Unless you require the duplicate rows to be eliminated,use UNION ALL.
Avoid making use of NOT IN and instead use NOT EXISTS for indexed columns
eg.
Normal Query -
[sourcecode language='sql']
select * from emp
where emp_id NOT IN
(select gid from company)
[/sourcecode]
Optimized Query -
[sourcecode language='sql']
select * from emp e
where NOT EXISTS
(select * from company c
where c.gid = e.emp_id)
[/sourcecode]
Avoid using NOT IN and instead use IN with MINUS incase of non indexed columns
eg.
Normal Query -
[sourcecode language='sql']
select * from emp
where emp_id NOT IN
(select gid from company)
[/sourcecode]
Optimized Query -
[sourcecode language='sql']
select * from emp
where emp_id IN
(select emp_id from emp
MINUS select gid from company)
[/sourcecode]
Avoid using EXISTS and instead use Joins for Unique Scan Indexes and tables with less number of records
eg.
Normal Query -
[sourcecode language='sql']
select emp_name,emp_id
from emp
where EXISTS (select * from company where gid = id)
[/sourcecode]
Optimized Query -
[sourcecode language='sql']
select name,id
from emp E,company C
where E.id = C.gid
[/sourcecode]
Avoid using of HAVING clause, instead make use of WHERE clause
Reason:
HAVING clause fetches all the rows and only then filters the selected rows, this would eat up lots of system resources and time. Thus as far as possible do not make use of HAVING clause in select query.
eg.
[sourcecode language='sql']
select *
from emp
where emp_name = ‘Bond’
having emp_id = ‘007’
[/sourcecode]
Instead use -
[sourcecode language='sql']
select *
from emp
where emp_name = ‘Bond’
and emp_id = ‘007’
[/sourcecode]
Avoid using DISTINCT and use EXISTS instead
eg.
Normal Query -
[sourcecode language='sql']
select DISTINCT emp_id, emp_name
from company C, emp E
where C.gid = E.emp_id
[/sourcecode]
Optimized Query -
[sourcecode language='sql']
select emp_id, emp_name
from emp E
where EXISTS (select ‘X’
from company C
where C.gid = e.emp_id );
[/sourcecode]
EXISTS processes faster as the query will be terminated once the sub-query has been satisfied .
Make use of DECODE to Reduce Processing
The DECODE statement avoids repetitive scanning of the same rows and the joins to the same table.
eg:
[sourcecode language='sql']
select SUM(SAL)
from emp
where dept_num = 007
AND emp_name LIKE ‘BOND%’;
[/sourcecode]
The same query can be optimized as
[sourcecode language='sql']
select
SUM(DECODE(dept_num,007, SAL, NULL)) D007_SAL,
from emp
where emp_name LIKE ‘BOND%’;
[/sourcecode]
DECODE can also be made used in place of GROUP BY or ORDER BY clause.
Some fast tips -
Avoid using of OR and instead make use of UNION -
Consider the use of UNION instead of OR in the WHERE clauses. Using OR on an indexed column causes the optimizer to perform a full-table scan rather than an indexed retrieval.
Avoid using of NOT when ever dealing with Indexed Columns
Reason:
Whenever NOT is made use of, a full-table scan is performed and it wont be using the index.
Indexes are built on the records which exists in a table, and not on which does not exist in a table.
The following statement will never use the index on emp_id column
[sourcecode language='sql']
select * from
emp
where emp_id not like ‘9%’
[/sourcecode]
Avoid usage of ‘!‘ operator use ‘>‘ instead
eg.
[sourcecode language='sql']
select *
from emp
where emp_id != 007
[/sourcecode]
Optimize by using :
[sourcecode language='sql']
select *
from emp
where emp_id > 0
[/sourcecode]
Avoid usage of ‘||‘ operator use ‘AND‘ instead
eg.
[sourcecode language='sql']
select *
from emp
where emp_name || emp_id = ‘BOND007’
[/sourcecode]
Optimize by using :
[sourcecode language='sql']
select *
from emp
where emp_firstname = ‘BOND’
AND emp_id = ‘007’
[/sourcecode]
Avoid usage of ‘TO_CHAR‘ and use ‘TRUNC‘ instead
eg.
[sourcecode language='sql']
select *
from emp
where to_char(emp_joindate,’mmddyyyy’) < to_char(sysdate,’mmddyyyy’)
[/sourcecode]
Optimize by using :
[sourcecode language='sql']
select *
from emp
where emp_joindate < trunc(sysdate)
[/sourcecode]
Avoid usage of ‘*’ instead make use of primary key or non null index column
eg.
[sourcecode language='sql']
select count(*) From emp
[/sourcecode]
Optimize by using :
[sourcecode language='sql']
select count (primary_key or non null INDEX column ) from emp
[/sourcecode]
Hi ,
In the examples provided i have a suggestion instead of using * you can still optimize using the column names .
Leave a Reply