"USING INDEX TABLESPACE" precises which tablespace to create the index on. Indeed, creating a primary key constraint creates an associated unique index. This index may be created on the data tablespace, but it is generally recommended to use different tablespaces for data and indexes, for performance considerations (the tablespace used for indexes may have different attributes than the tablespace for data, and data and indexes can be put on different physical devices if each tablespace has been created on a distinct device). I know the theoretical part, but I don't know what you might gain for real. I hope that will have helped you anyway.
Right, we're using a SAN to store all datafiles, and I'm wondering if there's really any need to separate the data from it's indexes. In the process of combining the two into one tablespace for testing, I came across this syntax. I wasn't sure if I would use the same for adding it into the data tablespace, or what.
If you create a primary key on a table without specifying which tablespace to create the associated index on, it will by default create the index in the same tablespace as the one used for the table. However, note that for other indexes created by CREATE INDEX, this is the user's DEFAULT TABLESPACE that will be used if you don't specify another. And be aware that by default (just after the Oracle Install), a new user's default tablespace is the SYSTEM tablespace, and Oracle strongly recommends not to create a user's schema on the SYSTEM tablespace, for it would mix user and dictionary data, which is neither clean nor good for performance. You can change a user's default tablespace as shown below :
ALTER USER usr DEFAULT TABLESPACE tblspace;
or precise the tablespace to create the index on at index creation :
CREATE INDEX indx ON table1(field1, field2,...) TABLESPACE tblspace;
I've also found that "USING INDEX TABLESPACE tablespace_name" is not as it seems. USING INDEX [index_name] is itself one phrase, where if the index_name is left off, the index is named the same as the constraint. And the TABLESPACE tablespace_name is exactly what it looks like, just directing where to create the index.
And as far as moving existing indexes to another tablespace, the following works:
ALTER INDEX schema.index_name REBUILD TABLESPACE tablespace_name [NOLOGGING]
Thanks again for helping me understand this stuff,