Database Reference
In-Depth Information
EODA@ORA12CR1> alter table partitioned
2 add constraint
3 partitioned_pk
4 primary key(id)
5 /
alter table partitioned
*
ERROR at line 1:
ORA-01408: such column list already indexed
Here, Oracle attempts to create a global index on
ID
, but finds that it cannot since an index already exists. The
preceding statements would work if the index we created was not partitioned, as Oracle would have used that index to
enforce the constraint.
The reasons why uniqueness cannot be enforced, unless the partition key is part of the constraint, are twofold.
First, if Oracle allowed this, it would void most of the advantages of partitions. Availability and scalability would
be lost, as each and every partition would always have to be
available
and
scanned
to do any inserts and updates.
The more partitions you had, the less available the data would be. The more partitions you had, the more index
partitions you would have to scan, and the less scalable partitions would become. Instead of providing availability and
scalability, doing this would actually decrease both.
Additionally, Oracle would have to effectively
serialize
inserts and updates to this table at the transaction level.
This is because if we add
ID=1
to
PART_1
, Oracle would have to somehow
prevent
anyone else from adding
ID=1
to
PART_2
. The only way to do this would be to prevent others from modifying index partition
PART_2
, since there isn't
anything to really lock in that partition.
In an OLTP system, unique constraints must be system enforced (i.e., enforced by Oracle) to ensure the integrity
of data. This implies that the logical model of your application will have an impact on the physical design. Uniqueness
constraints will either drive the underlying table partitioning scheme, driving the choice of the partition keys, or point
you toward the use of
global
indexes instead. We'll take a look at global indexes in more depth next.
Global Indexes
Global indexes are partitioned using a scheme that is different from that used in the underlying table. The table might
be partitioned by a
TIMESTAMP
column into ten partitions, and a global index on that table could be partitioned into
five partitions by the
REGION
column. Unlike local indexes, there is only one class of global index, and that is a
prefixed
global index
. There is no support for a global index whose index key does not begin with the partitioning key
for that
index
. That implies that whatever attribute(s) you use to partition the index will be on the leading edge of the index
key itself.
Building on our previous example, here is a quick example of the use of a global index. It shows that a global
partitioned index can be used to enforce uniqueness for a primary key, so you can have partitioned indexes
that enforce uniqueness, but do not include the partition key of the table. The following example creates a table
partitioned by
TIMESTAMP
that has an index partitioned by
ID
:
EODA@ORA12CR1> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (
7 PARTITION part_1 VALUES LESS THAN
8 ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
9 PARTITION part_2 VALUES LESS THAN
10 ( to_date('01-jan-2015','dd-mon-yyyy') )
