分布式执行计划管理

obclient> SELECT /*+ PARALLEL(3) */ MAX(L_QUANTITY) FROM tbl1;

/*+ PARALLEL(table_name n) */

obclient> CREATE TABLE lineitem(
    l_orderkey           NUMBER(20) NOT NULL ,
    l_linenumber         NUMBER(20) NOT NULL ,
    l_quantity           NUMBER(20) NOT NULL ,
    l_extendedprice      DECIMAL(10,2) NOT NULL ,
    l_discount           DECIMAL(10,2) NOT NULL ,
    l_tax                DECIMAL(10,2) NOT NULL ,
    l_shipdate           DATE NOT NULL,
    PRIMARY KEY(L_ORDERKEY, L_LINENUMBER));
Query OK, 1 row affected 

obclient> CREATE TABLE customer(
    c_custkey            NUMBER(20) NOT NULL ,
    c_name               VARCHAR(25) DEFAULT NULL,
    c_address            VARCHAR(40) DEFAULT NULL,
    c_nationkey          NUMBER(20) DEFAULT NULL,
    c_phone              CHAR(15) DEFAULT NULL,
    c_acctbal            DECIMAL(10,2) DEFAULT NULL,
    c_mktsegment         CHAR(10) DEFAULT NULL,
    c_comment            VARCHAR(117) DEFAULT NULL,
    PRIMARY KEY(c_custkey));
Query OK, 1 row affected 

obclient> CREATE TABLE orders(
    o_orderkey           NUMBER(20) NOT NULL ,
    o_custkey            NUMBER(20) NOT NULL ,
    o_orderstatus        CHAR(1) DEFAULT NULL,
    o_totalprice         DECIMAL(10,2) DEFAULT NULL,
    o_orderdate          DATE NOT NULL,
    o_orderpriority      CHAR(15) DEFAULT NULL,
    o_clerk              CHAR(15) DEFAULT NULL,
    o_shippriority       NUMBER(20) DEFAULT NULL,
    o_comment            VARCHAR(79) DEFAULT NULL,
    PRIMARY KEY(o_orderkey,o_orderdate,o_custkey));
Query OK, 1 row affected 

obclient> INSERT INTO lineitem VALUES(1,2,3,6.00,0.20,0.01,'01-JUN-02');
Query OK, 1 row affected 

obclient> INSERT INTO customer VALUES(1,'Leo',null,null,'1390000****',null,'BUILDING',null);
Query OK, 1 row affected 

obclient> INSERT INTO orders VALUES(1,1,null,null,'01-JUN-20',10,null,8,null);
Query OK, 1 row affected 

obclient>SELECT /*+ ORDERED USE_NL(orders) */o_orderdate, o_shippriority
        FROM customer, orders WHERE c_mktsegment = 'BUILDING' AND
         c_custkey = o_custkey GROUP BY o_orderdate, o_shippriority;

+-------------+----------------+
| O_ORDERDATE | O_SHIPPRIORITY |
+-------------+----------------+
| 01-JUN-20   |              8 |
+-------------+----------------+
1 row in set 

PQ_DISTRIBUTE(tablespec outer_distribution inner_distribution)

obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 DATE);
Query OK, 0 rows affected  

obclient> CREATE INDEX i1 ON t1(c3);
Query OK, 0 rows affected  

obclient> CREATE TABLE t2(c1 INT(11) NOT NULL, c2 INT(11) NOT NULL, c3 INT(11) NOT NULL, PRIMARY KEY (c1, c2, c3)) PARTITION BY KEY(c2) PARTITIONS 4;
Query OK, 0 rows affected 

obclient> EXPLAIN BASIC SELECT /*+USE_PX PARALLEL(3) PQ_DISTRIBUTE
        (t2 BROADCAST NONE) LEADING(t1 t2)*/ * FROM t1 JOIN t2 ON 
         t1.c2 = t2.c2\G
*************************** 1. row ***************************
Query Plan: 
================================================
|ID|OPERATOR                          |NAME    |
------------------------------------------------
|0 |EXCHANGE IN DISTR                 |        |
|1 | EXCHANGE OUT DISTR               |:EX10001|
|2 |  HASH JOIN                       |        |
|3 |   EXCHANGE IN DISTR              |        |
|4 |    EXCHANGE OUT DISTR (BROADCAST)|:EX10000|
|5 |     PX BLOCK ITERATOR            |        |
|6 |      TABLE SCAN                  |t1      |
|7 |   PX BLOCK ITERATOR              |        |
|8 |    TABLE SCAN                    |t2      |
================================================