Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 addressReceived on Thu May 23 2002 - 12:08:53 CDT