PolarDB for Oracle:Create a global index

Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

Description

• To create a global index, you must specify the GLOBAL keyword instead of the LOCAL keyword in the CREATE INDEX statement.
• If you do not specify the GLOBAL or LOCAL keyword, a local index is created by default.
• You can specify CONCURRENTLY in a CREATE INDEX statement that creates a global index.
• You cannot create global indexes for non-partitioned tables or the child tables of partitioned tables.
• You cannot create global indexes based on expressions.
• You cannot create global indexes based on the partition key columns of partitioned tables.

Global indexes provide the following benefits:

• Global indexes can provide unique constraints on the non-partition key columns of partitioned tables.
• Global indexes are created for partitioned tables by using non-partition key columns. This allows global indexes to accelerate queries on partitioned tables in scenarios in which no partition keys are specified.
• You can use the cross-node parallel execution feature to accelerate the process of creating global indexes of B-tree indexes. For more information, see Use cross-node parallel execution to accelerate index creation.

Global partitioned indexes:

• Range-partitioned or hash-partitioned global indexes can be created. The syntaxes are similar to that for partitioning tables.
• List-partitioned global indexes cannot be crated.
• Global partitioned indexes can be created only for the parent tables of partitioned tables. They can be created based on the partition key columns of partitioned tables.
• Global partitioned indexes support globally unique constraints.
• A global partitioned index is partitioned on a left prefix of the index columns.
• Range-partitioned global indexes support ordered scanning.
• Hash-partitioned global indexes do not support ordered scanning.
• Range-partitioned global indexes can be used to implement partition pruning based on optimizers and executors.
• Hash-partitioned global indexes can be used to implement partition pruning that is based on an equivalent condition.

Example

In the following example, a table is partitioned by time. Partitions are created on a regular basis to replace previous partitions.

CREATE TABLE partition_range (    id integer,
    a int,
    b int,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
CREATE TABLE partition_range_part02 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
CREATE TABLE partition_range_part03 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part03 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

When a large number of partitioned tables exist, if the created_date partition key is not specified in queries, the query performance is reduced.

SELECT * FROM partition_range WHERE id = $1

To resolve this issue, you can create global indexes to improve query performance. Execute the following statement to create a global index:

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global;

After the global index is created, the query performance is improved, as shown in the following example:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;
                              QUERY PLAN
-----------------------------------------------------------------------
 Global Index Scan using idx_partition_range_global on partition_range
   Index Cond: (id = 6)
(2 rows)

You can attach partitions to or detach partitions from partitioned tables for which global indexes are created.

• Attach a new partition.

  CREATE TABLE partition_range_part06 (    id integer,
      a int,
      b int,
      created_date timestamp without time zone
  );
  ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part06 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00');

• Detach a previous partition.

  ALTER TABLE partition_range DETACH PARTITION partition_range_part01;

CREATE TABLE partition_range (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
CREATE TABLE partition_range_part02 (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global
PARTITION BY range (id)
(
PARTITION idx_gpi_1_min_5 values less than(5),
PARTITION idx_gpi_2_5_10 values less than(10),
PARTITION idx_gpi_3_10_max values less than(maxvalue)
);

CREATE UNIQUE INDEX global_hash_idx ON partition_range(id) global

PARTITION BY HASH(id) PARTITIONS 8;

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id >= 5 and id < 7;

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_partition_range_global on partition_range
   Index Cond: ((id >= 5) AND (id < 7))
(2 rows)
                

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id = 6;

                              QUERY PLAN
------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_global on partition_range
   Index Cond: (id = 6)
(2 rows)