 |
|
Oracle Tips by Burleson |
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.
See Code Depot
www.oracle-script.com |
