Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Index create syntax

    I was looking over some scripts we've inherited, and I've been unable to find documentation describing the "USING INDEX TABLESPACE tablespace_name" syntax in the following:

    ALTER TABLE schema.table
    ADD (
    CONSTRAINT constraint_name
    PRIMARY KEY (field_name)
    USING INDEX TABLESPACE tablespace_name
    NOLOGGING
    );

    The tablespace that was set up to store this index has no attributes that are different from the tablespace which contains the data. I'm confused as to the nature of the "INDEX TABLESPACE" piece.

    Thanks,
    Chuck
    10g

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hi,

    "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.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    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.
    -cf

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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;

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Thanks RBARAER,

    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,
    Chuck

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    No problem. Thanks for your precisions,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •