Re: unbalanced indexes -- common wisdom?

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0205230836.658a2a2d_at_posting.google.com...
> "RSH" <RSH_Oracle_NOSPAM_at_worldnet.att.net> wrote in message
news:<D6VG8.30535$D41.1196339_at_bgtnsc05-news.ops.worldnet.att.net>...
> > And ALTER INDEX REBUILD.
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:ueo6681j7sjn38_at_corp.supernews.com...
> > > ALTER INDEX <index_name> COALESCE
> > > to free up space
>
> So COALESCE, or REBUILD (or, maybe both?)
>
> I know that fragmented index can be defragmented, I'm writing that
> it's maybe a good idea to give an option to create a normal index that
> reuses free index blocks immediately. It is possible to have an index
> that doesn't require maintenance, why am I deprived that option? Index
> maintenance is just yet another miniscule issue on a long DBA list.

According to the Oracle Myths thread in this group, there is only a need to rebuild an index if the height of the B-tree increases. The only other reason I know of is being short on diskspace. By design (indexes are a B+-tree) the typical maintenance-free index will exist only on tables that never change.
You will understand that if you read the theory on B+-trees: they will *never* be maintenance-free.
As you might be aware, if a key is reused, the free index slot is also reused.
For the exact definition and difference of coalesce versus rebuild please refer to the documentation.
You aren't deprived of any option, as it is possible to automate the task, run it at night, so I really don't see what the problem is. You can't party without earning money first.

Hth

Regards

--
Sybrand Bakker
Senior Oracle DBA

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