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

Richard Foote wrote:

>
> 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 ;)

Agreed. The new 9i notes mention it silently as a bullet point in the chapter about I/O (which is now chapter 3, but used to be chapter 8). They don't elaborate.

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

Correct. In Oracle's defense there is an *enormous* quantity of material to write, and tight teaching schedules within which to produce the course material specifically, and it is no wonder that some of their course notes have occasionally looked like a bad cut and paste job from the previous version. Remember that slide for 8.1.5 Oracle that said you couldn't have unique constraints on an IOT... straight lift out of the 8.0 material. And totally wrong, of course. I can't blame them: that's tens of thousands of hours to write it all from scratch. That's unfortunately why it's a bit of a lottery as to which instructor you get, I think. The good ones do their own research and can provide perspective and correction to the material where needed (and generally, it's pretty good, so that's not all the time by any means). But there are a lot that just regurgitate the material.

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

Entirely agree. And then go on to ask... what's your excuse for being up so late???!!

Oh, don't bother answering that. I just saw the rugby score. Are you still quite sober??

Regards
HJR
> Cheers
>
> Richard

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------