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.
1 – 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)0[m[1;4mSET[m[1m [1;4mOPTIMIZATION[m[1m [1;4mLEVEL[m[1m qqq> runtime-options qqqq>[m [1m [m
(B)0[m[1mruntime-options [m
[1m [m
[1mqqwqqq> 'string-literal' qqqqqqwqqqqq> [m
[1m tqqq> parameter qqqqqqqqqqqqqu [m
[1m mqqq> parameter-marker qqqqqqj [m
(B)0[m[1moptimization-level= [m
[1mqwq> DEFAULT qqqqqqqqqqqqqqqqqqqqqqqwqq>[m
[1mmwqwq> AGGRESSIVE SELECTIVITY[m [1mqqwqqu[m
[1mx[m [1mtq> FAST FIRST [m [1mqqu x [m
[1mx[m [1mtq> SAMPLED SELECTIVITY[m [1mqqqqu[m [1mx[m
[1m [m [1mx[m [1mmq> TOTAL TIME [m [1mqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqq, <qqqqqqqqqqqqqqqqqj[m
[1m [m [1m [m
3 – Arguments
3.1 – 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.2 – parameter
Specifies the value of the runtime-options, which must be a list
of keywords, separated by commas.
3.3 – parameter-marker
Specifies the value of the runtime-options, which must be a list
of keywords, separated by commas.
3.4 – '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.
4 – 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>