Select the Most Efficient Table Name Sequence

Oracle parser always processes table named from right to left, the table name you specify last (driving table ) is actually the first table processed.
If you specify more then one table in a FROM clause of the SELECT statement , you must choose the table containing the lowest number of rows as the driving table.
When Oracle processes multiple tables, it uses an internal sort/merge procedure to join those tables. First it scans and sorts the first table (the one specified last in the FROM clause).
Next it scans the second table (the one prior to the last in the FROM clause) and merges all of the rows retrievedd fromn the second table with those retrived from the first table.

Eg: Table TAB1 has 20,000 rows
Table TAB2 has 1 row.

For best approach select TAB2 as the driving table.

select count(*) from TAB1, TAB2 -- (Around 1 second taken for execution)

select count(*) from TAB2, TAB1 -- (Around 25 second taken to execute)

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

Eg:

The EMP table represents the intersection between the LOCATION table and the CATEGORY table.

select ....

from LOCATION L, CATEGORY C, EMP E

where E.EMP_NO Between 100 and 200

and E.CAT_NO =C.CAT_NO

and E.LOCN=L.LOCN

Is more efficient than this next example

select ...

from EMP E, LOCATION L,CATEGORY C

where E.CAT_NO=C.CAT_NO

and E.LOCN=L.LOCN

and E,EMP_NO Between 100 and 200

Position of Joins in WHERE clause

Table joins should be written first before any condition of the WHERE clause. And the conditions which filter out the maximum records should be placed at the end after the joins as the parsing is done from BOTTOM to TOP

Eg:
Least Efficient: (Total CPU-155 sec)

SELECT ...

FROM emp e

WHERE sal > 40000 AND job = 'MANAGER'

AND 25 < (SELECT COUNT (*) FROM emp

WHERE mgr = e.empno);

Most Efficient (Total CPU- 10.7 sec)

SELECT ...

FROM emp e

WHERE 25 < (SELECT COUNT (*)

FROM emp

WHERE mgr = e.empno)

AND sal > 40000;

Avoid using * in the SELECT clause

The dynamic SQL column reference (*) gives you a way to refer to all of the columns of a table.
Do not use * feature because it is a very inefficient one, as the * has to be converted to each column in turn.
The SQL parser handles all the field references by obtaining the names of the valid columns from the data dictionary and substitutes them on the command line which is time consuming.

Reduce the Number of Trips to the Database

Every time a SQL statement is executed, ORACLE needs to perform many internal processing steps; the statement needs to be parsed, indexes evaluated,variables bound, and data blocks read. The m ore you can reduce the num ber of database accesses, the m ore overhead you can save.

For e.g.

There are 3 distinct ways of retrieving data about em ployees who have em ployee num bers 0342 or 0291.

Method 1 ( Least Efficient) :

SELECT emp_name, salary, grade

FROM emp

WHERE emp_no = 0342;

SELECT emp_name, salary, grade

FROM emp

WHERE emp_no = 0291;

Method 2 ( Next Most Efficient) :

DECLARE

CURSOR C1( E_NO NUMBER) IS

SELECT EMP_NAME, SALARY, GRADE FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1( 342);

FETCH C1 INTO , , ;

OPEN C1( 291);

FETCH C1 INTO , , ;

CLOSE C1;

END;

Method 3 ( Most Efficient) :

SELECT a.emp_name, a.salary, a.grade,

b.emp_name, b.salary, b.grade

FROM emp a, emp b

WHERE a.emp_no = 0342

AND b.emp_no = 0291;

Not e:

One simple way to increase the number of rows of data you can fetch with one database access and thus reduce the number of physical calls needed is to reset the ARRAYSI ZE parameter in SQL* Plus, SQL* Form s & Pro* C. Suggested value is 200.

Use DECODE to Reduce Processing

The DECODE statement provides a way to avoid having to scan the same rows repetitively or to join the same table repetitively.

For e.g.

SELECT COUNT (*), SUM (sal)

FROM emp

WHERE dept_no = 0020 AND ename LIKE 'SMITH%';

SELECT COUNT (*), SUM (sal)

FROM emp

WHERE dept_no = 0030 AND ename LIKE 'SMITH%';

You can achieve the same result much m ore efficiently with DECODE:

