CREATE OUTLINE

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.

You enable or disable the use of stored outlines dynamically for an individual session or for the system.

See Also: Oracle8i Performance Guide and Reference ALTER OUTLINE for information on modifying an outline ALTER SESSION and ALTER SYSTEM for information on dynamically enabling and disabling stored outlines

Prerequisites

To create an outline, you must have the CREATE ANY OUTLINE system privilege.

Syntax

Keywords and Parameters

OR REPLACE

Specify OR REPLACE to replace an existing outline with a new outline of the same name.

outline

Specify the unique name to be assigned to the stored outline. If you do not specify outline, the system generates an outline name.

FOR CATEGORY category

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 DEFAULT category.

ON statement

Specify the SQL statement for which Oracle will create an outline when the statement is compiled. You can specify any one of the following statements:

• SELECT

• DELETE

• UPDATE

• INSERT ... SELECT

• CREATE TABLE ... AS SELECT

  Note: You can specify multiple outlines for a single statement, but each outline for the same statement must be in a different category.

Example

CREATE 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 ename, sal FROM emp;

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.