Query Tuning

Query Tuning:

Whenever we write a simple query in SQL, Query performance comes into picture. We often used to tune our SQL, PL/SQL, when it runs too slow or someone like DBA or client complaints about that. Most of us thinks that Query should be tuned once query is ready but Query tuning process starts when we starts writing our Query.

There are few very small things if we just pay attention to that our query can be good in performance.

Here I will mention very small points that can make your code run faster, one more point it is not possible to tune the query using single method, this article is more about writing efficient code rather than tuning the query but these approaches can also be used to tune your code.

Whenever we write a simple query in SQL, Query performance comes into picture. We often used to tune our SQL, PL/SQL, when it runs too slow or someone like DBA or client complaints about that. Most of us thinks that Query should be tuned once query is ready but Query tuning process starts when we starts writing our Query or we can say we should write  query good in performance rather than spending long hours to tune that.

There are few very small things if we just pay attention to that our query can be good in performance.

Here I will mention very small points that can make your code run faster, one more point it is not possible to tune the query using single method, this article is more about writing efficient code rather than tuning the query but these approaches can also be used to tune your code. This Article doesn't talk about Explain plan, TKPROF and SQL Trace.

Few General points to consider while writing PL/SQL code:

1. Avoid selects against the database when the same functionality can be achieved in PL/SQL.
2. Anonymous PL/SQL should be moved into a stored object.
3. Instead of using IN/IN NOT operator try to use EXISTS/NOT EXISTS operators
4. instead of using IS NULL/IS NOT NULL write your code with nvl(column_name,'somevalue') = 'somevalue'
5. Try to avoid use of inequality operators such as ‘’, ‘!=’.

1.select * from dept where dept_no 0;

replace this query with 

1.select * from dept where dept_no >0;

6. Avoid Rollback Segment Problems with huge Updates/Deletes. If you have to UPDATE or DELETE a huge   number of rows, you may encounter problems with Rollback Segments. One solution is to COMMIT after sets of n Rows but make sure with the choice of n, it should neither be too small nor too big.
7. Use table aliasing whenever you are using more than one table and don't forget to prefix the column names with alias name.
8. Do not perform any database operations on the columns used in where clause(indexed column)
Ex: 

1.select * form Employee where substr(Emp_name, 1,7) = 'Reetesh';

we can write this query

1.select * form Employee where emp_name like 'Reetesh%';

9. Union or Union ALL
The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge or filter. If your tables are mutually exclusive (include no duplicate records), or you don't care if duplicates are returned, the UNION ALL is much more efficient.Remember Sorting is very costly process.

10. Oracle automatically performs simple column type conversions (or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle
columns, if the type is supported in the programming language you are using.

Exmaple:
use

1.select * from emp where emp_id = to_number('123');

instead of 

1.select * from emp where to_char(emp_id) = '123'

second statement doesn't use index(assume emp_id is indexed column) while first one does.

11. Use EXISTS in place of DISTINCT(If possible)
Example:

1.SELECT DISTINCT d.deptno ,

2.d.dname ,

3.FROM dept d ,

4.emp e

5.WHERE d.deptno = e.deptno ;

The following SQL statement is a better alternative.

1.SELECT d.deptno ,

2.d.dname

3.FROM dept d

4.WHERE EXISTS ( SELECT e.deptno

5.FROM emp e

6.WHERE d.deptno = e.deptno ) ;

12. In general, join tables rather than specifying sub-queries.

1.SELECT *

2.FROM emp e

3.WHERE EXISTS ( SELECT d.deptno

4.FROM dept d

5.WHERE e.deptno = d.deptno

6.AND d.dname = 'RESEARCH') ;

To improve performance, use the following:

1.SELECT *

2.FROM emp e, dept d

3.WHERE e.deptno = d.deptno

4.AND d.dname = 'RESEARCH' ;