Skip Headers
Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CREATE OUTLINE

Purpose

Note:

Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

If you have existing stored outlines, please consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the DBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.

See Also: Oracle Database Performance Tuning Guide for more information about SQL plan management and Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SPM package

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:

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, then you must also have the SELECT_CATALOG_ROLE role.

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

See Also:

Syntax

create_outline::=

Description of create_outline.gif follows
Description of the illustration create_outline.gif

Note:

None of the clauses after outline 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

OR REPLACE

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

PUBLIC | PRIVATE

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 the DBMS_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.

FROM source_outline Clause

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.

FOR CATEGORY Clause

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.

ON Clause

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:

Note:

In subsequent statements, you can specify additional outlines for the same SQL statement, but each outline for the same statement must specify a different category in the CATEGORY clause.

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;