Defining the Optimizer Mode

Prior to Oracle 10g database, a SQL statement will default to the Cost Based Optimizer if any one of the tables involved in the statement has statistics present, unless the OPTIMIZER_MODE initialization parameter is set to RULE. In Oracle 10g database, a SQL statement will always use the CBO unless the /*+ RULE */ hint is present. The optimizer makes an educated guess as to the best access path for the other tables based on statistical information in the data dictionary.

The RDBMS kernel defaults to using the Cost Based Optimizer under a number of situations. The following list details those situations where the CBO will be used:

Initialization parameter: OPTIMIZER_MODE = CHOOSE (Oracle 7 to Oracle 9i, this parameter value is deprecated in Oracle 10g); statistics exist for at least one table involved in the statement

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS (Oracle 7 to Oracle 9i); statistics exist for at least one table involved in the statement

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS (Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS_n (where n = 1, 10, 100, or 1000; Oracle 9i to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

Initialization parameter: OPTIMIZER_MODE = ALL_ROWS (Oracle 7 to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = CHOOSE (Oracle 7 to Oracle 9i, this parameter is deprecated in Oracle 10g); statistics exist for at least one table involved in the statement

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (Oracle 7 to Oracle 9i); statistics exist for at least one table involved in the statement

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_n (where n = 1, 10, 100, or 1000; Oracle 9i to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS (Oracle 7 to Oracle 10g)

Hint: SELECT /*+ FIRST_ROWS */. . .

Hint: SELECT /*+ FIRST_ROWS_n */. . . (where n = 1, 10, 100, or 1000)

Hint: SELECT /*+ ALL_ROWS */. . .

SQL Optimizers - Cost Based

SQL Optimizers - Cost Based : The Oracle Cost Based Optimizer (CBO) is a sophisticated facility. It uses database information (e.g., table size, number of rows, key spread) rather than a set of rigid rules. This information is available once statistics have been calculated on the table, index, or cluster, To calculate statistics, the Oracle ANALYZE command (Oracle 8i or earlier) or the DBMS_STATS supplied packaged (Oracle 9i and Oracle 10g) is used. If a table does not have statistics, the Cost Based Optimizer can use only rule-based logic to select the best access path provided that table is the only table in the query and the database is pre-Oracle 10g. If multiple tables are involved in the query, and one table has statistics, but the others do not, the optimizer will quickly generate statistics for those tables provided that the database is pre-Oracle 10g. Those statistics have a good chance of leading to poor execution plans. It is advised to calculate statistics on all database objects.

In Oracle 10g, if no statistics are available, the CBO will perform dynamic sampling depending on the setting of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter. In Oracle 10g, the Rule Based Optimizer will only be used if the /*+ RULE */ hint is employed. In Oracle 10g, this hint is unsupported and should not be used.

The ANALYZE function (Oracle 8i and earlier) and the DBMS_STATS supplied package (Oracle 9i and Oracle 10g) collects statistics about tables, clusters, and indexes, and stores them in the data dictionary. The DBMS_STATS package lets you store statistics in your own schema in statistics tables that you create with the DBMS_STATS.CREATE_STAT_TABLE procedure. The DBMS_STATS package lets you export these statistics to be imported into another database. However, the CBO will only use statistics present in the data dictionary.

Tuning Database Triggers

Tuning Database Triggers  : This topic addresses how to put triggers to more effective use.

Move Trigger Code to Stored Procedures
Database triggers are not held in a compiled form until PL/SQL 2.3 (Oracle7.3). As a consequence, it is more efficient to move trigger logic to a stored procedure and simply call the procedure from the trigger. This may mean passing all necessary trigger information (NEW and OLD column values, DML statement type, etc.) to the procedure because it does not have access to the information.

Create or Replace Trigger Update_Sum_T1
After Update OR INSERT OR DELETE
On EMP For Each Row
BEGIN
 Update_Sum_Prc (:OLD.salary, :NEW.Salary, :NEW.rowid);
END;

Do Not Use Triggers
The most efficient trigger is no trigger at all. Often triggers are incorrectly used to initialize default table columns, perform simple data integrity checking, or enforce referential integrity rules. All of these trigger components can now be replaced by DDL table constraints and referential integrity. These actions operate below the user level (internal to the RDBMS kernel) and are always more efficient than row-level triggers. For example, this example:

Create or Replace Trigger Validate_EMP_T1 on EMP
After  Insert On EMP For Each Row
DECLARE
 CURSOR C1 IS
 SELECT 'x'
 FROM   dept
 WHERE  dept_no = :NEW.dept_no;
 --
 xdummy VARCHAR2(1);
BEGIN
 IF :NEW.salary <= 0 THEN
  RAISE form_application_error ( -20000,
   'Salary must be greater than $0.00');
 END IF;
 --
 IF :NEW.salary > 100,000
 AND :NEW.sal_grade < 10 THEN
  RAISE form_application_error ( -20001,
   'Salary cannot excede 100,000 for theis Job Grade');
 END IF;
 --
 OPEN C1;
 FETCH C1 INTO xdummy;
 IF C1%notfound THEN
  RAISE form_application_error ( -20002,
  'Invalid Department Number Entered');
 END IF;
 --
 :NEW.last_upd_date := sysdate;
 :NEW.last_upd_user := user;
END;

would be better coded as

CREATE TABLE EMP
(
emp_no  NUMBER(6) NOT NULL,
....
dept_no  NUMBER(4)  CONSTRAINT EMP_dept_FK
     REFERENCES dept(deptno),
salary  NUMBER(12,2)   CHECK (salary > 0),
sal_grade NUMBER(2),
last_upd_date DATE   DEFAULT SYSDATE NOT NULL,
last_upd_user VARCHAR2(10)  DEFAULT USER NOT NULL,
CONSTRAINT check_sal
    CHECK (salary < 100000 OR sal_grade >= 10)
)
Note that DDL DEFAULT values column definitions can often be confusing because default functionality changes depending on how the row column is inserted into the table. We have always assumed that default values would be applied if no column values (NULL) were provided on initial row insertion. But it turns out that not referencing the column during row creation and inserting it with a value of null are two separate things. For example:

Table Tab1
(fld1  VARCHAR2(20),
 fld2  VARCHAR2(10)  DEFAULT USER )
 INSERT INTO tab1        values  ('ABC', NULL);
 INSERT INTO tab1 (fld1) values  ('ABC');
  Fld1          Fld2
  ------------  -------------
  ABC           NULL            <-- Default did not activate
  XYZ           SCOTT           <-- Default did activate
Exploit the WHEN Clause
Oracle has always boasted that DDL-defined referential integrity, check constraints, and default column settings are more efficient than application-level coding. Oracle claims that this built-in functionality operates one level below any user-defined SQL code (whatever that means). These claims are quite true, and the WHEN clause associated with a table trigger is no exception.

Always use the WHEN clause before you resort to inline trigger coding. With versions before Oracle7.3 (triggers are precompiled in Oracle7.3), this is even more important. More often than not, the first line of the trigger code is of the form

IF :NEW.column1 = 'A' THEN
For every row updated, this trigger is to be compiled and executed, and yet the very first line of PL/SQL code excludes the trigger processing. All this expensive processing can be avoided by a well-written WHEN clause. Remember that WHEN clause has access to everything to which a trigger has access, including full SQL functionality. The following example:

Create or Replace Trigger Validate_EMP_T1 on EMP
After Insert  OR Update
On EMP For Each Row
BEGIN
 IF UPDATING THEN
  IF :NEW.salary   !=  :OLD.salary
  OR :NEW.dept_no  !=  :OLD.dept_no THEN
   .
  END IF;
 ELSIF  INSERTING THEN
  IF :NEW.salary  != 0 THEN
   .
  END IF;
 END IF;
END;
would be better coded as

Create or Replace Trigger Validate_EMP_T1 on EMP
After Insert  OR Update
On EMP For Each Row
WHEN ( UPDATING  AND (  NEW.salary  != OLD.salary
                     OR NEW.dept_no != OLD.dept_no )
    OR INSERTING AND    NEW.salary  != 0             )
BEGIN
 IF UPDATING THEN
  .
 ELSIF  INSERTING THEN
  .
 END IF;
END;

Increase the Size of the Shared Pool
Because trigger code is repeatedly parsed and reparsed when it is executed multiple times, retaining the "parse tree" in memory helps performance. Monitor the activity within the shared buffer pool and increase the size of the pool (physical memory permitting) if you think that it is not retaining cursor information long enough.

Stored Procedures and Function Tuning

Stored Procedures and Function Tuning :
Exploit the Power of SQL and PL/SQL
All of the SQL tuning and PL/SQL tuning tips and techniques detailed in this knowledge base apply directly to database procedures.

Increase the Size of the Shared Pool
All executing stored procedures are resident (in parsed pcode form) in the shared buffer pool. Once a procedure has finished executing, it is eligible to be swapped out of memory. Increasing the size of the shared pool will also increase the likelihood of a common stored procedure remaining in memory from one execution to the next. Monitor the shared buffer pool and increase it (physical memory permitting) if you think that it is not retaining commonly used SQL and PL/SQL code segments long enough.

Group Like Procedures into a Stored Package
It is often a good idea to group common procedures into a single package procedure. This is most appropriate when you have a number of procedures that are always referenced together or that physically call each other. Rather than having to call each procedure into memory as required, a common set of procedures is loaded into memory on the initial reference of any procedure within that group (package).

Pinning Procedures into Memory
Once a stored procedure has been loaded into the shared buffer pool, it can be "pinned" into the buffer pool via a special Oracle database package procedure. Pinning large, frequently accessed procedures into memory can be very beneficial. Pinned procedures can never be swapped out of memory until the database is shut down or until they are explicitly unpinned. (Note that flushing the shared pool will not release a pinned object.)

sqlplus> execute dbms_shared_pool.keep  ('scott.Chk_Value_Prc');
       execute dbms_shared_pool.unkeep('scott.Chk_Value_Prc');

Be careful not to pin too many of your application procedures and functions into memory, or you will use up all available shared buffer space and the performance of the remainder of the application will suffer.

Stored Packages Tuning

Stored Packages Tuning :
Exploit the Power of SQL and PL/SQL
All of the SQL tuning and PL/SQL tuning tips and techniques detailed in the previous sections apply directly to database packages.

Group Like Procedures into a Stored Package
It is often a good idea to group common procedures into a single package procedure. This is most appropriate when you have a number of procedures that are always referenced together or that physically call each other. Rather than having to call each procedure into memory as required, a common set of procedures is loaded into memory on the initial reference of any procedure within that group (package).

Pinning Procedures into Memory
Once a stored procedure has been loaded into the shared buffer pool, it can be "pinned" into the buffer pool via a special Oracle database package procedure. Pinning large, frequently accessed procedures into memory can be very beneficial. Pinned procedures can never be swapped out of memory until the database is shut down or until they are explicitly unpinned. (Note that flushing the shared pool will not release a pinned object.)

sqlplus> execute dbms_shared_pool.keep  ('scott.Chk_Value_Prc');
         execute dbms_shared_pool.unkeep('scott.Chk_Value_Prc');

Be careful not to pin too many of your application procedures and functions into memory, or you will use up all available shared buffer space and the performance of the remainder of the application will suffer.

Cost-Based Optimizer Tuning

Cost-Based Optimizer Tuning  : When using the Cost Based Optimizer (CBO), you can manually tune individual SQL statements, overriding the optimizer's decisions by including your own optimization hints within the SQL statement. By including your own optimization hints as "comments" within the SQL statement, you force the statement to follow your desired retrieval path, rather than the one calculated by the optimizer. In the following example, including /*+ RULE */ inside the SELECT statement instructs the optimizer to use the Rule Based Optimizer (RBO), rather than the Cost Based Optimizer:

# sql_jhint.sql
SELECT    /*+ RULE */  . . . .
  FROM    emp, dept
  WHERE   . . .
Note: Most likely, Oracle 10g is the end of the road for the RULE hint.

The optimizer hint(s) can be included only immediately after the initial SQL "action" verb and are ignored when included in INSERT statements.

SELECT  /*+ hint text */  . . . .
DELETE  /*+ hint text */  . . . .
UPDATE  /*+ hint text */  . . . .
Each hint is operational only within the statement block for which it appears.

A statement block is one of the following:

A simple SELECT, DELETE, or UPDATE statement
The parent portion of a complex statement
The subquery portion of a complex statement
Part of a compound query
Consider these examples:

    SELECT  /*+ RULE */  . . . .
    FROM    emp
    WHERE   emp_status = `PART-TIME'
    AND     EXISTS     ( SELECT  /*+ FIRST_ROWS */  `x'
                         FROM    emp_history
                         WHERE   emp_no = E.emp_no
                         AND     emp_status != `PART-TIME' )
    SELECT  /*+ RULE */  . . . .
    FROM    emp
    WHERE   emp_status = 'PART-TIME'
    UNION
    SELECT  /*+ ALL_ROWS */  . . . .
    FROM    emp_history
    WHERE   emp_status != 'PART-TIME'
Note: The RULE hint is a particularly helpful option if you have manually tuned some (but not all) of the  core application SQL statements using the Rule Based Optimizer and now wish to move the application to the Cost Based Optimizer. Be warned that Oracle 10g appears to be the end of the road for the RULE hint and the Rule Based Optimizer.

Optimizer Options Introduced in Oracle9i
The CBO now gives more meaningful cost estimates. Oracle has added new columns to the PLAN_TABLE table:

CPU_COST - The estimated CPU cost for the operation

IO_COST - The estimated IO cost for the operation

TEMP_SPACE - An estimate of temporary space that will be required by the operation
Further, the new features added to the CBO allow it to account for the effects of caching on the performance of nested-loop joins and index prefetching when costing out an execution plan.

Gathering System Statistics
Oracle9i has further enhanced the CBO so that it will consider the system's own performance statistics when costing plans. To take advantage of this new feature, first gather system statistics. Much like gathering object statistics, you must collect system statistics on a regular basis to derive the maximal benefit of this new feature. This is because CPU and IO footprints fluctuate over time.

As you gather system statistics, identify each statistic run with a specific statistic ID. This allows you to track system costs based on the query type footprint that the system is experiencing.

Note that SQL statements are not invalidated when system statistics are generated, thus only newly parsed SQL statements will benefit from the new statistics that are gathered. Oracle has added new procedures to gather system statistics. The DBMS_STATS procedures pertaining to system statistics are:

dbms_stats.gather_system_stats
dbms_stats.set_system_stats
dbms_stats.get_system_stats
dbms_stats.delete_system_stats
dbms_stats.export_system_stats
dbms_stats.import_system_stats
Gathering Dictionary Statistics
Prior to Oracle 9i, it was not a good idea to calculate statistics on Data Dictionary tables (those owned by SYS). Recursive SQL statements were not tuned with the CBO and statistics in mind. Calculating statistics on Data Dictionary tables was not supported. Oracle 9iR2 was the first version to support statistics on Data Dictionary tables. In order to calculate statistics on Data Dictionary tables, you must have been granted SYSDBA or the ANALYZE ANY DICTIONARY system privilege. The DBMS_STATS procedures pertaining to Dictionary statistics are:

dbms_stats.gather_dictionary_stats
dbms_stats.delete_dictionary_stats
dbms_stats.export_dictionary_stats
dbms_stats.import_dictionary_stats
dbms_stats.restore_dictionary_stats
Gathering Fixed Table Statistics
Oracle 10g was the first version to support calculating statistics on fixed objects, i.e. dynamic performance tables. Since Oracle 10g fully supports the Cost Based Optimizer, it makes sense that any object that is queried has statistics, including fixed tables. . In order to calculate statistics on fixed tables, you must have been granted SYSDBA or the ANALYZE ANY DICTIONARY system privilege. The DBMS_STATS procedures pertaining to fixed object statistics are:

dbms_stats.gather_fixed_object_stats
dbms_stats.delete_fixed_object_stats
dbms_stats.export_fixed_object_stats
dbms_stats.import_fixed_object_stats
dbms_stats.restore_fixed_object_stats

SQL RBO Tuning - Joining Three or More Tables

SQL RBO Tuning - Joining Three or More Tables : If three tables are being joined, try to select the intersection table as the driving table. The intersection table is the table that has the most dependencies on it. In the following example, the EMP table represents the intersection between the LOCATION table and the CATEGORY table. This first SELECT:

SELECT   . . .
FROM     location L,
         category C,
         emp E
  WHERE  E.emp_no BETWEEN 1000 AND 2000
    AND  E.cat_no =  C.cat_no
    AND  E.locn   =  L.locn

is more efficient under Rule Based Optimization 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 1000 AND 2000

RBO Tuning - Selecting the Driving Table

RBO Tuning - Selecting the Driving Table :  The object of all your SQL query and update statements is to minimize the total number of physical and logical database blocks that need to be read and/or written. If you specify more than one table in a FROM clause of a SELECT statement, those tables must be joined together and you must choose one table as the driving table. The driving table is the table that begins the join operation. By making the correct choice, you can make enormous improvements in performance.

Consider the following example of two tables processed by the Version 6 optimizer:

Table TAB1  has 16,384   rows
Table TAB2  has 1        row.
Suppose you select TAB2 as the driving table (by specifying it second in the FROM clause):

SELECT COUNT (*) FROM TAB1, TAB2 0.96 seconds elapsed
                count     phys      cr        cur      rows
               ------    -----    -----     -----    ------
    Parse          1         0      0         0
    Execute        1         95     100       4           0
    Fetch          1         0      0         0           1
Now suppose that you select TAB1 as the driving table:

SELECT COUNT (*) FROM TAB2, TAB1          26.09 seconds elapsed
                 count     phys      cr        cur      rows
                ------    -----    -----     -----    ------
    Parse          1         0      0         0
    Execute        1         95     49247     32770      0
    Fetch          1         0      0         0          1
You can see that specifying the correct driving table makes a huge difference in performance (0.96 versus 26.09 seconds). What's going on? When Oracle processes multiple tables, it must join the two tables. There are four different join methods in the Oracle database, the Nested Loop Join, the Sort-Merge Join, the Hash Join, and the Cartesian Join. In our case, we performed a Cartesian Join. A Cartesian Join will join every row of the driving table with every row of the second table. You can identify a Cartesian Join by the absence of any join condition in the WHERE clause. If there was a join condition specified, then the other three join operations could have been considered.

A Sort-Merge Join has a sort phase and a merge phase, hence its name. First, it scans and sorts the first (driving) table (the one specified second in the FROM clause). Next, it scans the second table (the one specified first in the FROM clause) and merges all of the rows retrieved from the second table with those retrieved from the first table.

The Nested Loop Join reads every row in the driving table and then finds matches in the second table. If there is no index on the join column, a full table scan is performed on the second table for every row in the driving table. If an index exists on the join column, the rows in the second table can be accessed much more quickly.

The Hash Join method uses a hashing algorithm (hence its name) to divide the driving table's rows into multiple buckets. The second table is hashed with the same algorithm. If the second table will be joined with a row from the first table, it will be found in that small bucket. The Hash Join method can be a quick join operation due to the fact that the work is broken down into tiny pieces without having to resort to expensive sort operations or multiple scans of the same table.

The 95 physical reads shown on the Execute line in the example above is the minimum number of database "block reads" needed to read all rows from the two tables. The large overhead differences arise from the ensuing number of consistent buffer reads ("cr") and current mode buffer reads ("cur").

If TAB1 is processed first, then all 16,384 rows (from TAB1) must be read first. Using the Sort-Merge Join, these records are sorted, but cannot fit into the area of memory allocated for each user's sort space (specified in the initilialization parameter SORT_AREA_SIZE or the PGA_AGGREGATE_TARGET). Consequently, these records are sorted in small runs with the data stored in temporary segments on disk. After all runs are completed, Oracle merges your data to produce the sorted data. The single piece of TAB2 data is read and joined with the TAB1 data.

By comparison, if TAB2 is processed first, only one row needs to be read (from TAB2), and this row can be sorted within the sort area. The TAB1 table is then joined with the TAB2 table.

Now consider an example in which the number of rows in TAB2 is slightly greater-four instead of one. The performance differential is not quite as extreme in this case.

Table TAB1  has 16,384   rows.
Table TAB2  has 4        rows.
    SELECT COUNT (*) FROM TAB1, TAB2          4.00 seconds elapsed
                     count     phys      cr        cur      rows
                     ------    -----    -----     -----    ------
        Parse          1         0      0         0
        Execute        1         384    386       10         0
        Fetch          1         0      0         0          1
    SELECT COUNT (*) FROM TAB2, TAB1 37.32 seconds elapsed
                     count     phys      cr        cur      rows
                     ------    -----    -----     -----    ------
        Parse          1         0      0         0
        Execute        1         95     49247     32770      0
        Fetch          1         0      0         0          1
Note that the driving table is the second table in the FROM clause of the SQL statement. This convention applies to the Rule Based Optimizer only. The Cost Based Optimizer (CBO) does not take the order of the tables in the FROM clause into account. Instead, it uses statistics on both tables in order to determine the optimal driving table and optimal join method. In this respect, the CBO relieves the DBA or application developer from the duty of determining the most favorable driving table.

Finally, the RBO has been desupported in Oracle 10g. In the future, the only optimizer will be the CBO. DBA's should be moving away from the RBO and converting to the CBO.

SQL Tuning - Rule-Based Optimizer Tuning

SQL Tuning - Rule-Based Optimizer Tuning  : The rule-based optimizer provides a good deal of scope for tuning. Because its behavior is predictable, governed by the 20 condition rankings, we are able to manipulate its choices. Although this manipulation can be very rewarding, it is generally difficult and confusing to the uninitiated. The first thing to do is to become familiar with the normal workings of the rule-based optimizer. Investigate and understand how the optimizer makes its decisions; how the creation and/or removal of an index influences the decision process, why a UNIQUE index has precedent over a non-UNIQUE index, etc. This information is all explained in your basic Oracle tuning manuals. But you can go still further.

SQL Tuning - Using NULL Values

SQL Tuning - Using NULL Values : 

When inexperienced programmers first encounter a null valued column or an equation involving a null constant, they are generally surprised and confused by the outcome. Programmers, developers, and analysts should all have a complete understanding of the null value and its properties. Once understood, null is no different from (or more mystical than) any other database column.

In general:

Null is never equal to (=) anything (including zero, space, or null).

Null is never NOT equal to (!= or <>) anything.

Null is never less than (<) or less than or equal to (<=) anything.

Null is never greater than (>) or greater than or equal to (>=) anything.
Programmers should never directly compare null to anything else. If you perform a comparison to a null value, the record will be rejected.

As with all good rules, there are a few exceptions. Null is in fact equal to null in the following limited situations:

DECODE parameters (where NULL is a value within the comparison list)
   SELECT  DECODE (sex,'M','Male','F','Female',NULL,'Unknown') Sex
   FROM    emp
   WHERE   emp_no = 1234
   EMP_NO    SEX
   ------    -----
    1234     Male
GROUP BY parameters (where several of rows have a column with a null value)
   SELECT  sex, count(*)
   FROM    emp
   GROUP BY sex
   SEX     Count(*)
   -----   --------
   M         3214
   F          956
   null        12
DISTINCT parameters (where a number of rows have a column with a null value)
   SELECT  count(DISTINCT sex)
   FROM    emp
   Count(distinct *)
   -----------------
          3
UNION/MINUS/INTERSECT columns (where a number of rows have a column with a null value)
   SELECT  emp_no,  sex
   FROM    emp
   MINUS
   SELECT  emp_no,  null
   FROM    emp_history
   sql> No rows selected
Note:  Oracle's treatment of null conforms to ANSI standards.

SQL Tuning - Stored Outlines

Creating Stored Outlines
Moving Stored Outlines
Using Stored Outlines
Administration of Stored Outlines
DBMS_OUTLN

Using DECODE or CASE Function

Using DECODE or CASE Function : 

Oracle 8i introduced the CASE function to be more compliant with ANSI SQL standards. The CASE function can replace the DECODE function in most situations. The CASE function lets you put IF-THEN-ELSE processing in your SQL statements. To use a CASE function instead of the DECODE function in the first example, the SQL statements would have to be rewritten as follows:

SELECT loc, CASE loc
WHEN 'NEW YORK' THEN 'EASTERN'
  WHEN 'BOSTON' THEN 'EASTERN'
  WHEN 'DALLAS' THEN 'CENTRAL'
WHEN 'LOS ANGELES' THEN 'WESTERN'
ELSE 'UNKNOWN'
END  AS region
  FROM dept;

The CASE function can be more readable than the DECODE function, although their role is the same when used this way. Additionally, other RDBMS systems do not support Oracle's DECODE function. Using CASE can save the application developer time when creating applications that will be used on multiple RDBMS platforms.

Using DECODE in ORDER BY and GROUP BY Clauses

Using DECODE in ORDER BY and GROUP BY Clauses :  You may need to specify many varying ORDER BY clauses to get the result you want. Rather than coding many identical queries, each with a different ORDER BY clause, you can specify a DECODE function such as the following:

SELECT . . .
 FROM   emp
 WHERE  emp_name LIKE 'SMITH%'
 ORDER
       BY DECODE(:BLK.SEQN_FLD 'E', emp_no, 'D', dept_no);

This approach can be extended further to include the GROUP BY clause:

   SELECT . . .
   FROM   emp
   WHERE  emp_name  LIKE  'SMITH%'
   GROUP
       BY DECODE(:INPUT,'E',emp_no,'D',dept_no);

Note:  DECODE verbs within ORDER BY and GROUP BY statements cannot use indexes. Instead, an internal sort is required. Because this is a slow process, use DECODE within ORDER BY only for online statements in which the number of rows returned by the WHERE clause is small. For reports, you need not worry about limits.

Using DECODE in ORDER BY and GROUP BY Clauses

Using DECODE in ORDER BY and GROUP BY Clauses :  You may need to specify many varying ORDER BY clauses to get the result you want. Rather than coding many identical queries, each with a different ORDER BY clause, you can specify a DECODE function such as the following:

SELECT . . .
 FROM   emp
 WHERE  emp_name LIKE 'SMITH%'
 ORDER
       BY DECODE(:BLK.SEQN_FLD 'E', emp_no, 'D', dept_no);

This approach can be extended further to include the GROUP BY clause:

   SELECT . . .
   FROM   emp
   WHERE  emp_name  LIKE  'SMITH%'
   GROUP
       BY DECODE(:INPUT,'E',emp_no,'D',dept_no);

Note:  DECODE verbs within ORDER BY and GROUP BY statements cannot use indexes. Instead, an internal sort is required. Because this is a slow process, use DECODE within ORDER BY only for online statements in which the number of rows returned by the WHERE clause is small. For reports, you need not worry about limits.

Using DECODE to Reduce Processing

Using 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. Consider the following example:

SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0020
AND    emp_name LIKE 'SMITH%' ;
SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE  dept_no  =  0030
AND    emp_name LIKE 'SMITH%' ;
You can achieve the same result much more efficiently with DECODE:

SELECT COUNT(DECODE(dept_no, 0020, 'X',    NULL)) D0020_kount,
       COUNT(DECODE(dept_no, 0030, 'X',    NULL)) D0030_kount,
       SUM  (DECODE(dept_no, 0020, salary, NULL)) D0020_sal,
       SUM  (DECODE(dept_no, 0030, salary, NULL)) D0030_sal
FROM   emp
WHERE  emp_name LIKE 'SMITH%';

Remember that null values are never included in, nor do they affect the outcome of, the COUNT and SUM functions.

Using DECODE to Make Decisions

Using DECODE to Make Decisions :

The most common usage of the DECODE function is to make decisions about a data value. In its basic form, the DECODE statement is like an IF-THEN-ELSE programming construct. Below is an example to show how DECODE can make decisions. In the SCOTT.DEPT table, the LOC column determines the department location. The information needed is which region that department falls in. Use the DECODE function to determine the regions:

SQL> SELECT loc,DECODE(loc,'NEW YORK','EASTERN','BOSTON','EASTERN',
  2  'DALLAS','CENTRAL','LOS ANGELES','WESTERN','UNKNOWN') AS region
  3  FROM dept;
LOC           REGION
------------- -------
NEW YORK      EASTERN
DALLAS        CENTRAL
CHICAGO       UNKNOWN
BOSTON        EASTERN

The LOC column is passed to the DECODE function. If the value of the LOC column is 'NEW YORK', then the department is in the EASTERN region, similarly for BOSTON. DALLAS is in the CENTRAL region and LOS ANGELES is in the WESTERN region. If the LOC column contains a value different than the ones enumerated in the DECODE function, then UNKNOWN will be returned.

A common trick with the DECODE function is to determine if the a column's value is above or below a static value. The trick with this operation is to use the SIGN function in the DECODE function. For instance, in the SCOTT.EMP table, anyone whose salary is higher than $3,000 is deemed to be a manager. Those whose salary is $3,000 and below are non-managers. Using the DECODE function, it is possible to quickly determine those employees who are managers or not.

SQL> SELECT empno,ename,sal,
  2  DECODE(SIGN(sal-3000),1,'MANAGER','NON-MANAGER') AS emp_type
  3  FROM emp;
     EMPNO ENAME             SAL EMP_TYPE
---------- ---------- ---------- -----------
      7369 SMITH             800 NON-MANAGER
      7499 ALLEN            1600 NON-MANAGER
      7521 WARD             1250 NON-MANAGER
      7566 JONES            2975 NON-MANAGER
      7654 MARTIN           1250 NON-MANAGER
      7698 BLAKE            2850 NON-MANAGER
      7782 CLARK            2450 NON-MANAGER
      7788 SCOTT            3000 NON-MANAGER
      7839 KING             5000 MANAGER
      7844 TURNER           1500 NON-MANAGER
      7876 ADAMS            1100 NON-MANAGER


The trick with the sign function is that when you take the SAL-3000 calculation, it will return a positive number of the salary is larger than $3,000. The SIGN function will return a '1' if the calculation is positive. If the difference is positive, then the employee is a manager. If the result is not positive, then they are not a manager. The DECODE function figures this out nicely for us.

SQL Tuning - Using DECODE

SQL Tuning - Using DECODE :
Programmers often need a way to count and/or sum variable conditions for a group of rows. The DECODE statement provides a very efficient way of doing this. Because DECODE is rather complex, few programmers take the time to learn to use this statement to full advantage. This section describes some common ways you can use DECODE to improve performance.

Using DECODE to Make Decisions
Using DECODE to Reduce Processing
Using DECODE in ORDER BY and GROUP BY Clauses
CASE Function

SQL Tuning - UNION ALL versus UNION

SQL Tuning - UNION ALL versus UNION :


Programmers of complex query statements that include a UNION clause should always ask whether a UNION ALL will suffice. The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned to the calling module. A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge, or filter.

Most statements that do include a UNION clause can in fact replace it with a UNION ALL. These statements are written in a fashion whereby duplicates cannot be generated (sourced from different tables), so why sort and test for them?

Consider the following example:

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'
Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
PROJECTION
SORT (UNIQUE)
UNION-ALL
TABLE ACCESS (BY ROWID) OF 'DEBIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'DEBIT_TRAN_IDX' (NON-UNIQUE)
TABLE ACCESS (BY ROWID) OF 'CREDIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'CREDIT_TRAN_IDX' (NON-UNIQUE)
To improve performance, replace this code with:

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'
Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
PROJECTION
UNION-ALL
TABLE ACCESS (BY ROWID) OF 'DEBIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'DEBIT_TRAN_IDX' (NON-UNIQUE)
TABLE ACCESS (BY ROWID) OF 'CREDIT_TRANSACTIONS'
INDEX (RANGE SCAN) OF 'CREDIT_TRAN_IDX' (NON-UNIQUE)

SQL TUNING - Consider EXISTS in Place of Table Joins

SQL TUNING -  Consider EXISTS in Place of Table Joins :
Consider breaking some table joins out to separate subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is small. When two tables are joined, all rows need to be matched from the driving table to the second table. If a large number of rows can be filtered from the driving table before having to perform the validation against the second table, the number of total physical reads can be dramatically reduced.

Consider the following example:

SELECT . . .                         SELECT . . .
FROM   dept D,                       FROM   dept D,
       emp  E                               emp E
WHERE  E.dept_no  = D.dept_no        WHERE  E.dept_no = D.dept_no
AND    E.emp_type = 'MANAGER'        AND    ( E.emp_type = 'MANAGER'
AND    D.dept_cat = 'A';             OR     D.dept_cat = 'A'     );
To improve performance, specify:

SELECT . . .
FROM   emp E
WHERE  EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
AND    E.emp_type = `MANAGER'
SELECT . . .
FROM   emp E
WHERE  E.emp_type = 'MANAGER'
OR     EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
Note:  The EXISTS clause must be positioned first in the WHEN clause when using an AND operator and last in the WHEN clause when using an OR operator.

SQL TUNING - Consider Table Joins in Place of EXISTS

SQL TUNING - Consider Table Joins in Place of EXISTS -

In general, consider joining tables rather than specifying subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is high. For example, if we are selecting records from the EMP table and are required to filter those records that have a department category of "A", then a table join will be more efficient.

Consider the following example:

SELECT emp_name
FROM   emp E
WHERE  EXISTS ( SELECT 'X'
                   FROM   dept
                   WHERE  dept_no  = E.dept_no
                   AND    dept_cat = 'A');
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      FILTER
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
To improve performance, specify:

   SELECT emp_name
   FROM   dept D,
          emp  E
   WHERE  E.dept_no  = D.dept_no
   AND    D.dept_cat = 'A';
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     NESTED LOOPS
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)