SELECT COUNT (DECODE (dept_no, 0020, ‘x’, NULL)) d0020_count,

COUNT (DECODE (dept_no, 0030, ‘x’, NULL)) d0030_count,

SUM (DECODE (dept_no, 0020, sal, NULL)) d0020_sal,

SUM (DECODE (dept_no, 0030, sal, NULL)) d0030_sal

FROM emp

WHERE ename LIKE 'SMITH%';

Similarly, DECODE can be used in GROUP BY or ORDER BY clause effectively.

Combine Simple, Unrelated Database Accesses

I f you are running a number of simple database queries, you can improve performance by combining them into a single query, even if they are not related.

For e.g.

SELECT NAME

FROM emp

WHERE emp_no = 1234;

SELECT NAME

FROM dpt

WHERE dpt_no = 10;

SELECT NAME

FROM cat

WHERE cat_type = 'RD';

The above three queries can be com bined as shown below:

SELECT e.NAME, d.NAME, c.NAME

FROM cat c, dpt d, emp e, DUAL x

WHERE NVL ('X', x.dummy) = NVL ('X', e.ROWID)(+)

AND NVL ('X', x.dummy) = NVL ('X', d.ROWID)(+)

AND NVL ('X', x.dummy) = NVL ('X', c.ROWID)(+)

AND e.emp_no(+) = 1234

AND d.dept_no(+) = 10

AND c.cat_type(+) = 'RD'

Deleting Duplicate Records

The efficient way to delete duplicate records from a table is shown below. I t takes advantage of the fact that a row’s ROWID must be unique.

DELETE FROM emp e

WHERE e.ROWID > (SELECT MIN (x.ROWID)

FROM emp x

WHERE x.emp_no = e.emp_no);

Use TRUNCATE instead of DELETE

When rows are removed from a table, under normal circumstances, the rollback segments are used to hold undo inform at ion; if you do not commit your transaction, Oracle restores the data to the state it was in before your transaction started.
With TRUNCATE, no undo information is generated. Once the table is truncated, the data cannot be recovered back. I t is faster and needs fewer resources.
Use TRUNCATE rather than DELETE for wiping the contents of small or large tables when you need no undo inform at ion generated.

Issue Frequent COMMIT statements

Whenever possible, issue frequent COMMIT statements in all your programs. By issuing frequent COMMIT statements, the performance of the program is enhanced & it s resource requirements are minimized as COMMIT frees up the following resources:

Information held in the rollback segments to undo the transaction, if necessary.
All locks acquired during statement processing.
Space in the redo log buffer cache
Overhead associated with any internal Oracle mechanisms to manage the resources in the previous three items.

Counting Rows from Tables

Contrary to popular belief, COUNT(* ) is faster than COUNT( 1). If the rows are being returned via an index, counting the indexed column– for example, COUNT( EMPNO) is faster st ill.

Use WHERE in Place of HAVING

Avoid including a HAVING clause in SELECT statements. The HAVING clause filters select ed rows only after all row s have been fetched. This could include sorting, summing, etc. Restricting rows via the WHERE clause, rather than the HAVING clause, helps reduce these overheads.

For e.g.

Least Efficient :

SELECT region, AVG (loc_size)

FROM LOCATION

GROUP BY region

HAVING region != 'SYDNEY' AND region != 'PERTH';

Most Efficient :

SELECT region, AVG (loc_size)

FROM LOCATION

WHERE region != 'YDNEY' AND region != 'PERTH'

GROUP BY region;

Minimize Table Lookups in a Query

To improve performance, minimize the num ber of table lookups in queries, particularly if your statements include sub- query SELECTs or m ult i- column UPDATEs.

For e.g.

Least Efficient :

SELECT tab_name

FROM TABLES

WHERE tab_name = (SELECT tab_name

FROM tab_columns

WHERE VERSION = 604) AND db_ver = (SELECT db_ver

FROM tab_columns

WHERE VERSION = 604)

Most Efficient :

SELECT tab_name

FROM TABLES

WHERE (tab_name, db_ver) = (SELECT tab_name, db_ver

FROM tab_columns

WHERE VERSION = 604)

Mult i- colum n UPDATE e.g.

Least Efficient :

UPDATE emp

