Friday, September 26, 2014

SQL Performance Tuning Tips

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
AS
   tdesc   VARCHAR2 (30);
   CURSOR c1
   IS
      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
AS
   ename   VARCHAR2 (30);
   CURSOR c1
   IS
      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.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)