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