Re: Oracle b-trees

In article <883173976.1261961218_at_dejanews.com>, <pkelley_at_coat.com> wrote:
>
>And has anyone heard of a stagnant index bug where query by leading
>column of an index for only one particular leading value in a table (and
>no others) results in response times 5 or 6 times slower than a full
>table scan and IOs approximately equal to sum(blocks in index + blocks in
>table)? The value is always the highest value less than the low value
>from a recent massive delete.

See the discussion about the Truncate command in your Server Administrators guide. When you delete records in a table, the storage is still allocated and the full table scan might have to read all those empty blocks. The delete also can mess up subsequent ads because the freed up blocks are added to the bottom of the free list.

Also, previous to 7.3, a commit would leave entries in the rollback segment, so when you try to access the data block, Oracle would try to generate a read consistent view of the data, and clean out the rollback, also generating cleanout redo records.

>
>Thanks.
>
>Paul Kelley
>pkelley_at_coat.com
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!