SET emp_cat = (SELECT MAX (CATEGORY)

FROM emp_categori es),

sal_range = (SELECT MAX (sal_range)

FROM emp_categori es)

WHERE emp_dept = 0020;

Most Efficient :

UPDATE emp

SET (emp_cat, sal_range) = (SELECT MAX (CATEGORY), MAX (sal_range)

FROM emp_categori es)

WHERE emp_dept = 0020;

Reduce SQL Overheads via " Inline" Stored Functions

SELECT h.empno, e.ename, h.hist_type, t.type_desc, COUNT (*)

FROM history_type t, emp e, emp_history h

WHERE h.empno = e.empno AND h.hist_type = t.hist_type

GROUP BY h.empno, e.ename, h.hist_type, t.type_desc;

The above statement's performance may be improved via an inline function call as shown below:

FUNCTION lookup_hist_type (typ IN NUMBER)

RETURN VARCHAR2

tdesc VARCHAR2 (30);

CURSOR c1

SELECT type_desc

FROM history_type

WHERE hist_type = typ;

BEGIN

OPEN c1;

FETCH c1

INTO tdesc;

CLOSE c1;

RETURN (NVL (tdesc, '?'));

END;

FUNCTION lookup_emp (emp IN NUMBER)

RETURN VARCHAR2

ename VARCHAR2 (30);

CURSOR c1

SELECT ename

FROM emp

WHERE empno = emp;

BEGIN

OPEN c1;

FETCH c1

INTO ename;

CLOSE c1;

RETURN (NVL (ename, '?'));

END;

SELECT h.empno, lookup_emp (h.empno), h.hist_type,

lookup_hist_type (h.hist_type), COUNT (*)

FROM emp_history h

GROUP BY h.empno, h.hist_type;

Use Table Aliases

Always use table aliases & prefix all column names by their aliases where there is m ore than one table involved in a query. This will reduce parse time and prevent syntax errors from occurring when ambiguously named columns are added later on.

Use EXISTS in Place of IN for Base Tables

Many base table queries have to actually join with another table to satisfy a selection criteria. In such cases, the EXISTS ( or NOT EXISTS) clause is often a better choice for performance.

For e.g.

Least Efficient :

SELECT *

FROM emp --( Base Table)

WHERE empno > 0

AND deptno IN (SELECT deptno

FROM dept

WHERE loc = 'MELB');

Most Efficient :

SELECT *

FROM emp

WHERE empno > 0

AND EXISTS (SELECT 'X'

FROM dept

WHERE deptno = emp.deptno

AND loc = 'MELB')

Use NOT EXISTS in Place of NOT IN

In sub-query statements such as the following, the NOT IN clause causes an internal sort/ merge.
The NOT IN clause is the all- time slowest test, because it forces a full read of the table in the sub-query SELECT.
Avoid using NOT IN clause either by replacing it with Outer Joins or with NOT EXISTS clause as shown below:

SELECT ...

FROM emp

WHERE dept_no NOT IN (SELECT dept_no

FROM dept

WHERE dept_cat = 'A');

To improve the performance, replace this code with:

Method 1 ( Efficient) :

SELECT ...

FROM emp a, dept b

WHERE a.dept_no = b.dept_no(+)

AND b.dept_no IS NULL

AND b.dept_cat(+) = 'A'

Method 2 ( Most Efficient) :

SELECT ...

FROM emp e

WHERE NOT EXISTS (SELECT 'X'

FROM dept

WHERE dept_no = e.dept_no

AND dept_cat = ‘a’);

Use Joins in Place of EXISTS

In general join tables rather than specifying sub- queries for them such as the following:

SELECT ename

FROM emp e

WHERE EXISTS (SELECT ‘x’

FROM dept

WHERE dept_no = e.dept_no AND dept_cat = ‘a’);

To improve the performance, specify:

SELECT ename

FROM dept d, emp e

WHERE e.dept_no = d.dept_no AND d.dept_cat = ‘a’;

Use EXISTS in Place of DISTINCT

Avoid joins that require the DISTINCT qualifier on the SELECT list when you submit queries used to determine information at the owner end of a one- to- many relationship ( e.g. departments that have many employees).

For e.g.

Least Efficient :

SELECT DISTINCT dept_no, dept_name

