Database Reference
In-Depth Information
Moving Stored Outlines
To move stored outlines, no particular feature is provided. Basically, you have to copy them yourself from one data
dictionary to the other. This is easy because the data about the stored outlines is stored in three tables in the
outln
schema:
ol$
,
ol$hints
, and
ol$nodes
. You could use the following commands to export and import all available
stored outlines:
exp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) file=outln.dmp
imp full=y ignore=y file=outln.dmp
To move a single stored outline (named
outline_from_text
in this case), you can add the following parameter to
the export command:
query="WHERE ol_name='OUTLINE_FROM_TEXT'"
To move all stored outlines belonging to a category (named
test
, in this case), you can add the following
parameter to the export command:
query="WHERE category='TEST'"
Be careful, because you may have to add some escape characters to successfully pass all parameters,
depending on your operating system and shell. For example, on my Linux server, with bash I had to execute the
following command:
exp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) file=outln.dmp \
query=\"WHERE ol_name=\'OUTLINE_FROM_TEXT\'\"
Editing Stored Outlines
With stored outlines, it's possible to lock up execution plans. However, this is useful only if the query optimizer is able
to generate an efficient execution plan that can later be captured and frozen by a stored outline. If that's not the case,
the first thing you should investigate is the possibility of modifying the execution environment, the access structures,
or the object statistics just for the creation of the stored outline storing an efficient execution plan. For instance, if
the execution plan for a given SQL statement uses an index scan that you want to avoid, you could drop (or make
invisible) the index on a test system, generate a stored outline there, and then move the stored outline in production.
When you find no way to force the query optimizer to automatically generate an efficient execution plan, the
last resort is to manually edit the stored outline. Simply put, you have to modify the hints associated with the stored
outline. However, in practice, you can't simply run a few SQL statements against the
public stored outlines
(which
are the kind discussed so far) stored in the data dictionary tables. Instead, you have to carry out the editing as
summarized in Figure
11-3
. This process is based on the modification of
private stored outlines.
These are like public
stored outlines, but instead of being stored in the data dictionary, they're stored in
working tables
. The aim of using
these working tables is to avoid modifying the data dictionary tables directly. Therefore, to edit a stored outline, you
have to create, modify, and test a private stored outline. Then, when the private stored outline is working correctly,
you publish it as a public stored outline. The
dbms_outln_edit
package and a few extensions to the
CREATE OUTLINE
statement are available for editing stored outlines.
