Can I set a default table space for Index and Tables?

Question

Currently by default create table and index DDLs will place tables in MYDB_DATA_TABLE_SPACE, I would like to specify a different default table space for indexes from table data so that I do not have to always explicitly mention that in my create index DDL. Is this possible?

Current:

Table -> MYDB_DATA_TABLE_SPACE

Required:

Table -> MYDB_DATA_TABLE_SPACE
Index -> MYDB_INDX_TABLE_SPACE

Answer 1

You can't specify a different default tablespace for indexes and for tables. Each user only gets one default tablespace.

Hopefully, you're not trying to maintain separate tablespaces for performance reasons. There is a relatively common myth that you can improve performance by storing data and indexes in separate tablespaces. I'm personally not a big fan of putting indexes and data in different tablespaces-- there are no performance differences, it is terribly unlikely that you could run the application without the index tablespace so there aren't any recoverability benefits. I understand that it can appeal to some folks sense of organization so I don't object categorically.

Answer 2

Sorry, there is no way to do that.

A default tablespace may be set per user, but it applies to all segment creation: tables, indexes, materialized views, etc.

Hope that helps.