|
 |
|
Oracle Tips by Burleson |
Creation of a OUTLINE object
Outlines are created using the CREATE OUTLINE command, the syntax
for this command is:
CREATE [OR REPLACE] OUTLINE outline_name
[FOR CATEGORY category_name]
ON statement;
Where:
-
Outline_name -- is a unique name for the outline
-
[FOR CATEGORY category_name] – This optional clause allows more
than one outline to be associated with a single query by
specifying multiple categories each named uniquely.
-
ON statement – This specifies the statement for which the
outline is prepared.
An example would be:
CREATE OR REPLACE OUTLINE get_tables
ON
SELECT
a.owner,
a.table_name,
a.tablespace_name,
SUM(b.bytes),
COUNT(b.table_name) extents
FROM
dba_tables a,
dba_extents b
WHERE
a.owner=b.owner
AND a.table_name=b.table_name
GROUP BY
a.owner, a.table_name, a.tablespace_name;
Assuming the above select is a part of a stored PL/SQL procedure or
perhaps part of a view, the stored outline will now be used each
time an exactly matching SQL statement is issued.
|
