Re: Oracle Myths- Tablespace placement answered by Oracle

Well - I'm off the web for a week, and look at the size of thread that comes up !

A question I posed at the EOUG conference yesterday was this:

"When an industry-recognised expert stands on this stage and tells you something that contradicts what you heard yesterday from an industry-recognised expert, who are you going to believe ?"

I hope the answer is "the one who tells you WHY they make a claim, demonstrates a proof of the claim, and shows you how to repeat the demonstration for yourself".

Take the comment below from Metalink. It is no longer 100% true. There is a new nested-loop optimisation that works as follows -

Get first row from table 1

    get related rowids by range scan of index on table2 Get next row from table 1

    get related rowids by range scan of index on table2

        ... repeat as necessary
Sort all acquired rowids into order
For each rowid in turn, visit table 2.

This means that all the I/O on index 2 occurs before any of the I/O on table 2 - which introduces another bias into the strategy for table/index/stripe allocation.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Alan wrote in message ...


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

>