Database Reference
In-Depth Information
First, you have to create a private stored outline. For that, you have two possibilities. The first is to create a private
stored outline from scratch with a SQL statement like the following. The
PRIVATE
keyword specifies the kind of stored
outline to be created:
SQL> CREATE OR REPLACE
PRIVATE
OUTLINE p_outline_editing
2 ON SELECT * FROM t WHERE n = 1970;
The second possibility is to copy a public stored outline already present in the data dictionary by means of a SQL
statement like the following. The
PRIVATE
and
PUBLIC
keywords specify the kind of stored outline to be created and
copied respectively:
SQL> CREATE
PRIVATE
OUTLINE p_outline_editing FROM
PUBLIC
outline_editing;
Both methods create a private stored outline in the working tables. Here's the list of hints associated with that
stored outline:
SQL> SELECT hint_text
2 FROM ol$hints
3 WHERE ol_name = 'P_OUTLINE_EDITING';
HINT_TEXT
--------------------------------------------
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."N"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
Once the private stored outline has been created, you can modify it with regular DML statements. However,
changing everything as needed isn't an easy task. A much simpler approach is to create an additional private stored
outline reproducing the expected execution plan and then swap the content of the two stored outlines. To create the
additional stored outline, you issue a SQL statement like the following. Notice the hint used to instruct the query using
a full table scan:
SQL> CREATE OR REPLACE PRIVATE OUTLINE p_outline_editing_hinted
2 ON SELECT /*+
full(t)
*/ * FROM t WHERE n = 1970;
Then, you swap the content by issuing SQL statements like these:
SQL> UPDATE ol$
2 SET hintcount = (SELECT hintcount
3 FROM ol$
4 WHERE ol_name = 'P_OUTLINE_EDITING_HINTED')
5 WHERE ol_name = 'P_OUTLINE_EDITING';
SQL> DELETE ol$hints
2 WHERE ol_name = 'P_OUTLINE_EDITING';
