When we have created some indexes as part of performance tuning,
CREATE INDEX index_emp ON emp(eid);
The index created in a default tablespace say myTS_D1. When I checked, some of the indexes are having their tablespace value
myTs_I1. And I understood that we should specify the tablespace name at the time of creating the index.
What is the impact ? Is this creates any issue, since some of them are in one tablespace and others in a different one.
The idea of having separate tablespaces is that you can put them on separate physical disks. Maybe you have a fast SSD that you want to use for some frequently accessed indexes for example. Or maybe you want to distribute disk I/O across multiple controllers to maximize throughput.
Aside from any performance or operational impact this tablespace placement has, there is no difference. For a database user (who only sees the logical schema) it looks the same.
Using separate tablespaces for indexes and data does not improve performance, it only creates more complexity which leads to more problems later. The more tablespaces and datafiles your DBAs have to manage the more likely something will go wrong.
