How to specify separate tablespaces for data and indexes?

Question

I have two tablespaces: one for data and one for index. I can't change this configuration. How can I specify in Hibernate the index tablespace?

I've read Hibernate Oracle Tablespace Annotation, but it's not the same problem. I need to specify the tablespace for data and a different tablespace for indexes.

Answer 1

I understand your challenge. I don't believe there is any easy way to do this from Hibernate. Here are some workarounds:

1. Let Hibernate go ahead and create the index in the "wrong" tablespace, then afterwards run on your database statements to rebuild (move) the indexes to the correct tablespace, i.e.: ALTER INDEX some_index_name REBUILD TABLESPACE your_index_ts;.

If you have a lot of indexes, this meta-script might assist you with finding and moving your misplaced indexes. The output will be ALTER statements like above.

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE your_index_ts;' stmt
  FROM all_indexes
  WHERE table_owner='YOUR_SCHEMA'
    AND tablespace_name != 'YOUR_INDEX_TS';

In theory you could automate this and put it in a stored proc.

Let me put the question like Tom Kyte would probably do: Why do you want indexes in a separate tablespace? What application or performance issue are you trying to solve?

A perspective (feel free to disagree in comments): It's old school to insist that indexes are stored in a separate tablespace. Indeed, there could be some cases where this practice provides a benefit, but I have not seen this lately. The reasoning was that if you were doing mass DML, you would have IO contention between data and index if they were on the same disk, so the DBA forced them to be on different devices via different tablespaces. However if the DBA put the data and index tablespaces on the same disk, then it would negate the intention. These days, your datafiles tend to be spread out over many spindles (by ASM, or even on non-ASM by RAID sets, but even more so when running on SAN). With ASSM tablespaces (not confused with ASM or ASMM), Oracle these days does just fine with index segments in the same tablespace as table segments. And if you have to ever do TSPITR, having your entire schema in one tablespace makes things a bit easier. I might put LOBs in a separate tablespace, but that's not what you're asking.

If your DBA says indexes need to be in a separate tablespace "just because they do", then you need a new DBA.

You might have hoped that Oracle 12c would be released with a "DEFAULT INDEX TABLESPACE" property per user; alas no such feature.