SQL$HELP_OLD72.HLB  —  SET_FLAGS, Arguments

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.

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  –  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.
Close Help