Re: Index organised table

"William Robertson" <william.robertson_at_bigfoot.com> wrote in message news:1133569801.522184.174990_at_g47g2000cwa.googlegroups.com...
> Pradeep wrote:
>> I am reading tom kytes Expert ....
>>
>> 1. I am unable to understand the statement
>> "with IOT logical I/O would be the same, the physical I/O would be
>> less"
>> May be because i don't know how data is physically stored with IOT.
>>
>> Suppose if I have a Index organised table with colum "ename", then is
>> it that all the blocks containing enames begining with "A" will be
>> together followed by the very next block with next sequence of enames,
>> or is it sorted within the block ??
>>
>> 2. It also states that in IOT data must go where it belongs. now
>> suppose if in the above table if i have a block with all rows with
>> enames beginning with "A" except one which begins with "B". Now if i
>> add a row with ename beginning with "A" then what happens ?
>>
>>
>> Thanks
>

As stated by others in this thread, an IOT is stored physically like an index. The rows must be in exact sequence, but multiple key values can share a block. If a block is full, then a block split is performed where a new block is created and half the rows are moved to the new block, which frees up space for new rows on the old and new block for inserts to be placed in the exact physical sequence.

A b-tree clustered table has all the rows on the block with the same clustering key. This works fine if there are sufficient rows for each block with the same clustering column, but is obviously wasteful of space if some key values only contain one or a few rows. But the rows are only on the correct block(s) according to the clustering column and time is not spent by Oracle ensuring the rows are in the exact physical sequence (as would happen with an IOT).

A good example is an employee table. If you want to cluster by last name, then an IOT table might be better because some last names only occur one time. If you want to cluster by department where there are usually multiple employees for each department, then a b-tree clustering scheme might be good.

But you also need to consider the amount of inserts and deletes performed on the table (and updates of the clustering column). If the table has relatively few inserts or deletes, then the extra performance hit of using the IOT will not be noticeable, and you will save space over the b-tree clustered index. Received on Fri Dec 02 2005 - 22:00:58 CST