Tips to Tune SQL Queries

In this post I hope to give some knowledge about the Oracle Execution plan and 
the some tips to optimize sql queries for better performances.


Execution Plan
Oracle can run Explain plan for given SELECT, INSERT,UPDATE, DELETE SQL 
statements as below
Oracle -

EXPLAIN PLAN FOR
          SELECT customer_name FROM CUSTOMER;

MySql - 

Explain SELECT customer_name FROM CUSTOMER;

Then it displays the execution plan chosen by the Oracle optimizer, which contains the 

sequence of operations used to run a particular sql statement. Execution plan contains,

 tables, views referenced by the statement

access methods for each tables and views (table access full,table access by index) 

join methods

data operations such as sort, aggregate

cost, cardinality

parallel executions

According to the execution plan you can get the idea to optimize a given sql statement in 

a proper way.Now I am going to explain the ways that can be used to tune/optimize the 

SQL statements for higher efficiency and performance. 

1. Indexing

   Indexes in DBMS are just like indexes in a book. Index creates entry for 

   each value in indexed columns with excluding all other detailed data. 

   So indexes can be stored in a less disk space. 

   Oracle use different types of indexes such as B-Tree (Default), Bitmap index 

   (Use rowid to store), function based index, partitioned index. 

   According to the execution plan, you can get an idea that oracle performs a 

   full table/view scan to find records or it accesses the table/view through the indexes. 

   If it accesses a table with a full scan you can index columns for higher performances. 

   Always create indexes when you need to retrieve less number of rows from 

   large table which contains millions of records.

   When you find a particular record or set of records 

   from a table with using WHERE clause or GROUP BY clause or ORDER BY clause, 

   to achieve higher performances,try to use indexed columns with these clauses.

   As well as, Indexing can be done in different ways to identify particular rows 

   from a table in an efficient way. 

   • • indexing primary and unique columns

     • Short indexes - this means less number of columns for indexes. 

       When there are less index entries it makes easier and 

       less number of comparisons since index entries are often involved 

       in comparisons 

     • 'SELECT first_name, last_name FROM Student 

       WHERE medium = 'ENGLISH'  AND gpa > 3'

       assume you have created index for media columns. IX(medium). 

       So when this statement is executing, Oracle may use 

       IX(media) index to search data from the table. 

       For higher performances you can create new Index on media and gpa

     ex : Create index Student_IX on Student(medium,gpa)

     • You can drop or alter indexes anytime without affecting other indexes, 

       tables and views because indexes are independent. 

     ex:  Drop index Student_IX

            Alter index Student_IX Rename to Student_IX1

     • As well as, join the multiple tables to improve performances and

               use indexed columns with those joins.  

2. Use WHERE clause instead of HAVING clause. Because HAVING clause filters

 rows after selecting all the rows in table/view. Use always indexed columns in the 

WHERE clause with symbol operators 

such as =, >=,<=.

3.  Avoid usage of NOT operator (NOT EXIST, NOT IN, !=). 

Because those prevent use of Indexes. 

Use positive operators such as EXIST, IN, =

ex; IN (val1, val2, val3) => col = val1 OR col val2 OR col val3 

It is enough one of three values evaluate to TRUE.  

But, NOT IN (val1, val2, val3) => col = val1 AND col = VAL2 AND col = VAL3

 Here, all 3 values must evaluate to TRUE. So it slows the performances.  

4. Use Wildcard instead of Substr with the WHERE clause because substr allows 

each single row to substring for performing the comparisons. 

So it prevents the use of indexes. 

For higher performances use postfix wild card with indexed columns 

instead of full wildcard and prefix wildcard.

5. Use 'Select (first_name, last_name)'  instead of 'Select * From..' 

6. Avoid use of IN comparisons. Instead use Straight value comparisons with 

symbol operators such as '= ,<=, >='. 

Because large IN lists also prevent use of indexes.  

7. Use 'UNION ALL' instead of 'UNION'. Because 'UNION' has to filter 

all the duplicate values and select only distinct values.

So, it has an extra step to Sort(Distinct)  which is highly cost than UNION ALL 

because UNION ALL selects all the data

with duplicate values. If it is required to use UNION use it with indexed columns. 

8. If COUNT is used to check the existence of particular column data , Use 'EXIST' for that

If (Select Count(*) from TABLE where student_age=24)>0

When executing this statement COUNT scans all the records but EXIST stops finding records 

when first record is found

So use it as If (Exist(Select 1 from TABLE where student_age=24)) 

9. If you use PLSQL functions or procedures to communicate with the database, avoid the 

execution of dynamic sql. Because it slows the performances of DBMS