Allows enabling and disabling of database system debug flags for
the current session.
The literal or host variable passed to this command can contain
a list of keywords, or negated keywords, separated by commas.
Spaces are ignored. The keywords may be abbreviated to an
unambiguous length.
NOTE
Oracle Corporation reserves the right to add new keywords
to the SET FLAGS statement in any release or update to
Oracle Rdb, which may change this unambiguous length.
Therefore, it is recommended that the full keyword be used
in applications.
1 – Environment
You can use the SET FLAGS statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
2 – Format
SET --+-> FLAGS -+--------------------------+-+-> <literal> ----+-+-->
| +-> (ON ALIAS alias-name) -+ +-> <host-variable>-+ |
+-> NOFLAGS --------------------------------------------------+
3 – Arguments
3.1 – FLAGS
Specifies whether or not a database system debug flag is set.
The following table shows the available keywords that can be
specified.
Table 1-7 Debug Flag Keywords
Debug
Flags
Keyword Equivalent Comment
ALTERNATE_ None The optimizer discards literal values
OUTLINE_ID when producing an outline identifer.
AUTO_INDEX Xq Allows CREATE TABLE and ALTER TABLE
to create indices for any PRIMARY
KEY, FOREIGN KEY or UNIQUE constraint
added to the table.
AUTO_OVERRIDE None Allows a user with the DBADM
(administrator) privilege to insert
or update a column defined as
AUTOMATIC.
BITMAPPED_SCAN None Enables use of in-memory compressed
DBkey bitmaps for index AND and OR
operations in the dynamic optimizer.
BLR B Displays the binary language (BLR)
representation request for the query
CARDINALITY K Shows cardinality updates
CARTESIAN_LIMIT None Limits the number of small tables
that are allowed to be placed
anywhere in the join order.
CHRONO_FLAG(n) Xc Forces timestamp-before-dump display.
The value of n can be 0, 1, or 2, or
n can be omitted. CHRONO_FLAG(0) and
NOCHRONO_FLAG are equivalent. If you
specify CHRONO_FLAG but omit n, the
default is CHRONO_FLAG(1). CHRONO_
FLAG(1) enables an additional trace
message that includes the attach
number and the current time. CHRONO_
FLAG(2) enables an additional trace
message that includes the attach
number and the current date and time.
If you supply a value for n that is
greater than 2, it is ignored, and a
value of 1 is used.
CONTROL_BITS Bc Displays a decoding of the BLR$K_
CONTROL_BITS semantic flags when used
with the BLR keyword.
COSTING Oc Displays traces on optimizer costing.
COUNT_SCAN None Enables count scan optimization
on sorted ranked indexes, where
the optimizer will use cardinality
information from the sorted ranked
index to determine the count of rows
that satisfy the query.
CURSOR_STATS Og Displays general cursor statistics
for the optimizer.
DATABASE_ P Displays the database parameter
PARAMETERS buffer during ATTACH, CREATE
DATABASE, ALTER DATABASE, IMPORT
DATABASE, and DISCONNECT statements.
DDL_BLR D Displays the binary language (BLR)
representation of expressions
within data definitions, such as
the expression for a computed column
within a table definition.
DETAIL_LEVEL None A debug flag used with other debug
flags to enable additional detailed
information in the debug output. The
DETAIL_LEVEL keyword can be followed
by a numeric value in parentheses.
For those debug flags that support
it, this indicates the degree of
aditional detail to be shown.
ESTIMATES O Displays the optimizer estimates.
EXECUTION E Displays an execution trace from the
dynamic optimizer. For a sequential
retrieval from a table that is
strictly partitioned, this includes
a count and a list of the selected
partitions each time the query
executes.
The EXECUTION keyword can be
followed by a numeric value in
parentheses. This represents the
number of lines to display before
stopping the execution trace for
the query execution. There can be no
spaces between the keyword and the
parameter. The default is 100.
IGNORE_OUTLINE None Ignores outlines defined in the
database. The IGNORE_OUTLINE keyword
has the same action as setting the
RDMS$BIND_OUTLINE_FLAGS logical name
to 1.
INDEX_COLUMN_ None Enables leading index columns as
GROUP workload column groups. This may
increase solution cardinality
accuracy.
INDEX_DEFER_ Xb When this flag is set and an index
ROOT is created, the index root node is
created in the database only when
there is data in the table. If the
table is empty, creation of the index
root node is deferred until rows are
inserted into the table.
INDEX_ Si Displays index partitioning
PARTITIONS information as part of a dynamic
execution trace.
INDEX_STATS Ai Enables debug flags output for the
progress of an ALTER, CREATE, or DROP
INDEX statement.
INTERNALS I Enables debug flags output for
internal queries such as constraints
and triggers. It can be used in
conjunction with other keywords such
as STRATEGY, BLR, and EXECUTION.
ITEM_LIST H Displays item list information passed
in for the database queries and as
compile-time query options
LAREA_READY Xr This flag can be used to investigate
table and index locking behavior.
This flag is disabled by default.
MAX_RECURSION None Sets the maximum number of recursions
that can be performed when executing
a match strategy. This prevents
excessive recursion in the processing
of the match strategy. The default
value is 100. The equivalent logical
name is RDMS$BIND_MAX_RECURSION.
MAX_SOLUTION None Enables maximum search space for
possible retrieval solutions. If
enabled, the optimizer will try
more solutions based on each leading
segment of the index, and thus may
create more solutions than before,
but may find more efficient solutions
applying multiple segments in index
retrieval. The equivalent logical
name is RDMS$DISABLE_MAX_SOLUTION.
Default is enabled.
MAX_STABILITY None Enables maximum stability; the
dynamic optimizer is not allowed.
The MAX_STABILITY keyword has the
same action as the RDMS$MAX_STABILITY
logical name.
MBLR M Displays the metadata binary language
representation request for the data
definition language statement
MODE(n) Allows you to specify which query
outline should be used by specifying
the mode value of that query outline.
The value of n can be any positive
or negative integer, or n can be
omitted. If you specify MODE but
omit n, the default is MODE(1). If
you specify MODE(0) or NOMODE, it
disables the display of the mode
in the SHOW FLAGS statement output.
MODE(0) is the default for Oracle Rdb
generated outlines.
NONE Not Used to turn off all currently
Applicable defined keywords. Equivalent to SET
NOFLAGS.
OBLR So Displays query outline in Binary
Language Representation (BLR).
OLD_COST_MODEL None Enables the old cost model. The
OLD_COST_MODEL keyword has the same
action as the RDMS$USE_OLD_COST_MODEL
logical name.
OPTIMIZATION_ None Used to change the default
LEVEL optimization level for a query.
If the query explicitly uses the
OPTIMIZE FOR clause, or is compiled
within an environment which overrides
the default using a method such
as SET OPTIMIZATION LEVEL, then no
change will occur. If the query uses
the default optimization level, then
the optimization will be modified by
this flag. With no option specified
or an empty options list, this will
default to TOTAL TIME. The flag
NOOPTIMIZATION_LEVEL will revert
to the default Rdb behavior.
OPTIMIZATION_ None Sets FAST FIRST as the default
LEVEL(FAST_ optimization level for queries in
FIRST) all sessions.
OPTIMIZATION_ None Sets TOTAL TIME as the default
LEVEL(TOTAL_ optimization level for queries in
TIME) all sessions.
OUTLINE Ss Displays query outline for this
query (can be used without STRATEGY
keyword)
PREFIX Bn Used with BLR keyword to inhibit
offset numbering and other formatting
of binary language representation
display.
REBUILD_SPAM_ None The flag REBUILD_SPAM_PAGES is
PAGES for use in conjunction with the
DDL commands ALTER TABLE, ALTER
STORAGE MAP, and ALTER INDEX. When
changing the row length or THRESHOLDS
clause for a table or index, the
corresponding SPAM pages for the
logical area may require rebuilding.
By default, these DDL commands
update the AIP and set a flag to
indicate that the SPAM pages should
be rebuilt. However, this flag may be
set prior to executing a COMMIT for
the transaction and the rebuild will
take place within this transaction.
Use SET FLAGS 'NOREBUILD_SPAM_PAGES'
to negate this flag.
REWRITE None When no parameters are provided,
all query rewrite optimizations are
enabled.
NOREWRITE None When no parameters are provided,
all query rewrite optimizations are
disabled.
REWRITE None Specifying the CONTAINING keyword
(CONTAINING) will enable only the CONTAINING
predicate rewrite optimization.
NOREWRITE None Specifying the CONTAINING keyword
(CONTAINING) will disable only the CONTAINING
predicate rewrite optimization.
REWRITE(LIKE) None Specifying the LIKE keyword will
enable only the LIKE predicate
rewrite optimization.
NOREWRITE(LIKE) None Specifying the LIKE keyword will
disable only the LIKE predicate
rewrite optimization.
REWRITE None Specifying the STARTING_WITH keyword
(STARTING_ will enable only the STARTING WITH
WITH) predicate rewrite optimization.
NOREWRITE None Specifying the STARTING_WITH keyword
(STARTING_ will disable only the STARTING WITH
WITH) predicate rewrite optimization.
REQUEST_NAMES Sn Displays the names of user requests,
triggers, and constraints
REVERSE_SCAN None Enables the reverse index scan
strategy. The NOREVERSE_SCAN
keyword has the same action as the
RDMS$DISABLE_REVERSE_SCAN logical
name.
SCROLL_ L Disables scrolling for old-style LIST
EMULATION OF BYTE VARYING (segmented string)
format. The SCROLL_EMULATION flag
has the same action as setting the
RDMS$DIAG_FLAGS logical name to L.
SELECTIVITY None Refers to the methods by which the
static optimizer estimates predicate
selectivity. This flag takes a
numeric value in parentheses from
0 to 3.
0 = standard (non-aggressive, non-
sampled) selectivity
1 = aggressive + non-sampled
selectivity
2 = sampled + non-aggressive
selectivity
3 = sampled + aggressive selectivity.
By default the flag is disabled,
which is the equivalent of setting
its value to 0.
SEQ_CACHE(n) None Adjusts the sequence cache size
for the process issuing the SET
FLAGS statement. The value n must
be a numeric value greater than
2. (Specifying a value of 1 is
equivalent to specifying NOSEQ_
CACHE.) Use SEQ_CACHE to override
the CACHE setting for all sequences
subsequently referenced by the
application. The new cache size
does not affect any sequence that
has already been referenced, or any
sequence defined as NOCACHE.
SOLUTIONS OsS Displays traces on optimizer
solutions.
SORTKEY_EXT S Reports if ORDER BY (or SORTED BY) is
referencing only external (constant)
value. The SORTKEY_EXT flag has the
same action as setting the RDMS$DIAG_
FLAGS logical name to S.
SORT_STATISTICS R Displays sort statistics during
execution.
STOMAP_STATS As Displays the processing of storage
maps for any tables that refer to the
dropped storage area. The output is
prefixed with "~As".
STRATEGY S Shows the optimizer strategy. If
a table is strictly partitioned,
the text "(partitioned scan#nn)"
appears after the table name, where
nn indicates the leaf number for a
sequential scan (there may be several
within a single query).
TEST_SYSTEM None This flag is used by the Oracle Rdb
testing environment to modify the
output of various functions, trace
and debugging displays. It is used
to eliminate data in test output
that would normally cause differences
between test executions.
TRACE Xt Enables output from TRACE statement
TRANSACTION_ T Displays the transaction parameter
PARAMETERS buffer during SET TRANSACTION,
COMMIT, and ROLLBACK and during
stored procedure compilation
TRANSITIVITY None Enables transitivity between
selections and join predicates.
The NOTRANSITIVITY keyword has the
same action as the RDMS$DISABLE_
TRANSITIVITY logical name.
VALIDATE_ None Enables revalidation of an
ROUTINE invalidated stored procedure or
stored function. The VALIDATE_ROUTINE
keyword has the same action as the
RDMS$VALIDATE_ROUTINE logical name.
VARIANCE_DOF(n) None Sets the default degree of freedom
(DOF) for calculation of the mean
(average) in small samples (instead
of using the VARIANCE function). Only
the values 0 and 1 are allowed.
WARN_DDL Xd Sometimes legal data definitions
can have side effects, this flag
allows these warning to be enabled
and disabled. This flag is is enabled
by default, with the exception for
when attached by PATHNAME. The data
definition statement still succeeds
even with the reported warnings. The
database administrator may choose to
rollback the statement based on this
feedback.
WARN_INVALID Xw Reports invalidated objects during
the ALTER INDEX, DROP INDEX, DROP
TABLE, and DROP MODULE statements.
WATCH_CALL Xa Traces the execution of queries,
triggers and stored functions and
procedures. The output includes
the name of the trigger, function
or procedure or "unnamed" for an
anonymous query. In most cases a
query can be named using the OPTIMIZE
AS clause. It also includes the value
of CURRENT_USER during the execution
of that routine. CURRENT_USER may be
inherited from any module that uses
the AUTHORIZATION clause.
WATCH_OPEN Xo Traces all queries executed on the
database. This may include SQL
runtime system queries to lookup
table names, etc as well as queries
executed by the application. The
output includes the 32 digit hex
identifier, the same as used by the
CREATE OUTLINE statement. This value
uniquely identifies the query being
executed.
If a query is a stored routine
(function or procedure) then the
notation "(stored)" is appended, if
the query is named then it will be
classified as "(query)", otherwise it
will be designated as "(unnamed)".
ZIGZAG_MATCH None Enables zigzag key skip on both
outer and inner match loops. When
you specify the ZIGZAG_MATCH keyword
with the NOZIGZAG_OUTER keyword, it
disables zigzag key skip on the outer
loop (and has the same action as
setting the RDMS$DISABLE_ZIGZAG_MATCH
logical name to 1). The NOZIGZAG_
MATCH keyword disables zigzag key
skip on both outer and inner match
loops (and has the same action as
setting the RDMS$DISABLE_ZIGZAG_MATCH
logical name to 2).
ZIGZAG_OUTER None Enables zigzag key skip on the outer
loop. See the entry for ZIGZAG_MATCH
for information on the action taken
when you specify ZIGZAG_OUTER and
ZIGZAG_MATCH together.
3.2 – NOFLAGS
The SET NOFLAGS statement disables all currently enabled flags.
It is equivalent to SET FLAGS 'NONE'. NOFLAGS is only permitted
in Interactive SQL.
3.3 – ON ALIAS alias-name
Using the ON ALIAS clause allows the database administrator to
set flags on just one database alias instead of on all currently
attached databases. Use the name of an alias as declared by the
ATTACH or CONNECT statement. If no ALIAS clause is used then the
alias name will default to RDB$DBHANDLE.
4 – Examples
Example 1: Enabling and disabling database system debug flags
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL>
SQL> SET FLAGS 'TRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,TRACE
SQL>
SQL> SET FLAGS 'STRATEGY';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX,TRACE
SQL>
SQL> SET FLAGS 'NOTRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX
SQL>
SQL> SET NOFLAGS;
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL>
Example 2: Using the PREFIX keyword
SQL> ATTACH 'FILENAME mf_personnel';
SQL> --
SQL> -- Show that the PREFIX keyword is enabled by default
SQL> --
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL> --
SQL> -- Enable TRACE
SQL> --
SQL> SET FLAGS 'TRACE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,TRACE
SQL> --
SQL> -- Show that the prefix is displayed
SQL> --
SQL> BEGIN
cont> TRACE 'AAA';
cont> END;
~Xt: AAA
SQL> --
SQL> -- Turn off the prefix
SQL> --
SQL> SET FLAGS 'NOPREFIX';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
TRACE
SQL> --
SQL> -- Show that the prefix is no longer displayed
SQL> --
SQL> BEGIN
cont> TRACE 'AAA';
cont> END;
AAA
Example 3: Using Host Variables in Interactive SQL
The example also demonstrates using literal strings with multiple
options to enable and disable flags.
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX
SQL> -- declare a host variable to be used with SET FLAGS
SQL> declare :hv char(40);
SQL> -- assign a value to the variable
SQL> begin
cont> set :hv = 'strategy, outline';
cont> end;
SQL> -- use the host variable to enable or disable flags
SQL> set flags :hv;
SQL> show flags
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,PREFIX,OUTLINE
SQL> -- use a string literal directly with the SET FLAGS statement
SQL> set flags 'noprefix,execution(10)';
SQL> show flags
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
STRATEGY,OUTLINE,EXECUTION(10)
Example 4: Using the MODE(n) Flag
SQL> SET FLAGS 'MODE(10), OUTLINE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,OUTLINE,MODE(10)
SQL> SELECT COUNT(*) FROM EMPLOYEES;
-- Rdb Generated Outline : 30-MAY-1997 16:35
create outline QO_B3F54F772CC05435_0000000A
id 'B3F54F772CC054350B2B454D95537995'
mode 10
as (
query (
-- For loop
subquery (
subquery (
EMPLOYEES 0 access path index EMP_EMPLOYEE_ID
)
)
)
)
compliance optional ;
100
1 row selected
Example 5: Using the WARN_INVALID Debug Flag
SQL> SET FLAGS 'WARN_INVALID';
SQL> SHOW FLAGS;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_INVALID
SQL> -- warning because of dependencies
SQL> DROP TABLE T1 CASCADE;
~Xw: Routine "P3" marked invalid
~Xw: Routine "P2" marked invalid
~Xw: Routine "P1" marked invalid
SQL>
SQL> -- Create an outline that references an INDEX.
SQL> CREATE TABLE T1 (A INTEGER, B INTEGER);
SQL> CREATE INDEX I1 ON T1 (A);
SQL> CREATE OUTLINE QO1
cont> ID '19412AB61A7FE1FA6053F43F8F01EE6D'
cont> MODE 0
cont> AS (
cont> QUERY (
cont> SUBQUERY (
cont> T1 0 ACCESS PATH INDEX I1
cont> )
cont> )
cont> )
cont> COMPLIANCE OPTIONAL;
SQL>
SQL> -- Warning because of disabled index
SQL> ALTER INDEX I1
cont> MAINTENANCE IS DISABLED;
~Xw: Outline "QO1" marked invalid (index "I1" disabled)
SQL> SHOW OUTLINE QO1;
QO1
Object has been marked INVALID
Source:
CREATE OUTLINE QO1
ID '19412AB61A7FE1FA6053F43F8F01EE6D'
MODE 0
AS (
QUERY (
SUBQUERY (
T1 0 ACCESS PATH INDEX I1
)
)
)
COMPLIANCE OPTIONAL;
Example 6: Using the INTERNAL Keyword to Display Trigger Actions
SQL> -- The following code shows the strategy used by the trigger
SQL> -- actions on the AFTER DELETE trigger on EMPLOYEES
SQL> SET FLAGS 'STRATEGY, INTERNALS, REQUEST_NAMES';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
INTERNALS,STRATEGY,PREFIX,REQUEST_NAMES
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = '00164';
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation DEGREES
Index name DEG_EMP_ID [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation JOB_HISTORY
Index name JOB_HISTORY_HASH [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Get Temporary relation Retrieval by index of relation SALARY_HISTORY
Index name SH_EMPLOYEE_ID [1:1]
~S: Trigger name EMPLOYEE_ID_CASCADE_DELETE
Conjunct Get Retrieval by index of relation DEPARTMENTS
Index name DEPARTMENTS_INDEX [0:0]
Temporary relation Get Retrieval by index of relation EMPLOYEES
Index name EMPLOYEES_HASH [1:1] Direct lookup
1 row deleted
Example 7: Using the INDEX_COLUMN_GROUP Keyword
SQL> -- The table STUDENTS has an index on the two columns
SQL> -- STU_NUM and COURSE_NUM. When the INDEX_COLUMN_GROUP
SQL> -- keyword is not set, the optimizer uses a fixed
SQL> -- proportion of the table cardinality based on the equality
SQL> -- with the STU_NUM column. In this example, 5134 rows are expected,
SQL> -- when in reality, only 9 are returned by the query.
SQL> CREATE INDEX STUDENT_NDX ON STUDENTS (STU_NUM,COURSE_NUM DESC);
SQL> --
SQL> SELECT STU_NUM FROM STUDENTS
cont> WHERE STU_NUM = 191270771
cont> ORDER BY OTHER_COLUMN;
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 4.5644922E+03
Cardinality of chosen solution 5.1342500E+03
~O: Physical statistics used
Sort
SortId# 7., # Keys 2
Item# 1, Dtype: 2, Order: 0, Off: 0, Len: 1
Item# 2, Dtype: 35, Order: 0, Off: 1, Len: 8
LRL: 32, NoDups:0, Blks:327, EqlKey:0, WkFls: 2
Leaf#01 BgrOnly STUDENTS Card=164296
BgrNdx1 STUDENT_NDX [1:1] Fan=14
191270771
191270771
191270771
191270771
191270771
191270771
191270771
191270771
SORT(9) SortId# 7, --------------------- Version: V5-000
Records Input: 9 Sorted: 9 Output: 0
LogRecLen Input: 32 Intern: 32 Output: 32
Nodes in SoTree: 5234 Init Dispersion Runs: 0
Max Merge Order: 0 Numb.of Merge passes: 0
Work File Alloc: 0
MBC for Input: 0 MBC for Output: 0
MBF for Input: 0 MBF for Output: 0
Big Allocated Chunk: 4606464 busy
191270771
9 rows selected
SQL> --
SQL> -- When you use the SET FLAGS statement to set the
SQL> -- INDEX_COLUMN_GROUP keyword, it activates the optimizer
SQL> -- to consider the index segment columns as a workload column
SQL> -- group, compute the statistics for duplicity factor and null
SQL> -- factor dynamically, and then apply them in estimating the
SQL> -- cardinality of the solution.
SQL> --
SQL> SET FLAGS 'INDEX_COLUMN_GROUP';
SQL> -- The following is the optimizer cost estimate and sort output trace
SQL> -- for the previous query with INDEX_COLUMN_GROUP enabled. The optimizer
SQL> -- now estimates a lower cardinality of about 8 rows.
Solutions tried 2
Solutions blocks created 1
Created solutions pruned 0
Cost of the chosen solution 3.8118614E+01
Cardinality of chosen solution 8.3961573E+00
~O: Workload and Physical statistics used
Sort
SortId# 2., # Keys 2
Item# 1, Dtype: 2, Order: 0, Off: 0, Len: 1
Item# 2, Dtype: 35, Order: 0, Off: 1, Len: 8
LRL: 32, NoDups:0, Blks:7, EqlKey:0, WkFls: 2
Leaf#01 BgrOnly STUDENTS Card=164296
BgrNdx1 STUDENT_NDX [1:1] Fan=14
191270771
191270771
191270771
191270771
191270771
191270771
191270771
191270771
SORT(2) SortId# 2, --------------------- Version: V5-000
Records Input: 9 Sorted: 9 Output: 0
LogRecLen Input: 32 Intern: 32 Output: 32
Nodes in SoTree: 114 Init Dispersion Runs: 0
Max Merge Order: 0 Numb.of Merge passes: 0
Work File Alloc: 0
MBC for Input: 0 MBC for Output: 0
MBF for Input: 0 MBF for Output: 0
Big Allocated Chunk: 87552 idle
191270771
9 rows selected
Example 8: Using the AUTO_OVERRIDE Keyword
SQL> -- Suppose that after year 2000 testing was performed on a
SQL> -- production system, the system date and time were not reset
SQL> -- to the correct date. This was not noticed until
SQL> -- after transactions for a full day had been stored. To
SQL> -- correct this problem, the database administrator overrides
SQL> -- the READ ONLY characteristic of the AUTOMATIC column and
SQL> -- adjusts the date and time.
SQL> SELECT * FROM ACCOUNTS
cont> WHERE LAST_UPDATE > DATE'2001-1-1';
ACCOUNT_NO LAST_NAME LAST_UPDATE CURRENT_BALANCE
NULL Smith 2001-06-02 100000.000
1 row selected
SQL> -- Attempts to fix the date and time fail because the
SQL> -- column is AUTOMATIC.
SQL> UPDATE ACCOUNTS
cont> SET LAST_UPDATE = LAST_UPDATE - INTERVAL'1' YEAR
cont> WHERE LAST_UPDATE > DATE'2000-1-1';
%RDB-E-READ_ONLY_FIELD, attempt to update the read-only field LAST_UPDATE
SQL> --
SQL> SET FLAGS 'AUTO_OVERRIDE';
SQL> SHOW FLAGS
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,AUTO_OVERRIDE
SQL>--
SQL> -- Fix the date and time.
SQL> UPDATE ACCOUNTS
cont> SET LAST_UPDATE = LAST_UPDATE - INTERVAL'1' YEAR
cont> WHERE LAST_UPDATE > DATE'2000-1-1';
1 row updated
SQL>
SQL> SELECT * FROM ACCOUNTS;
ACCOUNT_NO LAST_NAME LAST_UPDATE CURRENT_BALANCE
NULL Smith 1999-06-02 100000.000
1 row selected
SQL>
SQL> SET FLAGS 'NOAUTO_OVERRIDE';
Example 9: Using the AUTO_INDEX option
SQL> set dialect 'SQL92';
SQL> set flags 'AUTO_INDEX,INDEX_STATS';
SQL> create table PERSON
cont> (employee_id integer primary key,
cont> manager_id integer references PERSON (employee_id),
cont> last_name char(30),
cont> first_name char(30),
cont> unique (last_name, first_name));
~Ai create index "PERSON_PRIMARY_EMPLOYEE_ID"
~Ai larea length is 430
~Ai storage area (default) larea=57
~Ai create sorted index, ikey_len=5
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=430 %fill=0
~Ai create index "PERSON_FOREIGN1"
~Ai larea length is 215
~Ai storage area is shared: larea=57
~Ai create sorted index, ikey_len=5
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=0 %fill=0
~Ai create index "PERSON_UNIQUE1"
~Ai larea length is 215
~Ai storage area is shared: larea=57
~Ai create sorted index, ikey_len=62
Sort Get Retrieval sequentially of relation PERSON
~Ai create index partition, node=0 %fill=0
SQL>
SQL> show table (index) person
Information for table PERSON
Indexes on table PERSON:
PERSON_FOREIGN1 with column MANAGER_ID
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
PERSON_PRIMARY_EMPLOYEE_ID with column EMPLOYEE_ID
No Duplicates allowed
Type is Sorted
Key suffix compression is DISABLED
Node size 430
PERSON_UNIQUE1 with column LAST_NAME
and column FIRST_NAME
Duplicates are allowed
Type is Sorted
Key suffix compression is DISABLED
SQL>
Example 10: Using the WATCH_CALL option
This example shows the output of WATCH_CALL for an INSERT
statement which causes an AFTER INSERT trigger (AFTER_INSERT)
to be executed which calls an SQL function WRITE_TEXT to trace
the input data. It then traces a query named using OPTIMIZE AS
clause.
SQL> insert into SAMPLE_T values ('Fred');
~Xa: routine "(unnamed)", user=SMITH
~Xa: routine "AFTER_INSERT", user=SMITH
~Xa: routine "WRITE_TEXT", user=SMITH
~Xt: Fred
1 row inserted
SQL> select * from SAMPLE_T
cont> optimize as LOOKUP_SAMPLE_T;
~Xa: routine "LOOKUP_SAMPLE_T", user=SMITH
NEW_NAME
Fred
1 row selected
Example 11: Using the WATCH_OPEN option
This example shows the output of WATCH_OPEN for the same INSERT
statement as seen in example 10.
SQL> insert into SAMPLE_T values ('Fred');
~Xo: Start Request B667E51E3625026EB7FFF3F4D3A16DC3 (unnamed)
~Xo: Start Request A8568053FE5A1A0852A1BE83A884016F "AFTER_INSERT" (query)
~Xo: Start Request 08AE59062657299B4768F6C2DFB6928E "WRITE_TEXT" (stored)
~Xt: Fred
1 row inserted
SQL>
SQL> select * from SAMPLE_T
cont> optimize as LOOKUP_SAMPLE_T;
~Xo: Start Request F6025FAB1DD36B0DE0E52F3A9641BC5F "LOOKUP_SAMPLE_T" (query)
NEW_NAME
Fred
Fred
2 rows selected
Example 12: Using SET FLAGS from an application program
The SET FLAGS statement can be executed from Dynamic SQL using
one of two methods.
o The first method is immediate execution by passing a string
literal. The string literal argument to SET FLAGS requires
that the single quote marks be doubled for correct inclusion
in the string literal argument to EXECUTE IMMEDIATE.
o The second method is to pass the entire SET FLAGS statement in
a parameter to EXECUTE IMMEDIATE
exec sql
execute immediate 'set flags ''strategy''';
The entire SET FLAGS statement could be in a parameter to EXECUTE
IMMEDIATE
exec sql
execute immediate :set_flags_text;
If SET FLAGS is executed multiple times it can be prepared as
a dynamic statement (PREPARE) and then the statement name used
for multiple executions. The input marker (?) is substituted on
different calls to EXECUTE the previously prepared statement.
#include <string.h>
#include <sql_rdb_headers.h>
void main ()
{
int SQLCODE;
char myflags[40];
exec sql
prepare set_flags_stmt from 'set flags ?';
if (SQLCODE != 0)
sql_signal ();
strcpy (myflags, "transaction,item_list");
exec sql
execute set_flags_stmt using :myflags;
if (SQLCODE != 0)
sql_signal ();
exec sql
start transaction;
if (SQLCODE != 0)
sql_signal ();
strcpy (myflags, "notransaction,noitem_list");
exec sql
execute set_flags_stmt using :myflags;
if (SQLCODE != 0)
sql_signal ();
exec sql
rollback;
if (SQLCODE != 0)
sql_signal ();
}
Example 13: Using the CHRONO_FLAG option
SQL> set flags 'chrono_fla(2),transaction';
SQL> start transaction;
ATTACH #1, 29-NOV-2003 10:08:37.51
~T Compile transaction (1) on db: 1
~T Transaction Parameter Block: (len=2)
0000 (00000) TPB$K_VERSION = 1
0001 (00001) TPB$K_WRITE (read write)
ATTACH #1, 29-NOV-2003 10:08:37.58
~T Start_transaction (1) on db: 1, db count=1
SQL> rollback;
ATTACH #1, 29-NOV-2003 10:08:46.74
~T Rollback_transaction (1) on db: 1
SQL> rollback;
ATTACH #1, 29-NOV-2003 10:08:46.74
~T Rollback_transaction (1) on db: 1
SQL>
Example 14: Using the REBUILD_SPAM_PAGES option
When changing the row length or THRESHOLDS clause for a table
or index, the corresponding SPAM pages for the logical area
may require rebuilding. By default, these DDL commands update
the AIP and set a flag to indicate that the SPAM pages should
be rebuilt. However, this flag may be set prior to executing a
COMMIT for the transaction and the rebuild will take place within
this transaction.
The following example shows a simple change to the EMPLOYEES
table (mapped in this example to set of UNIFORM areas). The flag
STOMAP_STATS is used to enable more trace information from the
ALTER and COMMIT statements.
SQL> set transaction read write;
SQL>
SQL> set flags 'stomap_stats';
SQL>
SQL> alter table EMPLOYEES
cont> add column MANAGERS_COMMENTS varchar(300);
~As: reads: async 0 synch 94, writes: async 18 synch 1
SQL>
SQL> alter storage map EMPLOYEES_MAP
cont> store
cont> using (EMPLOYEE_ID)
cont> in EMPIDS_LOW
cont> (thresholds (34,76,90))
cont> with limit of ('00200')
cont> in EMPIDS_MID
cont> (thresholds (34,76,90))
cont> with limit of ('00400')
cont> otherwise in EMPIDS_OVER
cont> (thresholds (34,76,90));
~As locking table "EMPLOYEES" (PR -> PU)
~As: removing superseded routine EMPLOYEES_MAP
~As: creating storage mapping routine EMPLOYEES_MAP (columns=1)
~As: reads: async 0 synch 117, writes: async 56 synch 0
SQL>
SQL> set flags 'rebuild_spam_pages';
SQL>
SQL> commit;
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
%RDMS-I-LOGMODVAL, modified record length to 423
%RDMS-I-LOGMODVAL, modified space management thresholds to (34%, 76%, 90%)
SQL>
The message LOGMODVAL will appear for each logical area in the
storage map, one per partition.
This rebuild action only applies to UNIFORM storage areas and may
incur significant I/O as SPAM pages and data pages are read to
allow the SPAM page to be rebuilt.
Example 15: Using the OPTIMIZATION_LEVEL flag
The following example shows how the behavior of a query changes
using the dynamic optimizer with the OPTIMIZATION_LEVEL flag set.
SQL> -- show with default behavior (FFirst tactic used)
SQL> select *
cont> from xtest
cont> where col2 between 999980 and 1000000
cont> and col1 > 0
cont> ;
Tables:
0 = XTEST
Leaf#01 FFirst 0:XTEST Card=10
Bool: (0.COL2 >= 999980) AND (0.COL2 <= 1000000) AND (0.COL1 > 0)
BgrNdx1 XTEST_IDX [1:0] Fan=17
Keys: 0.COL1 > 0
0 rows selected
SQL>
SQL> -- use SET FLAGS
SQL> set flags 'optimization_level(total_time)';
SQL>
SQL> -- show that BgrOnly is used for TOTAL TIME
SQL> select *
cont> from xtest
cont> where col2 between 999980 and 1000000
cont> and col1 > 0
cont> ;
Tables:
0 = XTEST
Leaf#01 BgrOnly 0:XTEST Card=10
Bool: (0.COL2 >= 999980) AND (0.COL2 <= 1000000) AND (0.COL1 > 0)
BgrNdx1 XTEST_IDX [1:0] Fan=17
Keys: 0.COL1 > 0
0 rows selected
SQL>
Example 16: Using the ON ALIAS Clause
The default behavior for SET FLAGS is to establish the flag
settings on all currently attached databases. This clause
will allow the database administrator to set flags on just one
database alias.
The following example shows a case where the enabling of AUTO_
OVERRIDE required DBADM privilege on the target database but not
on the source database. It may be that the current user does not
have (or really need) DBADM privilege on that database.
SQL> -- Now enable AUTO_OVERRIDE on only one database
SQL> set flags (on alias abc_a) 'auto_override';
SQL> set flags (on alias abc_b) 'none';
SQL> insert into abc_a.SAMPLE_TABLE select * from abc_b.SAMPLE_SOURCE;
SQL> commit;
Example 17: Using the NOREWRITE keyword
SQL> set line length 70
SQL> show flags;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_DDL,INDEX_COLUMN_GROUP,MAX_SOLUTION,MAX_RECURSION(100)
,REWRITE(CONTAINING),REWRITE(LIKE),REWRITE(STARTING_WITH)
,REFINE_ESTIMATES(127),NOBITMAPPED_SCAN
SQL>
SQL> set flags 'norewrite';
SQL> show flags;
Alias RDB$DBHANDLE:
Flags currently set for Oracle Rdb:
PREFIX,WARN_DDL,INDEX_COLUMN_GROUP,MAX_SOLUTION,MAX_RECURSION(100)
,REFINE_ESTIMATES(127),NOBITMAPPED_SCAN
SQL>