Databases Reference
In-Depth Information
the
Oracle Database VLDB and Partitioning Guide
for your specific database release to get more
information on using prefixed and non-prefixed locally partitioned indexes.
When creating a unique locally-partitioned index, the partitioning column(s) must be included as
part of the index or you will receive the following error:
SQL> CREATE UNIQUE INDEX employees_part_pk
2 ON employees_part (employee_id)
3 LOCAL
4 /
ON employees_part (employee_id)
*
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index
After adding the partitioning column (in this case,
HIRE_DATE
) to the unique index definition, you
can now create the unique index on the
EMPLOYEES_PART TABLE
.
SQL> CREATE UNIQUE INDEX employees_part_pk
2 ON employees_part (employee_id, hire_date)
3 LOCAL
4 /
Index created.
Managing Primary Keys and Unique Indexes
It is generally regarded as good practice when a primary key constraint is needed on a table to first
create a unique index using the columns to be used for the primary key constraint, and then add the
constraint after the index has been created. For example,
CREATE UNIQUE INDEX employees_part_pk
ON employees_part (employee_id, hire_date)
LOCAL;
alter table employees_part add constraint employees_part_pk
primary key (employee_id, hire_date);
The advantage of doing this is it allows you to disable and re-enable the constraint when necessary
without dropping the underlying index. For a large table, that ability can represent a substantial time
savings when having to perform constraint management on a table. Disabling and then re-enabling
constraints is very common in data warehouse environments where a large volume of data is bulk
loaded into tables. In this scenario, constraints are disabled prior to loading and re-enabled after
loading. This can save substantial overall data processing time.
