Re: Oracle Myths- Tablespace placement answered by Oracle

I have Jonathan's book, and supoprt his rules comlpetely. I also add the following:

The only reliable benchmark is how it runs on your system.

Anyway, I posted the tablespace question on Metalink, and here is the repsonse:

"Queries are not serial in that one does not read all the relevant index block and then start retrieving rows. But, it's not full concurrent either, in that it reads 1 index block at a time then we fetch the relevant data blocks. i.e. read one index block, retrieve the relevant rows, repeat, etc. The only true parallelism would be parallel query.

Even without concurrency in the strictest sense it is still beneficial to put the indexes and data on different disks. The above info relates to one query. There is nothing to stop multiple queries against the same rows and indexes from processing concurrently.

So yes, you can get performance benefit by splitting the data and indexes onto different disks."

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3ce4be26$0$230$ed9e5944_at_reading.news.pipex.net...
> "Alan" <alanshein_at_erols.com> wrote in message
> news:ac1304$m75sv$1_at_ID-114862.news.dfncis.de...
> > Well, I read through the archived messages, and discovered that either
> half
> > the Oracle experts don't know what they're talking about, or that half
the
> > Oracle experts know what they are talking about. The other half aren't
> > talking. Anyway, I posted the question to Metalink, so we'll see what
the
> > consensus of the Oracle cube-dwellers is. Too bad there's no SQL
standard
> > related to this problem- if there was we could count on a definitive
> answer,
> > or at least one from Joe Celko.
>
> Of course you can ignore this if I don't know what I am talking about! but
> Jonathan's book has the following lesson drawn from the Cautionary Tale
> which I consider to be some of the best advice to be found in any Oracle
> book anywhere.
>
> 1. You only know what you have discovered so far
> 2. What worked last time might not work next time
> 3. Different isn't necessarily wrong.
>
> At least some of the rules I've referred to above as Myths have or had
some
> basis in fact. Others of them notably the buffer cache myth have some
merit
> but don't actually say what they are widely considered to say. I'm
> considering working some of these (say the top 5) into a presentation, but
> it will be interesting to see what comes out of your tar and Mark's offer
to
> raise with the devs. (plus I'm hoping to have 9i2 to play with soon
> bandwidth permitting)
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
Received on Fri May 17 2002 - 07:53:20 CDT