#
# Oracle Performance Tuning Chapter 7 p. 101
#
     SELECT    ROWID, . . .
        INTO   :EMP_ROWID, . . .
        FROM   EMP
        WHERE  EMP.EMP_NO  = 56722
        FOR UPDATE OF EMP.NAME ;

    UPDATE     EMP
        SET    EMP.NAME    = . . .
        WHERE ROWID = :EMP_ROWID ;

#
# Oracle Performance Tuning Chapter 7 p. 102
#
# Method 1 shows two separate database accesses:
    SELECT  EMP_NAME, SALARY, GRADE
        FROM  EMP
        WHERE EMP_NO = 0342;
    SELECT  EMP_NAME, SALARY, GRADE
        FROM  EMP
        WHERE EMP_NO = 0291;
#
# Method 2 shows the use of one cursor and two fetches:
    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 shows a SQL table join:
    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 ;
#
# Oracle Performance Tuning Chapter 7 p. 103
#
SELECT      'X'
    FROM    DUAL
    WHERE   'X'   =   NULL ;

SELECT      'X'
    FROM    DUAL
    WHERE   'X'   <>  NULL ;

SELECT      'X'
    FROM     DUAL
    WHERE    NULL  =  NULL ;

SELECT      'X'
    FROM    DUAL
    WHERE   NULL  <>  NULL ;

SELECT      'X'
    FROM    DUAL
    WHERE   NULL IN ('A','B',NULL);
#
# Oracle Performance Tuning Chapter 7 p. 104
#
SELECT      'X'
    FROM    DUAL
    WHERE   NULL NOT IN ('A', 'B', NULL);
#
#
SELECT      'X'
    FROM    DUAL
    WHERE   'X'   =   NVL(NULL, 'X');

SELECT      'X'
    FROM    DUAL
    WHERE   'X'  <>   NVL(NULL, 'Y');

SELECT      'X'
    FROM    DUAL
    WHERE   NULL IS NULL;

SELECT      'X'
    FROM    DUAL
    WHERE   'X'  IS NOT NULL  ;
#
# Oracle Performance Tuning Chapter 7 p. 105
#
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%' ;
#
SELECT  COUNT(DECODE   ( DEPT_NO, 0020, NULL, 'X'))  D0020_KOUNT,
    COUNT (DECODE      ( DEPT_NO, 0030, 'X', NULL ))  D0030_KOUNT,
    SUM   (DECODE      ( DEPT_NO, 0020, NULL, SALARY ))  D0020_SAL,
    SUM   (DECODE      ( DEPT_NO, 0030, SALARY, NULL ))  D0030_SAL
    FROM EMP 
WHERE  EMP_NAME LIKE 'SMITH%';
#
    SELECT  . . .    
        FROM   EMP 
        WHERE  EMP_NAME  LIKE  'SMITH%'
        ORDER BY  DECODE (:BLK.SEQN_FLD 'E', EMP_NO, 'D', DEPT_NO);
#
    SELECT  . . .
        FROM  EMP 
        WHERE  EMP_NAME  LIKE  'SMITH%'
        GROUP BY  DECODE (:INPUT, 'E', EMP_NO, 'D', DEPT_NO);
#
# Oracle Performance Tuning Chapter 7 p. 106
#
SELECT  NAME    
    FROM   EMP
    WHERE  EMP_NO = 1234;

SELECT  NAME 
    FROM   DPT
    WHERE  DPT_NO = 10;

SELECT  NAME
    FROM   CAT
    WHERE  CAT_TYPE = 'RD'  ;

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'
#
# Oracle Performance Tuning Chapter 7 p. 107
#
DELETE   FROM EMP  E
    WHERE E.ROWID  >   (  SELECT  MIN(X.ROWID)
                    FROM  EMP  X
                    WHERE X.EMP_NO  =  E.EMP_NO    );
#
SELECT REGION, AVG(LOC_SIZE)
   FROM        LOCATION
   GROUP BY    REGION
   HAVING      REGION     != 'SYDNEY'
   AND         REGION     != 'PERTH'
#
# Oracle Performance Tuning Chapter 7 p. 108
#
SELECT REGION, AVG(LOC_SIZE)  
    FROM       LOCATION
    WHERE      REGION     != 'SYDNEY'
    AND        REGION     != 'PERTH'
    GROUP BY   REGION
#
SELECT  TABLE_NAME 
    FROM  TABLES
    WHERE TABLE_NAME =   (SELECT  TABLE_NAME
                           FROM   TAB_COLUMNS
                           WHERE  VERSION = 604 )

    AND DB_VERSION =     (SELECT  DB_VERSION 
                           FROM   TAB_COLUMNS WHERE VERSION = 604 )
#
SELECT  TABLE_NAME 
    FROM  TABLES
    WHERE ( TABLE_NAME,  
          DB_VERSION ) = (SELECT  TABLE_NAME, DB_VERSION
                           FROM   TAB_COLUMNS
                           WHERE  VERSION = 604 )
