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.
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.
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;
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.
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
SELECT TAB2.INC_AMT FROM TAB1, TAB2
WHERE TAB1.COL1 = TAB2.COL1
WHERE TAB2.COL1 IN (SELECT TAB1.COL1
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.
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
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:
- The table which has least number of rows
- The table to which input conditions applied
- 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.