Sponsored

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.

Oracle Forms Recipes eBookDownload

Optimize your sql statements with simple useful changes to perform better.

Post a Comment

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.