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 <>
