SQL$HELP_OLD72.HLB  —  CREATE  OUTLINE  Arguments

1  –  ACCESS_PATH

    Syntax options:

       ACCESS PATH ANY
       ACCESS PATH SEQUENTIAL
       ACCESS PATH DBKEY
       ACCESS PATH ROWID
       ACCESS PATH NO INDEX

    See also ACCESS_PATH_INDEX. Specifies the access path to use to
    retrieve data from the underlying database table. The following
    table lists the valid access paths.

    Path           Meaning

    ANY            Indicates that the optimizer may choose the most
                   appropriate method.
    SEQUENTIAL     Indicates that sequential access should be used.
    DBKEY          Indicates the access by database key should be
                   used.
    ROWID          Indicates the access by database key should be
                   used.
    NO INDEX       Indicates that any access path not requiring
                   an index can be used. NOINDEX is accepted as a
                   synonym for NO INDEX.

    There is no default access path. An access path must be specified
    for each database table specified within a query outline
    definition.

2  –  ACCESS_PATH_INDEX

    Specifies that data should be retrieved using the specified index
    or list of indexes. If more that one index can be used, then
    separate each index name with a comma.

    Any index name specified should indicate an existing index
    associated with the table with which the access method is
    associated.

3  –  AS query list

    Provides the main definition of an outline.

    This clause is only required when creating an outline using the
    ID id-number clause.

