1  SET_OPTIMIZATION_LEVEL
   Allows the current session defaults to be specified for query
   optimization characteristics.

   This statement can reset the session defaults using DEFAULT, or
   can specify one or more keywords for SELECTIVITY or FAST FIRST or
   TOTAL TIME optimization.

   This statement affects all subsequent query compiles in
   interactive SQL, or queries specified using dynamic SQL.

   See the Oracle Rdb SQL Reference Manual for information on
   setting the optimization level in SQL module and precompiler
   languages.
 

2  Environment
   You can use the SET OPTIMIZATION LEVEL statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0SET OPTIMIZATION LEVEL qqq> runtime-options qqqq>         



 (B)0runtime-options                        
                                        
 qqwqqq> 'string-literal' qqqqqqwqqqqq> 
   tqqq> parameter qqqqqqqqqqqqqu       
   mqqq> parameter-marker qqqqqqj       



 (B)0optimization-level=     
 qwq> DEFAULT qqqqqqqqqqqqqqqqqqqqqqqwqq>
  mwqwq> AGGRESSIVE SELECTIVITY qqwqqu
   x tq> FAST FIRST             qqu  x  
   x tq> SAMPLED SELECTIVITY  qqqqu  x
   x mq> TOTAL TIME   qqqqqqqqqqqqj  x  
   mqqqqqqqqqqqqq, <qqqqqqqqqqqqqqqqqj
                  

 

2  Arguments
 

3  optimization-level

   Specifies the optimizer strategy to be used to reset session
   defaults. The passed string or parameter value must be a
   formatted list of keyword values. Select from the following
   options:

   o  AGGRESSIVE SELECTIVITY option if you expect a small number of
      rows to be selected.

   o  DEFAULT option to accept the Oracle Rdb defaults: FAST FIRST
      and DEFAULT SELECTIVITY.

   o  FAST FIRST option if you want your program to return data to
      the user as quickly as possible, even at the expense of total
      throughput.

   o  SAMPLED SELECTIVITY option to use literals in the query to
      perform preliminary estimation on indexes.

   o  TOTAL TIME option if you want your program to run at the
      fastest possible rate, returning all the data as quickly as
      possible. If your application runs in batch, accesses all the
      records in a query, and performs updates or writes reports,
      you should specify TOTAL TIME.

   Only one of the TOTAL TIME or FAST FIRST options can be selected.
   Only one of the AGGRESSIVE SELECTIVITY or SAMPLED SELECTIVITY
   options can be selected. Use a comma to separate the keywords and
   enclose the list in parentheses. No other options may be included
   if DEFAULT is selected.
 

3  parameter

   Specifies the value of the runtime-options, which must be a list
   of keywords, separated by commas.
 

3  parameter-marker

   Specifies the value of the runtime-options, which must be a list
   of keywords, separated by commas.
 

3  'string-literal'

   Specifies the value of the runtime-options, which must be a list
   of keywords, separated by commas.

   Only one of the options TOTAL TIME or FAST FIRST may be selected.
   Only one of the options AGGRESSIVE SELECTIVITY or SAMPLED
   SELECTIVITY may be selected. No other options may be included
   if DEFAULT is selected.
 

