Local Indexes

Unique local indexes on sharded tables must contain the sharding key.

The following example creates a local index named id1 for the id column of the account table.

CREATE INDEX id1 ON account (id) LOCAL;

The following example creates a local unique index named id2 for the id and state columns of the account table.

CREATE UNIQUE INDEX id2 ON account (id, state) LOCAL;

Global Indexes on Subpartitions

Global indexes on most sharded tables are not allowed because they can compromise the performance of online chunk movement. However, you can create a primary key/unique indexes on sharded tables that are composite partitioned without having to include sub-partition keys.

The following CREATE INDEX syntax is used to create a global index on a composite partitioned sharded table.

CREATE [UNIQUE] INDEX index_name ON table_name (col1, col2 ...)
[TABLESPACE SET tsset]
PARTITIONED AS TABLE;

For example, the following statement creates a composite sharded table with a primary key.

CREATE SHARDED TABLE customers 
( cust_id     NUMBER NOT NULL
, name        VARCHAR2(50)
, address     VARCHAR2(250)
, location_id VARCHAR2(20)
, class       VARCHAR2(3)
, signup_date DATE
, CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
TABLESPACE SET ts1
PARTITION BY CONSISTENT HASH (cust_id)
SUBPARTITION BY RANGE (signup_date)
SUBPARTITION TEMPLATE 
( SUBPARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
  SUBPARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
  SUBPARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY')),
  SUBPARTITION future VALUES LESS THAN (MAXVALUE))
PARTITIONS AUTO
;

The following statement shows the creation of a PARTITIONED AS TABLE index.

CREATE UNIQUE INDEX custid_idx
ON customers(cust_id)
TABLESPACE SET tsidx1
PARTITIONED AS TABLE;

SPLIT CHUNK Handling

The global partitioned index is split automatically when splitting the underlying table partition when chunks are split.

A chunk split is in fact a series of split partition operations. For a global index, the default behavior during partition split is to invalidate the entire index. For the sharded table index, because each index partition is equi-partitioned with the table, the distributed database can issue corresponding split index operations automatically.

MOVE CHUNK Handling

Rather than invalidate the entire index, for a sharded table index, the distributed database invalidates only the index partition affected and rebuilds indexes after all exchanges are done.

Vector Indexes

You can create vector indexes on sharded tables with some slight differences. See Vector Indexes in a Globally Distributed Database.