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

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f866670$0$29372$afc38c87_at_news.optusnet.com.au...
> Mark Townsend wrote:
>
> > Howard J. Rogers wrote:
> >> 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'.
> >
> > I don't believe that Jack is wrong.
>
> It's not a matter of belief. If the course notes say it in black and
white,
> then he's wrong.
>
> >In fact, if the courses ARE teaching
> > it differently,
>
> No, they've been teaching it in exactly the same way since at least
version
> 8.0.
>
> >then I think the course needs to be urgently updated -
> > can anybody send me a reference number, and I'll follow up on it.
>
> They just *have* been updated. Check out 9i Performance Tuning. They've
just
> released version 2.1 of that document, and it contains the 'separate
tables
> from indexes' "recommendation" in Chapter 3. I taught it just last week,
> and my notes were version 2.0, and the students had version 2.1... that's
> how recent the update of the material was... I didn't actually get told a
> new revision had been issued. Fortunately, the changes were minor (and
this
> wasn't one of them).
>
> I can't give you the course code, I'm afraid, because I haven't actually
> cleared my desk out yet, so the manuals are still sitting in the office.
>
> But I can promise you that Fundamentals I is still "recommending" the same
> thing, too.
>
> I only know it because every time I teach it I have to spend 15 minutes
> explaining why the course notes are wrong, usually to a bunch of
> initially-sceptical faces. And it's been that way for years, so I doubt
> anything will change in a hurry.
>
> If you care to wait until early next week, I can have the precise course
> codes and page numbers for you (unless Richard wants to jump in
> beforehand).
>

Hi Howard,

I don't have the latest and greatest notes but the last set of 8i Performance Tuning notes (30052GC10) does quote on P.8-5 "Tables and indexes should be split into separate tablespaces because indexes and tables are often inserted into and read from simultaneously." This was the advice these courses have always recommended.

However, the last Performance Tuning notes I taught were the much improved first 9i version (D11299GC11) which only mentions on the PowerPoint on page 6-6 quote "Split tables and indexes into separate tablespaces". It doesn't actually mention why which is kinda of an improvement ;)

There is no doubt that Oracle itself is the biggest myth spreader of them all. Just look at it's own OTN site where you can still read why indexes/tablespaces should be split for performance, that Oracle indexes become unbalanced over time and need frequent rebuilding, that you can SAME including your redo logs with your datafiles with no performance issues whatsoever, etc.....

Going back back back, *I* spread many of these myths myself as I didn't have the confidence and experience to say otherwise. It was only after a year or so of teaching and of investigating things for myself that I began to say, "Ummm, actually this not quite right" and that I *really* began teaching. There's an assumption that because one works for Oracle that you somehow know the ins and outs of everything when in actual fact, many in Oracle (especially outside the US) are just as "ignorant" (not sure that's quite the word I'm after) and often only have access to the same information as those outside of Oracle. The best way to actually learn how something works is to experiment and learn for yourself. It might sound extraordinary but it's true and many of those who write these documents do so by basing it on existing documents, hence keeping many myths "alive" and by having only a matter of weeks experience with the latest product or versions.

Oracle doco IMHO is actually quite good but it ain't perfect and just because "Oracle" says it's so doesn't necessarily mean that it is so.

Cheers

Richard Received on Fri Oct 10 2003 - 09:18:30 CDT