Re: Index on status field?

Index blocks are put to the freelist only when fully empty.

Index blocks have a "logical" sequence. Even same entries (0 in your case) have very specific locations in the "logical" sequence of index blocks. Remember i said the ordering was index key, rowid.

So even a 0 has a very specific spot to be placed, based on the rowid (i.e. logical location in table).

Fully empty blocks of the index are no longer part of it's logical structure, thus can be reused elsewhere in the index.

All other index blocks, have a very specific logical order. All index entries also have a very specific order, regardless of repeated values.

On Apr 8, 2005 11:55 AM, Martic Zoran <zoran_martic_at_yahoo.com> wrote:
> Christo,
>
> Thanks a lot.
>
> > A block can be reused only if all entries from it
> are removed, or a new entry's place is arround entries
> > in that block (based on your key and rowid)
> This is what I want to know more.
>
> My entry will be 0, the same so when it is going into
> the block that is not fully emptied (put back to the
> free list).
>
> If it is not by the free list then how?
>
> Regards,
> Zoran
>
> --- Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
> wrote:
>
> > Imagine an ordered list, ordered by YOUR_INDEX_KEY,
> > rowid.
> >
> > Your index will have:
> >
> > -- Block 1 --
> > 0, AAAb... 1
> > 0, AAAb... 2
> > 0, AAAb... 3
> > 0, AAAb... 101
> > 0, AAAb... 350
> > ...
> > -- Block 2 --
> > 0, AAAb... 551
> > 0, AAAb... 800
> > 0, AAAb... 950
> > 0, AAAb... 1001
> > 0, AAAb... 1002
> > ...
> >
> > As long as you dont leave unprocessed behind, as the
> > entries are
> > removed, blocks in the begining of the list will be
> > emptied, and thus
> > reused later on. A block can be reused only if all
> > entries from it are
> > removed, or a new entry's place is arround entries
> > in that block
> > (based on your key and rowid)
> >
> > The approach you have chosen is pretty much optimal.
> > The one thing i
> > would add is to maybe compress the index, but i am
> > not sure wehter you
> > will save much at all, given that you field is
> > already number(1).
> >
> > Does that answer your question ?
> >
>
> __________________________________
> Yahoo! Messenger
> Show us what our next emoticon should look like. Join the fun.
> http://www.advision.webevents.yahoo.com/emoticontest
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l