Creates a new query outline and stores this outline in the
database.
A query outline is an overall plan for how a query can be
implemented and may contain directives that control the join
order, join methods, index usage (or all of these) the optimizer
selects when processing a query. Use of query outlines helps
ensure that query performance is highly stable across releases of
Oracle Rdb.
1 – Environment
You can use the CREATE OUTLINE statement only in interactive SQL.
2 – Format
CREATE OUTLINE <outline-name> -+--------------------------------++
+> STORED NAME IS <stored-name> -+|
+------------------------- <-------------------------------------+
++-> FROM --> ( --> <sql-query> --> ) ----------+---------+
+-> on-clause ---------------------------------+ |
+-> ID 'id-number' ----------------------------+ |
+------------------------------ <-----------------------+
+-----+------------++--------------------------------+--+
+> MODE mode ++> AS ---+>( -> query-list -> )--+ |
+> USING + |
+--------------------------- <----------------------------+
++-----------------------------+--------------------------+
+> COMPLIANCE -+-> MANDATORY -+ |
+-> OPTIONAL --+ |
+--------------------------- <----------------------------+
++-------------------------------------------------------++
+> EXECUTION OPTIONS --> ( --> execution-options --> ) -+|
+--------------------------- <----------------------------+
++--------------------------------+----------------------------->
+> COMMENT IS --+-> 'string' ---++
+----- / <------+
on-clause =
---> ON --+-> PROCEDURE -+-+-> ID proc-id ----+-+->
+-> FUNCTION --+ +-> NAME <name> ---+ |
+-> COLUMN ------+-----> <name> ------+
+-> CONSTRAINT --+
+-> TRIGGER -----+
+-> VIEW --------+
query-list =
---+-> QUERY (source) ----------+---------->
+-------------<--------------+
source =
-+-+-------------+--+-> table-access --------------+--+-------->
| +-> FLOATING -+ +-> ORDERED ---+--> (source) --+ |
| +-> UNORDERED -+ | |
| +-> subquery-list -------------+ |
| +--------------------------------------------------+
| +-> JOIN BY -+--> CROSS ------+--- TO -----+--+
| | +--> MATCH ------+ | |
| | +--> ANY METHOD -+ | |
| +-> UNION WITH ----------------------------+ |
+------------------------<----------------------+
table-access =
---> <table-name> -+-------------------------+-> context --+
+-> MODULE <module-name> -+ |
+----------------------------------------------------------+
+-> ACCESS --> PATH ---+------------------------------+--->
+--> ANY ----------------------+
+--> SEQUENTIAL ---------------+
+--> DBKEY --------------------+
+--> ROWID --------------------+
+--> NO INDEX -----------------+
+--> INDEX -+> <index-name> --++
+------ , <-------+
subquery-list =
---+-> SUBQUERY (source) -------+---------->
+-------------<--------------+
execution-options =
-+-> ANY --------+------>
+-> NONE -------+
+-> FAST FIRST -+
+-> TOTAL TIME -+
3 – Arguments
3.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.
3.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.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.
3.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 (/).
3.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.
3.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.
3.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).
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.15 – ON_CONSTRAINT
Syntax options:
ON CONSTRAINT name
Generates an outline definition for the specified constraint.
3.16 – ON_FUNCTION
Syntax options:
ON FUNCTION ID proc-id | ON FUNCTION NAME name
Generates an outline definition for the specified stored
function.
3.17 – ON_PROCEDURE
Syntax options:
ON PROCEDURE ID proc-id | ON PROCEDURE NAME name
Generates an outline definition for the specified stored
procedure.
3.18 – ON_TRIGGER
Syntax options:
ON TRIGGER name
Generates an outline definition for the specified trigger.
3.19 – ON_VIEW
Syntax options:
ON VIEW name
Generates an outline definition for the specified view.
3.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.
3.21 – outline-name
The name of the new query outline. The name has a maximum length
of 31 characters.
3.22 – QUERY
Specifies that the data sources within the parentheses belong to
a separate query.
3.23 – SUBQUERY
Specifies that the data sources within the parentheses belong to
a separate subquery.
3.24 – table-name
Specifies the name of a database table.
3.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.
3.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.
3.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.
4 – Examples
Example 1: Creating an outline named AVAILABLE_EMPLOYEES
SQL> CREATE OUTLINE available_employees
cont> ID '09ADFE9073AB383CAABC4567BDEF3832' MODE 0
cont> AS (
cont> QUERY (
cont> --
cont> -- Cross the employees table with departments table first.
cont> --
cont> employees 0 ACCESS PATH SEQUENTIAL JOIN BY MATCH TO
cont> departments 3 ACCESS PATH INDEX dept_index JOIN BY MATCH TO
cont> SUBQUERY (
cont> job_fitness 2 ACCESS PATH INDEX job_fit_emp, job_fit_dept
cont> JOIN BY CROSS TO
cont> SKILLS 4 ACCESS PATH ANY
cont> ) JOIN BY MATCH TO
cont> SUBQUERY (
cont> major_proj 1 ACCESS PATH ANY JOIN BY CROSS TO
cont> education 6 ACCESS PATH ANY
cont> ) JOIN BY CROSS TO
cont> research_projects 5 ACCESS PATH ANY UNION WITH
cont> --
cont> -- Always do the union with employees table last
cont> --
cont> employees 7 ACCESS PATH ANY
cont> )
cont> )
cont> COMPLIANCE OPTIONAL
cont> COMMENT IS 'Available employees';
Example 2: Creating an outline using the FROM clause
SQL> CREATE OUTLINE degrees_for_emps_over_65
cont> FROM
cont> (SELECT e.last_name, e.first_name, e.employee_id,
cont> d.degree, d.year_given
cont> FROM employees e, degrees d
cont> WHERE e.birthday < '31-Dec-1930'
cont> AND e.employee_id = d.employee_id
cont> ORDER BY e.last_name)
cont> USING
cont> (QUERY
cont> (SUBQUERY
cont> (degrees 1 ACCESS PATH SEQUENTIAL
cont> JOIN BY CROSS TO
cont> employees 0 ACCESS PATH ANY
cont> )
cont> )
cont> )
cont> COMPLIANCE OPTIONAL
cont> COMMENT IS 'Outline to find employees over age 65 with college degrees';
SQL> --
SQL> SHOW OUTLINE degrees_for_emps_over_65
DEGREES_FOR_EMPS_OVER_65
Comment: Outline to find employees over age 65 with college degrees
Source:
-- Rdb Generated Outline : 13-NOV-1995 15:28
create outline DEGREES_FOR_EMPS_OVER_65
id 'B6923A6572B28E734D6F9E8E01598CD8'
mode 0
as (
query (
subquery (
DEGREES 1 access path sequential
join by cross to
EMPLOYEES 0 access path index EMPLOYEES_HASH
)
)
)
compliance optional ;
Example 3: Creating an outline using the ON FUNCTION clause
SQL> CREATE OUTLINE out1
cont> ON FUNCTION NAME function1;
SQL> COMMIT;
SQL> SHOW OUTLINE out1
OUT1
Source:
-- Rdb Generated Outline : 2-FEB-1996 15:46
create outline OUT1
id '264A6DDADCB483AE5B2CDF629C9C8C0F'
mode 0
as (
query (
subquery (
EMPLOYEES 0 access path index EMPLOYEES_HASH
)
)
)
compliance optional ;
Example 4: Creating an outline on a procedure that accesses a
declared local temporary table (see the CREATE MODULE statement
for the stored procedure and temporary table definition)
SQL> CREATE OUTLINE outline1
cont> ON PROCEDURE NAME paycheck_ins_decl
cont> MODE 0
cont> AS (
cont> QUERY (
cont> module.paycheck_decl_tab MODULE paycheck_decl_mod
cont> 0
cont> ACCESS PATH SEQUENTIAL
cont> )
cont> )
cont> COMPLIANCE OPTIONAL;
SQL> SHOW OUTLINE outline1
OUTLINE1
Source:
create outline OUTLINE1
mode 0
as (
query (
PAYCHECK_DECL_TAB MODULE PAYCHECK_DECL_MOD 0
access path sequential
)
)
compliance optional ;
Example 5: New Output from Query Outlines
SQL> BEGIN
cont> DECLARE :x INTEGER;
cont> -- Assignment
cont> SET :x = (SELECT COUNT(*) FROM TOUT_1);
cont> -- Delete statement
cont> DELETE FROM TOUT_1;
cont> -- Update statement
cont> UPDATE TOUT_1
cont> SET a = (SELECT AVG(a) FROM TOUT_2)
cont> WHERE a IS NULL;
cont> -- Singleton Select
cont> SELECT a INTO :x
cont> FROM TOUT_1
cont> WHERE a = 1;
cont> -- Trace (nothing if TRACE is disabled)
cont> TRACE 'The first value: ', (SELECT a FROM TOUT_1 LIMIT TO 1 ROW);
cont> END;
The query outline generated by Oracle Rdb appears with comments
after the QUERY keyword in the outline.
-- Rdb Generated Outline : 29-MAY-1997 23:17
create outline QO_C11395E6020C6FFA_00000000
id 'C11395E6020C6FFA5A183A6CCE7C1F33'
mode 0
as (
query (
-- Set
subquery (
TOUT_1 0 access path sequential
)
)
query (
-- Delete
subquery (
TOUT_1 0 access path sequential
)
)
query (
-- Update
subquery (
subquery (
TOUT_2 1 access path sequential
)
join by cross to
subquery (
TOUT_1 0 access path sequential
)
)
)
query (
-- Select
subquery (
TOUT_1 0 access path sequential
)
)
query (
-- Trace
subquery (
TOUT_1 0 access path sequential
)
)
)
compliance optional ;