cancel
Showing results for 
Search instead for 
Did you mean: 

Snapshot Indexes

SOLVED
Go to solution
Girija
Occasional Contributor

Snapshot Indexes

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.
5 REPLIES
Yogeeraj_1
Honored Contributor

Re: Snapshot Indexes

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Girija
Occasional Contributor

Re: Snapshot Indexes

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
Honored Contributor
Solution

Re: Snapshot Indexes

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Girija
Occasional Contributor

Re: Snapshot Indexes

Thanks for your reply it really helped.
Girija
Occasional Contributor

Re: Snapshot Indexes

Query Answered.