Re: Online index build

"Chuck" <chuckh_at_softhome.net> wrote in message news:anv67h$htghj$1_at_ID-85580.news.dfncis.de...
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:iwEo9.48804$g9.140500_at_newsfeeds.bigpond.com...
> >
> > "Chuck" <chuckh_at_softhome.net> wrote in message
> > news:anv3h6$hn1pd$1_at_ID-85580.news.dfncis.de...
> > > Platform: Oracle 8.1.7.2 on AIX
> > >
> > > I am getting an "ora-08108 may not build or rebuild this type of index
> > > online" on the following statement. Can anyone tell me why? The only
> > > restrictions on online index builds listed in the documentation or on
> > > Metalink have to do with IOTs and parallel DML.
> >
> > Check which version of the documentation you're using. Online rebuilds
> were
> > invented in 8i and onl apply to bog-standard b*tree indexes. Not to
> reverse
> > keys, function-based or key-compressed indexes, and not to IOT secondary
> > indexes either. Most of those restrictions were lifted in 9i, so I'm
> > wondering if you are reading 9i documentation?
> >
> > Regards
> > HJR
> >
>
> I am reading the 8i documentation because the instance in question is
> running verison 8.1.7.

OK, OK... keep your hair on! I was only asking. The version of the database you're running doesn't preclude the possibility of you picking up the wrong version of the documentation. However, the 8i SQL Reference is indeed mysteriously silent on a whole raft of restrictions which were, in fact, in place.

>The index is a normal b-tree index on a normal table.
> OTS is telling me that "DESC is function based" which is hogwash. Function
> based indexes came out in verion 8.

They actually came out in 8i (i.e, 8.1.5). But your general point is sound: DESC doesn't count as a 'function' in the sense that function-based indexes would use the term. At least, they shouldn't.

However, here's what happens in 9i:

SQL> create index blah on emp (ename, sal DESC); Index created.

SQL> alter index blah rebuild online;
Index altered.

So, it is an issue which goes away in 9i, and therefore presumably *does* count as a function as far as an online rebuild in 8i is concerned.

Sorry: it looks like its an inherent limitation in 8i, and has been fixed in 9i (along with all those other limitations with online rebuilds I mentioned earlier).

Regards
HJR
>Descending columns on indexes have been
> available since at least version 7. And people wonder why I ask questions
> here rather than of OTS. I've been dealing with them for 8 years and can
> count on one hand the number of tech. support people I've spoken too there
> that were worth the price I pay for support!
> --
> Chuck
>
>
Received on Tue Oct 08 2002 - 13:33:57 CDT