FROM dept d, emp e

WHERE d.dept_no = e.dept_no

Most Efficient :

SELECT dept_no, dept_name

FROM dept d

WHERE EXISTS (SELECT ‘x’

FROM emp e

WHERE e.dept_no = d.dept_no);

EXISTS is a faster alternative because the RDBMS kernel realizes that when the sub- query has been satisfied once, the query can be terminated.

Identify " Poorly Performing" SQL statements

Use the following queries to identify the poorly performing SQL statements.

SELECT executions, disk_reads, buffer_gets,

ROUND ((buffer_gets - disk_reads) / buffer_gets, 2) hit_ratio,

ROUND (disk_reads / executions, 2) reads_per_run, sql_text

FROM v$sqlarea

WHERE executions > 0

AND buffer_gets > 0

AND (buffer_gets - disk_reads) / buffer_gets < 0.80

ORDER BY 4 DESC;

Use TKPROF Utility to View Performance Statistics

The SQL trace facility writes a trace file containing performance statistics for the SQL statements being executed.
The trace file provides valuable information such as the number of parses, executes and fetches performed, various types of CPU & elapsed times, the number of physical & logical reads, etc, that you can use to tune your system
To enable SQL trace, use the following query:

ALTER SESSION SET SQL_TRACE TRUE

To globally enable SQL trace, you must set SQL_TRACE parameter to TRUE in init.ora. USER_DUMP_DEST parameter specifies the directory where SQL trace writes the trace file.

Use EXPLAIN PLAN To Analyze SQL Statements

Explain Plan is an Oracle function that analyzes SQL statements for performance without running the queries first .
The results of the Explain Plan tell you the order that Oracle will search/ join the tables, the types of access that will be employed ( indexed search or full table scan), and the names of indexes that will be used.
You should read the list of operations from the inside out and from top to bottom . Thus, if two operations are listed, the one that is the most indented will usually be executed first .
If the two operations are at the same level of indentation, then the one that is listed first ( with the lowest operation number) will be executed first.
NESTED LOOPS joins are among the few execution paths that do not follow the “ read from the inside out” rule of indented execution paths.
To read the NESTED LOOPS execution path correctly, examine the order of the operations that directly provide data to the NESTED LOOPS operation. Of those operations, the operation with the lowest number is executed first .

Use Indexes to Improve Performance

An index is a conceptual part of a database table that may be used to speed up the retrieval of data from that table. Internally, ORACLE uses a sophisticated self- balancing B- tree index structure.
Indexed retrieval of data from a database is almost always faster than a full- table scan.
The ORACLE optimizer uses the indexes defined for a table when it figures out the most efficient retrieval path for a query or update statement.
ORACLE also uses indexes in performing more efficient joins of multiple tables.
Another benefit of indexes is that they provide a way to guarantee the uniqueness of the primary key in a table.
You can index any column in a table except those defined with data types of LONG or LONG RAW .
In general, indexes are most useful when they are specified on large tables. If small tables are frequently joined, however, you’ll find that performance improves when you index these tables too.
Although indexes usually provide performance gains, there is a cost to using them . Indexes require storage space. They also require maintenance. Every time a record is added to or deleted from a table and every time an indexed column is modified, the index( es) it self must be updated as well. This can mean 4 or 5 extra disk I / Os per INSERT, DELETE or UPDATE for a record.
Because indexes incur the overhead of data storage and processing, you can actually degrade response time if you specify indexes that you don’t use.
The maximum number of indexes is usually between 4 & 6 per table.
Do keep the number of indexes over a single table to a minimum , but if an index is useful and response times can be kept below the agreed- upon limit for your site, then don’t hesitate to create the index.

Avoid Calculations on Indexed Columns

If the indexed column is a part of a function ( in the WHERE clause), the optimizer does not use an index and will perform a full- table scan.
Note: The SQL functions MIN and MAX are exceptions to this rule and will utilize all available indexes.

For e.g.
Least Efficient :

SELECT ...

FROM dept

WHERE sal * 12 > 25000;

Most Efficient :

SELECT ...

FROM dept

WHERE sal > 25000/12;

Automatically Suppressing Indexes

