1 SET_FLAGS 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. 2 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 -+--------------------------+-+-> ----+-+--> | +-> (ON ALIAS alias-name) -+ +-> -+ | +-> NOFLAGS --------------------------------------------------+ 2 Arguments 3 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 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 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. 2 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 #include 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>