Oracle-L: Re: Checking the rebuildability of an index

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 07 Aug 2002 14:16:39 -0800
Message-ID: <F001.004AE300.20020807141639@fatcity.com>

Here is some examples showing re-use of index space with different but "appropriate" values

Example 1: Index space being re-used

SQL> create table test ( p number );

Table created.

SQL> insert into test
  2 select rownum*10
  3 from sys.source$
  4 where rownum < 50000;

49999 rows created.

SQL> create index test_ix on test ( p );

Index created.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

> ====
> So we've got rows with multiples of 10 as values
> ====

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

55 49999 0

> ====
> And the index is ~ 55 blks
> ====

SQL> delete from test
2 where mod(trunc(p/10),5) = 0;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

> ====
> So I've now removed 10k rows, the ones which are
multiples of 50
> ====

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

55 49999 9999

SQL> insert into test
  2 select rownum*50+5
  3 from sys.source$
  4 where rownum < 10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

> ====
> And I've re-inserted rows which are slightly more
than multiples of 50. They are NEW values but they fit nicely into the space that was deleted
> ====

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

55 49999 0

And the index has not grown at all...

So far so good, but what if in the example above, all I do is create the index *BEFORE* I load the data

Example 2: Problems with space

SQL> drop table test;

Table dropped.

SQL> create table test ( p number );

Table created.

SQL> create index test_ix on test ( p );

Index created.

SQL> insert into test
  2 select rownum*10
  3 from sys.source$
  4 where rownum < 50000;

49999 rows created.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

50 49999 0

SQL> delete from test
2 where mod(trunc(p/10),5) = 0;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

50 49999 9999

SQL> insert into test
  2 select rownum*50+5
  3 from sys.source$
  4 where rownum < 10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

99 49999 0

OH NO!!! What has gone wrong? Well, indexes only take notice of pctfree when being built, so our pre-created index will be about 99%+ full. And the values (with the '5' suffix) we are trying to re-insert after deletion have an additional significant digit - and they won't fit! So bingo - more blocks! To prove that its not some problem inherent with the index, we can reverse the example, so that the rows start with a '5' suffix (and hence have more significant digits), delete some, and then insert rows that are multiples of 10

Example 3: Space sorts itself out again

SQL> drop table test;

Table dropped.

SQL> create table test ( p number );

Table created.

SQL> create index test_ix on test ( p );

Index created.

SQL> insert into test
  2 select rownum*10+5
  3 from sys.source$
  4 where rownum < 50000;

49999 rows created.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

50 49999 0

SQL> delete from test
2 where mod(trunc((p-5)/10),5) = 0;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

50 49999 9999

SQL> insert into test
  2 select rownum*50
  3 from sys.source$
  4 where rownum < 10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> analyze index test_ix validate structure;

Index analyzed.

SQL> select /*+ FIRST_ROWS */ * from test 2 where rownum < 20
3 and p > 0;

19 rows selected.

SQL> select lf_blks, lf_rows, del_lf_rows 2 from index_stats;

LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------

50 49999 0

Bottom Line: I stand by my original premise - statistics alone do not determine an indexes candicacy for rebuild. Some additional knowledge, eg "I know the rows I've deleted will never be replaced" etc is needed.

hth
connor

