Re: Index Insider

Changes to key values in a B-Tree index in general result in the original index entry being marked for deletion, and a brand new entry is created in an appropriate place. In other words, an update to a table results in a delete and an insert on the underlying index. This is a relatively trivial operation, which is why B-Trees are said to be 'relatively inexpensive' for updates and thus reasonable for use in an OLTP environment.

The new insert side of the equation, however, poses some tricky issues of its own. If you haven't set PCTFREE high enough for the Index segment, you may find that the new entry cannot fit into any existing leaf node. Accordingly, a block split will have to be performed -and the acquisition of an entirely fresh block *could* mean that an entirely new extent for the Index was acquired. It will also mean that new branch nodes may be required, and hence the height of your Index may increase.

Net result: hardly any real re-organisation on the Index is performed as part of your table entry, but the Index itself gets gradually more and more 'fluffy' -half-filled blocks, blocks loaded with deleted leaf nodes, height forever rising like an over-eager soufflé... hence the SQL command : Alter Index blah rebuild -which reconstructs the thing from scratch, flattening the structure and re-compacting all the data into the minimum required number of leaf nodes. An expensive operation, however, since it locks the entire table from DML whilst it's taking place (although there is an 'online' option in 8i which allegedly gets round that problem -I've never used it so I have no idea how well it works).

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


"Heiko Welter" <Heiko.Welter_at_t-mobil.de> wrote in message
news:3A0A63C6.8A87C253_at_t-mobil.de...


> Hi!

>

> I would like to know, how indices (B-Tree) ares organized in Oracle (V7

> or V8). Especially how

> they are "reorganized". Lets assume the following situation:

>

> We have a combined Index on three fields (customer_key, date_from,

> date_to). Fields

> "date_from" and "date_to" are to maintain a customer-history. Now here's

> my question:

> If a new row is inserted in the table, the "date_to"-key of the previous

> row in the customer-history

> is updated. How is the Index reorganised then? Most important: Does

> anyone have experience with a simalar situation?

> How efficient can the reorganisation be done (is ist only a

> pointer-change in the B-tree)?

>

> Thanks for your help

> Heiko

>

>