BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter