Re: separate data/inidex

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Tue, 30 Apr 2002 05:10:09 +1000
Message-ID: <aak5t5$l57$1@lust.ihug.co.nz>

"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0204291037.18f0bc9f_at_posting.google.com...
> ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message
news:<a8aed4.0204290646.4743b954_at_posting.google.com>...
> > gitaya_at_hotmail.com (MauMau) wrote in message
news:<1391a36e.0204210411.c3a275_at_posting.google.com>...
> > > "Titi" <thierry.constant2_at_wanadoo.fr> wrote in message
news:<3cbf2607$0$15182$626a54ce_at_news.free.fr>...
> > > > Hi,
> > > >
> > > > For a user, I have data and index in one tablespace.
> > > > I want to separate data and index ( good idea ??)
> > > >
> > > > How can do that ???
> > > >
> > > > Thanks in advance ...
> > > > ( oracle 8.1.7)
> > >
> > > No prob just rebuild your indexes using a predefined tablespace
> > > good idea to rebuild from time to time.
> >
> > Here goes Another Myth ... Can U tell us why u need to Rebuild teh
> > Indexes from Time to Time...

>

> to reduce index fragmentation (not tablespace fragmentation), and possibly
> reduce the depth of the B tree, so that your application won't have to
read
> half empty blocks on index scans. It's a good idea, though, to set a
nonzero
> pctfree to avoid excesive index block splits on inserts and updates.
>

Bong! Nope, though I plead guilty to having recommended frequent rebuilds as recently as last year.

First: when was the last time you ever saw an Oracle index with a height greater than 3? It's extremely hard to achieve, actually, because Oracle's re-balancing mechanism is pretty good. So 'reducing the depth of the B tree' is not an issue for 99.5% of all indexes out there.

"Half empty blocks"? I hope you're not implying that empty space in a leaf node can never be re-used, because it can. If you delete 'Bob' from the table, the index on the name column has a deleted leaf node entry for 'Bob', true enough, taking up space and being a waste of space. But if you were to insert a record for 'Bruce', that deleted entry is cleared out, and the space allocated to Bruce's index entry. Given a reasonably random pattern of insert activity, it is extemely unlikely that you'll have half-empty blocks sitting there for very long.

Third, a rebuild causes the index to be re-constructed nice and compactly, with no spare space, true enough. That might give you a bit of a lift in performance when reading that index immediately after the rebuild. But the next piece of DML you perform is going to cause the index to start keeling over again soon enough. At which point the index has to start re-acquiring all the extents it lost as a result of the rebuild, but which it had already gone to the trouble of acquiring before the rebuild. Extent acquisition is not cheap. In fact, in dictionary managed tablespace, it's bloody expensive! Therefore, the index actually has to repeat a lot of extremely expensive dictionary-management activity that, had you not rebuilt it, it would not have had to do. Performance can therefore actually be worse after a rebuild than before. Of course, in locally-managed tablespace, that argument is rather less strong than it used to be.

So, there's a balance to be struck. Tom Kyte once told me that he could count the number of times he's rebuilt an index in his entire life on the fingers (not thumbs) of both hands. That strikes me as a slightly too-Puritan approach! But Jonathan Lewis has a line in his book which reads (paraphrasing wildly) 'don't strive too officiously to achieve a mythical 100% efficiency for your indexes, because they'll never stay 100% efficient for long' -meaning, regular rebuilds are a waste of time. So somewhere between never and always is an appropriate rebuild frequency, but it's definitely on the 'occasional' end of the spectrum.

Regards
HJR Received on Mon Apr 29 2002 - 14:10:09 CDT