CODE OPTIMISATION TIPS

1. Avoid using functions on indexed columns

Example:

 where to_char(emp_no) = ‘121212’
The index on emp_no will be suppressed.

Solutions: where emp_no = to_number(’121212’)

2.Comparison of wrong data types, suppresses the index internally

where char_data = 123
The index on char_data will be suppressed as oracle re-writes the query.
Solution:
Avoid such errors
where char_data = to_char(123)

3.Oracle does not make use of an index on a column if NOT IN is present.
Example: 
select * from emp where deptno not in (select deptno from dept where deptstatus = ‘A’) 
Solution:
Use NOT EXISTS instead of NOT IN
Example: select * from emp where not exists (select ‘X’ from dept where deptstatus = ‘A’ and dept. deptno = emp. deptno).

4.the index on brand will not be used.
Example: 
select brand, sum(price) from products group by brand having brand = ‘Arrow’; 
Solution:
Replace HAVING with a WHERE clause where ever possible.
Example: 
select brand, sum(price) from products where brand = ‘Arrow’ group by brand;

5.Use of nested statements slows down the query execution
Example: 
select count(*) from products where itemcode in (Select itemcode from clothing where material = ‘Cotton’);
Solution:
Avoid nested selects.
Example:
select count(*) from products P, clothing C 
where P.barcode = C.barcode and C.material = ‘Cotton’;

6.Use of NVL() suppresses index usage.
Example: 
where NVL(this_value, 99) = other_value 
Try to re-write NVLs.
Example:
where (this_value is null and other_value = 99) OR 
(this_value is not null and this_value = other_value)

7.Composite Indexes are not used if the leading indexed column is not present in the WHERE clause.
Example: select count(*) from products where price <>

Solution: The leading column of the index must be present in the WHERE clause in order to hit the index. 

 select count(*) from products where brand > ‘0’   and price <>