Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Exclamation Unanswered: Where to place primary keys?

    I have read that the best design of the database is to hold the indexes in another tablespace different from tables.

    This is also right for primary keys?
    Thanks a lot,
    Alberto

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A primary key is a constraint, supported by an index. The same choices apply to the placement of the index as any other. See this Asktom Article for more information.

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Yes, you might as well place indexes used in constraints in the same place as other indexes.

    However, I think it's more of a tradition than a design principle.

    If the idea is to minimize effort when making backups by saving only tablespaces containing data (since indexes can always be recreated), perhaps you would come to regret that when you needed to restore a backup in a hurry. Or if it is for export/import of transportable tablespaces you might want to try out some scenarios to see what worked best for your application.

    If the idea is to improve load balancing and reduce disk contention, just define multiple datafiles for the tablespace (which is what generally happens anyway) or use a RAID device for storage (again, this is the norm these days).

    Unless anyone can suggest a more compelling reason, personally I wouldn't bother keeping tables and indexes separate.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In the bad old days of oracle 5,6,7 and before raid, you wanted to have the indexes on a different physical drive then the data to reduce disk contentation. This was a real problem with the slower disks of the day. With modern drives, sans and stripping, it is much less an issue.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Idex Tablespaces on different device

    It should not make any difference what device the indexes are on. The old thinking was that if you had them on a different device, you could start reading tables while you finished the index read. Now you just wnat to separate the index from the tables, so that yu can create a nologging tablespace for the indexes. It is ALMOST ALWAYS faster to re-create indexes than to try to recover them.

    HTH
    Peter

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    so that yu can create a nologging tablespace for the indexes
    Is it very interesting to create a NOLOGGING tablespace for indexes ? I understand it would be better in terms of performance, but AFAIK, it would require to recreate the indexes in case of loss of data and recovery, because indexes wouldn't have been completely logged, and would be corrupt when recovered. Are there other drawbacks ? Is the benefit big enough ?

    Thanks & regards,

    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
  •