Re: concepts document part about separating indexes and tablspaces

Mark
  Respectfully, I disagree. I think, separating tables and indexes is simply incorrect advice, if done for performance reasons. If it is done for ease of administration, I can understand that.
  In OLTP applications, typical pattern is access index, then access table. They are kind of sequential..
  In DSS applications, it is Full table scan or full index scan, collect rowids and then access table.

  Now, throw in (big enough) buffer cache and then unix buffer cache or OS buffer cache (don't have a clue about NT).

  Pattern completely changes. And then touch count based algorithm kicks in !

  Throw in multiple sessions performing similar operations, pattern gets complicated. So, this advice is not applicable even if there is just two disks. Better approach would be to use ASM or add both disks to one tablespace in the form of multiple files and muck up with extent size etc..

  One exception is that there is just one table and one index and only one session ever doing any activity and every logical I/O goes physical !

-- 
Cheers
Riyaj Shamsudeen
The Pythian Group - www.pythian.com
orainternals.wordpress.com


On Thu, May 15, 2008 at 4:01 PM, Powell, Mark D <mark.powell_at_eds.com> wrote:



>  The way the statement is written appears to imply that the tablespace in

> question is on one real physical disk and that when the indexes are

> separated into a different tablespace the index tablespace will be put on a

> different physical disk.  In this particular case separating the tables and

> indexes probably would help performance providing there are multiple

> sessions on the database that access other objects in the same tablespace

> concurrently.  Two disks are normally better than one.  The problem is just

> about no one has this type of disk arrangement any more.  The world is

> logical disks made up of multiple physical devices arrayed in RAID-0,

> RAID-5, or RAID-10 collections.

>

> -- Mark D Powell --

> Phone (313) 592-5148

>

>

>  ------------------------------

> *From:* oracle-l-bounce_at_freelists.org [mailto:

> oracle-l-bounce_at_freelists.org] *On Behalf Of *David Aldridge

> *Sent:* Thursday, May 15, 2008 4:32 PM

> *To:* davidsharples_at_gmail.com; ricks12345_at_gmail.com

> *Cc:* oracle-l_at_freelists.org

> *Subject:* Re: concepts document part about separating indexes and

> tablspaces

>

>  It's actually in the DBA Guide, and it's still there now:

> http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006372

>

> "Using different tablespaces (on different disks) for a table and its index

> produces better performance than storing the table and index in the same

> tablespace. Disk contention is reduced."

>

> ----- Original Message ----

> From: David Sharples <davidsharples_at_gmail.com>

> To: ricks12345_at_gmail.com

> Cc: oracle-l_at_freelists.org

> Sent: Thursday, May 15, 2008 3:18:48 PM

> Subject: Re: concepts document part about separating indexes and tablspaces

>

> because it doesn't

>

> 2008/5/15 Rick Ricky <ricks12345_at_gmail.com>:

>

>> i was talking to someone about this today. I cannot remember where in the

>> Concepts document that it says that separating data from indexes improves

>> performances?

>

>

>

>


--
http://www.freelists.org/webpage/oracle-l