1

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
  • 2
    Usually not, see stackoverflow.com/questions/25462668/… Sep 28 '18 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. Sep 29 '18 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... Sep 29 '18 at 8:11
  • What is your goal? Do you want to optimize performance? Or do you just want to structure your application?
    – jmk
    Sep 30 '18 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.
    – EdStevens
    Sep 30 '18 at 13:11
1

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.

1
  • 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 '18 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.