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: unbalanced indexes -- common wisdom?

Re: unbalanced indexes -- common wisdom?

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 24 May 2002 17:02:50 -0700
Message-ID: <bdf69bdf.0205241602.6b75cbb5@posting.google.com>


mikerault_at_earthlink.net (Mike Ault) wrote in message news:<37fab3ab.0205241025.68020d21_at_posting.google.com>...
> Hate to play the devils advocate (well not really...) but if rebuilds
> on the whole are a waste of time since Oracle is so efficient then why
> do index rebuilds when:
> 1. Levels are too high (very rare, I have never seen an index higher
> than 2 that I can remember and this is with giga to terabyte
> databases)

Level can easily grow to high numbers.
1. We insert a segment of numbers [1,9999], then delete [1000,9000] segment, then delete all numbers that are not multiple of 7.
2. Insert segment of numbers [10000,19000], then delete [11000,19000] segment, then delete all numbers that are not multiple of 11.
3. Insert segment of numbers [20000,29999], then delete [21000,19000] segments, then delete all numbers that are not multiple of 13.  

At this step I saw level 3 even though the total number of live nodes in the tree were < 500. (It's usefull to monitor those insertion/deletions with:

ANALYZE INDEX sys_c001579 VALIDATE STRUCTURE

SELECT height,

       del_lf_rows,
       lf_rows - del_lf_rows lf_rows_used,
       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadnness
    FROM index_stats
       where name = upper('SYS_C001579')

The level is logariphmically dependent on the number of nodes inserted, of course. It is correct, that one must do zillions insertions in order to get level to modest numbers like 6. Therefore, level is not a problem indeed.

> 2. Index is too broad (excessive IUD resulting in sparse nodes)

This is the real problem, thank you very much for putting the attention to it!

We might have a modest size b-tree, say, level 4 with 1M live nodes only and 1G of dead nodes. If index were rebuild, a range scan that returnes only 10 nodes becomes very selective and fast [on a rebuilt tree]. Fragmented tree, however, might contain a million dead nodes in that range, so the same range scan would certainly not perform. Received on Fri May 24 2002 - 19:02:50 CDT

Original text of this message

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