Re: separate data/inidex

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<aak5t5$l57$1_at_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.

You need not to.

>
> 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.

Ah, we are getting into another interesting discussion. I say "possibly" not "always". Your quote of 99.5% sounds a little too high to me but you could be correct. I heard more than one Senior DBAs say it always takes 3 disk reads to retrieve the rowid. See, there are quite a few people out there who think all the indexes have three levels in their B trees!

>
> "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.

In gereral, I find index rebuild make performance somewhat better if not much better. Performance can be worse but those are rare exceptions and I haven't seen one. If you set your parameters appropriately it normally won't happen. Almost all the DBAs have rebuilt indexes. If they find it help performance they keep doing it. Otherwise they stop. Don't take the phrase "half empty" at its face value. I really meant to say fragmentation. As you explained, index space can be reused. But it's not ALWAYS reused, nor is it NEVER reused as is the impression one might get from reading the documentation. How fragmented an index can be depends on the data. If the density of your index key is more or less uniform you won't have much fragmentation. If your data is highly skewed both ways, whether you use a regular or reversed key index you are going to have significant fragmentation over time and rebuilding this type of indexes will certainly help.

To find out what's the best thing to do you need to understand the access pattern and data distribution of your application. That will often point you in the right direction (assuming you understand the storage and access mechanism of your database). This approach has helped me much more than simply following some rules of thumb, or what we call "myths".

>
> 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.

That's the point. It's about BALANCE. And it's driven by your application.

>
> Regards
> HJR
Received on Mon Apr 29 2002 - 23:44:39 CDT