#
# Oracle Performance Tuning Chapter 7 p. 109
#
SELECT E.EMP_NO, NAME, TAX_NO, C.COMP_CODE, COMP_NAME
    FROM  COMPANY  C,
          EMP E
    WHERE E.COMP_CODE = C.COMP_CODE;
#
SELECT  E.EMP_NO,E.NAME,E.TAX_NO,C.COMP_CODE,C.COMP_NAME
    FROM  COMPANY  C,
          EMP E
    WHERE E.COMP_CODE = C.COMP_CODE;
#
SELECT . . .
    FROM  EMP
    WHERE DEPT_NO  NOT IN  (SELECT  DEPT_NO
                             FROM   DEPT
                             WHERE  DEPT_CAT = 'A');
#
SELECT  . . .
    FROM  EMP  E
    WHERE NOT EXISTS  (SELECT  X' 
                        FROM   DEPT
                        WHERE  DEPT_NO   =  E.DEPT_NO
                        AND    DEPT_CAT  =  'A');
#
# Oracle Performance Tuning Chapter 7 p. 110
#
SELECT . . .
    FROM  EMP  E
    WHERE  EXISTS  (SELECT  X' 
                     FROM   DEPT
                     WHERE  DEPT_NO   =  E.DEPT_NO
                     AND    DEPT_CAT  =  'A');
#
SELECT . . .
    FROM  DEPT   D, EMP   E
    WHERE E.DEPT_NO = D.DEPT_NO
    AND   DEPT_CAT  = 'A';
#
SELECT  DISTINCT  DEPT_CODE, DEPT_NAME
    FROM  DEPT  D, EMP E
    WHERE D.DEPT_CODE  =  E.DEPT_CODE
#
SELECT  DEPT_CODE, DEPT_NAME
    FROM  DEPT  D
    WHERE EXISTS  (SELECT 'X'
                    FROM  EMP E
                    WHERE E.DEPT_CODE  =  D.DEPT_CODE);
#
# Oracle Performance Tuning Chapter 7 p. 114
#
SELECT    EMP_NAME
    FROM  EMP
    WHERE DEPT_NO  =  0020
    AND   EMP_CAT  =  'A' ;

Explain Plan  Query Plan
--------------------------------------------------------------------
Table Access By Rowid on EMP
    And-Equal  
        Index Range Scan on Dept_Idx
        Index Range Scan on Cat_Idx
#
SELECT . . .
    FROM  DEPT D, EMP E
        WHERE E.EMP_CAT = 'A'
        AND   E.NAME LIKE 'SMITH%'
        AND   E.DEPT_NO = D.DEPT_NO
#
SELECT    EMP_NAME
    FROM  EMP
    WHERE DEPT_NO  >  0020
    AND   EMP_CAT  =  'A' ;

Explain Plan Query Plan
--------------------------------------------------------
Table Access By Rowid on EMP
    Index Range Scan on Cat_Idx
#
# Oracle Performance Tuning Chapter 7 p. 115
#
SELECT    EMP_NAME
    FROM  EMP
    WHERE DEPT_NO   >  0020
    AND   EMP_CAT   >  'B' ;

Explain Plan Query Plan
------------------------------------------------------------------
Table Access By Rowid on EMP
    Index Range Scan on Dept_Idx
#
SELECT    . . .
    FROM  EMP
    WHERE EMP_NO         =  12893
    AND   DEPT_CODE + 0  =  0010
    AND   EMP_TYPE || '' = 'A';
#
SELECT    . . .
    FROM  EMP
    WHERE EMP_TYPE   =  'A'
    AND   EMP_CLASS  =  'X' ;
#
# Oracle Performance Tuning Chapter 7 p. 116
#
SELECT    . . .
    FROM  EMP
    WHERE EMP_TYPE         = 'A'
    AND   EMP_CLASS || ''  = 'X' ;
#
SELECT . . .
    FROM DEPARTMENT
    WHERE SALARY * 12  >  25000;
#
SELECT
    FROM DEPARTMENT
    WHERE SALARY > 25000  / 12;
#
# Oracle Performance Tuning Chapter 7 p. 117
#
SELECT    EMP_NAME
    FROM  EMP
    WHERE EMP_NO    =  2362
    AND   EMP_DEPT  =  0020 ;

    Explain Plan Query Plan
    -------------------------------------------------------
    Table Access By Rowid on EMP
        Index Unique Scan on Emp_No_Idx
#
# Oracle Performance Tuning Chapter 7 p. 118
#
SELECT . . .
    FROM  DEPARTMENT
    WHERE DEPT_CODE  > 0;
#
SELECT . . .
    FROM  DEPARTMENT
    WHERE DEPT_CODE NOT  = 0;
# p.118-119
SELECT    LOC_ID, LOC_DESC, REGION
    FROM  LOCATION
    WHERE LOC_ID =  10
    UNION
