PolarDB：Gunakan pemindaian indeks partisi global dalam operasi DML

Penggunaan

• Buat tabel partisi dan indeks partisi global:

  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 PARTITION BY RANGE (b)
  (
      partition gpi_p0 values less than (500),
      partition gpi_p1  values less than (maxvalue)
  );
  ANALYZE gi_ora;

• Gunakan pemindaian indeks partisi global dalam operasi DELETE pada data yang ditentukan:

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

  Contoh hasil:

                                        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)

• Gunakan pemindaian indeks partisi global dalam operasi UPDATE pada data yang ditentukan:

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

  Contoh hasil:

                                        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)