In this case, the index entries of a partition of the table may exist outside the corresponding partition of the index. For such a situation, create a global index, as follows: ALTER TABLE trans ADD CONSTRAINT pk_trans PRIMARY KEY (trans_id) USING INDEX global; If the index is global, the index can become unusable when you drop the partition Since you are partitioning by an id that would appear to exclude range and list partitioning instead so the hash partition is probably the best for your application. Regarding global vs local partitions, I have seen timings indicate that global indexes can improve cross-partition queries slighly. Index partitioning with Oracle A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.