Jared Still <jkstill_at_cybcon.com> wrote: >
> After pondering this a bit while trying to find info
> in the docs, I
> recalled a little more on this.
>
> Conner and Mike ( and Tom ) are correct in that
> slots can be
> reused in an index block, provided that block is
> still on the
> free list.
>
> Once an index block is full ( only PCTFREE space
> left ), it is
> taken off of the free list.
>
> Subsequent deletes affecting that block do not put
> it back on
> the freelist, unless the delete(s) completely empty
> the block.
>
> Makes me wonder why indexes work this way, and don't
> use
> PCTUSED like table blocks. Maybe in some testing
> long ago,
> this caused index blocks to play ping-pong with the
> index
> freelists for some reason. I wonder if anyone at
> Oracle has
> revisited this in recent years.
>
> Back to the docs to try and back this up.
>
> Jared ( who should think longer before he types )
>
>
> On Wednesday 07 August 2002 02:29, Connor McDonald
> wrote:
> > Tom is correct...I like using the telephone book
> as an
> > analogy
> >
> > If you erase some entries on a page for a some
> people
> > called "Smith", then you can reuse those slots for
> any
> > surname that still keeps that page (and hence the
> > telephone book) in the correct order. Obviously a
> > "Jones" could not go in there (since it belongs
> with
> > the pages with "J", but any people with a last
> name
> > that is "near" that of Smith will be able to. It
> will
> > not matter if the page in question still had other
> > entries on it.
> >
> > hth
> > connor
> >
> > --- Jared.Still_at_radisys.com wrote: > Hmmm.
> >
> > > Though Tom Kyte is usually right on the money,
> the
> > > following statement
> > > from the article is questionable:
> > >
> > > ==============================
> > > But, the index on ename -- that's a little more
> > > chaotic. Say we have
> > > employees:
> > >
> > > Bill
> > > Bob
> > > Mary
> > >
> > > Say we fire Bob, now we have:
> > >
> > > Bill
> > > X-Bob (deleted entry)
> > > Mary
> > > Should we rebuild an index like that? Probably
> NOT,
> > > the reason -- we are
> > > going
> > > to hire Hillary:
> > >
> > > Bill
> > > Hillary
> > > Mary
> > >
> > > Hillary will reuse Bobs slot. There is no
> reason to
> > > get rid of that slot
> > > --
> > > only to have to rebuild it again later. If the
> data
> > > you are indexing has
> > > a good
> > > probability of reusing a slot like that --
> > > rebuilding can actually slow
> > > you down
> > > over time (it takes time to split a block --
> with
> > > empty entries -- the
> > > chances
> > > we need to split are reduced. If you rebuild --
> > > they'll go up -- you
> > > unsplit
> > > everything, got rid of the space. Now we have
> to
> > > re-split again -- every
> > > time
> > > you rebuild). I find most indexes hit a steady
> > > state -- if you rebuild
> > > them,
> > > they'll go back into that state over time. So
> > > rebuilding is somewhat self
> > >
> > > defeating in that case.
> > >
> > > ==============================
> > >
> > > Hillary may reuse Bob's slot in a table data
> block,
> > > if the deletion of Bob
> > > puts
> > > the block back on the freelist.
> > >
> > > Indexes do not use the same concept of a
> freelist,
> > > which is why they do
> > > not
> > > have a PCTUSED parameter. Index blocks are not
> > > reused until the entire
> > > block is empty. The only time that Hillary's
> record
> > > could reuse Bob's
> > > slot
> > > in the ENAME index is if Bob's record was the
> only
> > > index entry in that
> > > block
> > > of the ENAME index.
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
> > > Sent by: root_at_fatcity.com
> > > 08/06/2002 12:48 PM
> > > Please respond to ORACLE-L
> > >
> > >
> > > To: Multiple recipients of list
> ORACLE-L
> > > <ORACLE-L_at_fatcity.com>
> > > cc:
> > > Subject: RE: Checking the
> > > rebuildability of an index
> > >
> > >
> > > Interesting. I just checked AskTom and sure
> > > enough...
> >
> >
> http://asktom.oracle.com/pls/ask/f?p=4950:8:1134696::NO::F4950_P8_DISPLAYID
> >,
> >
> >
> F4950_P8_CRITERIA:2290062993260,%7Bindex%7D%20and%20%7Brebuild%7D
> >
> > > (paste the mangled URL parts together)
> > >
> > > It's not exactly "never", but an "it depends".
> I
> > > just ran into an "it
> > > depends" situation today. How timely!
> > >
> > > I think I'll just gather stats on leaf block
> density
> > > and deleted leaf rows
> > > for now, but I would think that the height of
> the
> > > index would have a
> > > direct
> > > effect on the performance of a query using that
> > > index.
> > >
> > > Thanks!
> > >
> > > Rich Jesse
> System/Database
> > > Administrator
> > > Rich.Jesse_at_qtiworld.com Quad/Tech
> > > International, Sussex, WI
> > > USA
> > >
> > > > -----Original Message-----
> > > > From: Post, Ethan [mailto:Ethan.Post_at_ps.net]
> > > > Sent: Tuesday, August 06, 2002 1:38 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: RE: Checking the rebuildability of an
> > >
> > > index
> > >
> > > > If memory serves correct (and it might not)
> both
> > >
> > > Tom Kyte and
> > >
> > > > Jonathan Lewis
> > > > have stated that in most circumstances
> rebuilding
> > >
> > > indexes is
> > >
> > > > useless. I
> > > > think Tom may have said he could count the #
> of
> > >
> > > times he had
> > >
> > > > to rebuild an
> > > > index on one hand (something like that).
> Thus, I
> > >
> > > have
> > >
> > > > stopped worrying
> > > > about this one so much and will only entertain
> > >
> > > rebuilds if I
> > >
> > > > start to see
> > > > performance issues. Of course if you have the
> > >
> > > time and need
> > >
> > > > to look busy
> > > > keep rebuilding those indexes :)
> > > >
> > > > By the way the reason it is useless is not
> that
> > >
> > > there is
> > >
> > > > never a performance
> > > > gain but that most indexes that need to be
> rebuild
> > >
> > > quickly
> > >
> > > > degrade again and
> > > > the period that one experiences a performance
> gain
> > >
> > > is
> > >
> > > > minimal. Operating
> > > > off a poor memory here so hopefully I have not
> > >
> > > misrepresented anyone.
> > >
> > > > Ethan Post
> > > > perotdba (AIM), epost1 (Yahoo)
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Jesse, Rich
> > > INET: Rich.Jesse_at_qtiworld.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> >
> >
>

> >
> > > 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).
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author:
> > > INET: Jared.Still_at_radisys.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> >
> >
>

> >
> > > 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).
> >
> > =====
> > Connor McDonald
> > http://www.oracledba.co.uk
> > http://www.oaktable.net
> >
> > "Remember amateurs built the ark - Professionals
> built the Titanic"
> >
> > __________________________________________________
> > 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 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

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

Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"Remember amateurs built the ark - Professionals built the Titanic"

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  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Wed Aug 07 2002 - 17:16:39 CDT