Re: Oracle's Myth: keep tables and indexes in separate tablespaces

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f858a85$0$28118$afc38c87_at_news.optusnet.com.au>...
> Jack wrote:
>
> >
> > Look:
> > http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
> >
> > You might have a look at Juan Loaiza's article "Optimal Storage
> > Configuration Made Easy" http://technet.oracle.com/deploy/performance/ for
> > a
> > different approach to the problem. Since this is on technet, it appears
> > to be Oracle's latest recommendation.
>
>
> Sorry Jack: you're wrong on this, though I suppose it depends on what you
> mean by 'recommendation'.
>
> Of course there are papers about RAID and SANE on their website. I'm not
> disputing that. I'm not even disputing that RAID and SANE are jolly good
> ideas and should be implemented wherever possible,
>
> But what I am saying is that as of last week, their own Performance Tuning
> course notes and their own DBA Fundamentals course notes state, in black
> and white, to separate indexes from tables, and that performance benefits
> will arise as a result.
>
> We may agree to differ on what constitutes a recommendation, but as far as
> I'm concerned, what they tell their instructors to teach to fee-paying
> students is much more of an 'official' point of view than what freebie pdfs
> they might have hanging around on their website.
>
> That's all I'm saying. And all what the original poster was saying.
>
> Though I will go one step further, though it's only a logical conclusion
> from what's already been discussed: one should be very careful about making
> statements about what Oracle recommends or what its 'position' on something
> is. Because the right hand quite frequently doesn't agree with what the
> left hand has been writing.

At least one hand is getting it right. From 9.2 performance manual http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/iodesign.htm#23329 :

• Begin Inclusion ------------------- One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. It is not sufficient to separate a datafile simply because the datafile contains indexes or table data. The decision to segregate a file should be made only when the I/O rate for that file affects database performance.
• End Inclusion ---------------------

jg

--
@home.com doesn't know what cox.net is doing.
"I've got one hand in my pocket, and the other is flashing a peace
sign." - whatshername