Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Purpose
Use the CREATE
OUTLINE
statement to create a stored outline, which is a set of attributes used by the optimizer to generate an execution plan. You can then instruct the optimizer to use a set of outlines to influence the generation of execution plans whenever a particular SQL statement is issued, regardless of changes in factors that can affect optimization. You can also modify an outline so that it takes into account changes in these factors.
Note:
The SQL statement you want to affect must be an exact string match of the statement specified when creating the outline.See Also:
Oracle Database Performance Tuning Guide for information on execution plans
ALTER OUTLINE for information on modifying an outline
ALTER SESSION and ALTER SYSTEM for information on the USE_STORED_OUTLINES
and USE_PRIVATE_OUTLINES
parameters
Prerequisites
To create a public or private outline, you must have the CREATE
ANY
OUTLINE
system privilege.
If you are creating a clone outline from a source outline, you must also have the role.
You can enable or disable the use of stored outlines dynamically for an individual session or for the system:
Enable the USE_STORED_OUTLINES
parameter to use public outlines.
Enable the USE_PRIVATE_OUTLINES
parameter to use private stored outlines.
See Also:
Oracle Database Performance Tuning Guide for information on using outlines for performance tuning
PL/SQL Packages and Types Reference for information on the DBMS_OUTLN_EDIT
package
Syntax
create_outline::=
Note:
None of the clauses afteroutline
are required. However, you must specify at least one clause after outline
, and it must be either the FROM
clause or the ON
clause.Semantics
Specify OR
REPLACE
to replace an existing outline with a new outline of the same name.
Specify PUBLIC
if you are creating an outline for use by PUBLIC
. This is the default.
Specify PRIVATE
to create an outline for private use by the current session only. The data of this outline is stored in the current schema.
Note:
To create a private outline, you must provide an outline editing table to hold the outline data in your schema by executing theDBMS_OUTLN_EDIT.CREATE_EDIT_TABLES
procedure. You must have the EXECUTE
object privilege on the DBMS_OUTLN_EDIT
package to execute this procedure.outline
Specify the unique name to be assigned to the stored outline. If you do not specify outline
, then the database generates an outline name.
See Also:
"Creating an Outline: Example"Use the FROM
clause to create a new outline by copying an existing one. By default, Oracle Database looks for source_category
in the public area. If you specify PRIVATE
, then the database looks for the outline in the current schema.
Restriction on Copying an Outline If you specify the FROM
clause, then you cannot specify the ON
clause.
See Also:
"Creating a Private Clone Outline: Example" and "Publicizing a Private Outline to the Public Area: Example"Specify an optional name used to group stored outlines. For example, you could specify a category of outlines for end-of-week use and another for end-of-quarter use. If you do not specify category
, then the outline is stored in the DEFAULT
category.
Specify the SQL statement for which the database will create an outline when the statement is compiled. This clause is optional only if you are creating a copy of an existing outline using the FROM
clause.
You can specify any one of the following statements: SELECT
, DELETE
, UPDATE
, INSERT
... SELECT
, CREATE
TABLE
... AS
SELECT
.
Restrictions on the ON Clause This clause is subject to the following restrictions:
If you specify the ON
clause, you cannot specify the FROM
clause.
You cannot create an outline on a multitable INSERT
statement.
The SQL statement in the ON
clause cannot include any DML operation on a remote object.
Note:
You can specify multiple outlines for a single statement, but each outline for the same statement must be in a different category.Example
Creating an Outline: Example The following statement creates a stored outline by compiling the ON
statement. The outline is called salaries
and is stored in the category special
.
CREATE OUTLINE salaries FOR CATEGORY special ON SELECT last_name, salary FROM employees;
When this same SELECT
statement is subsequently compiled, if the USE_STORED_OUTLINES
parameter is set to special
, the database generates the same execution plan as was generated when the outline salaries
was created.
Creating a Private Clone Outline: Example The following statement creates a stored private outline my_salaries
based on the public category salaries
created in the preceding example. In order to create a private outline, the user creating the private outline must have the EXECUTE
object privilege on the DBMS_OUTLN_EDIT
package and must execute the CREATE_EDIT_TABLES
procedure of that package.
EXECUTE DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES; CREATE OR REPLACE PRIVATE OUTLINE my_salaries FROM salaries;
Publicizing a Private Outline to the Public Area: Example The following statement copies back (publicizes) a private outline to the public area after private editing:
CREATE OR REPLACE OUTLINE public_salaries FROM PRIVATE my_salaries;