If a table has two ( or more) available indexes, and that one index is unique and the other index is not unique, in such cases, ORACLE uses the unique retrieval path and completely ignores the second option.

For e.g.

SELECT ename

FROM emp

WHERE empno = 2362 AND deptno = 20;

Here, there is a unique index over EMPNO and a non- unique index over DEPTNO The EMPNO index is used to fetch the row. The second predicate ( DEPTNO = 20) is then evaluated ( no index used).
The Explain Plan is as shown below:

TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX

Avoid NOT on Indexed Columns

In general, avoid using NOT when testing indexed columns. The NOT function has the same effect on indexed columns that functions do.
When ORACLE encounters a NOT, it will choose not to use the index and will perform a full- table scan instead.

For e.g.

Least Efficient : ( Here, index will not be used)

SELECT ...

FROM dept

WHERE dept_code != 0;

Most Efficient : ( Here, index will be used)

SELECT ...

FROM dept

WHERE dept_code > 0;

In a few cases, the ORACLE optimizer will automatically transform NOTs
( when they are specified with other operators) to the corresponding functions:

NOT > to < =
NOT > = to <
NOT < to > =
NOT < = to >

Use > = instead of >

If there is an index on DEPTNO, then try

SELECT *

FROM emp

WHERE deptno > = 4

Instead of

SELECT *

FROM emp

WHERE deptno > 3

Because instead of looking in the index for the first row with column = 3 and
then scanning forward for the first value that is > 3, the DBMS may jump directly to the first entry that is = 4.

Use UNION in Place of OR ( in case of Indexed Columns)

In general, always use UNION instead of OR in WHERE clause. Using OR on an indexed column causes the optimizer to perform a full- table scan rather than an indexed retrieval.
Note, however, that choosing UNION over OR will be effective only if both columns are indexed;
if either column is not indexed, you may actually increase overheads by not choosing OR.

In the following example, both LOC_ID and REGION are indexed. Specify the following:

SELECT loc_id, loc_desc, region

FROM LOCATION

WHERE loc_id = 10

UNION

SELECT loc_id, loc_desc, region

FROM LOCATION

WHERE region = 'MELBOURNE'

instead of

SELECT loc_id, loc_desc, region

FROM LOCATION

WHERE loc_id = 10

OR region = 'MELBOURNE'

If you do use OR, be sure that you put the most specific index first in the OR’s predicate list , and put the index that passes the most records last in the list .

Note that the following:

WHERE KEY1 = 10 Should return least rows
OR KEY2 = 20 Should return most rows

is internally translated to:

WHERE KEY1 = 10
AND ( KEY1 NOT = 10 AND KEY2 = 20)

Use IN in Place of OR

The following query can be replaced to improve the performance as shown below:

Least Efficient :

SELECT ...

FROM LOCATION

WHERE loc_id = 10 OR loc_id = 20 OR loc_id = 30

Most Efficient :

SELECT ...

FROM LOCATION

WHERE loc_id IN(10,20,30)

Avoid IS NULL and IS NOT NULL on Indexed Columns

Avoid using any column that contains a null as a part of an index. ORACLE can never use an index to locate rows via a predicate such as IS NULL or IS NOT NULL.
In a single- column index, if the column is null, there is no entry within the index. For concatenated index, if every part of the key is null, no index entry exists. If at least one column of a concatenated index is non- null, an index entry does exist.

For e.g.

If a UNIQUE index is created over a table for columns A and B and a key value of ( 123, null) already exists, the system will reject the next record with that key as a duplicate. However, if all of the indexed columns are null ( e.g. null, null), the keys are not considered to be the same, because in this case ORACLE considers the whole key to be null and null can never equal null. You could end up with 1000 rows all with the same key, a value of null

Because null values are not a part of an index domain, specifying null on an indexed column will cause that index to be omitted from the execution plan.

For e.g.

Least Efficient : ( Here, index will not be used)

SELECT ...

FROM department

WHERE dept_code IS NOT NULL;

Most Efficient : ( Here, index will be used)

SELECT ...

FROM department

WHERE dept_code >= 0 ;

Always Use Leading Column of a Multicolumn Index

If the index is created on multiple columns, then the index will only be used if the leading column of the index is used in a limiting condition ( where clause) of the query. If your query specifies values for only the non- leading columns of the index, then the index will not be used to resolve the query.

