> Yechiel, > > You had mentioned only one possible scenario (i.e. "user A accesses table while user >B simultaneously > accesses index") where there are several other possible, equally-likely scenarios >(i.e. "user A accesses > table while user B simultaneously accesses table", "user A accesses index while user >B simultaneously > accesses index", etc). Separating tables and indexes to separate devices does >nothing for those other, > equally-likely scenarios, does it? That's the reason for the question "why?" in the >beginning of my last > reply... > > At issue here is not the concept of parallelism in I/O. At issue (at least for me) >is the "conventional > wisdom" that states/implies that there is some performance benefit of separating >tables and indexes to > separate devices. My assertion is that this is irrelevant for two reasons: a) >within a single process the > accessing of table blocks and index blocks are purely sequential and b) tables and >indexes have different > I/O characteristics which make it less likely that they will conflict with each >other. In fact, in most > situations datafiles/tablespaces containing indexes generate far fewer physical I/Os >than > datafiles/tablespaces containing tables. From an I/O perspective, the key is not to >focus on whether the > datafile/tablespace contains tables or indexes but rather to focus on the volume and >type of physical I/O > they generate. > > By focusing on the I/O statistics rather than whether they are tables or indexes, >one can make better > determinations on how to distribute I/O across non-RAID devices. > > Hope this helps... > > -Tim
Tim, I fully subscribe to your conclusion but I wouldn't be that harsh about conventional wisdom, which once had some ring of truth to it and still has it on rustic configurations. Granted, for a given user parallelizing his or her table and index accesses doesn't make much sense. But when you have a lot of happy users merrily issuing their queries, you can hope that at some point in time some will be hitting indexes while others will be hitting tables - and when dbwr and its gang will join the party, both indexes and tables will be hit too. This is probably what Yechiel meant. I see conventional wisdom as a rough-and-ready rule-of-thumb to make people spread their I/Os. And at least the benefit of having separate tablespaces is that you have separate files which are easier to move around when you have a finer appreciation of what is going on. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
