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:
$ sqlplus eoda/foo@PDB1
SQL> drop table partitioned;
SQL> CREATE TABLE partitioned( timestamp date,id int)
PARTITION BY RANGE (timestamp)
PARTITION part_1 VALUES LESS THAN
( to_date(’01-jan-2021′,’dd-mon-yyyy’) ) , PARTITION part_2 VALUES LESS THAN
( to_date(’01-jan-2022′,’dd-mon-yyyy’) ));Table created.
SQL> create index partitioned_indexon partitioned(id)GLOBALpartition by range(id)
partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) );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 ). 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:
SQL> alter table partitioned add constraint partitioned_pk primarykey(id);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:
SQL> 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 nonprefixed global index, we only need try the following:
SQL> create index partitioned_index2on partitioned(timestamp,id)GLOBALpartition by range(id)(partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) );partition by range(id)
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed

The error message is pretty clear. The global index must be prefixed. So, when would you use a global index? We’ll take a look at two system types, data warehouse and OLTP, and see when they might apply.

Data Warehousing and Global Indexes

Many data warehouses implement a sliding window approach to managing data—that is, drop the oldest partition of a table and add a new partition for the newly loaded data. In the following sections, we’ll take a look at what is meant by a sliding window of data and the potential impact of a global index on it.