Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why is PCTUSED not used for index?

Re: Why is PCTUSED not used for index?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 14 Aug 2001 10:23:12 -0700
Message-ID: <9lbmq001tn6@drn.newsguy.com>

In article <q89intguueab55nq37kjasvu6ogihtjf5k_at_4ax.com>, Dino says...
>
>Dear all,
>
>Quoting a book,
>"...Finally, pctused is not used in index definitions. Since all items
>in an index must be in the right order for the index to work, Oracle
>must put an index entry into a block, no matter what. Thus, pctused is
>not used."
>
>In my visulization of a block, if some data row or index entry is
>removed from a block, the upper part of data or index in the block
>moves down to fill the gap, thus moves closer to the pctused position.
>I don't why an index block behaves differently than a data block.
>Anyone explains this? Thanks in advance.
>
>Dino
>

An index is a data structure. When we insert a value into an index -- where it goes it dicated by its value. We cannot just goto a freelist, find a block with free space and stick it there like we could with a heap table -- we MUST stick it on the block where it belongs. Hence, we find that block and if sufficient space is there -- we stick it on that block, else we split that block and put it on the correct block after the split.

Blocks from an index only get onto the freelist when they are 100% empty -- else they are left where they are in the data structure that is the index. They are candidates for insertion even when they are NOT on the freelist (unlike tables, there a block must be on the freelist or above the high water mark to be a candidate for insertion).

It is different then a heap organized table where the location of the data in the heap is not really relevant. In a heap table, we just want to find free space and stick it there. In a heap therefore -- when a block has sufficient free space for new data -- we put it on a free list. The block doesn't have to be completely emptied.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Tue Aug 14 2001 - 12:23:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US