SELECT    LOC_ID, LOC_DESC, REGION
    FROM  LOCATION
    WHERE REGION  = 'MELBOURNE'
#
# Oracle Performance Tuning Chapter 7 p. 119
#
SELECT    LOC_ID, LOC_DESC, REGION
    FROM  LOCATION
    WHERE LOC_ID  =  10 
    OR    REGION  =  'MELBOURNE'
#
# Oracle Performance Tuning Chapter 7 p. 120
#
SELECT . . .
    FROM DEPARTMENT
    WHERE DEPT_CODE >= 0;
#
SELECT . . .
    FROM DEPARTMENT
    WHERE DEPT_CODE  IS NOT NULL;
#
# Oracle Performance Tuning Chapter 7 p. 122
#
SELECT    . . .            
    FROM  DEPARTMENT        
    WHERE DEPT_TYPE  >  0    

    Explain Plan Query Plan
    -----------------------------------------------------------------
    Table Access By Rowid on EMP
        Index Range Scan  on Dept_Idx
#
SELECT . . .
    FROM DEPARTMENT
    ORDER BY  DEPT_TYPE 

    Explain Plan Query Plan
    ----------------------------------------------------------------
    Sort Order By
        Table Access FULL
#
# Oracle Performance Tuning Chapter 7 p. 123
#
SELECT . . .
    FROM EMP
    WHERE EMP_NO = '123'
#
SELECT . . .
    FROM EMP
    WHERE EMP_NO = TO_NUMBER('123')
#
SELECT  . . .
    FROM EMP
    WHERE EMP_TYPE = 123
#
SELECT  . . .
    FROM EMP
    WHERE TO_NUMBER(EMP_TYPE) = 123
#
# Oracle Performance Tuning Chapter 7 p. 124
#
# DO NOT USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE SUBSTR(ACCOUNT_NAME,1,7) = 'CAPITAL';
#
# USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE ACCOUNT_NAME LIKE 'CAPITAL%';
#
# DO NOT USE
SELECT  ACCOUNT_NAME,TRANS_DATE,AMOUNT  
    FROM  TRANSACTION
    WHERE AMOUNT != 0;
#
# USE
SELECT  ACCOUNT_NAME,TRANS_DATE,AMOUNT  
    FROM  TRANSACTION
    WHERE AMOUNT  > 0 ;
#
# DO NOT USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
   FROM   TRANSACTION
   WHERE TRUNC(TRANS_DATE) = TRUNC(SYSDATE);
#
# USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE TRANS_DATE BETWEEN TRUNC(SYSDATE) 
          AND TRUNC(SYSDATE) + .99999;   

#
SELECT  TO_DATE('01-JAN-93') + .99999    
    FROM DUAL;    
#
SELECT TO_DATE('01-JAN-93') + .999999
    FROM DUAL;
#
# Oracle Performance Tuning Chapter 7 p. 125
#
# DO NOT USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE ACCOUNT_NAME  ||  ACCOUNT_TYPE = 'AMEXA';
#
# USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT
    FROM  TRANSACTION
    WHERE ACCOUNT_NAME  = 'AMEX'   
    AND   ACCOUNT_TYPE  =  'A' ;
#
# DO NOT USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE AMOUNT+3000 < 5000;
#
# USE
SELECT  ACCOUNT_NAME, TRANS_DATE, AMOUNT  
    FROM  TRANSACTION
    WHERE AMOUNT <  2000;
#
# DO NOT USE
SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT
    FROM  TRANSACTION
    WHERE ACCOUNT_NAME=NVL(:ACC_NAME, ACCOUNT_NAME);
#
# USE
SELECT ACCOUNT_NAME, TRANS_DATE, AMOUNT
    FROM  TRANSACTION
    WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, '%');
#
# Oracle Performance Tuning Chapter 7 p. 126
#
INSERT INTO  EMP_AUDIT
SELECT USER, SYSDATE, A.*
    FROM  EMP A
    WHERE EMP_NO = :EMP_NO;
#
INSERT INTO EMP_AUDIT
SELECT 'WHO',  'WHEN',  'WHERE',  E.*
    FROM  EMP  E  
    WHERE  E.EMP_NO  = 123;
#
SELECT  E.EMP_NO, E.EMP_NAME, D.*, C.*
    FROM CAT  C,
         DPT  D,
         EMP  E
    WHERE E.DEPT_NO  =  D.DEPT_NO  (+)
    AND E.CAT_TYPE   =  C.CAT_TYPE  (+);
#
# Oracle Performance Tuning Chapter 7 p. 129
#
CREATE PUBLIC DATABASE LINK PRD 
    CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'X:ORASRV';
    INSERT INTO SYSTEM.PRODUCT_USER_PROFILE
                (  PRODUCT,  USERID,  ATTRIBUTE,  CHAR_VALUE  )
    VALUES      (  'SQL*PLUS',  'OPS$%',  'CONNECT', 'DISABLED' );