 |
|
Creating a Materialized View or
Snapshot
Oracle Tips by Burleson Consulting
|
The format for the CREATE MATERIALIZED VIEW
command is complex enough that I refer you once again to the SQL
manual for details. However, we will look at a couple of examples. In
perusing the manual, at one point it states that the first step in
creating a materialized view is to create a DIMENSION. However, in
investigating this claim, I found no way to tie a DIMENSION to a
MATERIALIZED VIEW; I also learned that DIMENSIONs are really only of
use in data warehousing, where rollup and aggregation are of
importance. I will touch on DIMENSION creation in a later section of
this chapter; for now, note that there are no direct ties between
MATERIALIZED VIEWs and DIMENSIONs. Perhaps the Oracle8i or Oracle9i
database engine itself ties them together, but one is not required for
the other to function, as far as I can determine. Let’s get on with
some simple examples.
In the first example, let’s do some summary
work against the Remote DBA_views so we can query about total space, total
extents, and so on without having to place the code into our reports.
This will actually be a materialized view based on two example tables,
TAB_EXAMPLE1 and TAB_EXAMPLE2, that are based on the underlying
Remote DBA_views, Remote DBA_TABLES, and Remote DBA_EXTENTS.
CREATE
MATERIALIZED VIEW table_extents
TABLESPACE tools
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
NOLOGGING
BUILD IMMEDIATE
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24
AS
SELECT
a.owner
owner,
a.table_name table_name,
a.tablespace_name tablespace_name,
count(b.owner) extents,
sum(b.bytes) bytes
FROM
tab_example1 a,
tab_example2 b
WHERE
a.owner <>'SYSTEM'
AND a.owner=b.owner
AND a.table_name=b.segment_name
AND b.segment_type=''TABLE''
GROUP BY
a.owner,a.table_name, a.tablespace_name;
What does a materialized view buy us as far as
performance? Let’s look at the explain plans for a query on a regular
view and then one on the materialized view we just created. First we
create an identical normal view:
CREATE VIEW
test_view
AS
SELECT
a.owner owner,
a.table_name table_name,
a.tablespace_name tablespace_name,
count(b.owner) extents,
sum(b.bytes) bytes
FROM
tab_example1 a, tab_example2 b
WHERE
a.owner <>''SYSTEM''
AND a.owner=b.owner
AND a.table_name=b.segment_name
AND b.segment_type=''TABLE''
GROUP BY a.owner,a.table_name, a.tablespace_name;
Now let’s set autotrace on, with the explain
option and see what happens when we select against each of these
objects.
SQL> set
autotrace on explain
SQL> select * from test_view
2* where extents>1
OWNER TABLE_NAME
TABLESPACE_NAME EXTENTS
BYTES
----- -------------------- -----------------
--------- --------
SYS ACCESS$
SYSTEM
8 536576
SYS ARGUMENT$
SYSTEM
10 1191936
SYS COM$
SYSTEM
7 368640
SYS CON$
SYSTEM
3 45056
SYS DEPENDENCY$
SYSTEM
7 352256
SYS ERROR$
SYSTEM
2 24576
SYS EXTENT_TO_OBJECT_TAB SYSTEM
3 45056
SYS EXT_TO_OBJ
SYSTEM
4 86016
SYS HIST_HEAD$
SYSTEM
3 45056
SYS IDL_CHAR$ SYSTEM
7 368640
SYS IDL_SB4$
SYSTEM
9 802816
SYS IDL_UB1$
SYSTEM
14 5861376
SYS IDL_UB2$
SYSTEM
13 3915776
SYS OBJ$
SYSTEM
7 352256
SYS OBJAUTH$
SYSTEM
3 45056
SYS PROCEDURE$
SYSTEM
2 24576
SYS SEQ$
SYSTEM
2 24576
SYS Source$
SYSTEM
18 29503488
SYS SYN$
SYSTEM
3 45056
SYS VIEW$
SYSTEM
10 1191936
20 rows
selected.
Execution
Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW OF 'TEST_VIEW'
2 1 FILTER
3 2 SORT
(GROUP BY)
4 3
MERGE JOIN
5 4
SORT (JOIN)
6 5
TABLE ACCESS (FULL) OF 'TAB_EXAMPLE2'
7 4
SORT (JOIN)
8 7
TABLE ACCESS (FULL) OF 'TAB_EXAMPLE1'
SQL> select * from table_extents
2* where extents>1
OWNER
TABLE_NAME
TABLESPACE_NAME EXTENTS
BYTES
----- -------------------- -----------------
--------- --------
SYS ACCESS$
SYSTEM
8 536576
SYS ARGUMENT$
SYSTEM
10 1191936
SYS COM$
SYSTEM
7 368640
SYS CON$
SYSTEM 3 45056
SYS DEPENDENCY$
SYSTEM
7 352256
SYS ERROR$
SYSTEM
2 24576
SYS EXTENT_TO_OBJECT_TAB SYSTEM
3 45056
SYS EXT_TO_OBJ SYSTEM
4 86016
SYS HIST_HEAD$
SYSTEM
3 45056
SYS IDL_CHAR$
SYSTEM
7 368640
SYS IDL_SB4$
SYSTEM
9 802816
SYS IDL_UB1$
SYSTEM
14 5861376
SYS IDL_UB2$
SYSTEM
13 3915776
SYS OBJ$
SYSTEM
7 352256
SYS OBJAUTH$
SYSTEM
3 45056
SYS PROCEDURE$
SYSTEM
2 24576
SYS SEQ$
SYSTEM
2 24576
SYS Source$
SYSTEM
18 29503488
SYS SYN$
SYSTEM 3
45056
SYS VIEW$
SYSTEM
10 1191936
20 rows
selected.
Execution
Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ''MVIEW_TEST''
As you can see, we get identical results; but
the second query against the materialized view only does a single
table scan against the materialized view table, not two table scans
against the underlying base tables. The results would be even more
advantageous for a remote snapshot since no network traffic would be
involved. Also notice that in the materialized view we are updating
once an hour. While a view will give an instantaneous result (after
the view itself is instantiated) the materialized view will only be as
current as its last refresh. The materialized view can be created such
that any commit against the base table forces a refresh against the
materialized view if the materialized view has no aggregations or
includes no joins.
Altering a Materialized View or Snapshot
As with snapshots, a materialized view can
have its physical attributes altered, its index parameters changed,
and its logging and cache parameters changed (look at the syntax for
the command at the document link for the SQL manual); in addition, a
materialized view can have the capability to allow query rewrite
enabled or disabled.
Dropping a Materialized View
The command to drop a materialized view or
snapshot is rather simple:
DROP
MATERIALIZED VIEW|SNAPSHOT [schema.]materialized_view_name|snapshot_name;
Refreshing Materialized Views
Normally, a materialized view will be
refreshed using the DBMS_JOB queues. This means that you must have at
least one job queue set up and operating; normally, I suggest at least
two queues or more be set up using the JOB_QUEUE_PROCESSES and
JOB_QUEUE_INTERVAL initialization parameters. (Note: These parameters
are synonymous with the SNAPSHOT_QUEUE_PROCESSES and SNAPSHOT_INTERVAL
parameters in prior releases.) A third parameter,
JOB_QUEUE_KEEP_CONNECTIONS, forces the database links to be opened for
remote snapshots, or for materialized views to be held open between
refreshes.
Materialized views can be refreshed using
COMPLETE, FAST, FORCE, ON DEMAND, or ON COMMIT, depending on the
complexity of the materialized view. A COMPLETE truncates the
materialized view table and reloads it from scratch. A FAST uses a
materialized view log only to update changed rows. If you intend to
use the FAST refresh method, you must create the materialized view log
first and then the materialized view. A FORCE will perform a FAST if
possible and a COMPLETE if required. ON DEMAND uses the DBMS_MVIEW or
DBMS_SNAP packages to complete a refresh, and ON COMMIT refreshes a
materialized view or snapshot whenever a commit is executed against
the base table (for a simple materialized view with no joins or
aggregations).
Oracle8i provided the DBMS_MVIEW package,
which handles refresh activity on materialized views on demand. This
package is covered in the script zip file at the Wiley Web site.
Snapshot Usage
Source 7.5 shows the use of the CREATE
SNAPSHOT command for a simple snapshot. It is followed directly by
Source 7.6, which shows use of the CREATE SNAPSHOT command with a
complex snapshot. The sizing considerations should mirror those for
the source table. If the source table is stable, a large initial
extent with smaller subsequent extents should be used. Since snapshots
will most likely be on slow-growth tables, set pctincrease to 0 in
most cases.
SOURCE 7.5 Example of the CREATE
SNAPSHOT command for a simple snapshot.
CREATE
SNAPSHOT new_drugs
PCTFREE 10 PCTUSED 70
TABLESPACE clinical_tests
STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 0)
REFRESH
START WITH ROUND(SYSDATE + 7) + 2/24
NEXT NEXT_DAY(TRUNC(SYSDATE, 'TUESDAY') + 2/24
AS SELECT * FROM appl_Remote DBA.test_drugs@kcgc
In the snapshot in Source 7.5, the entire
test_drugs table is used to create a snapshot from its location at a
remote database identified in the kcgc connect string into the
tablespace clinical_tests in the current database. It will first
be refreshed in seven days at
2:00 o’clock in the morning and subsequently at seven-day intervals on
every Tuesday thereafter at 2:00 o’clock in the morning. Since no
refresh mode is specified, if the table has a snapshot log, the fast
mode will be used since it is a simple snapshot. If no snapshot log is
available, then the complete mode will be used. If you specify the
FORCE option, it will always try to do a FAST refresh first.
SOURCE 7.6 Script to produce a complex
snapshot.
CREATE
SNAPSHOT trial_summary
PCTFREE 5 PCTUSED 60
TABLESPACE clinical_tests
STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 0)
REFRESH COMPLETE
START WITH ROUND(SYSDATE + 14) + 6/24
NEXT NEXT_DAY(TRUNC(SYSDATE, 'FRIDAY') + 19/24
AS
select td.drug_name, s.trial_number, dr.doctor_id,
s.comment_line,s.comment
from
appl_Remote DBA.test_drugs@kcgc td,
appl_Remote DBA.trial_doctors@kcgc dr,
appl_Remote DBA.trial_summaries@kcgc s
where
td.drug_id = s.drug_id and
s.trial_id = dr.trial_id and
s.doctor_id = dr.doctor_id;
The script in Source 7.6 produces a complex
snapshot called trial_summary with data from the test_drugs,
trial_doctors, and trial_summaries tables in the database specified in
the connect string kcgc. The snapshot is refreshed using the complete
mode since it is a complex query and is created in the clinical_tests
tablespace of the local database.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
