Index performance - separate tablespace? 1

[mcowen]

Hi all,

Do you think it would be quicker to store indexes on a separate tablespace than where actual data is stored? I am thinking that having it stored on a separate disk will minimise bottlenecks during retrieval.

Your views are appreciated.

Rgds
Matt

 

[jimbopalmer]

Yes I always keep my indexes and data files on seperate Disks, by using two tablespaces this allows the read/write heads to track smoother, not thrash back and forth I tried to remain child-like, all I acheived was childish.

 

[THoey]

Just as a tag along. We have an NT server with all of it's physical drives merged into two logical drives. C: is the operating system and other system type file and D: is the data\application drive. So, Oracle and all of my instance data files are loaded on D:. There is only one HD controller card that manages these drives. What do you do in this instance? Out of habit, I still create separate tablespaces for my data and indexes, but don't understand how this can improve performance in this instance.

Any suggestions? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Dagon]

No, there probably wouldn't be any performance advantages. But there might be administrative advantages. For example, you could save downtime by not backing up index tablespaces. If the database is lost, you just restore the data tablespaces and then rebuild the index tablespaces separately from scripts.

 

[THoey]

Thanks Dagon... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[carp]

There is another advantage to keeping the indexes in a separate tablespace, even if they're on the same drive; namely, the index will not be competing with the table for the same physical storage space.