Monday, October 3, 2011

Putting data in separate tablespaces, is it affect any performance issue in oracle database ?

Putting indexes and tables in different tablespaces will not affect performance.

Performance is based on the number of physical disks you can use, the RAID configuration, and your ability to distribute I/O across the physical disks.

putting an index for a table on disk 1 and the table data for a table on disk 2 is not good rather take disk 1 and disk 2 and stripe them together and have index and table data evenly spread over both devices so as to get even IO over both.

Putting data in separate tablespaces is only useful for management or administration purposes (such as Storage maintenance, backup and recovery).

And tom kytes says indexes would be in a different tablespace from the data but only because they are a different (extent) SIZE then the data not for performance .

See below links for details from oracle gurus

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1463404632043

https://forums.oracle.com/forums/thread.jspa?messageID=9909917#9909917

No comments: