Nov 182015
 

Basic Tuning for SQL Query

 

Ordering of the FROM clause. 

When we are fetching data from more than one table, write the table with fewer rows at the end of the FROM clause. Therefore, the table returning the least number of rows should be last in the FROM list.

Example:

SELECT COL1 FROM TAB1, TAB2, TAB3

WHERE VAL = ‘ABC’;

In the above query the no. of rows present in TAB1 is maximum, followed by TAB2 and TAB3 contains the least rows. Therefore, order of arranging tables in FROM clause is TAB1, TAB2 and then TAB3

Disabling use of indexes 

Generally indexed are disabled inadvertently due to modification of column data type.

Example:

SELECT INC_AMT FROM TAB1

WHERE TO_CHAR (TRAN_ID) = ‘1234’;

The reason for this is that oracle cannot use an index if the column is modified in the where clause. It is up to the developer to ensure that, WHERE clause columns, aren’t modified in any way.

Below query will also stop the usage of index

SELECT INC_AMT FROM TAB1

WHERE TRAN_ID + 1 = 1234;

Correct usage:

SELECT INC_AMT FROM TAB1

WHERE TRAN_ID = ‘1234’;

Use of ! = operator

Unless it is absolutely necessary, avoid using the! = operator. The use of this operator disables the use of indexes, because Oracle assumes that the query will be retrieving most of the rows in the table. This operator results in a Full table scan which can be lead to serious performance issues if the table size is large.

Index Rule

Only use indexes for retrieving data from tables, where your want less than 20 percent of the rows. Full table scans are quicker if you want more than 20 percent of a table’s data.

INSERT using *

Although the use of the character ‘*’ is allowed by the oracle, it is considered to be bad programming practice and hence it should be avoided.

Resource intensive operations 

Queries, which use DISTINCT, UNION, MINUS, INTERSECT, ORDER BY and GROUP BY clause upon Oracle to perform resource intensive sorts. Generally, these operators cause a full table scan. Re-writing SQL query without these operators using other alternative approaches is the best way.

Avoiding unnecessary joins

SQL is a very expressive language and there are normally several ways of performing the same query.  Developers should investigate different wordings of the same query, so as to identify the optimal query.

Below query can be written in multiple ways

Example 1:

SELECT TAB2.INC_AMT FROM TAB1, TAB2

WHERE TAB1.COL1 = TAB2.COL1

AND TAB1.FLG=’Y’;

Example 2:

SELECT TAB2.INC_AMT

  FROM TAB2

WHERE TAB2.COL1 IN (SELECT TAB1.COL1

                    FROM TAB1

                    WHERE TAB1.FLG =’Y’);

Example 2 executed in a less time and plan cost is lower than example 1. Avoiding unnecessary joins is a good SQL practice. Therefore, example 2 is an optimal way of writing the above query.

Problem with Implicit type conversion / using functions on Indexed columns.

In Oracle, the same Information stored in different data type columns in two tables. E.g. the PO Line Id in PO_Lines table is “number”, whereas the same Line_id is stored in PK1_Value (Varchar2) in Fnd_Attached_Documents table. So, if we need to join these tables with this criterion, we are comparing “varchar2” with “Number”. In these cases, oracle uses implicit conversion of data types and leads to full table scan.

Use Table Aliases:

Use table aliases to address tables when more than one table present in FROM clause. It will reduce the parser’s work. In addition, avoids the confusion, as same column name may exist in more than one table. Later a column with the same name added to other table and table aliases will prevent the column ambiguity. 

SELECT emp.emp_no,emp.emp_name,emp.dept_no

        FROM employee emp, Department dept

     WHERE dept.dept_no = emp.dept_no; 

Use EXISTS in the place of IN

Whenever an outer query check a presences of some records in an inner query then use EXISTS with it. IN option in a query will give a full table scan. It will affect the performance of a query when huge records present in the table.

E.g. select customer details, who are all also a members of a club

SELECT cust.cno,cust.cname,cust.addr

 FROM customer cust

WHERE exists (Select * from club Where cno = cust.cno)

Also use NOT EXIST instead of NOT IN because NOT IN cannot use indexes.

Ordering Table in FROM clause

Arrange the tables in the right end of FROM clause in such a way that:

    1. The table which has least number of rows
    2. The table to which input conditions applied
    3. The table which has more joins in WHERE clause, the table in the right end of the FROM clause is called driving table.

Use ‘>=, <=’ operators instead of BETWEEN

The BETWEEN operator will be converted to ‘<= and >=’ internally this will affect the execution time of the query.

Use of GROUP BY and ORDER BY

Filter all the possible conditions in the WHERE clause instead of using HAVING clause.

In addition, do not use ORDER BY clause whenever you are using UNION, MINUS, DISTINCT or GROUP BY kind of features because these queries will be in sorted form in ascending order.

UNION and UNION ALL

When use more than one UNION in a query, use UNION ALL for all the queries and UNION for last join of the query.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)