SQL Query Tuning

Non-Column Expressions 

-----------------------------------

Write the query as

SELECT id, name, salary 

FROM employee 

WHERE salary < 25000;

Instead of:

SELECT id, name, salary 

FROM employee 

WHERE salary + 10000 < 35000;

Usage of DECODE to avoid multiscan

----------------------------------------------

To avoid the scanning of same rows or joining the same table repetitively. 

DECODE can also be made used in place of GROUP BY or ORDER BY clause. 

For Example: Write the query as

SELECT id FROM employee 

WHERE name LIKE 'S%' 

and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee 

WHERE name LIKE 'Ramesh%';