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 SET OPTIMIZATION LEVEL ---> runtime-options ----> runtime-options --+---> 'string-literal' ------+-----> +---> parameter -------------+ +---> parameter-marker ------+ optimization-level= -+-> DEFAULT -----------------------+--> ++-+-> AGGRESSIVE SELECTIVITY --+--+ | +-> FAST FIRST --+ | | +-> SAMPLED SELECTIVITY ----+ | | +-> TOTAL TIME ------------+ | +-------------, <-----------------+ 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>