Snapshot Indexes

[Girija]

When a snapshot is created, Oracle creates a table, two views, and an index in the schema of the snapshot. Oracle uses these objects
to maintain the snapshot's data.

Could anyone give me more details on the index created for a snapshot?In which data dictionary view are these details on snapshot indexes stored?When we rebuild indexes on the master table, does these snapshot indexes also get rebuilt, on refresh?.
Thanks in advance.

[Yogeeraj_1]

hi,

You did not mention the version of Oracle database that you are using. Anyway, a materialized view is a greatly enhanced snapshot with added features like query rewrite, on commit refresh.

Since the Materialized view is like a "table", the index is like an "index" on a table. Thus, should be found in the usual data dictionary view (USER_INDEXES). This also implies that on a refresh, the index gets refreshed also. Beware about the statistics which have to be gathered again!

regards
Yogeeraj

[Girija]

Thanks.That was a brief answer :).The db version we use is 9i, does that make a difference? any features enhanced regarding the same?

[Yogeeraj_1 · Accepted solution]

hi,

one thing that i can remember is:

8i
===
If a materialized view contains joins and aggregates, then it cannot be fast refreshed using a materialized view log. Therefore, for a fast refresh to be possible, only new data can be added to the detail tables and it must be loaded using the direct path method.

9i
===
Fast refresh for a materialized view containing joins and aggregates is possible
after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND.

A REFRESH ON COMMIT, materialized view will be refreshed automatically when a transaction that does DML to one of the materialized views commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the
commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

kind regards
Yogeeraj

[Girija]

Thanks for your reply it really helped.

[Girija]

Query Answered.