4  –  COMMENT_IS

    Adds a comment about the outline. SQL displays the text when it
    executes a SHOW OUTLINES statement in interactive SQL. Enclose
    the comment in single quotation marks ( ')  and separate multiple
    lines in a comment with a slash mark (/).

5  –  COMPLIANCE

    Syntax options:

    COMPLIANCE MANDATORY | COMPLIANCE OPTIONAL

    Specifies the compliance level for this outline.

    MANDATORY indicates that all outline directives such as table
    order and index usage should be followed as specified. If the
    optimizer is unable to follow any outline directive, an exception
    is raised.

    OPTIONAL indicates that all outline directives are optional and
    that if they cannot be followed, no exception should be raised.
    If OPTIONAL is specified, the strategy chosen by the optimizer
    to carry out the underlying request may not match the strategy
    specified within the outline.

    Use MANDATORY when the strategy that the optimizer chooses must
    be followed exactly as specified from version to version of
    Oracle Rdb even if the optimizer finds a more efficient strategy
    in a future version of Oracle Rdb.

    The default is COMPLIANCE OPTIONAL.

6  –  context

    Specifies the context number for this table. Specify an unsigned
    integer. This number is allocated to the table by the optimizer
    during optimization. Context numbers are unique within queries.

7  –  EXECUTION_OPTIONS

    Specifies options that the optimizer should take into account
    during optimization. The following table lists the valid options.

    Option         Meaning

    ANY            Indicates that the optimizer can choose any
                   optimization method
    FAST FIRST     Indicates that the optimizer can use FAST FIRST
                   optimization if and when appropriate
    NONE           Indicates that optional optimizations should not
                   be applied
    TOTAL TIME     Indicates that the optimizer can use TOTAL TIME
                   optimization if and when appropriate

    The default is EXECUTION OPTIONS (ANY).

8  –  FLOATING

    Specifies that the following data source should be considered to
    be floating and that the order of the data source relative to the
    other data sources within the same level is not fixed.

9  –  FROM sql query

    Enables an outline to be created directly from an SQL statement.

    If the AS clause is not specified, the sql-query is compiled and
    the resulting outline is stored. If the AS clause is specified,
    the sql-query provides an alternate means of specifying the ID.
    If the USING clause is specified, the sql-query is optimized
    using the designated outline as a starting point.

    The only statement accepted as an sql-query in the FROM clause is
    a SELECT statement. Do not end the sql-query with a semicolon.

10  –  ID id number

    Specifies the internal hash identification number of the request
    to which this outline should be applied. Specify a 32-byte string
    representing a 32-hexadecimal character identification code. The
    internal hash identification code is generated by the optimizer
    whenever query outlines are created by the Oracle Rdb optimizer
    during optimization.

    You can optionally specify the MODE clause. You are required to
    specify the AS clause. You cannot specify the USING clause with
    the ID id-number clause.

11  –  JOIN_BY

    Syntax options:

       JOIN BY CROSS
       JOIN BY MATCH
       JOIN BY ANY METHOD

 Specifies the method with which two data sources should be joined.
 The following table lists the valid methods.

    Method         Meaning

    CROSS          Indicates that a cross strategy should be used
    MATCH          Indicates that a match strategy should be used
    ANY METHOD     Indicates that the optimizer can choose any method
                   to join the two data sources

                                   NOTE

       The match join strategy requires that an equivalent join
       column exist between the inner and outer context of the
       join order. If the query for which the outline is created
       does not have an equivalent join column, then the optimizer
       cannot use the match join strategy specified in the outline.

    There is no default join method.

12  –  MODE mode

    Mode is a value assigned to an outline when it is generated by
    the optimizer. The default mode is 0. Specify a signed integer.

    If you create multiple outlines for a single query, the outlines
    cannot have the same outline mode. When more than one outline
    exists for a query, you can set the RDMS$BIND_OUTLINE_MODE
    logical name to the value of the outline mode for the outline you
    want the optimizer to use. For example, if you have a query that
    runs during the day and at night and you created two outlines for
    the query, you could keep the default outline mode of 0 for the
    outline to be used during the day, and assign an outline mode of
    -1 for the outline to be used at night. By setting the RDMS$BIND_
    OUTLINE_MODE logical name to -1 at night, the appropriate outline
    is run at the appropriate time.

    Valid values for modes are -2,147,483,648 to 2,147,483,647.
    Positive mode values are reserved for future use, so it is
    recommended that you specify a value between 0 and -2,147,483,648
    for the mode value.

13  –  MODULE module name

    Associates an outline with a declared local temporary table by
    qualifying the table name with the name of the stored module. In
    order to apply the outline to the declared local temporary table,
    the keyword MODULE is required.

14  –  ON_COLUMN

    Syntax options:

    ON COLUMN name

    Generates an outline for the specified columns DEFAULT, COMPUTED
    BY or AUTOMATIC expression. This is a partial outline which will
    be used when the column is referenced. If a column has both an
    AUTOMATIC UPDATE AS clause and a DEFAULT expression, then only
    one outline is created for the AUTOMATIC clause.

15  –  ON_CONSTRAINT

    Syntax options:

    ON CONSTRAINT name

    Generates an outline definition for the specified constraint.

16  –  ON_FUNCTION

    Syntax options:

    ON FUNCTION ID proc-id | ON FUNCTION NAME name

    Generates an outline definition for the specified stored
    function.

17  –  ON_PROCEDURE

    Syntax options:

    ON PROCEDURE ID proc-id | ON PROCEDURE NAME name

    Generates an outline definition for the specified stored
    procedure.

18  –  ON_TRIGGER

    Syntax options:

    ON TRIGGER name

    Generates an outline definition for the specified trigger.

19  –  ON_VIEW

    Syntax options:

    ON VIEW name

    Generates an outline definition for the specified view.

20  –  ORDERED

    Specifies that all nonfloating data sources within the
    parentheses should be retrieved in the order specified. Join
    items in the group are placed adjacently.

21  –  outline-name

    The name of the new query outline. The name has a maximum length
    of 31 characters.

22  –  QUERY

    Specifies that the data sources within the parentheses belong to
    a separate query.

23  –  SUBQUERY

    Specifies that the data sources within the parentheses belong to
    a separate subquery.

24  –  table-name

    Specifies the name of a database table.

25  –  UNION_WITH

    Specifies the union of two data sources.

    Either a join or union method must be specified between all data
    sources with the exception of QUERY source blocks.

                                   NOTE

       When a join method appears immediately before an ordered
       or unordered group, the join method is associated with the
       first join item named in the group.

    The union strategy is only valid for queries that use the UNION
    operator, and all queries that specify the UNION operator must
    use the union strategy.

26  –  UNORDERED

    Specifies that all data sources within the parentheses should be
    considered floating and that no order is implied. Join items in
    the group are placed adjacently.

27  –  USING query list

    Specifies the outline to be used for compilation of the contents
    of the FROM and ON clauses.

    You cannot use this clause with the ID id-number clause.
Close Help