Re: Online Index Rebuild Tuning

Ah, yes, I was bitten by one of those persistent myths some weeks ago, involving this very subject.

Never stop learning...

Jraed

On Friday 06 December 2002 01:04, Connor McDonald wrote:
> A few nasties still exist with compression on indexes
> if they are going to be used as a unique or primary
> key - you'll have to come to my session at UKOUG if
> you want to see why :-)
>
> But also jumping back to Mark's point about rebuilding
> the index because it contains ever increasing values -
> this is not necessarily a driver toward rebuild the
> index. A simple example follows:
>
> SQL> create table t1 ( x number, y number);
>
> Table created.
>
> SQL> create index t1x on t1 (x);
>
> Index created.
>
> SQL> insert into t1
> 2 select rownum,rownum
> 3 from sys.source$
> 4 where rownum < 100000;
>
> 99999 rows created.
>
> SQL> analyze index t1x compute statistics;
>
> Index analyzed.
>
> SQL> select leaf_blocks from user_indexes
> 2 where index_name = 'T1X';
>
> LEAF_BLOCKS
> -----------
> 200
>
> -- remove the "lower" half of the rows
>
> SQL> delete from t1 where x < 50000;
>
> 49999 rows deleted.
>
> SQL> commit;
>
> Commit complete.
>
> rem
> rem and add to the top
> rem
>
> SQL> insert into t1
> 2 select rownum+100000,rownum+100000
> 3 from sys.source$
> 4 where rownum < 50000;
>
> 49999 rows created.
>
> SQL> analyze index t1x compute statistics;
>
> Index analyzed.
>
> SQL> select leaf_blocks from user_indexes
> 2 where index_name = 'T1X';
>
> LEAF_BLOCKS
> -----------
> 202
>
> -- and again
>
> SQL> delete from t1 where x < 100000;
>
> 50000 rows deleted.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> insert into t1
> 2 select rownum+150000,rownum+150000
> 3 from sys.source$
> 4 where rownum < 50000;
>
> 49999 rows created.
>
> SQL> analyze index t1x compute statistics;
>
> Index analyzed.
>
> SQL> select leaf_blocks from user_indexes
> 2 where index_name = 'T1X';
>
> LEAF_BLOCKS
> -----------
> 205
>
> Cheers
> Connor
>
> --- Mogens_Nørgaard <mln_at_miracleas.dk> wrote: >
> Compressing indexes has been an option since 8i, and
>
> > Jonathan Lewis has
> > done some interesting presentations on this (I
> > witnessed it at our
> > Database Forum in Middelfart this year - very
> > impresive).
> >
> > In short, it changes the way you should think of
> > concatenated indexes,
> > ie you should put the least selective column first,
> > then compress it.
> > That way you'll end up with very small indexes
> > compared to the old days
> > and ways.
> >
> > I don't see any drawbacks to this approach except
> > that you of course has
> > to unlearn what you have learned (Yoda?)...
> >
> > Other index things Jonathan adresses include: It is
> > actually better to
> > index small tables, even one-row tables.
> >
> > Mogens
> >
> > Rachel Carmichael wrote:
> >
> >
> >http://www.tusc.com/oracle/download/author.html#loneyk
> >
> > >--- John Kanagaraj <john.kanagaraj_at_hds.com> wrote:
> > >>Mark,
> > >>
> > >>>Also, I have heard about compressing indexes, but
> >
> > it is
> >
> > >>>something I have
> > >>>never used before. Can anyone shed some light on
> >
> > the topic?
> >
> > >>>Are there any
> > >>>drawbacks (ie: reduced IO but increased
> >
> > processing)?
> >
> > >>Kevin Loney presented a paper on this at IOUG 2002
> >
> > - should be in the
> >
> > >>archives at www.ioug.org.
> > >>
> > >>John Kanagaraj
> > >>Oracle Applications DBA
> > >>DB Soft Inc
> > >>Work : (408) 970 7002
> > >>
> > >>Listen to great, commercial-free christian music
> >
> > 24x7x365 at
> >
> > >>http://www.klove.com
> > >>
> > >>** The opinions and facts contained in this
> >
> > message are entirely mine
> >
> > >>and do not reflect those of my employer or
> >
> > customers **
> >
> > >>--
> > >>Please see the official ORACLE-L FAQ:
> >
> > http://www.orafaq.com
> >
> > >>--
> > >>Author: John Kanagaraj
> > >> INET: john.kanagaraj_at_hds.com
> > >>
> > >>Fat City Network Services -- 858-538-5051
> >
> > http://www.fatcity.com
> >
> > >>San Diego, California -- Mailing list and
> >
> > web hosting services
> >
> >>---------------------------------------------------------------------
> >>
> > >>To REMOVE yourself from this mailing list, send an
> >
> > E-Mail message
> >
> > >>to: ListGuru_at_fatcity.com (note EXACT spelling of
> >
> > 'ListGuru') and in
> >
> > >>the message BODY, include a line containing: UNSUB
> >
> > ORACLE-L
> >
> > >>(or the name of mailing list you want to be
> >
> > removed from). You may
> >
> > >>also send the HELP command for other information
> >
> > (like subscribing).
> >
> > >__________________________________________________
> > >Do you Yahoo!?
> > >Yahoo! Mail Plus - Powerful. Affordable. Sign up
> >
> > now.
> >
> > >http://mailplus.yahoo.com
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
> and...he will sit in a boat and drink beer all day"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).