| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Is it posible to create indexes on a snapshot ?
fumi wrote:
>
> 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.
Whilst possibly being overcautious, the oracle doco recommends indexes but NOT unique indexes on the underlying table since it doesn't guarantee to ensure uniqueness during a refresh ie they may insert a duplicate before removing the original etc
Cheers
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Oct 20 1999 - 07:11:14 CDT
 |
 |