2  Example
   Example 1: Setting the optimization level

   The dynamic optimizer can use either FAST FIRST or TOTAL TIME
   tactics to return rows to the application. The default setting,
   FAST FIRST, assumes that applications, especially those using
   interactive SQL, will want to see rows as quickly as possible
   and possibly abort the query before completion. Therefore, if
   the FAST FIRST tactic is possible the optimizer will sacrifice
   overall retrieval time to initially return rows quickly. This
   choice can be affected by setting the OPTIMIZATION LEVEL.

   The following example contrasts the query strategies selected
   when FAST FIRST versus TOTAL TIME is in effect. Databases and
   queries will vary in their requirements. Queries should be tuned
   to see which setting best suits the needs of the application
   environment. For the MF_PERSONNEL database there is little or
   no difference between these tactics, but for larger tables the
   differences could be noticeable.

   SQL> set flags 'STRATEGY,DETAIL';
   SQL> --
   SQL> -- No optimization level has been selected. The optimizer
   SQL> -- selects the FAST FIRST (FFirst) retrieval tactic to
   SQL> -- retrieve the rows from the EMPLOYEES table in the
   SQL> -- following query:
   SQL> --
   SQL> select EMPLOYEE_ID, LAST_NAME
   cont> from EMPLOYEES
   cont> where EMPLOYEE_ID IN ('00167', '00168');
   Tables:
     0 = EMPLOYEES
   Leaf#01 FFirst 0:EMPLOYEES Card=100
     Bool: (0.EMPLOYEE_ID = '00167') OR (0.EMPLOYEE_ID = '00168')
     BgrNdx1 EMPLOYEES_HASH [(1:1)2] Fan=1
       Keys: r0: 0.EMPLOYEE_ID = '00168'
             r1: 0.EMPLOYEE_ID = '00167'
    EMPLOYEE_ID   LAST_NAME
    00167         Kilpatrick
    00168         Nash
   2 rows selected
   SQL> --
   SQL> -- Use the SET OPTIMIZATION LEVEL statement to specify that
   SQL> -- you want the TOTAL TIME (BgrOnly) retrieval strategy to
   SQL> -- be used.
   SQL> --
   SQL> SET OPTIMIZATION LEVEL 'TOTAL TIME';
   SQL> select EMPLOYEE_ID, LAST_NAME
   cont> from EMPLOYEES
   cont> where EMPLOYEE_ID IN ('00167', '00168');
   Tables:
     0 = EMPLOYEES
   Leaf#01 BgrOnly 0:EMPLOYEES Card=100
     Bool: (0.EMPLOYEE_ID = '00167') OR (0.EMPLOYEE_ID = '00168')
     BgrNdx1 EMPLOYEES_HASH [(1:1)2] Fan=1
       Keys: r0: 0.EMPLOYEE_ID = '00168'
             r1: 0.EMPLOYEE_ID = '00167'
    EMPLOYEE_ID   LAST_NAME
    00167         Kilpatrick
    00168         Nash
   2 rows selected
   SQL> --
   SQL> -- When the SET OPTIMIZATION LEVEL 'DEFAULT' statement
   SQL> -- is specified the session will revert to the default FAST FIRST
   SQL> -- optimizer tactic.
   SQL> --
   SQL> SET OPTIMIZATION LEVEL 'DEFAULT';
   SQL> select EMPLOYEE_ID, LAST_NAME
   cont> from EMPLOYEES
   cont> where EMPLOYEE_ID IN ('00167', '00168');
   Tables:
     0 = EMPLOYEES
   Leaf#01 FFirst 0:EMPLOYEES Card=100
     Bool: (0.EMPLOYEE_ID = '00167') OR (0.EMPLOYEE_ID = '00168')
     BgrNdx1 EMPLOYEES_HASH [(1:1)2] Fan=1
       Keys: r0: 0.EMPLOYEE_ID = '00168'
             r1: 0.EMPLOYEE_ID = '00167'
    EMPLOYEE_ID   LAST_NAME
    00167         Kilpatrick
    00168         Nash
   2 rows selected
   SQL>

   Example 2: Using sampled selectivity

   This example shows the use of the SET OPTIMIZATION LEVEL command
   and the resulting use of "Estim" prior to query compile. The
   estimate (34 rows) is quite close to the final result of 37 rows.

SQL> set flags 'strategy,detail,execution';
SQL> set optimization level 'total time, sampled selectivity';
SQL> select * from employees where employee_id between '00000' and '00200';
~Estim EMP_EMPLOYEE_ID Sorted: Split lev=2, Seps=2 Est=34
~Estim EMP_EMPLOYEE_ID Sorted: Split lev=2, Seps=2 Est=34
~S#0005
Tables:
    0 = EMPLOYEES
Leaf#01 BgrOnly 0:EMPLOYEES Card=100
  Bool: (0.EMPLOYEE_ID >= '00000' AND (0.EMPLOYEE_ID <= '00200')
  BgrNdx1 EMP_EMPLOYEE_ID [1:1] Fan=17
    Keys: (0:EMPLOYEE_ID >= '00000') AND 0.EMPLOYEE_ID <= '00200')
~Estim EMP_EMPLOYEE_ID Sorted: Split lev=2, Seps=1 Est=17
~E#0005.01(1) Estim   Index/Estimate 1/17
~E#0005.01(1) Bgrndx1 EofData  DBKeys=37  Fetches=0+0  RecsOut=0  #Bufs=30
 EMPLOYEE_ID   LAST_NAME        FIRST_NAME  MIDDLE_INITIAL  ADDRESS_DATA1             ADRESS_DATA_2
   CITY                   STATE   POSTAL_CODE  SEX    BIRTHDAY     STATUS_CODE
 00190         O'Sullivan       Rick        G.              78 Mason Rd.               NULL
   Fremont                NH      03044        M      12-Jan-1923  1
   .
   .
   .
~E#005.01(1) Fin     Buf      DBKeys=37  Fetches=0+32  RecsOut=37
 00174         Myotte           Daniel      V.              95 Princeton Rd.           NULL
   Bennington             MA      03442        M      17-Jan-1948  1

   37 rows selected
SQL>