Re: Online index build

Howard J. Rogers wrote:
>
> "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
> >
> >

I think at some version (8.1.7? 9?) the DESC became "real" rather than just ignored, and the implementation is effectively FBI (sort of the same way reverse indexes are).

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."