The
SET CURRENT OPTIMIZATION PROFILE statement assigns a value to the
CURRENT OPTIMIZATION PROFILE special register. The value specifies
the optimization profile the optimizer should use when preparing dynamic
DML statements.
This statement is not under transaction control.
When
the statement is evaluated, the name of the optimization profile is
checked for validity, but the profile is not processed until the
optimizer encounters a dynamic DML statement.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
Authorization
None required.
Syntax
.-=-.
>>-SET CURRENT OPTIMIZATION PROFILE--+---+--+-optimization-profile-name-+-><
+-host-variable-------------+
+-string-constant-----------+
'-NULL----------------------'
Description
- optimization-profile-name
- The two-part name of the optimization profile. The name can be
specified with a literal, host variable, or special register. The
name specified is the name entered into the CURRENT OPTIMIZATION PROFILE
special register.
If the specified optimization-profile-name is
unqualified, the value of the CURRENT DEFAULT SCHEMA register is used
as the implicit qualifier. The default value of the special register
is null.
- host-variable
- A variable of type CHAR or VARCHAR that includes the name of the
optimization profile. A host variable that includes a null indicator
indicates that the value of the OPTPROFILE bind option is to be used
if that value is specified for the current package. A host variable
of zero length, or of white space only, indicates that no optimization
profile is to be used.
The host variable must meet the following
characteristics:
- The content of the string is a single or two-part identifier (separated
by a period), with no leading blanks.
- The identifier or identifiers can be delimited or non-delimited.
- The content of the string is not folded to upper case.
- Lower case and special characters cannot be used in non-delimited
strings.
- If the first character is a double quotation mark, a closing double
quotation mark must either precede a period or be the last non-blank
character in the string.
- If the first character following a period is a double quotation
mark, then a double quotation mark must be the last non-blank character
in the string.
- If the identifier is delimited, then to include double quotation
marks in the identifier, specify the character twice.
- Any period that is not inside a delimited identifier is treated
as a separator, and only one period separator can exist in the string.
- string-constant
- Specifies a constant as a character string that is the name of
the optimization profile. The content of a string constant must meet
the same characteristics as a host variable.
- NULL
- Sets the CURRENT OPTIMIZATION PROFILE register to null.
Table 1 provides
examples of string literals and identifiers that might be used to
assign the register as per the optimization profile naming rules.
The value in the SCHEMA and NAME column represent an optimization
profile name as it might appear in the OPT_PROFILE table. The valid
string literals column shows string literals that match the optimization
profile named by the corresponding SCHEMA and NAME column values.
The valid identifiers column shows identifiers that would identify
that same optimization profile.
Table 1. Examples
of string literals and identifiersSCHEMA |
NAME |
Valid string literals |
Valid identifiers |
SIMMEN |
BIG_PROF |
'BIG_PROF' 'SIMMEN.BIG_PROF'
'"BIG_PROF"'
'"SIMMEN"."BIG_PROF"'
|
BIG_PROF SIMMEN.BIG_PROF
"BIG_PROF"
"SIMMEN"."BIG_PROF"
|
SIMMEN |
low_profile |
'"low_profile"' 'SIMMEN."low_profile"'
'"SIMMEN"."low_profile"'
|
"low_profile" SIMMEN."low_profile"
"SIMMEN"."low_profile"
|
eliaz |
DBA3 |
'DBA3' '"DBA3"'
'"eliaz".DBA3'
'"eliaz"."DBA3"'
|
DBA3 "eliaz".DBA3
"eliaz"."DBA3"
|
SNOW |
PROFILE1.0 |
'"PROFILE1.0"' 'SNOW."PROFILE1.0"'
'"SNOW"."PROFILE1.0"'
|
"PROFILE1.0" SNOW."PROFILE1.0"
"SNOW"."PROFILE1.0"
|
Notes
- If the value of the register specifies the name of an existing
optimization profile, the specified optimization profile is used
when preparing subsequent dynamic DML statements.
- If the value of the register is null, the optimization profile
specified by the OPTPROFILE bind option, if any, is used when preparing
subsequent dynamic DML statements.
- If the value of the register is null, and the OPTPROFILE bind
option is not set, no optimization profile is used when preparing
subsequent dynamic DML statements.
- If the value of the register is the empty string, then no optimization
profile is used when preparing subsequent dynamic DML statements,
regardless of whether the OPTPROFILE bind option is set.
- Subsequent changes to CURRENT DEFAULT SCHEMA do not have any effect
on the optimization profile. The CURRENT OPTIMIZATION PROFILE register
value is set with the two part name that is in effect at the time
SET CURRENT OPTIMIZATION PROFILE statement is evaluated. Only another
SET CURRENT OPTIMIZATION PROFILE statement can change the optimization
profile that is used.
Examples
- Example 1: The optimization profile RICK.FOO is used for
statements 1, 2, and 3. TOM.FOO is used for statement 4.
SET CURRENT SCHEMA = 'RICK'
SET CURRENT OPTIMIZATION PROFILE = 'FOO'
statement 1
statement 2
SET CURRENT SCHEMA = 'TOM'
statement 3
SET CURRENT OPTIMIZATION PROFILE = 'FOO'
statement 4
- Example 2: An application with the following statements
was bound with the options OPTPROFILE("Foo") and QUALIFIER("John").
The optimization profile KAAREL.BAR is used for statement 1 and optimization
profile "John"."Foo" is used for statement 2.
SET CURRENT SCHEMA = 'KAAREL'
SET CURRENT OPTIMIZATION PROFILE = 'BAR'
statement 1
SET CURRENT SCHEMA = "Tom"
SET CURRENT OPTIMIZATION PROFILE NULL
statement 2
- Example 3: The empty string is a special value that indicates
that no optimization profile is to be used. Optimization profile
"Hamid"."Foo" is used for statement 1 and no optimization profile
is used for statement 2.
SET CURRENT OPTIMIZATION PROFILE = '"Hamid"."Foo"'
statement 1
SET CURRENT OPTIMIZATION PROFILE = ''
statement 2