Query writing techniques

1) SELECT only the columns and rows needed.
SQL query becomes faster if you use the actual columns names in SELECT statement instead of than '*‘ and the fewer rows. Also this would  reduce the amount of data needs to be transferred through the network   ,also optimizer pickup the correct indexes otherwise it’ll do a full table scan.

For Example: Write the query as
           SELECT id, first_name, last_name, age, subject FROM student_details;
           Instead of:
           SELECT * FROM student_details; 

2) Try to minimize the number of sub query block in your query
Sometimes you may have more than one sub queries in your main query ( may be correlated subquery ). Try to minimize the number of sub query block in your query. this would give better performance.
For Example: Write the query as
         SELECT name   FROM employee   WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)          FROM employee_details)  AND dept = 'Electronics';
         Instead of:
         SELECT name FROM employee   WHERE salary = (SELECT MAX(salary) FROM employee_details)          AND age = (SELECT MAX(age) FROM employee_details)   AND emp_dept = 'Electronics'; 


3) Perform order by operation as required
Perform order by operation as required try to use numeric field for sort operation.it would give better performance than use of string field.

For Example: Write the query as
        Select e_id,name ,age,salary from employee order by e_id;

4) Use operator EXISTS, IN and table joins appropriately in your query.

• Usually IN has the slowest performance.
• IN is efficient when most of the filter criteria is in the sub-query.
• EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as
        Select * from product p  where EXISTS (select * from order_items o where o.product_id = p.product_id)
Instead of:
       Select * from product p  where product_id IN (select product_id from order_items )


      
5) Try to use UNION ALL in place of UNION. unless you need to retrieve distinct set of records
For Example: Write the query as
       SELECT id, first_name FROM student_details_class10
       UNION ALL
       SELECT id, first_name FROM sports_team;
Instead of:
        SELECT id, first_name, subject FROM student_details_class10
        UNION
        SELECT id, first_name FROM sports_team;  


6) Write ANSCI complaint SQL, it's more clarity
 
User INNER JOIN,OUTER JOINS etc. in the query,
 
SELECT ename, dname FROM emp left outer join  dept  on emp.deptno   = dept.deptno;
SELECT ename, dname FROM emp inner join  dept  on emp.deptno   = dept.deptno;
Instead of:
SELECT ename, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;
SELECT ename, dname FROM emp, dept WHERE emp.deptno  = dept.deptno;

7) Be careful while using <>,!= operators in WHERE clause. 
Even though indexes are in pleased for the columns, above operators (<> != ) are ignored by oracle optimizer , therefore try to implement positive conditions as possible.
For Example: Write the query as
SELECT id, first_name, age FROM student_details WHERE age != 10; 


8) Be careful while using functions/calculations in WHERE clause. 
Usage of functions in predicates will ignore the indexes, try to avoid it or add function base index for those predicates.
For Example: Write the query as
------------------------------------------------------------------------------------------------------------
SELECT id, name, salary FROM employee WHERE dept = 'Electronics' AND location = 'Bangalore';
Instead of:
SELECT id, name, salary FROM employee WHERE dept || location= 'ElectronicsBangalore';
------------------------------------------------------------------------------------------------------------
SELECT id, name, salary FROM employee WHERE salary < 25000;
Instead of:
SELECT id, name, salary FROM employee WHERE salary + 10000 < 35000;

9) Be careful while using  LIKE operators in WHERE clause. 

Where using LIKE in WHERE clause ,NEVER use % in beginning of where condition. Oracle will not use index .Always put some value in beginning then use %.
ename like ‘%CO%’ ; — Index will not be used ename like ‘SC%’; — Index will be used