Oracle-L: Re: Online Index Rebuild Tuning

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 06 Dec 2002 01:04:36 -0800
Message-ID: <F001.005141DA.20021206010436@fatcity.com>

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: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

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).

Received on Fri Dec 06 2002 - 03:04:36 CST