Databases Reference
In-Depth Information
8 partition manager_400 values less than (400),
9 partition manager_500 values less than (500),
10 partition manager_600 values less than (600),
11 partition manager_700 values less than (700),
12 partition manager_800 values less than (800),
13 partition manager_900 values less than (900),
14* partition manager_max values less than (maxvalue));
Index created.
You can create a globally partitioned index on a partitioned table or a non-partitioned table. In
other words, there is no requirement for the table to be partitioned in order to create a globally
partitioned index. Because of the maintenance considerations on globally partitioned indexes on
partitioned tables discussed later in the chapter, globally partitioned indexes are not often used. They
are therefore often underutilized or ignored as an option to improve query performance, especially on
non-partitioned tables.
For range-based globally partitioned indexes, there must always be a maximum specified for the
index, with a high value of
MAXVALUE
. This assures that any new insertions into the corresponding table
will have a place in the globally partitioned index. In other words, with a globally partitioned index, you
can never have an "out of bounds" condition on the index. If you don't specify a high-bound partition on
a globally partitioned index, Oracle will simply not allow you to create the index, and you will receive the
following error:
SQL> CREATE INDEX employees_gi2
2 ON employees (manager_id)
3 GLOBAL
4 partition by range(manager_id)
5 (partition manager_100 values less than (100),
6 partition manager_200 values less than (200),
7 partition manager_300 values less than (300),
8 partition manager_400 values less than (400),
9 partition manager_500 values less than (500),
10 partition manager_600 values less than (600),
11 partition manager_700 values less than (700),
12 partition manager_800 values less than (800),
13* partition manager_900 values less than (900))
SQL> /
partition manager_900 values less than (900))
*
ERROR at line 13:
ORA-14021: MAXVALUE must be specified for all columns
Because of having to have to include a high-bound partition within every globally partitioned index,
the only manner in which you can add a partition to a globally partitioned index is with the
ALTER INDEX
... SPLIT
partition command. For example,
SQL> alter index employees_12
2 split partition manager_max at (1000)
3 into (partition manager_max, partition manager_1000);
Index altered.
