 |
|
Oracle Data Warehouse
Creating a OUTLINE Object
Oracle Data Warehouse Tips by Burleson Consulting |
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 catagories 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.
Altering a OUTLINE
Outlines are altered using the ALTER OUTLINE
or CREATE OR REPLACE form of the CREATE command. The format of the
command is identical whether it is used for initial creation or
replacement of an existing outline. For example, what if we want to
add SUM(b.blocks) to the previous example?
CREATE OR
REPLACE OUTLINE get_tables
ON
SELECT
a.owner,
a.table_name,
a.tablespace_name,
SUM(b.bytes),
COUNT(b.table_name) extents,
SUM(b.blocks)
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;
The above example has the effect of altering
the stored outline get_tables to include any changes brought about
by inclusion of the SUM(b.blocks) in the SELECT list. But what if we
want to rename the outline or change a category name? The ALTER
OUTLINE command has the format:
ALTER
OUTLINE outline_name
[REBUILD]
[RENAME TO new_outline_name]
[CHANGE CATEGORY TO new_category_name]
The ALTER OUTLINE command allows us to
rebuild the outline for an existing outline_name as well as rename
the outline or change its category. The benefit of using the ALTER
OUTLINE command is that we do not have to respecify the complete SQL
statement as we would have to using the CREATE OR REPLACE command.
Dropping an OUTLINE
Outlines are dropped using the DROP OUTLINE
command the syntax for this command is:
DROP
OUTLINE outline_name;
Use of the OUTLN_PKG To Manage SQL Stored
Outlines
The OUTLN_PKG package provides for the
management of stored outlines. A stored outline is an execution plan
for a specific SQL statement. A stored outline permits the optimizer
to stabilize a SQL statements execution plan giving repeatable
execution plans even when data and statistics change.
The DBA should take care to who they grant
execute on the OUTLN_PKG, by default it is not granted to the public
user group nor is a public synonym created.
The following sections show the packages in
the OUTLN_PKG.
DROP_UNUSED
The drop_unused procedure is used to drop
outlines that have not been used in the compilation of SQL
statements. The drop_unused procedure has no arguments.
SQL>
EXECUTE OUTLN_PKG.DROP_UNUSED;
PL/SQL
procedure successfully executed.
To determine if a SQL statement OUTLINE is
unused, perform a select against the DBA_OUTLINES view:
SQL> desc
dba_outlines;
Name
Null? Type
------------------------------- -------- ----
NAME
VARCHAR2(30)
OWNER
VARCHAR2(30)
CATEGORY
VARCHAR2(30)
USED
VARCHAR2(9)
TIMESTAMP DATE
VERSION
VARCHAR2(64)
SQL_TEXT
LONG
SQL> set
long 1000
SQL> select * from dba_outlines where used='UNUSED';
NAME
OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT
------------ ------ -------- ------ --------- ----------
----------------------
TEST_OUTLINE SYSTEM TEST UNUSED 08-MAY-99
8.1.3.0.0 select a.table_name,
b.tablespace_name,
c.file_name
from
dba_tables a,
dba_tablespaces b,
dba_data_files c
where
a.tablespace_name =
b.tablespace_name
and b.tablespace_name
= c.tablespace_name
and c.file_id =
(select
min(d.file_id) from
dba_data_files
d
where
c.tablespace_name =
d.tablespace_name)
1 row
selected.
SQL>
execute sys.outln_pkg.drop_unused;
PL/SQL
procedure successfully completed.
SQL>
select * from dba_outlines where used='UNUSED';
no rows
selected
Remember, the procedure drops all unused
outlines so use it carefully.
DROP_BY_CAT
The drop_by_cat procedure drops all outlines
that belong to a specific category. The procedure drop_by_cat has
one input variable, cat, a VARCHAR 2 that corresponds to the name of
the category you want to drop.
SQL>
create outline test_outline for category test on
2 select a.table_name, b.tablespace_name, c.file_name from
3 dba_tables a, dba_tablespaces b, dba_data_files c
4 where
5 a.tablespace_name=b.tablespace_name
6 and b.tablespace_name=c.tablespace_name
7 and c.file_id = (select min(d.file_id) from dba_data_files d
8 where c.tablespace_name=d.tablespace_name)
9 ;
Operation
180 succeeded.
SQL>
select * from dba_outlines where category='TEST';
NAME
OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT
------------ ------ -------- ------ --------- ----------
-------------------------
TEST_OUTLINE SYSTEM TEST UNUSED 08-MAY-99
8.1.3.0.0 select a.table_name, b.ta
blespace_name, c.file_nam
e from
dba_tables a, dba_tablesp
aces b, dba_data_files c
where
a.tablespace_name=b.table
space_name
and b.tablespace_name=c.t
ablespace_name
and c.file_id = (select m
in(d.file_id) from dba_da
ta_files d
where c.tablespace_name=d
.tablespace_name)
1 row
selected.
SQL>
execute sys.outln_pkg.drop_by_cat('TEST');
PL/SQL
procedure successfully completed.
SQL>
select * from dba_outlines where category='TEST';
no rows
selected
UPDATE_BY_CAT
The update_by_cat procedure changes all of
the outlines in one category to a new category. If the SQL text in
an outline already has an outline in the target category, then it is
not merged into the new category. The procedure has two input
variables, oldcat VARCHAR2 and newcat VARCHAR2 where oldcat
corresponds to the category to be merged and newcat is the new
category that oldcat is to be merged with.
SQL>
create outline test_outline for category test on
2 select a.table_name, b.tablespace_name, c.file_name from
3 dba_tables a, dba_tablespaces b, dba_data_files c
4 where
5 a.tablespace_name=b.tablespace_name
6 and b.tablespace_name=c.tablespace_name
7 and c.file_id = (select min(d.file_id) from dba_data_files d
8 where c.tablespace_name=d.tablespace_name)
9 ;
Operation
180 succeeded.
SQL>
create outline test_outline2 for category test on
2 select * from dba_data_files;
Operation
180 succeeded.
SQL>
create outline prod_outline1 for category prod on
2 select owner,table_name from dba_tables;
Operation
180 succeeded.
SQL>
create outline prod_outline2 for category prod on
2 select * from dba_data_files;
Operation
180 succeeded.
SQL>
select name,category from dba_outlines order by category
NAME
CATEGORY
--------------- --------
PROD_OUTLINE1 PROD
PROD_OUTLINE2 PROD
TEST_OUTLINE2 TEST
TEST_OUTLINE TEST
4 rows
selected.
SQL>
execute sys.outln_pkg.update_by_cat('TEST','PROD');
PL/SQL
procedure successfully completed.
SQL>
select name,category from dba_outlines order by category;
NAME
CATEGORY
--------------- --------
TEST_OUTLINE PROD
PROD_OUTLINE1 PROD
PROD_OUTLINE2 PROD
TEST_OUTLINE2 TEST
4 rows
selected.
As a result of the update_by_cat procedure
call we moved the TEST_OUTLINE outline into the PROD category, but
the TEST_OUTLINE2, since it is a duplicate of PROD_OUTLINE2,
was not merged.
Oracle8i Materialized Views, Summaries
and Data Warehousing
The concept of snapshots, not particularily
useful in data warehousing (unless you snapshot to a datamart) has
been expanded in Oracle8i to include materialized views.
Materialized views are similar to snapshots except that they can
reside in the same database as their master table(s). Another
powerful feature of materialized views is that they can be the
subject of DIMENSIONS, a new concept in Oracle8i that explains to
the optimizer how a hierarchical or parent-child relationship in a
materialized view is constructed, thus allowing query re-write.
Query re-write is when the optimizer recognizes that a query on a
materialized views base table(s) can be re-written to use the
materialized view to operate more efficiently.
As with snapshots, a materialized view can
have a materialized view log to speed refresh operations. Since the
materialized view log must exist first before a materialized view
will use it, let's look at materialized view logs first.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
