Summary of DBMS_OUTLN Subprograms

Table 41-1 DBMS_OUTLN Package Subprograms

Subprogram	Description
DROP_BY_CAT Procedure	Drops outlines that belong to a specified category.
DROP_COLLISION Procedure	Drops an outline with an ol$.hintcount value that does not match the number of hints for that outline in ol$hints.
DROP_EXTRAS Procedure	Cleans up after an import by dropping extra hint tuples not accounted for by hintcount.
DROP_UNREFD_HINTS Procedure	Drops hint tuples that have no corresponding outline in the OL$ table.
DROP_BY_CAT Procedure	Drops outlines that have never been applied in the compilation of a SQL statement.
UPDATE_BY_CAT Procedure	Changes the category of outlines in one category to a new category.
GENERATE_SIGNATURE Procedure	Generates a signature for the specified SQL text.

DROP_BY_CAT Procedure

This procedure drops outlines that belong to a specified category.

Syntax

DBMS_OUTLN.DROP_BY_CAT 
   cat VARCHAR2);

Parameters

Table 41-2 DROP_BY_CAT Procedure Parameters

Parameter	Description
cat	Category of outlines to drop.

Usage Notes

This procedure purges a category of outlines in a single call.

Example

This example drops all outlines in the DEFAULT category:

DBMS_OUTLN.DROP_BY_CAT('DEFAULT'); 

DROP_COLLISION Procedure

This procedure drops an outline with an ol$.hintcount value that does not match the number of hints for that outline in ol$hints.

Syntax

DBMS_OUTLN.DROP_COLLISION;

Usage Notes

A concurrency problem can occur if an outline is created or altered at the same time it is being imported. Because the outline must be imported according to its original design, if the concurrent operation changes the outline in mid-import, the outline will be dropped as unreliable based on the inconsistent metadata.

DROP_EXTRAS Procedure

This procedure cleans up after an import by dropping extra hint tuples not accounted for by hintcount.

Syntax

DBMS_OUTLN.DROP_EXTRAS;

Usage Notes

The OL$-tuple of an outline will be rejected if an outline already exists in the target database, either with the same name or the same signature. Hint tuples will also be rejected, up to the number of hints in the already existing outline. Therefore, if the rejected outline has more hint tuples than the existing one, spurious tuples will be inserted into the OL$HINTS table. This procedure, executed automatically as a post table action, will remove the wrongly inserted hint tuples.

DROP_UNREFD_HINTS Procedure

This procedure drops hint tuples that have no corresponding outline in the OLSable.

Syntax

DBMS_OUTLN.DROP_UNREFD_HINTS;

Usage Notes

This procedure will execute automatically as a post table action to remove hints with no corresponding entry in the OL$ table, a condition that can arise if an outline is dropped and imported concurrently.

DROP_UNUSED Procedure

This procedure drops outlines that have never been applied in the compilation of a SQL statement.

Syntax

DBMS_OUTLN.DROP_UNUSED;

Usage Notes

You can use DROP_UNUSED for outlines generated by an application for one-time use only, created as a result of dynamic SQL statements. These outlines are never used and take up valuable disk space.

UPDATE_BY_CAT Procedure

This procedure changes the category of all outlines in one category to a new category. If the SQL text in an outline already has an outline in the target category, it is not merged into the new category.

Syntax

DBMS.OUTLN.UPDATE_BY_CAT ( 
   oldcat VARCHAR2 DEFAULT 'DEFAULT', 
   newcat VARCHAR2 DEFAULT 'DEFAULT');

Parameters

Table 41-3 UPDATE_BY_CAT Procedure Parameters

Parameter	Description
oldcat	Current category to be changed.
newcat	Target category to change outline to.

Usage Notes

Once satisfied with a set of outlines, you can move outlines from an experimental category to a production category. Likewise, you may want to merge a set of outlines from one category into another pre-existing category.

Example

This example changes all outlines in the DEFAULT category to the CAT1 category:

DBMS_OUTLN.UPDATE_BY_CAT('DEFAULT', 'CAT1'); 

GENERATE_SIGNATURE Procedure

This procedure generates a signature for the specified SQL text.

Syntax

DBMS_OUTLN.GENERATE_SIGNATURE ( 
   sqltxt      IN   VARCHAR2, 
   signature   OUT  RAW);

Parameters

Table 41-4 GENERATE_SIGNATURE Procedure Parameters

Parameter	Description
sqltxt	The specified SQL.
signature	The signature to be generated.