EXPLAIN

-- Create the tpcds.customer_address_p1 table.
MogDB=# CREATE TABLE tpcds.customer_address_p1 AS TABLE tpcds.customer_address;

-- Change the value of explain_perf_mode to normal.
MogDB=# SET explain_perf_mode=normal;

-- Display an execution plan for simple queries in the table.
MogDB=# EXPLAIN SELECT * FROM tpcds.customer_address_p1;
QUERY PLAN
--------------------------------------------------
Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
Node/s: All dbnodes
(2 rows)

-- Generate an execution plan in JSON format (with explain_perf_mode being normal).
MogDB=# EXPLAIN(FORMAT JSON) SELECT * FROM tpcds.customer_address_p1;
              QUERY PLAN
--------------------------------------
 [                                   +
   {                                 +
     "Plan": {                       +
       "Node Type": "Data Node Scan",+
       "Startup Cost": 0.00,         +
       "Total Cost": 0.00,           +
       "Plan Rows": 0,               +
       "Plan Width": 0,              +
       "Node/s": "All dbnodes"     +
     }                               +
   }                                 +
 ]
(1 row)

-- If there is an index and we use a query with an indexable WHERE condition, EXPLAIN might show a different plan.
MogDB=# EXPLAIN SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;
QUERY PLAN
--------------------------------------------------
Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
Node/s: dn_6005_6006
(2 rows)

-- Generate an execution plan in YAML format (with explain_perf_mode being normal).
MogDB=# EXPLAIN(FORMAT YAML) SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;
           QUERY PLAN
---------------------------------
 - Plan:                        +
     Node Type: "Data Node Scan"+
     Startup Cost: 0.00         +
     Total Cost: 0.00           +
     Plan Rows: 0               +
     Plan Width: 0              +
     Node/s: "dn_6005_6006"
(1 row)

-- Here is an example of a query plan with cost estimates suppressed:
MogDB=# EXPLAIN(COSTS FALSE)SELECT * FROM tpcds.customer_address_p1 WHERE ca_address_sk=10000;
       QUERY PLAN
------------------------
 Data Node Scan
   Node/s: dn_6005_6006
(2 rows)

-- Here is an example of a query plan for a query using an aggregate function:
MogDB=# EXPLAIN SELECT SUM(ca_address_sk) FROM tpcds.customer_address_p1 WHERE ca_address_sk<10000;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=18.19..14.32 rows=1 width=4)
   ->  Streaming (type: GATHER)  (cost=18.19..14.32 rows=3 width=4)
         Node/s: All dbnodes
         ->  Aggregate  (cost=14.19..14.20 rows=3 width=4)
               ->  Seq Scan on customer_address_p1  (cost=0.00..14.18 rows=10 width=4)
                     Filter: (ca_address_sk < 10000)
(6 rows)

-- Create a level-2 partitioned table.
MogDB=# CREATE TABLE range_list
MogDB-# (
MogDB(#     month_code VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     dept_code  VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     user_no    VARCHAR2 ( 30 ) NOT NULL ,
MogDB(#     sales_amt  int
MogDB(# )
MogDB-# PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
MogDB-# (
MogDB(#   PARTITION p_201901 VALUES LESS THAN( '201903' )
MogDB(#   (
MogDB(#     SUBPARTITION p_201901_a values ('1'),
MogDB(#     SUBPARTITION p_201901_b values ('2')
MogDB(#   ),
MogDB(#   PARTITION p_201902 VALUES LESS THAN( '201910' )
MogDB(#   (
MogDB(#     SUBPARTITION p_201902_a values ('1'),
MogDB(#     SUBPARTITION p_201902_b values ('2')
MogDB(#   )
MogDB(# );
CREATE TABLE

-- Run a query statement containing a level-2 partitioned table.
-- Iterations and Sub Iterations specifies the numbers of level-1 and level-2 partitions that are traversed, respectively.
-- Selected Partitions specifies which level-1 partitions are actually scanned. Selected Subpartitions (p:s) indicates that s level-2 partitions under the pth level-1 partition are actually scanned. If all level-2 partitions under the level-1 partition are scanned, the value of s is ALL.
MogDB=# EXPLAIN SELECT * FROM range_list WHERE dept_code = '1';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..13.81 rows=2 width=238)
   Iterations: 2, Sub Iterations: 2
   ->  Partitioned Seq Scan on range_list  (cost=0.00..13.81 rows=2 width=238)
         Filter: ((dept_code)::text = '1'::text)
         Selected Partitions:  1..2
         Selected Subpartitions:  1:1, 2:1
(6 rows)

-- Delete the tpcds.customer_address_p1 table.
MogDB=# DROP TABLE tpcds.customer_address_p1;