PolarDB:Use global partitioned index scan in DML operations - PolarDB

When a partitioned table has a global partitioned index (GPI), PolarDB for Oracle can use a global partitioned index scan to locate rows in DELETE and UPDATE operations. This avoids full partition scans and improves DML performance when the WHERE clause filters on the index key rather than the partition key.

How it works

A global partitioned index spans all table partitions and is partitioned independently from the table. When you run DELETE or UPDATE with a WHERE clause on an indexed column, the query planner uses the GPI to locate matching rows across partitions, instead of scanning each table partition individually.

Reading EXPLAIN output

The EXPLAIN output for a DML operation that uses a GPI scan looks like this:

DELETE ON public.gi_ora
  ->  GLOBAL PARTITIONED INDEX SCAN(1/2) USING gi_ora_b_tableoid_idx ON public.gi_ora
        Output: gi_ora.tableoid, gi_ora.ctid
        Index Cond: (gi_ora.b = 198)

Field	Meaning
`DELETE ON` / `UPDATE ON`	Top-level DML node. The actual row modifications happen here.
`GLOBAL PARTITIONED INDEX SCAN(1/2)`	The planner is scanning index partition 1 of 2 to find matching rows.
`USING gi_ora_b_tableoid_idx`	The auto-generated name of the global partitioned index being used.
`Output: tableoid, ctid`	System columns that identify the physical location of each matching row. For UPDATE, this also includes the computed new column values.
`Index Cond`	The filter applied to the index, confirming the global index key column is used.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for Oracle cluster
A partitioned table with a global partitioned index

Set up the example

Create a range-partitioned table with nine partitions:

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 799 rows of sample data:

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 a global partitioned index on column b, then collect statistics:
```
CREATE INDEX ON gi_ora(b) global PARTITION BY RANGE (b)
(
    partition gpi_p0 values less than (500),
    partition gpi_p1  values less than (maxvalue)
);
ANALYZE gi_ora;
```
This creates the index gi_ora_b_tableoid_idx, partitioned into two index partitions (gpi_p0 and gpi_p1), independent of the table's nine partitions.

Use global partitioned index scan in DELETE

Run EXPLAIN to verify the query plan 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 public.gi_ora
   ->  GLOBAL PARTITIONED INDEX SCAN(1/2) USING gi_ora_b_tableoid_idx ON public.gi_ora
         Output: gi_ora.tableoid, gi_ora.ctid
         Index Cond: (gi_ora.b = 198)
(4 rows)

The plan shows a DELETE node on top, with GLOBAL PARTITIONED INDEX SCAN as the child node that locates the rows to delete. The notation (1/2) means the planner is scanning index partition 1 of 2 — gpi_p0, which covers values less than 500 — to find rows where b = 198.

Use global partitioned index scan in UPDATE

Run EXPLAIN to verify the query plan 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 public.gi_ora
   ->  GLOBAL PARTITIONED INDEX SCAN(1/2) USING gi_ora_b_tableoid_idx ON public.gi_ora
         Output: (gi_ora.a - 10), gi_ora.tableoid, gi_ora.ctid
         Index Cond: (gi_ora.b = 198)
(4 rows)

The Output row includes the computed new value (gi_ora.a - 10) alongside the system columns tableoid and ctid, which identify the physical location of each row to update. The Index Cond confirms that the scan uses the index key b = 198.

Limitations

Global partitioned index scan in DML is supported for DELETE and UPDATE only.
The WHERE clause must include a condition on the global index key column to trigger the scan.