Re: Is it posible to create indexes on a snapshot ?

Guy Dallaire <gdallair_at_criq.qc.ca> wrote in message news:380c8032.70844806_at_news.risq.qc.ca...
> I was reading a post mentionning that one cannot create a UNIQUE index
> on a snapshot. But I was wondering if it is possible to create other
> types of indexes on a snapshot.

Of cause, a unique index can be created on a snapshot's underlying table, so do other types of indexes.
But it NOT recommended to create a non-deferrable unique constraints for a snapshot.

> If it is possible, on which object does the index need to be created.
> Isn't a snapshot a VIEW ? I don't think we can create an index on a
> view, can we ? ? ?
>
> Also, the view is based on some SNAP$... object. Should the indexes be
> created on this object then ?

The indexes should be created on the underlaying table. In Oracle8i, this is not a issue.
The snapshot and the underlaying table are of the same name.

> Finally, how does oracle maintain a snapshot, will I lose my index
> when the snapshot gets refreshed ? I mean, does it only truncate the
> table or does it drop + recreate it ?

It depends on the refresh method.
Nothing to worry about, you will not lose any thing. Received on Wed Oct 20 1999 - 11:16:35 CDT