云原生数据库 PolarDB：示例

示例：SELECT

EXPLAIN语句用于显示语句的执行计划。您可以使用EXPLAIN语句来确认是否从查询的执行计划中剪去分区。

1. 首先，创建一张简单表：

   CREATE TABLE sales
   (
     dept_no     number,   
     part_no     varchar2,
     country     varchar2(20),
     date        date,
     amount      number
   )
   PARTITION BY LIST(country)
   (
     PARTITION europe VALUES('FRANCE', 'ITALY'),
     PARTITION asia VALUES('INDIA', 'PAKISTAN'),
     PARTITION americas VALUES('US', 'CANADA')
   );

2. 然后，执行包含EXPLAIN语句的约束查询：

   EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';

   查询计划结果如下，服务器将只扫描表sales_asia，country值为INDIA的记录将会存储到该表中：

   postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
                       QUERY PLAN                     
   ---------------------------------------------------
   
    Append
      ->  Seq Scan on sales
            Filter: ((country)::text = 'INDIA'::text)
      ->  Seq Scan on sales_asia
            Filter: ((country)::text = 'INDIA'::text)
   (5 rows)

3. 可执行如下查询，搜索与未包括在分区键中的值匹配的记录：

   EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';  

   查询计划结果如下，服务器将查询所有的分区，从而定位满足查询的记录：

   postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';
                  QUERY PLAN               
   -----------------------------------------
    Append
      ->  Seq Scan on sales
            Filter: (dept_no = 30::numeric)
      ->  Seq Scan on sales_europe
            Filter: (dept_no = 30::numeric)
      ->  Seq Scan on sales_asia
            Filter: (dept_no = 30::numeric)
      ->  Seq Scan on sales_americas
            Filter: (dept_no = 30::numeric)
   (9 rows)

4. 排除约束在查询子分区表时同样适用：

   CREATE TABLE sales
   (
     dept_no     number,
     part_no     varchar2,
     country     varchar2(20),
     date        date,
     amount      number
   )
   PARTITION BY RANGE(date) SUBPARTITION BY LIST (country)
   (
     PARTITION "2011" VALUES LESS THAN('01-JAN-2012')
     (
       SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),
       SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),
       SUBPARTITION americas_2011 VALUES ('US', 'CANADA')
     ),
     PARTITION "2012" VALUES LESS THAN('01-JAN-2013')
     (
       SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),
       SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),
       SUBPARTITION americas_2012 VALUES ('US', 'CANADA')
     ),
     PARTITION "2013" VALUES LESS THAN('01-JAN-2014')
     (
       SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
       SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
       SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
     )
   );

   当您查询这张表时，查询计划器会从搜索路径中剪去任何可能不包含您想要的结果集的分区或子分区。

   postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';
                                    QUERY PLAN                                 
   -----------------------------------------------------------------------------
    Append
      ->  Seq Scan on sales
            Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
      ->  Seq Scan on sales_2012
            Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
      ->  Seq Scan on sales_americas_2012
            Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
   (7 rows)

示例：UPDATE、DELETE