Indexes vs Primary Key

[nirasha]

Hello,

I have been contemplating moving tables to different tablespaces, and obviously will be sorting out the matching indexes to their relevant tablespaces.

I have been informed via the Oracle forum that the primary key goes with the table when I move. Can anyone shed light on why the primary key is treated differently? Should we have them stored in a different fashion to our regular indexes? Basically what is the difference between how Oracle keeps our indexes compared to primary keys?

Thanks in advance,
Nirasha

[rpardip]

You could store indexes along with the table or in a seperate tablespace. However, if is advisable to store all the indexes
including the primary key indexes in seperate tablespace(s) other the tablespace(s) that store table data. The bottom line, you can control where you want to store the index/pk data.

Hope this helps!

[Chandra]

you can create default primary key index on any tablespace
as you wish.
One more thing, are you talking partition table or
non-partition table?
it will make a difference for the partition option.

Good Luck.

[marist89]

Primary keys are implemented as a unique index. As with other types of indicies, you typically will store them in a different tablespace than your data. If you have many indicies that are actively being used, you may want to place the PK in a different tablespace than the rest of the indicies to prevent I/O contention.

[nirasha]

Thanks for the advice. I noticed that with the alter table move tablespace, it keeps the pk in the new ts (along with the table). I was wondering why Oracle makes them 'move together', and whether they should be kept together.

Anyway, I think I will move them too. I am aware of regular indexes and separate them, I was unsure of primary keys.

BTW these are for non partitioned tables.
Thanks,
Nirasha

[chrisrlong]

Just a thought, but are these INDEX ORGANIZED tables? If so, then I'm pretty sure that you cannot separate the PK index from the table because they are tightly integrated.
Just a thought,

- Chris

[nirasha]

Nope, just regular tables.
Thanks, for all the help so far!!!

I have also noticed that primary keys (upon the alter table move tablespace command ) 'go' with the table. Is there some reason for this? I am trying to separate data and indexes (and perform some cleanup) on the database. Since I am performing this exercise, is it recommended that I use the alter index statement and move primary keys (i.e treat them like a regular index)?

Also any suggestions regarding placement of primary keys (if it is recommended that I move them)??

Thanks in advance,
Nirasha


[Edited by nirasha on 02-13-2001 at 10:34 AM]