|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: CREATE LIBRARY to CREATE SPFILE, 6 of 16
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.
The SQL statement issued subsequently must be an exact string match of the statement specified when creating the outline.
To create a public or private outline, you must have the
OUTLINE system privilege.
If you are creating a clone outline from a source outline, you must also have the
To create a private outline, you must provide an outline editing table to hold the outline data in your schema by executing the
DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES procedure. You must have the
EXECUTE privilege on the
DBMS_OUTLN_EDIT package to execute this procedure.
You enable or disable the use of stored outlines dynamically for an individual session or for the system:
USE_STORED_OUTLINESparameter to use public outlines
USE_PRIVATE_OUTLINESparameter to use private stored outlines.
REPLACE to replace an existing outline with a new outline of the same name.
PUBLIC if you are creating an outline for use by
PUBLIC. This is the default.
PRIVATE to create an outline for private use by the current session only. The data of this outline is stored in the current schema.
Before first creating a private outline, you must run the
Specify the unique name to be assigned to the stored outline. If you do not specify
outline, the system generates an outline name.
FROM clause to create a new outline by copying an existing one. By default, Oracle looks for
source_category in the public area. If you specify
PRIVATE, Oracle will look for the outline in the current schema.
Restriction on the FROM clause: If you specify the
FROM clause, you cannot specify the
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, the outline is stored in the
Specify the SQL statement for which Oracle 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
You can specify any one of the following statements:
Restrictions on the ON clause:
ONclause, you cannot specify the
The following statement creates a stored outline by compiling the
ON statement. The outline is called
salaries and is stored in the category
When this same
SELECT statement is subsequently compiled, if the
USE_STORED_OUTLINES parameter is set to
special, Oracle generates the same execution plan as was generated when the outline
salaries was created.
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 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;
The following statement copies back (or publicizes) a private outline to the public area after private editing: