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

Performance Questions

How do I create the PLUSTRACE role for use with AUTOTRACE?
How do I interpret the output from Explain Plan?

The Basics of Oracle Performance Tuning

Performance is a very typical word associated with every other concepts, things, workforce, technology that drives the very business.

For Oracle database also no exception .You will find thousands of articles where cmplicated thing are represented simple.And many other articles where simple thing get twisted with authr's imagination.

To me perfomance is someting in some good enough form to attain the result.

Oracle perofrmance often termed as High perofmance depends on very basic components it is made of. We can divide oracle performance in to following category.

1. Instance/Memory Tuning Tuning

2. Network Tuning

3. SQL/ Application Tuning

4. Database Tuning

We will try to limit our scope of this discussion in its broad perpescetive for understanding purpose.I will try to give more detail explanations using a case study for better understanding in my next blog.

New Topics in This Blog

On Readers Demand - Introducing Performane Tuning in a NutShell!!