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>