Databases Reference
In-Depth Information
If your client requirement states that the partitioning column, in this case
HIRE_DATE
, can't be part of
the primary key, you can also simply create the constraint using the same syntax, which creates the
constraint along with a non-partitioned underlying index. The following query demonstrates the result:
SQL> select i.index_name, c.constraint_type, i.partitioned
2 from user_indexes i join user_constraints c
3 on (i.index_name = c.constraint_name)
4* where i.index_name = 'EMPLOYEES_PART_PK';
INDEX_NAME C PAR
------------------------------ - ---
EMPLOYEES_PART_PK P NO
An index did indeed get built for your table, but it is non-partitioned. The advantage of this is that
the limitation of the partitioning column having to be part of a unique index has been lifted, and you can
create the "natural" primary key constraint on the single
EMPLOYEE_ID
column. The disadvantage is that
now you have a non-partitioned index on top of your partitioned
EMPLOYEES
table. If you need to perform
any partition-level operations now on your table—such as truncating a partition, moving a partition, or
splitting a partition, to name a few—the entire underlying non-partitioned index is marked
UNUSABLE
and
must be rebuilt after any partition-level operation. Refer to the "Maintaining Indexes on Partitioned
Tables" section for more information.
Creating a Globally Partitioned Index
Globally partitioned indexes essentially mean that the index has a different partitioning scheme and is
partitioned based on a different column or set of columns than the data. This is primarily done to
increase the performance of queries against the data in the database. Based on user queries against a
given table, it may lend itself to having a globally partitioned index on a given queried column in order to
improve query performance. See Figure 6-2 for an example of how a globally partition is constructed
based on the data in a given table.
