Tips to Write Efficient SQL Queries

PURPOSE
=======

This document gives some tips on writing effective SQL queries.

SCOPE & APPLICATION
===================

This is intended for any user who uses DML statements in his/her application.

RELATED DOCUMENTS
=================

Oracle7 Server Tuning – On-line Documentation.

SQL is a flexible language. More than one SQL statement may meet the needs of
your application. Although two SQL statements may produce the same result,
Oracle may process one faster than the other. You can use the results of the
EXPLAIN PLAN statement to compare the execution plans and costs of the two
statements and determine which is more efficient. Following are some tips
which help in writing efficient queries.

1. If you specify 2 tables in the FROM clause of a SELECT statement, the
parser will process the tables from right to left, so the table name you
specify last will be processed first. In this case you have to choose one
table as driving table. Always choose the table with less number of records
as the driving table.

2. If three tables are being joined, select the intersection tables as the
driving table. The intersection table is the table that has many tables
dependent on it.

3. Never compare NULL to anything else. All expressions return NULL if one of
the operands is NULL. This is applicable for all operators except
Concatenation operator (||).

4. Use DECODE when you want to scan same rows repetitively or join the same
table repetitively.

Example:
——–

SELECT COUNT(*) , SUM(SAL)
FROM EMP
WHERE DEPTNO = 10
AND ENAME LIKE ‘MILLER’ ;

SELECT COUNT(*) , SUM(SAL)
FROM EMP
WHERE DEPTNO = 20
AND ENAME LIKE ‘MILLER’ ;

The same result can be achieved using a single query as follows:

SELECT COUNT(DECODE(DEPTNO,20,’X’)) DEPT20_COUNT,
COUNT(DECODE(DEPTNO,10,’X’)) DEPT10_COUNT,
SUM(DECODE(DEPTNO,20,SAL)) DEPT20_SAL,
SUM(DECODE(DEPTNO,10,SAL)) DEPT10_SAL
FROM EMP
WHERE ENAME LIKE ‘MILLER’ ;

5. Always use table alias and prefix all column names with the aliases when you
are using more than one table.

6. Use NOT EXISTS in place of NOT IN.

In sub-query statement such as the following NOT IN clause causes an
internal Sort/Merge.

SELECT *
FROM emp e
WHERE e.deptno NOT IN ( SELECT d.deptno
FROM dept d
WHERE d.dname like %S% ) ;

To improve performance use the following code.

SELECT *
FROM emp e
WHERE NOT EXISTS ( SELECT d.deptno
FROM dept d
WHERE d.deptno = e.deptno
AND d.dname LIKE ‘%S%’ ) ;

This would allow such statements to use an index, if one exists.

7. Use Joins in place of EXISTS.

In general, join tables rather than specifying sub-queries.

SELECT *
FROM emp e
WHERE EXISTS ( SELECT d.deptno
FROM dept d
WHERE e.deptno = d.deptno
AND d.dname = ‘RESEARCH’) ;

To improve performance use the following:

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.dname = ‘RESEARCH’ ;

8. EXISTS in place of DISTINCT.

Use EXISTS in place of DISTINCT if you want the result set to contain
distinct values while joining tables.

SELECT DISTINCT d.deptno ,
d.dname ,
FROM dept d ,
emp e
WHERE d.deptno = e.deptno ;

The following SQL statement is a better alternative.

SELECT d.deptno ,
d.dname
FROM dept d
WHERE EXISTS ( SELECT e.deptno
FROM emp e
WHERE d.deptno = e.deptno ) ;

9. The optimizer fully evaluates expressions whenever possible and translates
certain syntactic constructs into equivalent constructs. This is done either
because Oracle can more quickly evaluate the resulting expression than the
original expression or because the original expression is merely a syntactic
equivalent of the resulting expression.

Any computation of constants is performed only once when the statement is
optimized rather than each time the statement is executed. Consider these
conditions that test for monthly salaries greater than 2000:

sal > 24000/12

sal > 2000

sal*12 > 24000

If a SQL statement contains the first condition, the optimizer simplifies it
into the second condition.

Note that the optimizer does not simplify expressions across comparison
operators. The optimizer does not simplify the third expression into the
second. For this reason, application developers should write conditions
that compare columns with constants whenever possible, rather than
conditions with expressions involving columns.

The Optimizer does not use index for the following statement.

SELECT *
FROM emp
WHERE sal*12 > 24000 ;

Instead use the following statement.

SELECT *
FROM emp
WHERE sal > 24000/12 ;

10. Never use NOT on an indexed column. Whenever Oracle encounters a NOT on an
index column, it will perform full-table scan.

SELECT *
FROM emp
WHERE NOT deptno = 0;

Instead use the following.

Note : Optimizer uses the index only when optimizer_goal=FIRST_ROWS,
otherwise it doesn’t use it in the below query either:

SELECT *
FROM emp
WHERE deptno > 0;

11. Never use a function / calculation on an indexed column. If there is any
function is used on an index column, optimizer will not use index. Use some
other alternative.

Examples:

/** Do Not use **/

SELECT *
FROM emp
WHERE SUBSTR(ENAME,1,3) = ‘MIL’ ;

/** Suggested Alternative **/

Note: Optimizer uses the index only when optimizer_goal is set to
FIRST_ROWS.

SELECT *
FROM emp
WHERE ENAME LIKE ‘MIL%’ ;

/** Do Not use **/

SELECT *
FROM emp
WHERE sal != 0 ;

Note: Index can tell you what is there in a table but not what is not in a
table.

Note: Optimizer uses the index only when optimizer_goal = FIRST_ROWS.

/** Suggested Alternative **/

SELECT *
FROM emp
WHERE sal > 0 ;

/** Do Not use **/

SELECT *
FROM emp
WHERE ename || job = ‘MILLERCLERK’ ;

Note: || is the concatenate function. Like other functions it disables
index.

/** Suggested Alternative **/

Note : Optimizer uses the index only when optimizer_goal=FIRST_ROWS.

SELECT *
FROM emp
WHERE ename = ‘MILLER’
AND job = ‘CLERK’ ;

.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PLSQL TUNING e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s