Oracle Query Tuning and Optimization Techniques

With the overwhelming hits from allover the world for my blogs I thought of sharing some performance tuning methods with all.Performance is a parameter, which is critical for any application. Since no users will be willing to compromise on performance of his system, we are subjected to develop a system with less response time and greater throughput.Oracle is one of the widely use database in the world. We will go through some optimization technique that can be implemented to develop  better systems.
Avoid data type mismatch for index columns
Most people, who are novice in database use single quote (in the filter condition) irrespective of the data-type they are querying.This makes oracle to do an internal typecast to the required data-type.

Before Optimization	After Optimization
select name,age,city,state from employee where employee_id='111';	select name,age,city,state from employee where employee_id=111;
Time taken : 2.3 sec	Time taken : 0.3 sec

Avoid functions on indexed columns
Normally, we identify the most frequently queried column from table and create an index on it.But while querying we use functions on the indexed column. This will ultimately annul the purpose of creating an index on the column. 

Before Optimization	After Optimization
select name,age,city from employee where substr(employee_name,1,3)='raj';	select name,age,city from employee where employee_name like 'raj%';
Time Taken : 2.8 sec	Time Taken : 0.3 sec

If we cannot avoid using a function in the query. we can create functional index on the specific column
Specify the condition in WHERE instead of HAVING

Before Optimization	After Optimization
select name,          count(1) from employee group by name having name='raj';	select name,          count(1) from employee where name='raj' group by name;
Time Taken = 2.2 sec	Time Taken = 0.3 sec

This is an obvious error.If you are not filtering before grouping, then all unnecessary data will be grouped and finally the required data will be filtered. Applying filter before grouping will avoid unnecessary sorting and grouping.
 Use Joins instead of inner query

Before Optimization	After Optimization
select employee_name from employee where employee_id in ( select employee_id from defaulters)	select employee_name from employee e,        defaulters d where e.employee_id=d.employee_id
Time Taken : 14.1 sec	Time Taken : 5.5 sec

 This is actually considered as a bad practice in writing SQL, writing inner query results in querying the outer table for each output of the inner query.I would consider this as potential area where we can improve the system performance.
Thanks for Viewing my Blog , please share your views , happy to answer any queries :) .