PolarDB:Use global index scan in DML operations

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for Oracle cluster

• A partitioned table with a global index

Set up a partitioned table with a global index

The following example creates a range-partitioned table, inserts sample data, and adds a global index on column b.

CREATE TABLE gi_ora
(
    a int,
    b int,
    c int GENERATED ALWAYS AS (b * 2),
    d int DEFAULT 0,
    e int,
    f text,
    g text
) PARTITION BY RANGE (a)
(
    partition p0 values less than (50),
    partition p1 values less than (100),
    partition p2 values less than (200),
    partition p3 values less than (300),
    partition p4 values less than (400),
    partition p5 values less than (500),
    partition p6 values less than (600),
    partition p7 values less than (700),
    partition p8 values less than (800)
);

INSERT INTO gi_ora(a,b,d,e,f,g)
SELECT i, i, i,
       (random()*1000)::int,
       (random()*30000000)::int::text,
       (random()*30000000)::int::text
FROM generate_series(1,799) i;

CREATE INDEX ON gi_ora(b) global;
ANALYZE gi_ora;

The CREATE INDEX ... global statement creates a global index named gi_ora_global_idx that spans all partitions. A global index maintains entries across all partitions, so a single index lookup is enough to find any matching row regardless of which partition it lives in. A local index, by contrast, is scoped to a single partition.

Use global index scan in DELETE

Run EXPLAIN to confirm the planner uses a global index scan for a DELETE filtered on column b:

EXPLAIN (costs off, verbose on) DELETE FROM gi_ora WHERE b = 198;

Expected output:

QUERY PLAN
----------------------------------------------------------------------------
 DELETE ON global_index_dml.gi_ora
   ->  GLOBAL INDEX SCAN USING gi_ora_global_idx ON global_index_dml.gi_ora
         Output: gi_ora.tableoid, gi_ora.ctid
         Index Cond: (gi_ora.b = 198)
(4 rows)

The GLOBAL INDEX SCAN USING gi_ora_global_idx line confirms the planner used the global index to locate the target row. Index Cond shows the filter applied at the index level. tableoid and ctid identify the physical location of the matching row so the DELETE can find and remove it without scanning each partition.

To see the impact, compare with a DELETE that filters on a column without a global index. The planner falls back to scanning each partition:

EXPLAIN (costs off, verbose on) DELETE FROM gi_ora WHERE e = 500;

Expected output:

QUERY PLAN
-------------------------------------------------------------------------------------
 Delete on global_index_dml.gi_ora
   ->  Append
         ->  Seq Scan on global_index_dml.gi_ora_p0
               Filter: (gi_ora_p0.e = 500)
         ->  Seq Scan on global_index_dml.gi_ora_p1
               Filter: (gi_ora_p1.e = 500)
         ->  Seq Scan on global_index_dml.gi_ora_p2
               Filter: (gi_ora_p2.e = 500)
         ...
(n rows)

The comparison plan above is illustrative. The exact output depends on your table definition and partition count.

Use global index scan in UPDATE

Run EXPLAIN to confirm the planner uses a global index scan for an UPDATE filtered on column b:

EXPLAIN (costs off, verbose on) UPDATE gi_ora SET a = a - 10 WHERE b = 198;

Expected output:

QUERY PLAN
----------------------------------------------------------------------------
 UPDATE ON global_index_dml.gi_ora
   ->  GLOBAL INDEX SCAN USING gi_ora_global_idx ON global_index_dml.gi_ora
         Output: (gi_ora.a - 10), gi_ora.tableoid, gi_ora.ctid
         Index Cond: (gi_ora.b = 198)
(4 rows)

The plan structure is the same as for DELETE. The Output line now includes the updated value (gi_ora.a - 10), which is the new value written back to the matched row.

Usage notes