Re: unbalanced indexes -- common wisdom?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:uev1k1niduhd05_at_corp.supernews.com...
>
> "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*.
>

I agree about the hairsplitting, since we seem to have done this one to death by now. But 3 isn't a real maximum, though it's quite hard to get a higher height than that in real life. Just had a student on a performance tuning course: one of his indexes had a height of 10, and that was after a rebuild!!

There are always degenerate cases, I guess.

Regards
HJR
> --
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
>
>
Received on Sun May 26 2002 - 15:37:15 CDT