Re: default tablespace

On 28/09/05, David Sharples <davidsharples_at_gmail.com> wrote:
> why would it want to do that?
>
>
>
> On 9/28/05, Lou Fangxin <anysql_at_gmail.com> wrote:
> >
> > Maybe Oracle will add a default index tablespacce to user.
> >
> >
>

I seem to recall that an enhancement request was submitted for this some time ago.

There seem to be different schools of thought on whether indexes should be in the same tablespace as the tables, different tablespaces but on the same disks/volumes or different tablespaces on different disks/volumes.

I seem to recall reading something by Connor where he debunked the myth that putting tables and indexes on separate disks improves performance because Oracle can read the index and the table without having to move the disk heads back and forth. It doesn't work that way, Oracle doesn't read them in parralell it reads the index and then the table. What I read did seem to only consider a single transaction.

My gut feeling is that, like many things in Oracle (and IT in general), it depends on your app. There may be some apps that would benefit from indexes being stored separately to tables, one possible example that comes to mind is where you have a large number of similtaneous queries by a key value that are widely dispersed accross a large table (e.g. queries to an inventory table from an ordering system where products are selected by a product ID which is the primary key). Due to there being a large number queries all running at the same time there's a good chance of the table and it's index being read at the same time albeit by different processes.

Stephen

--
It's better to ask a silly question than to make a silly assumption.
--
http://www.freelists.org/webpage/oracle-l