5 Useful Tips To Tune Your SQL Statements

Whenever we get some database related task to perform some query for analysis purpose or any other reports related, then we just don't think much and start writing SQL Query to fetch the data. You simply run a query specifying the condition you want, and the database engine process to get it, but sometimes, you can improve the performance of your SQL queries by taking care of little things.

The following are the 5 useful tips to tune and optimize Oracle SQL query.

1. Use Table Joins Rather than Multiple Queries

If you want to fetch the data from multiple tables then do not write multiple SQL statements, write single statement by joining those tables to get the results. The following is the bad example given to get the data from tables:

SELECT empno, ename, deptno
FROM scott_emp
WHERE empno = 7369;

SELECT department_name
FROM dept
WHERE department_id = 20;

Instead of writing two queries to get the information, you should write a single query to get the results in more meaningful way. The following is the example to get the same result as above but in a better way through a single query:

SELECT a.empno,
a.ename,
a.deptno,
b.department_name
FROM scott_emp a, dept b
WHERE empno = 7369 AND a.deptno = b.department_id;

2. Use Fully Qualified Column References In Where Clause

Always use table aliases in your queries and use the alias for each column in your query's where clause and this is very important. The following is the example in which no column references are used.

SELECT empno,
ename,
deptno,
department_name
FROM scott_emp, dept
WHERE empno = 7369 AND deptno = department_id;

This is not recommended and will reduce the performance of the query. The following query shows the right way to do it:

SELECT a.empno,
a.ename,
a.deptno,
b.department_name
FROM scott_emp a, dept b
WHERE a.empno = 7369 AND a.deptno = b.department_id;

3. Use CASE Expressions Rather than Multiple Queries

Use CASE expressions rather than multiple queries when you need to do calculations on the same rows in a table. The following are the examples:

SELECT COUNT ( * )
FROM scott_emp
WHERE deptno = 20;

SELECT COUNT ( * )
FROM scott_emp
WHERE deptno = 30;

SELECT COUNT ( * )
FROM scott_emp
WHERE deptno = 40;

Instead of this you can perform this task in a single query, here is the example:

SELECT COUNT (CASE WHEN deptno = 20 THEN 1 ELSE NULL END) dept_20,
COUNT (CASE WHEN deptno = 30 THEN 1 ELSE NULL END) dept_30,
COUNT (CASE WHEN deptno = 40 THEN 1 ELSE NULL END) dept_40
FROM scott_emp;

4. Use Exists Rather Than IN

EXISTS is faster than IN, because EXISTS just checks for the existence of rows, whereas IN checks for actual values. The following are the examples:

SELECT a.empno, a.ename
FROM scott_emp a
WHERE a.deptno IN (SELECT b.department_Id
FROM dept b);

SELECT a.empno, a.ename
FROM scott_emp a
WHERE EXISTS (SELECT 1
FROM dept b
WHERE b.department_id = a.deptno);

5. Add Indexes To All Tables Required For Query

Just check for the Indexes on the tables on which you are going to write the query, if any table is not having index then you must create an index on that columns which you are planning to use in your query's where clause.