Re: unbalanced indexes -- common wisdom?

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0205241602.6b75cbb5_at_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.

I think you should better concentrate on naming your constraints properly, instead of continuing to be hairsplitting on indexes. IIRC the maximum level of a B+tree index in Oracle is *3*.

--
Regards

Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address