Can I set the default tablespace 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 I should not always explicitly indicate that in my creation index DDL, is this possible?

Current:

 Table -> MYDB_DATA_TABLE_SPACE 

It is required:

 Table -> MYDB_DATA_TABLE_SPACE Index -> MYDB_INDX_TABLE_SPACE 

Answer 1

Sorry, there is no way to do this.

A default table space can be set for each user, but it applies to the entire creation of a segment: tables, indexes, materialized views, etc.

Hope this helps.

Answer 2

You cannot specify a different default tablespace for indexes and tables. Each user receives only one default tablespace.

Hope you are not trying to maintain separate table spaces for performance reasons. There is a relatively common myth that you can improve performance by storing data and indexes in separate table spaces. I personally am not a big fan of placing indexes and data in different table spaces - there are no differences in performance, it is unlikely that you could launch an application without an index table space so that there are no benefits for recovery. I understand that some feelings of the organization may appeal to him, so I absolutely do not mind.