Use UNION ALL in Place of UNION ( Where Possible)

When the query perform s a UNION of the results of two queries, the two result sets are merged via UNION ALL operation and then the result set is

processed by a SORT UNIQUE operation before the records are returned to the user.

If the query had used a UNION ALL function in place of UNION, then the SORT UNIQUE operation would not have been necessary, thus improving the performance of the query.

For e.g.

Least Efficient :

SELECT acct_num, balance_amt

FROM debit_transactions

WHERE tran_date = '31- DEC- 95'

UNION

SELECT acct_num, balance_amt

FROM credit_transactions

WHERE tran_date = '31- DEC- 95'

Most Efficient :

SELECT acct_num, balance_amt

FROM debit_transactions

WHERE tran_date = '31- DEC- 95'

UNION ALL

SELECT acct_num, balance_amt

FROM credit_transactions

WHERE tran_date = '31- DEC- 95'

Using Hints

For table accesses, there are 2 relevant hints: FULL and ROWID
The FULL hint tells ORACLE to perform a full table scan on the listed table.

For e.g.

SELECT /* + FULL( EMP) */ *

FROM emp

WHERE empno = 7839;

The ROWID hint tells the optimizer to use a TABLE ACCESS BY ROWID operation to access the rows in the table. In general, you should use a TABLE ACCESS BY ROWID operation, whenever you need to return rows quickly to users and whenever the tables are large. To use the TABLE ACCESS BY ROWID operation, you need to either know the ROWID values or use an index.
If a large table has not been marked as a cached table and you wish for its data to stay in the SGA after the query completes, you can use the CACHE hint to tell the optimizer to keep the data in the SGA for as long as possible.
The CACHE hint is usually used in conjunction with the FULL hint.

For e.g.

SELECT /* + FULL( WORKER) CACHE( WORKER) */ *

FROM worker;

The INDEX hint tells the optimizer to use an index- based scan on the specified table.
You do not need to mention the index name when using the INDEX hint, although you can list specific indexes if you choose.

For e.g.

SELECT /* + INDEX( LODGI NG) */ lodgi ng

FROM lodging

WHERE manager = 'BILL GATES;

The above query should use the index without the hint being needed. However, if the index is non- selective and you are using the CBO, then the optimizer may choose to ignore the index during the processing. In that case, you can use the INDEX hint to force an index- based data access path to be used.
There are several hints available in ORACLE such as ALL_ROWS, FIRST_ROWS, RULE, USE_NL, USE_MERGE, USE_HASH, etc for tuning the queries.

Use WHERE Instead of ORDER BY Clause

ORDER BY clauses use an index only if they meet 2 rigid requirements.
All of the columns that make up the ORDER BY clause must be contained within a single index in the same sequence.
All of the columns that make up the ORDER BY clause must be defined as NOT NULL within the table definition. Remember, null values are not contained within an index.
WHERE clause indexes and ORDER BY indexes cannot be used in parallel.

For e.g.

Consider a table DEPT with the following fields:

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

NON UNIQUE INDEX ( DEPT_TYPE)

Least Efficient : ( Here, index will not be used)

SELECT dept_code

FROM dept

ORDER BY dept_type

Explain Plan:

SORT ORDER BY

TABLE ACCESS FULL

Most Efficient : ( Here, index will be used)

SELECT dept_code

FROM dept

WHERE dept_type > 0

Explain Plan:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

Avoid Converting Index Column Types

ORACLE automatically performs simple column type conversion or casting, when it compares two columns of different types.

Assume that EMPNO is an indexed numeric column.

SELECT ...

FROM emp

WHERE empno = '123'

In fact, because of conversion, this statement will actually be processed as:

SELECT ...

FROM emp

WHERE empno = TO_NUMBER ('123')

Here, even though a type conversion has taken place, index usage is not affected.

Now assume that EMP_TYPE is an indexed CHAR column.

SELECT ...

FROM emp

WHERE emp_type = 123

This statement will actually be processed as:

SELECT ...

FROM emp

WHERE TO_NUMBER (emp_type) = 123

Indexes cannot be used, if they are included in a function. Therefore, this internal conversion will keep the index from being used.

Beware of the WHEREs

