Partitioning - Expert Oracle Database Architecture - page 624

11 )
12 /
Table created.
EODA@ORA12CR1> create index partitioned_index
2 on partitioned(id)
3 GLOBAL
4 partition by range(id)
5 (
6 partition part_1 values less than(1000),
7 partition part_2 values less than (MAXVALUE)
8 )
9 /
Index created.
Note the use of MAXVALUE in this index. MAXVALUE can be used in any range partitioned table as well as in the
index. It represents an infinite upper bound on the range. In our examples so far, we've used hard upper bounds on
the ranges (values less than < some value >). However, a global index has a requirement that the highest partition (the
last partition) must have a partition bound whose value is MAXVALUE . This ensures that all rows in the underlying table
can be placed in the index.
Now, completing this example, we'll add our primary key to the table:
EODA@ORA12CR1> alter table partitioned add constraint
2 partitioned_pk
3 primary key(id)
4 /
Table altered.
It is not evident from this code that Oracle is using the index we created to enforce the primary key (it is to me
because I know that Oracle is using it), so we can prove it by simply trying to drop that index:
EODA@ORA12CR1> drop index partitioned_index;
drop index partitioned_index
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
To show that Oracle will not allow us to create a non prefixed global index, we only need try the following:
EODA@ORA12CR1> create index partitioned_index2
2 on partitioned(timestamp,id)
3 GLOBAL
4 partition by range(id)
5 (
6 partition part_1 values less than(1000),
7 partition part_2 values less than (MAXVALUE)
8 )
9 /
partition by range(id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed