Re: separate data/inidex

Daniel A. Morgan wrote:

> I can't believe it either. Please explain Howard.
>
> And please don't just throw out jargon like RAID and striping. This
> statement appears to fly in the face of more than a few books by well
> respected authors (not that you aren't one yourself now) as well as a lot
> of HTM and PDF files put out on technet.
>
> Daniel Morgan
>

Arggh! What's there to explain? For there to be a performance difference, a piece of DML must want to update the index and the table simultaneously. A select statement must want to read *from* the index and the table simultaneously. However... they don't. Table access and index access are serialised. Separating the two segments onto separate spindles to get a performance improvement is just chasing a mirage.   

(If it makes anyone happier, I always recommending separating them in practice because day-to-day management is easier that way).

Of course, having more spindles is always good for performance. But that's a general thing, and has nothing to do specifically with indexes and tables being kept apart.

Assuming you don't house each table in its own tablespace on its own spindle, you have far more to worry about having simultaneous access by different users to different table segments than you do about simultaneous access to one table and its associated index. Strangely, no-one seems to get worked up at the idea of different tables being housed in the same tablespace... yet an index and its table! Quelle horreur!!

Read what Thomas Kyte has to say about it, in this very thread.

And I didn't mention RAID once.

Bummer... I just did.

Regards
HJR Received on Sun Apr 21 2002 - 02:39:07 CDT