Search code examples
oracle-databaseoracle11g

Implementing primary key on a partitioned table in Oracle

I have created a partitioned table in Oracle SQL where column PKID is the primary key, and it is the key from the raw data table from which my table was created.

I have partitioned my table using a range of 3 months. When I am adding new rows to this table, I would prefer not to have to verify that the PKID is unique on every partition, but only on the partition the data will be residing on.

I believe this will lower the execution time on the insert statements. Would it be better to make the primary key a combination of date and PKID, or to make the primary key local? Is there some other option that would be better?

Solution

  • You have two options.

    • Create a global index. The index spans over the entire table. When you drop or truncate a partition, then the index becomes invalid and you need to rebuild it.
    • Create a local index. In this case, the partition key must be part of the primary key.