Some SELECT statement WHERE clauses do not use indexes at all. Here, are some of the examples shown below:

In the following example, the != function cannot use an index.

Remember, indexes can tell you what is in a table, but not what is not in a table.

All references to NOT, != and < > disable index usage:

Do Not Use:

SELECT account_name

FROM TRANSACTION

WHERE amount != 0;

Use:

SELECT account_name

FROM TRANSACTION

WHERE amount > 0;

In the following example, || is the concatenate function. It's like other functions, disables indexes.

Do Not Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE account_name || account_type = 'AMEXA'

Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE account_name = 'AMEX' AND account_type = 'A';

In the following example, addition (+) is a function and disables the index.

The other arithmetic operators ( - , * , and / ) have the same effect.

Do Not Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE amount + 3000 < 5000;

Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE amount < 2000;

In the following example, indexes cannot be used to compare indexed columns against the same index column. This causes a full- table scan.

Do Not Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE account_name = NVL (:acc_name, account_name);

Use:

SELECT account_name, amount

FROM TRANSACTION

WHERE account_name LIKE NVL (:acc_name, '%');

Avoid Resource Intensive Operations

Queries which uses DISTINCT, UNION, MINUS, INTERSECT, ORDER BY and GROUP BY call upon SQL engine to perform resource intensive sorts.
A DISTINCT requires one sort, the other set operators requires at least two sorts.
For example, a UNION of queries in which each query contains a group by clause will require nested sorts; a sorting operation would be required for each of the queries, followed by the SORT UNIQUE operation required for the UNION.
The sort operation required for the UNION will not be able to begin until the sorts for the group by clauses have completed. The more deeply nested the sorts are, the greater the performance impact on your queries.
Other ways of writing these queries should be found. Most queries that use the set operators, UNION, MINUS and INTERSECT, can be rewritten in other ways.

GROUP BY and Predicate Clauses

The performance of GROUP BY queries can be improved by eliminating unwanted rows early in the selection process.

The following two queries return the same data, however, the second is potentially quicker, since rows will be eliminated before the set operators are applied.

For e.g.

Least Efficient :

SELECT job, AVG (sal)

FROM emp

GROUP BY job

HAVING job = 'PREDIDENT' OR job = 'MANAGER'

Most Efficient :

SELECT job, AVG (sal)

FROM emp

WHERE job = 'PREDI DENT' OR job = 'MANAGER'

GROUP BY job

Using Dates

When using dates, note that, if more than 5 decimal places are added to a date, the date is actually rounded up to the next day.

For e.g.

SELECT TO_DATE ('01-JAN-93') + .99999 FROM DUAL;

returns:

’01- JAN- 93 23: 59: 59’

And,

SELECT TO_DATE ('01-JAN-93') + .999999 FROM DUAL;

returns:

‘02- JAN- 93 00: 00: 00’

Use Explicit Cursors

When implicit cursors are used, two calls are made to the database, once to fetch the record and then to check for the TOO MANY ROWS exception. Explicit cursors prevent the second call.

Tuning EXPort and IMPort

Run Export and Import with a large buffer size, say 10 MB ( 10,240,000) to speed up the process. Oracle will acquire as much as you specify and will not return an error if it can't find that amount.

Set this value to at least as large as the largest table column value, otherwise the field will be truncated.

Table and Index Splitting

Always create separate tablespaces for your tables and indexes and never put objects that are not part of the core Oracle system in the system tablespace. Also ensure that data tablespaces and index tablespaces reside on separate disk drives.

The reason is to allow the disk head on one disk to read the index information while the disk head on the other disk reads the table data. Both reads happen faster because one disk head is on the index and the other is on the table data. If the objects were on the same disk, the disk head would need to reposition it self from the index extent to the data extent between the index read and the data read. This can dramatically decrease the throughput of data in a system.

Oracle Apps Epicentre

Friday, September 26, 2014

SQL Performance Tuning Tips

Reduce the Number of Trips to the Database

Use DECODE to Reduce Processing

Combine Simple, Unrelated Database Accesses

Deleting Duplicate Records

Use TRUNCATE instead of DELETE

Issue Frequent COMMIT statements

Minimize Table Lookups in a Query

Popular Posts

Apps Consultant

Popular Posts