Re: FW: concepts document part about separating indexes and tablespaces

From: Jared Still <>
Date: Mon, 19 May 2008 09:36:31 -0700
Message-ID: <>

> ... Let's grant Richard's contention that it is unlikely you'll win on
> performance of an individual index range scan query requiring table access.
> Now consider dozens of users with queries completely satisfied by indexes
> not competing with i/o for a full table scan in another tablespace. Unless
> you're going to argue that a full table scan is never the right thing to do
> and that none of your queries are satisfied from an index alone, I believe
> that means you sometimes get a win from separation of data and indexes,
> because statisitcally you will have less seek. If your underlying disk farm
> is SAME, you'll minimize that win and protect yourself from the possibility
> of unbalanced i/o generating sufficient queueing to degrade performance. ...
> PS: as for finding support for any stance in the Oracle documents, they do
> indeed call for SAME as a best practice, which effectively statistically
> spreads everything on the underlying disk farm.

In a perfect world we might be able to design a system based on known access

patterns, and it will yield the most balanced IO possible.

SAME just eliminates a lot a problems in an imperfect world.

  • few people have time for the type of analysis required to know all access patterns, or even a significant portion of them.
  • access patterns will change with changes in data. These could be due to new lines of business, acquisitions, divestitures, new reporting systems that query the data differently, application upgrades, ...
  • the analysis ultimately fails when simultaneous sessions are exercising different access patterns.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Received on Mon May 19 2008 - 11:36:31 CDT

Original text of this message