Table Master_Table is created in App1_Schema1. Index_I01 is created in default TABLE SPACE. Index_I02 is created in TABLE SPACE "APP2_INDEX".
Having different Table space for different indexes of same table, will causes any performance impact ?
8
-
2Usually not, see stackoverflow.com/questions/25462668/…– Wernfried DomscheitSep 28, 2018 at 8:17
-
In the Middle Ages of computing, when servers had individually addressable disk drives, people needed to balance I/O by manually distributing data files to different spindles. But this is 2018. We are using intelligent storage systems with striping and mirroring, and placing files on different spindles is just no longer possible and makes no sense.– Albert GodfrindSep 29, 2018 at 8:05
-
That does not mean that distributing tables and indexes to specific tablespaces is no longer important. It is but for administrative and storage lifecycle reasons. Like having HR tables in one tablespace. Or partitioning tables and indexes by transaction dates into different tablespaces so that older data can easily be moved to cheaper storage ... And that current data can be backed up selectively...– Albert GodfrindSep 29, 2018 at 8:11
-
What is your goal? Do you want to optimize performance? Or do you just want to structure your application?– jmkSep 30, 2018 at 7:45
-
@AlbertGodfrind - I think that even in the Dark Ages, trying to physically separeate indexes and tables onto separate disks was a myth. Consider that given a table access by index the I/O on the index has to be completed (thus releasing the R/W head for other uses) before it is even known where to go get the data that was pointed to by the index. It is inherently impossible for there to be contention between reading an index and the subsequent read of data based on the results of the index read.– EdStevensSep 30, 2018 at 13:11
|
Show 3 more comments
1 Answer
Placing the indexes for a table in the same or different tablespaces from the tables and other indexes on the same table should have no impact on performance unless you have done something very wrong with your disk farm.
-
One of a few minor advantages with having indexes on a separate tablespace is you could exclude that tablespace from backups, assuming you have scripts available to recreate the indexes. Oct 3, 2018 at 23:01