Database Reference
In-Depth Information
Creating Stored Outlines
You can use two main methods to create stored outlines. Either you let the database automatically create them or you
do it manually. The first method is useful if you want to create a stored outline for each SQL statement executed by a
given session or even by the whole system. Nevertheless, as mentioned earlier, this is usually not desirable. Because of
this, you'll usually create them manually.
To activate the automatic creation, you have to set the
create_stored_outlines
initialization parameter either to
TRUE
or to another value specifying a
category.
The purpose of the category is to group together several stored outlines
for management purposes. The default category, which is used when the initialization parameter is set to
TRUE
, is
named
DEFAULT
. The initialization parameter is dynamic and can be changed at the session and system levels. To
disable automatic creation, you have to set the initialization parameter to
FALSE
.
To manually create a stored outline, you have to use the
CREATE OUTLINE
statement. The following SQL
statement, an excerpt of the
outline_from_text.sql
script, shows the creation of a stored outline named
outline_from_text
, associated with the
test
category and based on the query specified in the
ON
clause:
CREATE OR REPLACE OUTLINE outline_from_text
FOR CATEGORY test
ON SELECT * FROM t WHERE n = 1970
Once created, you can display information about stored outlines and their properties through the
user_outlines
and
user_outline_hints
views (for both, the
all
,
dba
, and, in a 12.1 multitenant environment,
cdb
views exist as
well). The
user_outlines
view displays all information except the hints. The following queries show the information
provided for the stored outline created by the previous SQL statement:
SQL> SELECT category, sql_text, signature
2 FROM user_outlines
3 WHERE name = 'OUTLINE_FROM_TEXT';
CATEGORY SQL_TEXT SIGNATURE
-------- ------------------------------ --------------------------------
TEST SELECT * FROM t WHERE n = 1970 73DC40455AF10A40D84EF59A2F8CBFFE
SQL> SELECT hint
2 FROM user_outline_hints
3 WHERE name = 'OUTLINE_FROM_TEXT';
HINT
-------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
