Tags: common, created, database, index, indexes, mysql, oracle, separate, sql, tables, tablespace, tablespace1
Index as separate tablespace
Should we have indexes created for our tables in a separate tablespace? or it is ok to keep with the data tablespace?
1. What is the common and recommended way of doing this?
2. Why is it better to do so?
3. What are the consideration that we need to take?
Leave a comment...
- 5 Comments
- The reason to put data and indexes in a different tablespace is to be able to divide the access to the data to seperate disc controllers.#1; Sun, 24 Feb 2008 10:24:00 GMT
- So, is better to separate or not to separate?
If separate, will this cost more I/O and degrade performance?
Thanks.#2; Sun, 24 Feb 2008 10:25:00 GMT
- You should separate the indexes form the tables. This will not degrade performance, it will improve performance since the I/O will be spread to different harddisks and different disk controllers. It will not give any extra I/O, it will divide I/O over more devices#3; Sun, 24 Feb 2008 10:26:00 GMT
If I have indexes currently created in the data tablespace, which I want to transfer to another separate dedicated tablespace for index, how should I go about? in other words, how do I transfer the index from one tablepsace to another new tablespace?
Thanks.#4; Sun, 24 Feb 2008 10:27:00 GMT
- Use this SQL. Spool the result and execute the spool file:
select 'alter index '| |index_name| |' rebuild tablespace &&NEW_INDEX_TS ;'
null#5; Sun, 24 Feb 2008 10:28:00 GMT