-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
>>
>>?section cdefs
>>-------------------------------------------------
>>--  Control query defaults for schhp8 test run. --
>>-------------------------------------------------
>>control query default ARKCMP_FAKE_HW 'ON';

--- SQL operation complete.
>>
>>control query default HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION  'OFF';

--- SQL operation complete.
>>
>>control query default HIST_ON_DEMAND_STATS_SIZE  '0';

--- SQL operation complete.
>>control query default DEF_NUM_SMP_CPUS '1';

--- SQL operation complete.
>>control query default MAX_ESPS_PER_CPU_PER_OP '1';

--- SQL operation complete.
>>control query default DEF_NUM_NODES_IN_ACTIVE_CLUSTERS '8';

--- SQL operation complete.
>>control query default OPTIMIZATION_LEVEL 'FULL';

--- SQL operation complete.
>>control query default ZIG_ZAG_TREES 'ON';

--- SQL operation complete.
>>
>>control query default HIST_SAME_TABLE_PRED_REDUCTION '0.0';

--- SQL operation complete.
>>
>>control query default fake_volume_assignments 'ON';

--- SQL operation complete.
>>
>>control query default PREFERRED_PROBING_ORDER_FOR_NESTED_JOIN 'OFF';

--- SQL operation complete.
>>
>>control query default target_code 'RELEASE';

--- SQL operation complete.
>>control query default MEMORY_UNITS_SIZE	 '20490';

--- SQL operation complete.
>>control query default MDAM_SCAN_METHOD 'ON';

--- SQL operation complete.
>>control query default CACHE_HISTOGRAMS 'ON';

--- SQL operation complete.
>>control query default HIST_DEFAULT_BASE_SEL_FOR_LIKE_WILDCARD '0.5';

--- SQL operation complete.
>>control query default HIST_MISSING_STATS_WARNING_LEVEL '0';

--- SQL operation complete.
>>
>>control query default MSCF_ET_CPU '0.000014';

--- SQL operation complete.
>>control query default TARGET_CPU_FREQUENCY '1600';

--- SQL operation complete.
>>control query default REFERENCE_CPU_FREQUENCY '199';

--- SQL operation complete.
>>
>>control query default MSCF_ET_IO_TRANSFER '0.00002';

--- SQL operation complete.
>>control query default TARGET_IO_SEQ_READ_RATE '50.0';

--- SQL operation complete.
>>control query default REFERENCE_IO_SEQ_READ_RATE '50.0';

--- SQL operation complete.
>>
>>control query default MSCF_ET_NUM_IO_SEEKS '.0038';

--- SQL operation complete.
>>control query default TARGET_IO_SEEK_TIME '.0038';

--- SQL operation complete.
>>control query default REFERENCE_IO_SEEK_TIME '.0038';

--- SQL operation complete.
>>
>>-- disabling the inner table key scan hueristic for subquery unnesting
>>-- This setting will allow queries 17 and 20 to be unnested.
>>control query default COMP_BOOL_168 'ON' ;

--- SQL operation complete.
>>
>>
>>?section tests
>>-- test new scan optimizer first
>>obey optdml03(q01);
>>--------------------
>>--  TPCH Query 01 --
>>--------------------
>>prepare XX from
+>SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty,
+>       CAST(SUM(l_extendedprice) AS NUMERIC(18,2)) AS sum_base_price,
+>       CAST(SUM(l_extendedprice * (1-l_discount)) AS NUMERIC(18,2)) AS sum_disc_price,
+>       CAST(SUM(l_extendedprice * (1-l_discount) * (1 + l_tax))
+>            AS NUMERIC(18,2)) AS sum_charge,
+>       AVG(l_quantity) AS avg_qty,
+>       AVG(l_extendedprice) AS avg_price,
+>       AVG(CAST(l_discount AS NUMERIC (10,3))) AS avg_disc,
+>       COUNT(*) AS count_order
+>FROM lineitem
+>WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
+>GROUP BY l_returnflag, l_linestatus
+>ORDER BY l_returnflag, l_linestatus;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

08  ROOT              7   ?               6.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.860E+003 
07  ESP_EXCHANGE      6   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.860E+003 
06  SORT_PARTIAL_GRO  5   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.757E-007   1.860E+003 
05  SORT              4   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.204E-007   1.860E+003 
04  ESP_EXCHANGE      3   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.536E-004   1.860E+003 
03  HASH_PARTIAL_GRO  2   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.343E+000   1.860E+003 
02  PARTITION_ACCESS  1   ?               5.864E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.858E+003 
01  FILE_SCAN         ?   ?   LINEITEM    5.864E+008   FUNKY_OPT_UNIQUE  1.858E+003   1.858E+003 

--- 8 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

7    .    8    root                                                  6.00E+000
6    .    7    esp_exchange                    1:8(hash2) (m)        6.00E+000
5    .    6    sort_partial_groupby                                  6.00E+000
4    .    5    sort                                                  6.00E+000
3    .    4    esp_exchange                    8(hash2):8(hash2)     6.00E+000
2    .    3    hash_partial_groupby                                  6.00E+000
1    .    2    partition_access                                      5.86E+008
.    .    1    file_scan             fr        LINEITEM              5.86E+008

--- SQL operation complete.
>>obey optdml03(q02);
>>--------------------
>>--  TPCH Query 02 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address,
+>       s_phone, s_comment
+>FROM part,supplier,partsupp, nation, region
+>WHERE p_partkey = ps_partkey
+>      AND s_suppkey = ps_suppkey
+>      AND p_size = 15
+>      AND p_type like  '%BRASS'
+>      AND s_nationkey = n_nationkey
+>      AND n_regionkey = r_regionkey
+>      AND r_name = 'EUROPE'
+>      AND ps_supplycost = (SELECT MIN(ps_supplycost)
+>                           FROM partsupp, supplier, nation, region
+>                           WHERE p_partkey = ps_partkey
+>                                 AND s_suppkey = ps_suppkey
+>                                 AND s_nationkey = n_nationkey
+>                                 AND n_regionkey = r_regionkey
+>                                 AND r_name = 'EUROPE')
+>ORDER BY s_acctbal desc, n_name, s_name, p_partkey;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

39  ROOT              38  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.024E+002 
38  FIRSTN            37  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.024E+002 
37  ESP_EXCHANGE      36  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.024E+002 
36  SORT              35  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.540E-007   1.024E+002 
35  HASH_GROUPBY      34  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.200E-004   1.024E+002 
34  HYBRID_HASH_JOIN  33  3               1.598E+004   FUNKY_OPT_UNIQUE  1.931E-003   1.024E+002 
33  HYBRID_HASH_JOIN  32  6               7.991E+004   FUNKY_OPT_UNIQUE  3.148E-003   1.024E+002 
32  HYBRID_HASH_JOIN  31  29              7.991E+004   FUNKY_OPT_UNIQUE  1.957E-002   1.024E+002 
31  PARTITION_ACCESS  30  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.245E+000 
30  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  4.245E+000   4.245E+000 
29  ESP_EXCHANGE      28  ?               7.991E+004   FUNKY_OPT_UNIQUE  2.741E-003   9.816E+001 
28  NESTED_JOIN       24  27              7.991E+004   FUNKY_OPT_UNIQUE  9.830E-004   9.815E+001 
27  SPLIT_TOP         26  ?               4.000E+000   FUNKY_OPT_UNIQUE  9.689E-004   9.496E+000 
26  PARTITION_ACCESS  25  ?               4.000E+000   FUNKY_OPT_UNIQUE  9.689E-004   9.496E+000 
25  FILE_SCAN         ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  9.495E+000   9.495E+000 
24  HYBRID_HASH_JOIN  23  9               1.997E+004   FUNKY_OPT_UNIQUE  9.324E-004   8.866E+001 
23  HYBRID_HASH_JOIN  22  12              9.989E+004   FUNKY_OPT_UNIQUE  1.567E-003   8.865E+001 
22  HYBRID_HASH_JOIN  21  18              9.989E+004   FUNKY_OPT_UNIQUE  5.027E-003   8.865E+001 
21  ESP_EXCHANGE      20  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
20  PARTITION_ACCESS  19  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
19  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
18  ESP_EXCHANGE      17  ?               9.989E+004   FUNKY_OPT_UNIQUE  2.955E-003   8.839E+001 
17  NESTED_JOIN       14  16              9.989E+004   FUNKY_OPT_UNIQUE  1.113E-003   8.839E+001 
16  PARTITION_ACCESS  15  ?               4.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.186E+001 
15  FILE_SCAN         ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  1.186E+001   1.186E+001 
14  PARTITION_ACCESS  13  ?               2.497E+004   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
13  FILE_SCAN         ?   ?   PART        2.497E+004   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
12  ESP_EXCHANGE      11  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.940E-004   1.478E-003 
11  PARTITION_ACCESS  10  ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
10  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
08  PARTITION_ACCESS  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
07  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.940E-004   1.678E-003 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
04  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
01  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 

--- 39 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

38   .    39   root                                                  1.00E+000
37   .    38   firstn                                                1.00E+000
36   .    37   esp_exchange                    1:8(hash2) (m)        1.00E+000
35   .    36   sort                                                  1.00E+000
34   .    35   hash_groupby                                          1.00E+000
33   3    34   hybrid_hash_join      u                               1.59E+004
32   6    33   hybrid_hash_join      u                               7.99E+004
31   29   32   hybrid_hash_join                                      7.99E+004
30   .    31   partition_access                                      1.00E+006
.    .    30   file_scan             fr        SUPPLIER              1.00E+006
28   .    29   esp_exchange                    8(hash2):8(hash2)     7.99E+004
24   27   28   nested_join                                           7.99E+004
26   .    27   split_top                       8(rep-n):8(hash2)     4.00E+000
25   .    26   partition_access                                      4.00E+000
.    .    25   file_scan             fr        PARTSUPP              4.00E+000
23   9    24   hybrid_hash_join      u                               1.99E+004
22   12   23   hybrid_hash_join      u                               9.98E+004
21   18   22   hybrid_hash_join                                      9.98E+004
20   .    21   esp_exchange                    8(hash2):8(hash2)     1.00E+006
19   .    20   partition_access                                      1.00E+006
.    .    19   index_scan            fr        SUIDX2                1.00E+006
17   .    18   esp_exchange                    8(hash2):8(hash2)     9.98E+004
14   16   17   nested_join                                           9.98E+004
15   .    16   partition_access                                      4.00E+000
.    .    15   file_scan             fr        PARTSUPP              4.00E+000
13   .    14   partition_access                                      2.49E+004
.    .    13   file_scan             fr        PART                  2.49E+004
11   .    12   esp_exchange                    8(rep-b):1 (m)        2.50E+001
10   .    11   partition_access                                      2.50E+001
.    .    10   file_scan             fr        NATION                2.50E+001
8    .    9    esp_exchange                    8(rep-b):1 (m)        1.00E+000
7    .    8    partition_access                                      1.00E+000
.    .    7    file_scan             fr        REGION                1.00E+000
5    .    6    esp_exchange                    8(rep-b):1 (m)        2.50E+001
4    .    5    partition_access                                      2.50E+001
.    .    4    file_scan             fr        NATION                2.50E+001
2    .    3    esp_exchange                    8(rep-b):1 (m)        1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        REGION                1.00E+000

--- SQL operation complete.
>>obey optdml03(q03);
>>--------------------
>>--  TPCH Query 03 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 10] l_orderkey, 
+>       CAST(SUM(l_extendedprice*(1-l_discount))AS NUMERIC(18,2)) AS revenue,
+>       o_orderdate, o_shippriority
+>FROM customer,orders,lineitem
+>WHERE c_mktsegment = 'BUILDING'
+>      AND c_custkey = o_custkey
+>      AND l_orderkey = o_orderkey
+>      AND o_orderdate < DATE '1995-03-15'
+>      AND l_shipdate > DATE '1995-03-15'
+>GROUP BY l_orderkey, o_orderdate, o_shippriority
+>ORDER BY revenue DESC, o_orderdate;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

14  ROOT              13  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.370E+003 
13  FIRSTN            12  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.370E+003 
12  ESP_EXCHANGE      11  ?               4.717E+007   FUNKY_OPT_UNIQUE  2.800E-009   2.370E+003 
11  SORT              10  ?               4.717E+007   FUNKY_OPT_UNIQUE  5.491E+000   2.370E+003 
10  HASH_GROUPBY      9   ?               4.717E+007   FUNKY_OPT_UNIQUE  5.888E-001   2.364E+003 
09  HYBRID_HASH_JOIN  8   6               4.717E+007   FUNKY_OPT_UNIQUE  1.959E+000   2.364E+003 
08  PARTITION_ACCESS  7   ?               3.231E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.855E+003 
07  FILE_SCAN         ?   ?   LINEITEM    3.231E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
06  HYBRID_HASH_JOIN  5   3               2.187E+007   FUNKY_OPT_UNIQUE  5.088E-001   5.071E+002 
05  PARTITION_ACCESS  4   ?               7.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.311E+002 
04  FILE_SCAN         ?   ?   ORDERS      7.291E+007   FUNKY_OPT_UNIQUE  4.311E+002   4.311E+002 
03  ESP_EXCHANGE      2   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.682E-001   7.546E+001 
02  PARTITION_ACCESS  1   ?               3.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.529E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    3.000E+006   FUNKY_OPT_UNIQUE  7.529E+001   7.529E+001 

--- 14 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

13   .    14   root                                                  4.71E+007
12   .    13   firstn                                                4.71E+007
11   .    12   esp_exchange                    1:8(hash2) (m)        4.71E+007
10   .    11   sort                                                  4.71E+007
9    .    10   hash_groupby                                          4.71E+007
8    6    9    hybrid_hash_join                                      4.71E+007
7    .    8    partition_access                                      3.23E+008
.    .    7    file_scan             fr        LINEITEM              3.23E+008
5    3    6    hybrid_hash_join      u                               2.18E+007
4    .    5    partition_access                                      7.29E+007
.    .    4    file_scan             fr        ORDERS                7.29E+007
2    .    3    esp_exchange                    8(rep-b):8(hash2)     3.00E+006
1    .    2    partition_access                                      3.00E+006
.    .    1    file_scan             fr        CUSTOMER              3.00E+006

--- SQL operation complete.
>>obey optdml03(q04);
>>--------------------
>>--  TPCH Query 04 --
>>--------------------
>>prepare XX from
+>SELECT o_orderpriority, COUNT(*) as order_count
+>FROM orders
+>WHERE o_orderdate >= DATE '1993-07-01'
+>      AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
+>      AND EXISTS (SELECT *
+>                  FROM lineitem
+>                  WHERE l_orderkey = o_orderkey
+>                        AND l_commitdate < l_receiptdate)
+>GROUP BY o_orderpriority
+>ORDER BY o_orderpriority;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               5.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.285E+003 
10  ESP_EXCHANGE      9   ?               5.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   2.285E+003 
09  SORT_PARTIAL_GRO  8   ?               5.000E+000   FUNKY_OPT_UNIQUE  9.587E-008   2.285E+003 
08  SORT              7   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.381E-008   2.285E+003 
07  ESP_EXCHANGE      6   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.343E-004   2.285E+003 
06  HASH_PARTIAL_GRO  5   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.877E-003   2.285E+003 
05  HYBRID_HASH_SEMI  4   2               5.798E+006   FUNKY_OPT_UNIQUE  5.484E-001   2.285E+003 
04  PARTITION_ACCESS  3   ?               5.798E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.308E+002 
03  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  4.308E+002   4.308E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    1.997E+008   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 11 row(s) selected.
>>
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  5.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        5.00E+000
8    .    9    sort_partial_groupby                                  5.00E+000
7    .    8    sort                                                  5.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     5.00E+000
5    .    6    hash_partial_groupby                                  5.00E+000
4    2    5    hybrid_hash_semi_joi                                  5.79E+006
3    .    4    partition_access                                      5.79E+006
.    .    3    file_scan             fr        ORDERS                5.79E+006
1    .    2    partition_access                                      1.99E+008
.    .    1    file_scan             fr        LINEITEM              1.99E+008

--- SQL operation complete.
>>obey optdml03(q05);
>>--------------------
>>--  TPCH Query 05 --
>>--------------------
>>prepare XX from
+>SELECT n_name, 
+>       CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue
+>FROM customer,orders,lineitem,supplier,nation, region
+>WHERE c_custkey = o_custkey
+>      AND o_orderkey = l_orderkey
+>      AND l_suppkey = s_suppkey
+>      AND c_nationkey= s_nationkey
+>      AND s_nationkey = n_nationkey
+>      AND n_regionkey = r_regionkey
+>      AND r_name =  'ASIA'
+>      AND o_orderdate >=  DATE '1994-01-01'
+>      AND o_orderdate <  DATE '1994-01-01' + INTERVAL '1' YEAR
+>GROUP BY n_name
+>ORDER BY revenue desc;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   2.292E+003 
28  ESP_EXCHANGE      27  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.800E-009   2.292E+003 
27  SORT              26  ?               2.500E+001   FUNKY_OPT_UNIQUE  3.854E-007   2.292E+003 
26  HASH_PARTIAL_GRO  25  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.141E-007   2.292E+003 
25  ESP_EXCHANGE      24  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.794E-004   2.292E+003 
24  HASH_PARTIAL_GRO  23  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.360E-003   2.292E+003 
23  HYBRID_HASH_JOIN  22  3               7.302E+005   FUNKY_OPT_UNIQUE  1.831E-001   2.292E+003 
22  HYBRID_HASH_JOIN  21  19              1.825E+007   FUNKY_OPT_UNIQUE  3.466E+000   2.291E+003 
21  PARTITION_ACCESS  20  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.856E+003 
20  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.856E+003   1.856E+003 
19  ESP_EXCHANGE      18  ?               4.563E+006   FUNKY_OPT_UNIQUE  9.239E-002   4.315E+002 
18  HYBRID_HASH_JOIN  17  14              4.563E+006   FUNKY_OPT_UNIQUE  1.321E-001   4.314E+002 
17  ESP_EXCHANGE      16  ?               2.281E+007   FUNKY_OPT_UNIQUE  2.262E-001   4.310E+002 
16  PARTITION_ACCESS  15  ?               2.281E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.308E+002 
15  FILE_SCAN         ?   ?   ORDERS      2.281E+007   FUNKY_OPT_UNIQUE  4.308E+002   4.308E+002 
14  ESP_EXCHANGE      13  ?               3.000E+006   FUNKY_OPT_UNIQUE  6.434E-002   2.175E-001 
13  NESTED_JOIN       10  12              3.000E+006   FUNKY_OPT_UNIQUE  2.111E-002   1.531E-001 
12  PARTITION_ACCESS  11  ?               6.000E+005   FUNKY_OPT_UNIQUE  0.000E+000   1.298E-001 
11  INDEX_SCAN        ?   ?   CUSTOMER    6.000E+005   FUNKY_OPT_UNIQUE  1.298E-001   1.298E-001 
10  ESP_EXCHANGE      9   ?               5.000E+000   FUNKY_OPT_UNIQUE  3.452E-004   2.276E-003 
09  MERGE_JOIN        5   8               5.000E+000   FUNKY_OPT_UNIQUE  1.120E-006   1.931E-003 
08  SORT              7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.675E-006   1.289E-003 
07  PARTITION_ACCESS  6   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
06  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
03  ESP_EXCHANGE      2   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.397E-002   3.265E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 

--- 29 row(s) selected.
>>
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  2.50E+001
27   .    28   esp_exchange                    1:8(hash2) (m)        2.50E+001
26   .    27   sort                                                  2.50E+001
25   .    26   hash_partial_groupby                                  2.50E+001
24   .    25   esp_exchange                    8(hash2):8(hash2)     2.50E+001
23   .    24   hash_partial_groupby                                  2.50E+001
22   3    23   hybrid_hash_join      u                               7.30E+005
21   19   22   hybrid_hash_join                                      1.82E+007
20   .    21   partition_access                                      5.99E+008
.    .    20   file_scan             fr        LINEITEM              5.99E+008
18   .    19   esp_exchange                    8(hash2):8(hash2)     4.56E+006
17   14   18   hybrid_hash_join                                      4.56E+006
16   .    17   esp_exchange                    8(hash2):8(hash2)     2.28E+007
15   .    16   partition_access                                      2.28E+007
.    .    15   file_scan             fr        ORDERS                2.28E+007
13   .    14   esp_exchange                    8(hash2):8(hash2)     3.00E+006
10   12   13   nested_join                                           3.00E+006
11   .    12   partition_access                                      6.00E+005
.    .    11   index_scan            fr        CUIDX2                6.00E+005
9    .    10   esp_exchange                    8(rep-b):1            5.00E+000
5    8    9    merge_join                                            5.00E+000
7    .    8    sort                                                  2.50E+001
6    .    7    partition_access                                      2.50E+001
.    .    6    file_scan             fr        NATION                2.50E+001
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SUIDX2                1.00E+006

--- SQL operation complete.
>>obey optdml03(q06);
>>--------------------
>>--  TPCH Query 06 --
>>--------------------
>>prepare XX from
+>SELECT CAST(SUM(l_extendedprice*l_discount) AS NUMERIC(18,2)) AS revenue
+>FROM lineitem
+>WHERE l_shipdate >= DATE '1994-01-01'
+>      AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+>      AND l_discount BETWEEN .06 - 0.01 AND .06 + 0.01
+>      AND l_quantity < 24;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

06  ROOT              5   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.853E+003 
04  SPLIT_TOP         3   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   1.853E+003 
03  PARTITION_ACCESS  2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   1.853E+003 
02  SORT_PARTIAL_AGG  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.396E-002   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    1.143E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 6 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

5    .    6    root                                                  1.00E+000
4    .    5    sort_partial_aggr_ro                                  1.00E+000
3    .    4    split_top                       1:8(hash2)            1.00E+000
2    .    3    partition_access                                      1.00E+000
1    .    2    sort_partial_aggr_le                                  1.00E+000
.    .    1    file_scan                       LINEITEM              1.14E+007

--- SQL operation complete.
>>obey optdml03(q07);
>>--------------------
>>--  TPCH Query 07 --
>>--------------------
>>prepare XX from
+>SELECT supp_nation, cust_nation, yr, sum(volume) as revenue
+>   FROM
+>    (SELECT n1.n_name as supp_nation, n2.n_name as cust_nation,
+>             extract(year from l_shipdate) as yr,
+>       CAST(l_extendedprice*(1-l_discount) AS NUMERIC(18,2)) as volume
+>     FROM supplier,lineitem,orders,customer, nation n1, nation n2
+>     WHERE s_suppkey = l_suppkey
+>      AND o_orderkey = l_orderkey
+>      AND c_custkey = o_custkey
+>      AND s_nationkey = n1.n_nationkey
+>      AND c_nationkey = n2.n_nationkey
+>      AND ((n1.n_name  = 'FRANCE' AND
+>            n2.n_name = 'GERMANY')
+>          OR (n1.n_name = 'GERMANY'  AND
+>            n2.n_name = 'FRANCE'))
+>      AND l_shipdate BETWEEN  DATE '1995-01-01' AND  DATE '1996-12-31') as shipping
+>
+>GROUP BY supp_nation, cust_nation, yr
+>ORDER BY supp_nation, cust_nation, yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               8.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.897E+003 
28  ESP_EXCHANGE      27  ?               8.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.897E+003 
27  SORT_PARTIAL_GRO  26  ?               8.000E+000   FUNKY_OPT_UNIQUE  1.332E-007   1.897E+003 
26  SORT              25  ?               8.000E+000   FUNKY_OPT_UNIQUE  7.839E-008   1.897E+003 
25  ESP_EXCHANGE      24  ?               8.000E+000   FUNKY_OPT_UNIQUE  2.240E-004   1.897E+003 
24  HASH_PARTIAL_GRO  23  ?               8.000E+000   FUNKY_OPT_UNIQUE  1.428E-003   1.897E+003 
23  HYBRID_HASH_JOIN  22  3               5.828E+005   FUNKY_OPT_UNIQUE  2.608E-001   1.897E+003 
22  HYBRID_HASH_JOIN  21  19              2.188E+007   FUNKY_OPT_UNIQUE  1.441E+000   1.897E+003 
21  PARTITION_ACCESS  20  ?               1.821E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
20  FILE_SCAN         ?   ?   LINEITEM    1.821E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
19  ESP_EXCHANGE      18  ?               1.800E+007   FUNKY_OPT_UNIQUE  4.806E-001   4.138E+001 
18  HYBRID_HASH_JOIN  17  15              1.800E+007   FUNKY_OPT_UNIQUE  6.983E-001   4.090E+001 
17  PARTITION_ACCESS  16  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+001 
16  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.634E+001   3.634E+001 
15  HYBRID_HASH_JOIN  14  11              1.200E+006   FUNKY_OPT_UNIQUE  6.316E-002   3.859E+000 
14  ESP_EXCHANGE      13  ?               1.500E+007   FUNKY_OPT_UNIQUE  1.571E-001   3.792E+000 
13  PARTITION_ACCESS  12  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+000 
12  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  3.634E+000   3.634E+000 
11  ESP_EXCHANGE      10  ?               2.000E+000   FUNKY_OPT_UNIQUE  8.546E-004   4.143E-003 
10  HYBRID_HASH_JOIN  9   6               2.000E+000   FUNKY_OPT_UNIQUE  7.267E-006   3.289E-003 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.430E-004   1.627E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
04  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
03  ESP_EXCHANGE      2   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.397E-002   3.265E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 

--- 29 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  8.00E+000
27   .    28   esp_exchange                    1:8(hash2) (m)        8.00E+000
26   .    27   sort_partial_groupby                                  8.00E+000
25   .    26   sort                                                  8.00E+000
24   .    25   esp_exchange                    8(hash2):8(hash2)     8.00E+000
23   .    24   hash_partial_groupby                                  8.00E+000
22   3    23   hybrid_hash_join      u                               5.82E+005
21   19   22   hybrid_hash_join                                      2.18E+007
20   .    21   partition_access                                      1.82E+008
.    .    20   file_scan             fr        LINEITEM              1.82E+008
18   .    19   esp_exchange                    8(hash2):8(hash2)     1.80E+007
17   15   18   hybrid_hash_join                                      1.80E+007
16   .    17   partition_access                                      1.50E+008
.    .    16   index_scan            fr        ORDIDX2               1.50E+008
14   11   15   hybrid_hash_join                                      1.20E+006
13   .    14   esp_exchange                    8(hash2):8(hash2)     1.50E+007
12   .    13   partition_access                                      1.50E+007
.    .    12   index_scan            fr        CUIDX2                1.50E+007
10   .    11   esp_exchange                    8(rep-b):8(hash2)     2.00E+000
9    6    10   hybrid_hash_join                                      2.00E+000
8    .    9    esp_exchange                    8(hash2):1 (m)        2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    8(rep-b):1 (m)        2.50E+001
4    .    5    partition_access                                      2.50E+001
.    .    4    file_scan             fr        NATION                2.50E+001
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SUIDX2                1.00E+006

--- SQL operation complete.
>>obey optdml03(q08);
>>--------------------
>>--  TPCH Query 08 --
>>--------------------
>>prepare XX from
+>SELECT yr, sum(CASE when nation = 'BRAZIL'
+>                  then volume
+>                   else 0 end)
+>              / sum(volume) as mkt_share
+>  FROM
+>    (SELECT extract(year from o_orderdate) as yr,
+>       CAST(l_extendedprice*(1-l_discount) AS NUMERIC(16,2)) as volume,
+>          n2.n_name as nation
+>     FROM part,supplier,lineitem,orders,customer, nation n1, nation  n2,region
+>     WHERE p_partkey = l_partkey
+>      AND s_suppkey = l_suppkey
+>      AND l_orderkey = o_orderkey
+>      AND o_custkey = c_custkey
+>      AND c_nationkey = n1.n_nationkey
+>      AND n1.n_regionkey = r_regionkey
+>      AND r_name = 'AMERICA'
+>      AND s_nationkey = n2.n_nationkey
+>      AND o_orderdate BETWEEN  DATE '1995-01-01' AND  DATE '1996-12-31'
+>      AND p_type ='ECONOMY ANODIZED STEEL')  all_nations
+>GROUP BY yr
+>ORDER BY yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

37  ROOT              36  ?               2.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.373E+003 
36  ESP_EXCHANGE      35  ?               2.000E+000   FUNKY_OPT_UNIQUE  2.359E-004   2.373E+003 
35  SORT              34  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.666E-008   2.373E+003 
34  HASH_PARTIAL_GRO  33  ?               2.000E+000   FUNKY_OPT_UNIQUE  8.467E-008   2.373E+003 
33  ESP_EXCHANGE      32  ?               2.000E+000   FUNKY_OPT_UNIQUE  1.372E-004   2.373E+003 
32  HASH_PARTIAL_GRO  31  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.649E-004   2.373E+003 
31  HYBRID_HASH_JOIN  30  3               2.432E+005   FUNKY_OPT_UNIQUE  2.920E-003   2.373E+003 
30  HYBRID_HASH_JOIN  29  26              2.432E+005   FUNKY_OPT_UNIQUE  6.379E-003   2.373E+003 
29  ESP_EXCHANGE      28  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
28  PARTITION_ACCESS  27  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
27  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
26  ESP_EXCHANGE      25  ?               2.432E+005   FUNKY_OPT_UNIQUE  4.719E-003   2.372E+003 
25  HYBRID_HASH_JOIN  24  6               2.432E+005   FUNKY_OPT_UNIQUE  9.342E-003   2.372E+003 
24  HYBRID_HASH_JOIN  23  9               1.216E+006   FUNKY_OPT_UNIQUE  1.576E-002   2.372E+003 
23  HYBRID_HASH_JOIN  22  19              1.216E+006   FUNKY_OPT_UNIQUE  6.767E-002   2.372E+003 
22  ESP_EXCHANGE      21  ?               1.500E+007   FUNKY_OPT_UNIQUE  1.571E-001   3.792E+000 
21  PARTITION_ACCESS  20  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+000 
20  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  3.634E+000   3.634E+000 
19  ESP_EXCHANGE      18  ?               1.216E+006   FUNKY_OPT_UNIQUE  2.311E-002   2.369E+003 
18  HYBRID_HASH_JOIN  17  15              1.216E+006   FUNKY_OPT_UNIQUE  2.251E-001   2.368E+003 
17  PARTITION_ACCESS  16  ?               4.560E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.309E+002 
16  FILE_SCAN         ?   ?   ORDERS      4.560E+007   FUNKY_OPT_UNIQUE  4.309E+002   4.309E+002 
15  HYBRID_HASH_JOIN  14  12              3.996E+006   FUNKY_OPT_UNIQUE  3.912E+000   1.937E+003 
14  PARTITION_ACCESS  13  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
13  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.857E+003   1.857E+003 
12  ESP_EXCHANGE      11  ?               1.333E+005   FUNKY_OPT_UNIQUE  7.690E-003   7.653E+001 
11  PARTITION_ACCESS  10  ?               1.333E+005   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
10  FILE_SCAN         ?   ?   PART        1.333E+005   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  1.940E-004   1.478E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 37 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

36   .    37   root                                                  2.00E+000
35   .    36   esp_exchange                    1:8(hash2) (m)        2.00E+000
34   .    35   sort                                                  2.00E+000
33   .    34   hash_partial_groupby                                  2.00E+000
32   .    33   esp_exchange                    8(hash2):8(hash2)     2.00E+000
31   .    32   hash_partial_groupby                                  2.00E+000
30   3    31   hybrid_hash_join      u                               2.43E+005
29   26   30   hybrid_hash_join                                      2.43E+005
28   .    29   esp_exchange                    8(hash2):8(hash2)     1.00E+006
27   .    28   partition_access                                      1.00E+006
.    .    27   index_scan            fr        SUIDX2                1.00E+006
25   .    26   esp_exchange                    8(hash2):8(hash2)     2.43E+005
24   6    25   hybrid_hash_join      u                               2.43E+005
23   9    24   hybrid_hash_join      u                               1.21E+006
22   19   23   hybrid_hash_join                                      1.21E+006
21   .    22   esp_exchange                    8(hash2):8(hash2)     1.50E+007
20   .    21   partition_access                                      1.50E+007
.    .    20   index_scan            fr        CUIDX2                1.50E+007
18   .    19   esp_exchange                    8(hash2):8(hash2)     1.21E+006
17   15   18   hybrid_hash_join                                      1.21E+006
16   .    17   partition_access                                      4.56E+007
.    .    16   file_scan             fr        ORDERS                4.56E+007
14   12   15   hybrid_hash_join      u                               3.99E+006
13   .    14   partition_access                                      5.99E+008
.    .    13   file_scan             fr        LINEITEM              5.99E+008
11   .    12   esp_exchange                    8(rep-b):8(hash2)     1.33E+005
10   .    11   partition_access                                      1.33E+005
.    .    10   file_scan             fr        PART                  1.33E+005
8    .    9    esp_exchange                    8(rep-b):1 (m)        2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    8(rep-b):1 (m)        1.00E+000
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q09);
>>--------------------
>>--  TPCH Query 09 --
>>--------------------
>>
>>prepare XX from
+>SELECT nation, yr, sum(amount) as sum_profit
+>  from  (SELECT n_name as nation, EXTRACT (YEAR from o_orderdate) as yr,
+>                l_extendedprice*(1-l_discount)-ps_supplycost*l_quantity as amount
+>FROM part,supplier,lineitem,partsupp,orders, nation
+>WHERE s_suppkey  = l_suppkey
+>      AND ps_suppkey = l_suppkey
+>      AND ps_partkey = l_partkey
+>      AND p_partkey  = l_partkey
+>      AND o_orderkey = l_orderkey
+>      AND p_name like '%green%'
+>      AND s_nationkey = n_nationkey) as profit
+>GROUP BY nation, yr
+>ORDER BY nation, yr DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

28  ROOT              27  ?               1.750E+002   FUNKY_OPT_UNIQUE  0.000E+000   2.357E+003 
27  ESP_EXCHANGE      26  ?               1.750E+002   FUNKY_OPT_UNIQUE  2.800E-009   2.357E+003 
26  SORT_PARTIAL_GRO  25  ?               1.750E+002   FUNKY_OPT_UNIQUE  2.447E-006   2.357E+003 
25  SORT              24  ?               1.750E+002   FUNKY_OPT_UNIQUE  4.645E-006   2.357E+003 
24  ESP_EXCHANGE      23  ?               1.750E+002   FUNKY_OPT_UNIQUE  1.888E-004   2.357E+003 
23  HASH_PARTIAL_GRO  22  ?               1.750E+002   FUNKY_OPT_UNIQUE  7.346E-002   2.357E+003 
22  HYBRID_HASH_JOIN  21  3               3.746E+007   FUNKY_OPT_UNIQUE  4.991E-001   2.357E+003 
21  HYBRID_HASH_JOIN  20  17              3.746E+007   FUNKY_OPT_UNIQUE  9.294E-001   2.357E+003 
20  ESP_EXCHANGE      19  ?               1.500E+008   FUNKY_OPT_UNIQUE  1.486E+000   3.783E+001 
19  PARTITION_ACCESS  18  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+001 
18  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.634E+001   3.634E+001 
17  HYBRID_HASH_JOIN  16  14              3.746E+007   FUNKY_OPT_UNIQUE  4.578E+000   2.318E+003 
16  PARTITION_ACCESS  15  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
15  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.857E+003   1.857E+003 
14  ESP_EXCHANGE      13  ?               5.000E+006   FUNKY_OPT_UNIQUE  6.266E-001   4.563E+002 
13  HYBRID_HASH_JOIN  12  6               5.000E+006   FUNKY_OPT_UNIQUE  5.532E-002   4.556E+002 
12  ESP_EXCHANGE      11  ?               5.000E+006   FUNKY_OPT_UNIQUE  7.044E-002   4.553E+002 
11  HYBRID_HASH_JOIN  10  8               5.000E+006   FUNKY_OPT_UNIQUE  4.191E-001   4.553E+002 
10  PARTITION_ACCESS  9   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
09  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
08  PARTITION_ACCESS  7   ?               1.250E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
07  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
06  ESP_EXCHANGE      5   ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
05  PARTITION_ACCESS  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
04  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 28 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

27   .    28   root                                                  1.75E+002
26   .    27   esp_exchange                    1:8(hash2) (m)        1.75E+002
25   .    26   sort_partial_groupby                                  1.75E+002
24   .    25   sort                                                  1.75E+002
23   .    24   esp_exchange                    8(hash2):8(hash2)     1.75E+002
22   .    23   hash_partial_groupby                                  1.75E+002
21   3    22   hybrid_hash_join      u                               3.74E+007
20   17   21   hybrid_hash_join                                      3.74E+007
19   .    20   esp_exchange                    8(hash2):8(hash2)     1.50E+008
18   .    19   partition_access                                      1.50E+008
.    .    18   index_scan            fr        ORDIDX3               1.50E+008
16   14   17   hybrid_hash_join                                      3.74E+007
15   .    16   partition_access                                      5.99E+008
.    .    15   file_scan             fr        LINEITEM              5.99E+008
13   .    14   esp_exchange                    8(rep-b):8(hash2)     5.00E+006
12   6    13   hybrid_hash_join      u                               5.00E+006
11   .    12   esp_exchange                    8(hash2):8(hash2)     5.00E+006
10   8    11   hybrid_hash_join                                      5.00E+006
9    .    10   partition_access                                      8.00E+007
.    .    9    file_scan             fr        PARTSUPP              8.00E+007
7    .    8    partition_access                                      1.25E+006
.    .    7    file_scan             fr        PART                  1.25E+006
5    .    6    esp_exchange                    8(hash2):8(hash2)     1.00E+006
4    .    5    partition_access                                      1.00E+006
.    .    4    index_scan            fr        SUIDX2                1.00E+006
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q10);
>>--------------------
>>--  TPCH Query 10 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 20] c_custkey, c_name,
+>       CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue,
+>       c_acctbal,  n_name, c_address, c_phone, c_comment
+>FROM customer,orders,lineitem, nation
+>WHERE c_custkey = o_custkey
+>      AND l_orderkey = o_orderkey
+>      AND o_orderdate >=  DATE '1993-10-01'
+>      AND o_orderdate <  DATE '1993-10-01' + INTERVAL '3' MONTH
+>      AND l_returnflag = 'R'
+>      AND c_nationkey = n_nationkey
+>GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name,
+>         c_address, c_comment
+>ORDER BY revenue DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.363E+003 
17  FIRSTN            16  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.363E+003 
16  ESP_EXCHANGE      15  ?               5.723E+006   FUNKY_OPT_UNIQUE  2.800E-009   2.363E+003 
15  SORT              14  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.203E+000   2.363E+003 
14  HASH_GROUPBY      13  ?               5.723E+006   FUNKY_OPT_UNIQUE  2.234E-001   2.362E+003 
13  HYBRID_HASH_JOIN  12  3               5.723E+006   FUNKY_OPT_UNIQUE  2.178E-001   2.362E+003 
12  HYBRID_HASH_JOIN  11  9               5.723E+006   FUNKY_OPT_UNIQUE  4.065E-001   2.361E+003 
11  PARTITION_ACCESS  10  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.556E+001 
10  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  7.556E+001   7.556E+001 
09  ESP_EXCHANGE      8   ?               5.723E+006   FUNKY_OPT_UNIQUE  8.057E-002   2.285E+003 
08  HYBRID_HASH_JOIN  7   5               5.723E+006   FUNKY_OPT_UNIQUE  7.729E-001   2.285E+003 
07  PARTITION_ACCESS  6   ?               1.479E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
06  FILE_SCAN         ?   ?   LINEITEM    1.479E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.307E+002 
04  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  4.307E+002   4.307E+002 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  5.72E+006
16   .    17   firstn                                                5.72E+006
15   .    16   esp_exchange                    1:8(hash2) (m)        5.72E+006
14   .    15   sort                                                  5.72E+006
13   .    14   hash_groupby                                          5.72E+006
12   3    13   hybrid_hash_join      u                               5.72E+006
11   9    12   hybrid_hash_join                                      5.72E+006
10   .    11   partition_access                                      1.50E+007
.    .    10   file_scan             fr        CUSTOMER              1.50E+007
8    .    9    esp_exchange                    8(hash2):8(hash2)     5.72E+006
7    5    8    hybrid_hash_join      u                               5.72E+006
6    .    7    partition_access                                      1.47E+008
.    .    6    file_scan             fr        LINEITEM              1.47E+008
4    .    5    partition_access                                      5.79E+006
.    .    4    file_scan             fr        ORDERS                5.79E+006
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q11);
>>--------------------
>>--  TPCH Query 11 --
>>--------------------
>>prepare XX from
+>SELECT ps_partkey, SUM(ps_supplycost * ps_availqty) AS ps_value
+>FROM partsupp,supplier,nation
+>WHERE ps_suppkey = s_suppkey
+>      AND s_nationkey = n_nationkey
+>      AND n_name = 'GERMANY'
+>GROUP BY ps_partkey
+>HAVING SUM(ps_supplycost*ps_availqty) >
+>       (SELECT SUM(ps_supplycost*ps_availqty) * 0.000001
+>        FROM partsupp,supplier,nation
+>        WHERE ps_suppkey = s_suppkey
+>              AND s_nationkey = n_nationkey
+>              AND n_name = 'GERMANY')
+>ORDER BY ps_value DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               1.074E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.579E+002 
28  ESP_EXCHANGE      27  ?               1.074E+006   FUNKY_OPT_UNIQUE  2.800E-009   7.579E+002 
27  SORT              26  ?               1.074E+006   FUNKY_OPT_UNIQUE  5.313E-002   7.579E+002 
26  HYBRID_HASH_JOIN  25  14              1.074E+006   FUNKY_OPT_UNIQUE  3.908E-002   7.578E+002 
25  HASH_GROUPBY      24  ?               3.223E+006   FUNKY_OPT_UNIQUE  4.167E-002   3.790E+002 
24  HYBRID_HASH_JOIN  23  21              3.223E+006   FUNKY_OPT_UNIQUE  5.386E-001   3.790E+002 
23  PARTITION_ACCESS  22  ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.784E+002 
22  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.784E+002   3.784E+002 
21  ESP_EXCHANGE      20  ?               4.028E+004   FUNKY_OPT_UNIQUE  1.827E-002   2.453E-002 
20  NESTED_JOIN       17  19              4.028E+004   FUNKY_OPT_UNIQUE  9.874E-005   6.257E-003 
19  PARTITION_ACCESS  18  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.748E-003 
18  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.748E-003   4.748E-003 
17  ESP_EXCHANGE      16  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
16  PARTITION_ACCESS  15  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
15  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
14  ESP_EXCHANGE      13  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.827E-004   3.787E+002 
13  SORT_PARTIAL_AGG  12  ?               1.000E+000   FUNKY_OPT_UNIQUE  7.126E-008   3.787E+002 
12  ESP_EXCHANGE      11  ?               1.000E+000   FUNKY_OPT_UNIQUE  6.302E-005   3.787E+002 
11  SORT_PARTIAL_AGG  10  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.184E-003   3.787E+002 
10  HYBRID_HASH_JOIN  9   7               3.223E+006   FUNKY_OPT_UNIQUE  3.799E-001   3.787E+002 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
08  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
07  ESP_EXCHANGE      6   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.827E-002   2.453E-002 
06  NESTED_JOIN       3   5               4.028E+004   FUNKY_OPT_UNIQUE  9.874E-005   6.257E-003 
05  PARTITION_ACCESS  4   ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.748E-003 
04  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.748E-003   4.748E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
01  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 

--- 29 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  1.07E+006
27   .    28   esp_exchange                    1:8(hash2) (m)        1.07E+006
26   .    27   sort                                                  1.07E+006
25   14   26   hybrid_hash_join                                      1.07E+006
24   .    25   hash_groupby                                          3.22E+006
23   21   24   hybrid_hash_join      u                               3.22E+006
22   .    23   partition_access                                      8.00E+007
.    .    22   file_scan             fr        PARTSUPP              8.00E+007
20   .    21   esp_exchange                    8(rep-b):8(hash2)     4.02E+004
17   19   20   nested_join                                           4.02E+004
18   .    19   partition_access                                      4.02E+004
.    .    18   index_scan            fr        SUIDX2                4.02E+004
16   .    17   esp_exchange                    8(rep-b):1 (m)        1.00E+000
15   .    16   partition_access                                      1.00E+000
.    .    15   file_scan             fr        NATION                1.00E+000
13   .    14   esp_exchange                    8(rep-b):1            1.00E+000
12   .    13   sort_partial_aggr_ro                                  1.00E+000
11   .    12   esp_exchange                    1:8(hash2)            1.00E+000
10   .    11   sort_partial_aggr_le                                  1.00E+000
9    7    10   hybrid_hash_join      u                               3.22E+006
8    .    9    partition_access                                      8.00E+007
.    .    8    file_scan             fr        PARTSUPP              8.00E+007
6    .    7    esp_exchange                    8(rep-b):8(hash2)     4.02E+004
3    5    6    nested_join                                           4.02E+004
4    .    5    partition_access                                      4.02E+004
.    .    4    index_scan            fr        SUIDX2                4.02E+004
2    .    3    esp_exchange                    8(rep-b):1 (m)        1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        NATION                1.00E+000

--- SQL operation complete.
>>obey optdml03(q12);
>>--------------------
>>--  TPCH Query 12 --
>>--------------------
>>prepare XX from
+>SELECT l_shipmode,
+>       SUM(CASE WHEN o_orderpriority ='1-URGENT'
+>                     OR o_orderpriority ='2-HIGH'
+>                THEN 1
+>                ELSE 0
+>           END) AS high_line_count,
+>        SUM(CASE WHEN o_orderpriority <> '1-URGENT'
+>                      AND o_orderpriority <> '2-HIGH'
+>                 THEN 1
+>                 ELSE 0
+>            END) AS low_line_count
+>FROM orders,lineitem
+>where o_orderkey = l_orderkey
+>      AND l_shipmode in ('MAIL','SHIP')
+>      AND l_commitdate < l_receiptdate
+>      AND l_shipdate < l_commitdate
+>      AND l_receiptdate >=  DATE '1994-01-01'
+>      AND l_receiptdate <  DATE '1994-01-01' + INTERVAL '1' YEAR
+>GROUP BY l_shipmode
+>ORDER BY l_shipmode;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               2.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.285E+003 
10  ESP_EXCHANGE      9   ?               2.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   2.285E+003 
09  SORT_PARTIAL_GRO  8   ?               2.000E+000   FUNKY_OPT_UNIQUE  1.019E-007   2.285E+003 
08  SORT              7   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.785E-008   2.285E+003 
07  ESP_EXCHANGE      6   ?               2.000E+000   FUNKY_OPT_UNIQUE  1.428E-004   2.285E+003 
06  HASH_PARTIAL_GRO  5   ?               2.000E+000   FUNKY_OPT_UNIQUE  3.314E-003   2.285E+003 
05  HYBRID_HASH_JOIN  4   2               2.892E+006   FUNKY_OPT_UNIQUE  5.457E-001   2.285E+003 
04  PARTITION_ACCESS  3   ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   4.312E+002 
03  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  4.312E+002   4.312E+002 
02  PARTITION_ACCESS  1   ?               2.892E+006   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    2.892E+006   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 11 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  2.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        2.00E+000
8    .    9    sort_partial_groupby                                  2.00E+000
7    .    8    sort                                                  2.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     2.00E+000
5    .    6    hash_partial_groupby                                  2.00E+000
4    2    5    hybrid_hash_join                                      2.89E+006
3    .    4    partition_access                                      1.50E+008
.    .    3    file_scan             fr        ORDERS                1.50E+008
1    .    2    partition_access                                      2.89E+006
.    .    1    file_scan             fr        LINEITEM              2.89E+006

--- SQL operation complete.
>>obey optdml03(q13);
>>--------------------
>>--  TPCH Query 13 --
>>--------------------
>>prepare XX from
+>
+>SELECT yr, sum(revenue) as revenue
+>  FROM
+>    (SELECT extract(year from o_orderdate) as yr,
+>       CAST(l_extendedprice * (1-l_discount) AS NUMERIC(18,2)) as revenue
+>FROM lineitem, orders
+>WHERE o_orderkey = l_orderkey
+>      AND o_clerk = 'Clerk#000000088'
+>      AND l_returnflag = 'R') as performance
+>GROUP BY yr
+>ORDER BY yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               7.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   4.314E+002 
10  ESP_EXCHANGE      9   ?               7.000E+000   FUNKY_OPT_UNIQUE  2.364E-004   4.314E+002 
09  SORT_PARTIAL_GRO  8   ?               7.000E+000   FUNKY_OPT_UNIQUE  6.233E-008   4.314E+002 
08  SORT              7   ?               7.000E+000   FUNKY_OPT_UNIQUE  3.666E-008   4.314E+002 
07  ESP_EXCHANGE      6   ?               7.000E+000   FUNKY_OPT_UNIQUE  1.047E-004   4.314E+002 
06  HASH_PARTIAL_GRO  5   ?               7.000E+000   FUNKY_OPT_UNIQUE  1.760E-006   4.314E+002 
05  NESTED_JOIN       2   4               1.481E+003   FUNKY_OPT_UNIQUE  1.722E-005   4.314E+002 
04  PARTITION_ACCESS  3   ?               9.873E-001   FUNKY_OPT_UNIQUE  0.000E+000   7.151E-001 
03  FILE_SCAN         ?   ?   LINEITEM    9.873E-001   FUNKY_OPT_UNIQUE  7.151E-001   7.151E-001 
02  PARTITION_ACCESS  1   ?               1.500E+003   FUNKY_OPT_UNIQUE  0.000E+000   4.307E+002 
01  FILE_SCAN         ?   ?   ORDERS      1.500E+003   FUNKY_OPT_UNIQUE  4.307E+002   4.307E+002 

--- 11 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  7.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        7.00E+000
8    .    9    sort_partial_groupby                                  7.00E+000
7    .    8    sort                                                  7.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     7.00E+000
5    .    6    hash_partial_groupby                                  7.00E+000
2    4    5    nested_join                                           1.48E+003
3    .    4    partition_access                                      9.87E-001
.    .    3    file_scan             fr        LINEITEM              9.87E-001
1    .    2    partition_access                                      1.50E+003
.    .    1    file_scan             fr        ORDERS                1.50E+003

--- SQL operation complete.
>>obey optdml03(q14);
>>--------------------
>>--  TPCH Query 14 --
>>--------------------
>>prepare XX from
+>SELECT 100.00 * SUM(CASE WHEN p_type = 'promo' -- p_type like 'PROMO%'
+>                         THEN (l_extendedprice * (1-l_discount))
+>                         ELSE 0
+>                    END) / 
+>        SUM((l_extendedprice*(1-l_discount))) AS promo_revenue
+>FROM  lineitem, part
+>WHERE l_partkey = p_partkey
+>      AND l_shipdate >=  DATE '1995-09-01'
+>      AND l_shipdate <  DATE '1995-09-01' + INTERVAL '1' MONTH;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

10  ROOT              9   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.930E+003 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.215E-008   1.930E+003 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.454E-005   1.930E+003 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  8.107E-003   1.930E+003 
06  HYBRID_HASH_JOIN  5   3               7.720E+006   FUNKY_OPT_UNIQUE  2.248E-001   1.930E+003 
05  PARTITION_ACCESS  4   ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.666E+001 
04  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  7.666E+001   7.666E+001 
03  ESP_EXCHANGE      2   ?               7.720E+006   FUNKY_OPT_UNIQUE  1.213E-001   1.853E+003 
02  PARTITION_ACCESS  1   ?               7.720E+006   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    7.720E+006   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 10 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

9    .    10   root                                                  1.00E+000
8    .    9    sort_partial_aggr_ro                                  1.00E+000
7    .    8    esp_exchange                    1:8(hash2)            1.00E+000
6    .    7    sort_partial_aggr_le                                  1.00E+000
5    3    6    hybrid_hash_join                                      7.72E+006
4    .    5    partition_access                                      2.00E+007
.    .    4    file_scan             fr        PART                  2.00E+007
2    .    3    esp_exchange                    8(hash2):8(hash2)     7.72E+006
1    .    2    partition_access                                      7.72E+006
.    .    1    file_scan             fr        LINEITEM              7.72E+006

--- SQL operation complete.
>>obey optdml03(q15);
>>--------------------
>>--  TPCH Query 15 --
>>--------------------
>>create view revenue (supplier_no, total_revenue) AS
+>     SELECT l_suppkey, SUM(l_extendedprice * (1-l_discount))
+>     FROM lineitem
+>     WHERE l_shipdate >= DATE '1996-01-01'
+>       AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
+>     GROUP BY l_suppkey;

--- SQL operation complete.
>>
>>prepare XX from
+>SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
+>FROM  supplier, revenue
+>WHERE s_suppkey = supplier_no
+>  AND total_revenue = (SELECT MAX(total_revenue)
+>                       FROM revenue)
+>ORDER BY s_suppkey;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

21  ROOT              20  ?               3.333E+005   FUNKY_OPT_UNIQUE  0.000E+000   3.711E+003 
20  ESP_EXCHANGE      19  ?               3.333E+005   FUNKY_OPT_UNIQUE  2.800E-009   3.711E+003 
19  SORT              18  ?               3.333E+005   FUNKY_OPT_UNIQUE  3.576E-002   3.711E+003 
18  HYBRID_HASH_JOIN  17  15              3.333E+005   FUNKY_OPT_UNIQUE  1.729E-002   3.711E+003 
17  PARTITION_ACCESS  16  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.239E+000 
16  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  4.239E+000   4.239E+000 
15  HYBRID_HASH_JOIN  14  9               3.333E+005   FUNKY_OPT_UNIQUE  7.304E-003   3.707E+003 
14  HASH_PARTIAL_GRO  13  ?               1.000E+006   FUNKY_OPT_UNIQUE  8.709E-002   1.853E+003 
13  ESP_EXCHANGE      12  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.842E-002   1.853E+003 
12  HASH_PARTIAL_GRO  11  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.944E-002   1.853E+003 
11  PARTITION_ACCESS  10  ?               2.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
10  FILE_SCAN         ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.682E-004   1.853E+003 
08  SORT_PARTIAL_AGG  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.853E+003 
07  ESP_EXCHANGE      6   ?               1.000E+000   FUNKY_OPT_UNIQUE  6.685E-005   1.853E+003 
06  SORT_PARTIAL_AGG  5   ?               1.000E+000   FUNKY_OPT_UNIQUE  8.575E-004   1.853E+003 
05  HASH_PARTIAL_GRO  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.651E-002   1.853E+003 
04  ESP_EXCHANGE      3   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.842E-002   1.853E+003 
03  HASH_PARTIAL_GRO  2   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.944E-002   1.853E+003 
02  PARTITION_ACCESS  1   ?               2.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 21 row(s) selected.
>>
>>drop view revenue;

--- SQL operation complete.
>>
>>--prepare XX from
>>--SELECT s_suppkey,s_name,s_address,s_phone, total_revenue
>>--FROM supplier, (SELECT l_suppkey, SUM(l_extendedprice * (1-l_discount))
>>--                FROM lineitem
>>--                WHERE l_shipdate >=  DATE '1996-01-01'
>>--                      AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
>>--                GROUP BY l_suppkey) revenue(supplier_no, total_revenue)
>>--WHERE s_suppkey = supplier_no
>>--      AND total_revenue = (SELECT MAX(total_revenue)
>>--                           FROM (SELECT l_suppkey,
>>--                                        sum(l_extendedprice * (1-l_discount))
>>--                                 FROM lineitem
>>--                                 WHERE l_shipdate >=  DATE '1996-01-01'
>>--                                 AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
>>--                                 GROUP BY l_suppkey)
>>--                           revenue(supplier_no, total_revenue))
>>--ORDER BY s_suppkey;
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

20   .    21   root                                                  3.33E+005
19   .    20   esp_exchange                    1:8(hash2) (m)        3.33E+005
18   .    19   sort                                                  3.33E+005
17   15   18   hybrid_hash_join      u                               3.33E+005
16   .    17   partition_access                                      1.00E+006
.    .    16   file_scan             fr        SUPPLIER              1.00E+006
14   9    15   hybrid_hash_join      u                               3.33E+005
13   .    14   hash_partial_groupby                                  1.00E+006
12   .    13   esp_exchange                    8(hash2):8(hash2)     1.48E+007
11   .    12   hash_partial_groupby                                  1.48E+007
10   .    11   partition_access                                      2.29E+007
.    .    10   file_scan             fr        LINEITEM              2.29E+007
8    .    9    esp_exchange                    8(rep-b):1            1.00E+000
7    .    8    sort_partial_aggr_ro                                  1.00E+000
6    .    7    esp_exchange                    1:8(hash2)            1.00E+000
5    .    6    sort_partial_aggr_le                                  1.00E+000
4    .    5    hash_partial_groupby                                  1.00E+006
3    .    4    esp_exchange                    8(hash2):8(hash2)     1.48E+007
2    .    3    hash_partial_groupby                                  1.48E+007
1    .    2    partition_access                                      2.29E+007
.    .    1    file_scan             fr        LINEITEM              2.29E+007

--- SQL operation complete.
>>obey optdml03(q16);
>>--------------------
>>--  TPCH Query 16 --
>>--------------------
>>prepare XX from
+>SELECT p_brand, p_type, p_size, COUNT(DISTINCT ps_suppkey) AS supplier_cnt
+>FROM partsupp, part
+>WHERE p_partkey = ps_partkey
+>      AND p_brand <> 'Brand#45'
+>      AND p_type NOT LIKE 'MEDIUM POLISHED%'
+>      AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
+>      AND ps_suppkey NOT IN (SELECT s_suppkey
+>                             FROM supplier
+>                             WHERE s_comment LIKE
+>                                  '%Better Business Bureau%Complaints%')
+>GROUP BY p_brand, p_type, p_size
+>ORDER BY supplier_cnt DESC, p_brand, p_type, p_size;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               2.840E+004   FUNKY_OPT_UNIQUE  0.000E+000   1.073E+002 
17  ESP_EXCHANGE      16  ?               2.840E+004   FUNKY_OPT_UNIQUE  2.800E-009   1.073E+002 
16  SORT              15  ?               2.840E+004   FUNKY_OPT_UNIQUE  1.679E-003   1.073E+002 
15  HASH_PARTIAL_GRO  14  ?               2.840E+004   FUNKY_OPT_UNIQUE  4.870E-004   1.073E+002 
14  ESP_EXCHANGE      13  ?               2.840E+004   FUNKY_OPT_UNIQUE  3.699E-004   1.073E+002 
13  HASH_PARTIAL_GRO  12  ?               2.840E+004   FUNKY_OPT_UNIQUE  2.361E-002   1.073E+002 
12  HASH_PARTIAL_GRO  11  ?               1.125E+007   FUNKY_OPT_UNIQUE  1.849E-001   1.073E+002 
11  ESP_EXCHANGE      10  ?               1.125E+007   FUNKY_OPT_UNIQUE  6.625E-002   1.071E+002 
10  HASH_PARTIAL_GRO  9   ?               1.125E+007   FUNKY_OPT_UNIQUE  4.624E-002   1.070E+002 
09  HYBRID_HASH_ANTI  8   3               1.125E+007   FUNKY_OPT_UNIQUE  1.923E-001   1.070E+002 
08  HYBRID_HASH_JOIN  7   5               1.200E+007   FUNKY_OPT_UNIQUE  4.002E-001   1.026E+002 
07  PARTITION_ACCESS  6   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.566E+001 
06  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  2.566E+001   2.566E+001 
05  PARTITION_ACCESS  4   ?               3.001E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.655E+001 
04  FILE_SCAN         ?   ?   PART        3.001E+006   FUNKY_OPT_UNIQUE  7.655E+001   7.655E+001 
03  ESP_EXCHANGE      2   ?               6.250E+004   FUNKY_OPT_UNIQUE  3.724E-003   4.232E+000 
02  PARTITION_ACCESS  1   ?               6.250E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.228E+000 
01  FILE_SCAN         ?   ?   SUPPLIER    6.250E+004   FUNKY_OPT_UNIQUE  4.228E+000   4.228E+000 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  2.83E+004
16   .    17   esp_exchange                    1:8(hash2) (m)        2.83E+004
15   .    16   sort                                                  2.83E+004
14   .    15   hash_partial_groupby                                  2.83E+004
13   .    14   esp_exchange                    8(hash2):8(hash2)     2.83E+004
12   .    13   hash_partial_groupby                                  2.83E+004
11   .    12   hash_partial_groupby                                  1.12E+007
10   .    11   esp_exchange                    8(hash2):8(hash2)     1.12E+007
9    .    10   hash_partial_groupby                                  1.12E+007
8    3    9    hybrid_hash_anti_sem                                  1.12E+007
7    5    8    hybrid_hash_join                                      1.20E+007
6    .    7    partition_access                                      8.00E+007
.    .    6    index_scan            fr        PSIDX1                8.00E+007
4    .    5    partition_access                                      3.00E+006
.    .    4    file_scan             fr        PART                  3.00E+006
2    .    3    esp_exchange                    8(rep-b):8(hash2)     6.25E+004
1    .    2    partition_access                                      6.25E+004
.    .    1    file_scan             fr        SUPPLIER              6.25E+004

--- SQL operation complete.
>>obey optdml03(q17);
>>--------------------
>>--  TPCH Query 17 --
>>--------------------
>>prepare XX from
+>SELECT CAST((SUM(l_extendedprice)/7.0) AS NUMERIC(18,2)) AS avg_yearly
+>FROM lineitem, part
+>WHERE p_partkey = l_partkey
+>      AND p_brand = 'Brand#23'
+>      AND p_container = 'MED BOX'
+>      AND l_quantity < (SELECT 0.2*avg(l_quantity)
+>                        FROM lineitem 
+>                        WHERE l_partkey = p_partkey);

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

15  ROOT              14  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   3.795E+003 
14  SORT_PARTIAL_AGG  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  5.268E-008   3.795E+003 
13  ESP_EXCHANGE      12  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.982E-005   3.795E+003 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.638E-003   3.795E+003 
11  HASH_GROUPBY      10  ?               6.000E+006   FUNKY_OPT_UNIQUE  1.455E-001   3.795E+003 
10  HYBRID_HASH_JOIN  9   7               1.800E+007   FUNKY_OPT_UNIQUE  3.429E+000   3.795E+003 
09  PARTITION_ACCESS  8   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.856E+003 
08  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.856E+003   1.856E+003 
07  ESP_EXCHANGE      6   ?               6.007E+005   FUNKY_OPT_UNIQUE  5.865E-002   1.935E+003 
06  HYBRID_HASH_JOIN  5   3               6.007E+005   FUNKY_OPT_UNIQUE  2.546E+000   1.935E+003 
05  PARTITION_ACCESS  4   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.855E+003 
04  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
03  ESP_EXCHANGE      2   ?               2.004E+004   FUNKY_OPT_UNIQUE  1.346E-003   7.652E+001 
02  PARTITION_ACCESS  1   ?               2.004E+004   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
01  FILE_SCAN         ?   ?   PART        2.004E+004   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 

--- 15 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

14   .    15   root                                                  1.00E+000
13   .    14   sort_partial_aggr_ro                                  1.00E+000
12   .    13   esp_exchange                    1:8(hash2)            1.00E+000
11   .    12   sort_partial_aggr_le                                  1.00E+000
10   .    11   hash_groupby                                          6.00E+006
9    7    10   hybrid_hash_join                                      1.80E+007
8    .    9    partition_access                                      5.99E+008
.    .    8    file_scan             fr        LINEITEM              5.99E+008
6    .    7    esp_exchange                    8(rep-b):8(hash2)     6.00E+005
5    3    6    hybrid_hash_join      u                               6.00E+005
4    .    5    partition_access                                      5.99E+008
.    .    4    file_scan             fr        LINEITEM              5.99E+008
2    .    3    esp_exchange                    8(rep-b):8(hash2)     2.00E+004
1    .    2    partition_access                                      2.00E+004
.    .    1    file_scan             fr        PART                  2.00E+004

--- SQL operation complete.
>>obey optdml03(q18);
>>--------------------
>>--  TPCH Query 18 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
+>                   SUM(l_quantity)
+>FROM customer, orders, lineitem
+>WHERE o_orderkey IN (SELECT l_orderkey
+>                     FROM lineitem
+>                     GROUP BY l_orderkey HAVING SUM(l_quantity) >  300)
+>      AND c_custkey = o_custkey
+>      AND o_orderkey = l_orderkey
+>GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+>ORDER BY o_totalprice DESC, o_orderdate;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.235E+003 
17  FIRSTN            16  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.235E+003 
16  ESP_EXCHANGE      15  ?               4.994E+007   FUNKY_OPT_UNIQUE  2.800E-009   4.235E+003 
15  SORT              14  ?               4.994E+007   FUNKY_OPT_UNIQUE  8.167E+000   4.235E+003 
14  HYBRID_HASH_JOIN  13  2               4.994E+007   FUNKY_OPT_UNIQUE  8.793E-001   4.227E+003 
13  ESP_EXCHANGE      12  ?               4.994E+007   FUNKY_OPT_UNIQUE  9.255E-001   4.151E+003 
12  HYBRID_HASH_JOIN  11  9               4.994E+007   FUNKY_OPT_UNIQUE  1.357E+000   4.150E+003 
11  PARTITION_ACCESS  10  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   4.315E+002 
10  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  4.315E+002   4.315E+002 
09  HYBRID_HASH_JOIN  8   5               4.994E+007   FUNKY_OPT_UNIQUE  9.701E-001   3.717E+003 
08  PARTITION_ACCESS  7   ?               1.498E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.858E+003 
07  SORT_GROUPBY      6   ?               1.498E+008   FUNKY_OPT_UNIQUE  2.416E+000   1.858E+003 
06  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
05  PARTITION_ACCESS  4   ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
04  SORT_GROUPBY      3   ?               4.994E+007   FUNKY_OPT_UNIQUE  1.902E+000   1.857E+003 
03  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.539E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  7.539E+001   7.539E+001 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  4.99E+007
16   .    17   firstn                                                4.99E+007
15   .    16   esp_exchange                    1:8(hash2) (m)        4.99E+007
14   .    15   sort                                                  4.99E+007
13   2    14   hybrid_hash_join                                      4.99E+007
12   .    13   esp_exchange                    8(hash2):8(hash2)     4.99E+007
11   9    12   hybrid_hash_join                                      4.99E+007
10   .    11   partition_access                                      1.50E+008
.    .    10   file_scan             fr        ORDERS                1.50E+008
8    5    9    hybrid_hash_join                                      4.99E+007
7    .    8    partition_access                                      1.49E+008
6    .    7    sort_groupby                                          1.49E+008
.    .    6    file_scan                       LINEITEM              5.99E+008
4    .    5    partition_access                                      4.99E+007
3    .    4    sort_groupby                                          4.99E+007
.    .    3    file_scan                       LINEITEM              5.99E+008
1    .    2    partition_access                                      1.50E+007
.    .    1    file_scan             fr        CUSTOMER              1.50E+007

--- SQL operation complete.
>>obey optdml03(q19);
>>--------------------
>>--  TPCH Query 19 --
>>--------------------
>>prepare XX from
+>SELECT CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue
+>FROM lineitem, part
+>WHERE 
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#24'
+>    AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
+>    AND l_quantity >= 1 AND l_quantity <= 1 + 10
+>    AND p_size BETWEEN 1 AND 5
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   )
+> OR
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#16'
+>    AND p_container IN ( 'MED CASE', 'MED BOX', 'MED PACK', 'MED PKG' )
+>    AND l_quantity >= 10 AND l_quantity <= 10 + 10
+>    AND p_size BETWEEN 1 AND 10
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   )
+> OR
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#42'
+>    AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
+>    AND l_quantity >= 20 AND l_quantity <= 20 + 10
+>    AND p_size BETWEEN 1 AND 15
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   );

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

10  ROOT              9   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.931E+003 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.931E+003 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  6.685E-005   1.931E+003 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.768E-005   1.931E+003 
06  HYBRID_HASH_JOIN  5   2               2.380E+004   FUNKY_OPT_UNIQUE  5.678E-001   1.931E+003 
05  ESP_EXCHANGE      4   ?               4.281E+007   FUNKY_OPT_UNIQUE  7.346E-001   1.854E+003 
04  PARTITION_ACCESS  3   ?               4.281E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
03  FILE_SCAN         ?   ?   LINEITEM    4.281E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
02  PARTITION_ACCESS  1   ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.665E+001 
01  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  7.665E+001   7.665E+001 

--- 10 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

9    .    10   root                                                  1.00E+000
8    .    9    sort_partial_aggr_ro                                  1.00E+000
7    .    8    esp_exchange                    1:8(hash2)            1.00E+000
6    .    7    sort_partial_aggr_le                                  1.00E+000
5    2    6    hybrid_hash_join                                      2.38E+004
4    .    5    esp_exchange                    8(hash2):8(hash2)     4.28E+007
3    .    4    partition_access                                      4.28E+007
.    .    3    file_scan             fr        LINEITEM              4.28E+007
1    .    2    partition_access                                      2.00E+007
.    .    1    file_scan             fr        PART                  2.00E+007

--- SQL operation complete.
>>obey optdml03(q20);
>>--------------------
>>--  TPCH Query 20 --
>>--------------------
>>prepare XX from
+>SELECT s_name, s_address
+>FROM supplier, nation
+>WHERE s_suppkey IN (SELECT ps_suppkey
+>                    FROM partsupp
+>                    WHERE ps_partkey IN (SELECT p_partkey
+>                                         FROM part
+>                                         WHERE p_name LIKE 'forest%')
+>                      AND ps_availqty > (SELECT 0.5 * sum(l_quantity)
+>                                         FROM lineitem
+>                                         WHERE l_partkey = ps_partkey
+>                                           AND l_suppkey = ps_suppkey
+>                                           AND l_shipdate >= DATE '1994-01-01'
+>                                           AND l_shipdate <  DATE '1994-01-01'
+>                                                              + INTERVAL '1' YEAR)
+>                     )
+>  AND s_nationkey = n_nationkey
+>  AND n_name = 'CANADA'
+>ORDER by s_name;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   2.314E+003 
21  ESP_EXCHANGE      20  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.800E-009   2.314E+003 
20  SORT              19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.888E-003   2.314E+003 
19  HYBRID_HASH_SEMI  18  12              4.028E+004   FUNKY_OPT_UNIQUE  5.476E-003   2.314E+003 
18  NESTED_JOIN       15  17              4.028E+004   FUNKY_OPT_UNIQUE  4.158E-004   4.234E+000 
17  PARTITION_ACCESS  16  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.232E+000 
16  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.232E+000   4.232E+000 
15  ESP_EXCHANGE      14  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
14  PARTITION_ACCESS  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
13  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
12  ESP_EXCHANGE      11  ?               1.898E+006   FUNKY_OPT_UNIQUE  1.351E-002   2.309E+003 
11  HASH_GROUPBY      10  ?               1.898E+006   FUNKY_OPT_UNIQUE  3.585E-002   2.309E+003 
10  HYBRID_HASH_JOIN  9   7               5.696E+006   FUNKY_OPT_UNIQUE  3.982E-001   2.309E+003 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
08  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
07  ESP_EXCHANGE      6   ?               5.696E+006   FUNKY_OPT_UNIQUE  8.020E-002   1.931E+003 
06  HYBRID_HASH_JOIN  5   3               5.696E+006   FUNKY_OPT_UNIQUE  4.990E-001   1.930E+003 
05  PARTITION_ACCESS  4   ?               9.114E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
04  FILE_SCAN         ?   ?   LINEITEM    9.114E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
03  ESP_EXCHANGE      2   ?               1.250E+006   FUNKY_OPT_UNIQUE  7.022E-002   7.659E+001 
02  PARTITION_ACCESS  1   ?               1.250E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
01  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 

--- 22 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

21   .    22   root                                                  4.02E+004
20   .    21   esp_exchange                    1:8(hash2) (m)        4.02E+004
19   .    20   sort                                                  4.02E+004
18   12   19   hybrid_hash_semi_joi                                  4.02E+004
15   17   18   nested_join                                           4.02E+004
16   .    17   partition_access                                      4.02E+004
.    .    16   file_scan             fr        SUPPLIER              4.02E+004
14   .    15   esp_exchange                    8(rep-b):1 (m)        1.00E+000
13   .    14   partition_access                                      1.00E+000
.    .    13   file_scan             fr        NATION                1.00E+000
11   .    12   esp_exchange                    8(hash2):8(hash2)     1.89E+006
10   .    11   hash_groupby                                          1.89E+006
9    7    10   hybrid_hash_join                                      5.69E+006
8    .    9    partition_access                                      8.00E+007
.    .    8    file_scan             fr        PARTSUPP              8.00E+007
6    .    7    esp_exchange                    8(hash2):8(hash2)     5.69E+006
5    3    6    hybrid_hash_join                                      5.69E+006
4    .    5    partition_access                                      9.11E+007
.    .    4    file_scan             fr        LINEITEM              9.11E+007
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.25E+006
1    .    2    partition_access                                      1.25E+006
.    .    1    file_scan             fr        PART                  1.25E+006

--- SQL operation complete.
>>obey optdml03(q21);
>>--------------------
>>--  TPCH Query 21 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] s_name, count(*) as numwait
+>FROM supplier, lineitem l1, part, orders, nation
+>WHERE s_suppkey = l1.l_suppkey
+>  AND l1.l_partkey = p_partkey
+>  AND o_orderkey = l1.l_orderkey
+>  AND o_orderstatus = 'F'
+>  AND l1.l_receiptdate > l1.l_commitdate
+>  AND EXISTS (SELECT *
+>              FROM lineitem l2
+>              WHERE l2.l_orderkey = l1.l_orderkey
+>                AND l2.l_suppkey <> l1.l_suppkey)
+>  AND NOT EXISTS (SELECT *
+>                  FROM lineitem l3
+>                  WHERE l3.l_orderkey = l1.l_orderkey
+>                    AND l3.l_suppkey <> l1.l_suppkey
+>                    AND l3.l_receiptdate > l3.l_commitdate)
+>  AND s_nationkey = n_nationkey
+>  AND n_name = 'SAUDI ARABIA'
+>GROUP BY s_name
+>ORDER BY numwait DESC, s_name;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.358E+003 
30  FIRSTN            29  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.358E+003 
29  ESP_EXCHANGE      28  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.800E-009   4.358E+003 
28  SORT              27  ?               1.000E+006   FUNKY_OPT_UNIQUE  6.661E-002   4.358E+003 
27  HASH_PARTIAL_GRO  26  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.570E-002   4.358E+003 
26  ESP_EXCHANGE      25  ?               1.231E+006   FUNKY_OPT_UNIQUE  6.349E-003   4.358E+003 
25  HASH_PARTIAL_GRO  24  ?               1.231E+006   FUNKY_OPT_UNIQUE  4.304E-003   4.358E+003 
24  HYBRID_HASH_JOIN  23  21              1.308E+006   FUNKY_OPT_UNIQUE  6.558E-002   4.358E+003 
23  PARTITION_ACCESS  22  ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.826E+000 
22  INDEX_SCAN        ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  4.826E+000   4.826E+000 
21  ESP_EXCHANGE      20  ?               1.308E+006   FUNKY_OPT_UNIQUE  2.526E-002   4.353E+003 
20  HYBRID_HASH_SEMI  19  3               1.308E+006   FUNKY_OPT_UNIQUE  9.847E+000   4.353E+003 
19  HYBRID_HASH_ANTI  18  5               1.308E+006   FUNKY_OPT_UNIQUE  7.514E-001   4.145E+003 
18  HYBRID_HASH_JOIN  17  7               3.925E+006   FUNKY_OPT_UNIQUE  2.680E-001   2.290E+003 
17  HYBRID_HASH_JOIN  16  14              8.049E+006   FUNKY_OPT_UNIQUE  9.099E-001   1.859E+003 
16  PARTITION_ACCESS  15  ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
15  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
14  ESP_EXCHANGE      13  ?               4.028E+004   FUNKY_OPT_UNIQUE  6.677E-003   4.240E+000 
13  NESTED_JOIN       10  12              4.028E+004   FUNKY_OPT_UNIQUE  2.658E-004   4.234E+000 
12  PARTITION_ACCESS  11  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.232E+000 
11  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.232E+000   4.232E+000 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
09  PARTITION_ACCESS  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
08  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
07  PARTITION_ACCESS  6   ?               7.307E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.309E+002 
06  FILE_SCAN         ?   ?   ORDERS      7.307E+007   FUNKY_OPT_UNIQUE  4.309E+002   4.309E+002 
05  PARTITION_ACCESS  4   ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
04  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
03  ESP_EXCHANGE      2   ?               5.994E+008   FUNKY_OPT_UNIQUE  5.934E+000   1.982E+002 
02  PARTITION_ACCESS  1   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.923E+002 
01  INDEX_SCAN        ?   ?   LINEITEM)   5.994E+008   FUNKY_OPT_UNIQUE  1.923E+002   1.923E+002 

--- 31 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

30   .    31   root                                                  1.00E+006
29   .    30   firstn                                                1.00E+006
28   .    29   esp_exchange                    1:8(hash2) (m)        1.00E+006
27   .    28   sort                                                  1.00E+006
26   .    27   hash_partial_groupby                                  1.00E+006
25   .    26   esp_exchange                    8(hash2):8(hash2)     1.23E+006
24   .    25   hash_partial_groupby                                  1.23E+006
23   21   24   hybrid_hash_join                                      1.30E+006
22   .    23   partition_access                                      2.00E+007
.    .    22   index_scan            fr        PAIDX1                2.00E+007
20   .    21   esp_exchange                    8(hash2):8(hash2)     1.30E+006
19   3    20   hybrid_hash_semi_joi                                  1.30E+006
18   5    19   hybrid_hash_anti_sem                                  1.30E+006
17   7    18   hybrid_hash_join                                      3.92E+006
16   14   17   hybrid_hash_join      u                               8.04E+006
15   .    16   partition_access                                      1.99E+008
.    .    15   file_scan             fr        LINEITEM              1.99E+008
13   .    14   esp_exchange                    8(rep-b):8(hash2)     4.02E+004
10   12   13   nested_join                                           4.02E+004
11   .    12   partition_access                                      4.02E+004
.    .    11   file_scan             fr        SUPPLIER              4.02E+004
9    .    10   esp_exchange                    8(rep-b):1 (m)        1.00E+000
8    .    9    partition_access                                      1.00E+000
.    .    8    file_scan             fr        NATION                1.00E+000
6    .    7    partition_access                                      7.30E+007
.    .    6    file_scan             fr        ORDERS                7.30E+007
4    .    5    partition_access                                      1.99E+008
.    .    4    file_scan             fr        LINEITEM              1.99E+008
2    .    3    esp_exchange                    8(hash2):8(hash2)     5.99E+008
1    .    2    partition_access                                      5.99E+008
.    .    1    index_scan            fr        LIIDX3                5.99E+008

--- SQL operation complete.
>>obey optdml03(q22);
>>--------------------
>>--  TPCH Query 22 --
>>--------------------
>>prepare XX from
+>SELECT cntrycode, count(*) AS numcust, sum(c_acctbal) AS totacctbal
+>FROM (SELECT SUBSTRING(c_phone FROM 1 FOR 2) AS cntrycode, c_acctbal
+>      FROM customer
+>      WHERE SUBSTRING(c_phone FROM 1 FOR 2) IN ('13','17','18','23','29','30','31')
+>        AND c_acctbal > (SELECT AVG(c_acctbal)
+>                         FROM customer
+>                         WHERE c_acctbal > 0.00
+>                           AND SUBSTRING (c_phone FROM 1 FOR 2) IN
+>                                               ('13','17','18','23','29','30','31'))
+>        AND NOT EXISTS (SELECT *
+>                        FROM orders
+>                        WHERE o_custkey = c_custkey)
+>       ) AS custsale
+>GROUP BY cntrycode
+>ORDER BY cntrycode;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               1.000E+002   FUNKY_OPT_UNIQUE  0.000E+000   1.873E+002 
17  ESP_EXCHANGE      16  ?               1.000E+002   FUNKY_OPT_UNIQUE  2.800E-009   1.873E+002 
16  SORT              15  ?               1.300E+001   FUNKY_OPT_UNIQUE  1.123E-007   1.873E+002 
15  HASH_PARTIAL_GRO  14  ?               1.300E+001   FUNKY_OPT_UNIQUE  6.311E-007   1.873E+002 
14  ESP_EXCHANGE      13  ?               1.000E+002   FUNKY_OPT_UNIQUE  1.256E-004   1.873E+002 
13  HASH_PARTIAL_GRO  12  ?               1.000E+002   FUNKY_OPT_UNIQUE  1.755E-003   1.873E+002 
12  HYBRID_HASH_ANTI  11  2               1.653E+006   FUNKY_OPT_UNIQUE  4.132E-001   1.873E+002 
11  NESTED_JOIN       8   10              4.960E+006   FUNKY_OPT_UNIQUE  2.430E-002   1.507E+002 
10  PARTITION_ACCESS  9   ?               4.960E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.532E+001 
09  FILE_SCAN         ?   ?   CUSTOMER    4.960E+006   FUNKY_OPT_UNIQUE  7.532E+001   7.532E+001 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.682E-004   7.536E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   7.536E+001 
06  SPLIT_TOP         5   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   7.536E+001 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   7.536E+001 
04  SORT_PARTIAL_AGG  3   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.281E-002   7.536E+001 
03  FILE_SCAN         ?   ?   CUSTOMER    1.352E+007   FUNKY_OPT_UNIQUE  7.533E+001   7.533E+001 
02  PARTITION_ACCESS  1   ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.619E+001 
01  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.619E+001   3.619E+001 

--- 18 row(s) selected.
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  1.00E+002
16   .    17   esp_exchange                    1:8(hash2) (m)        1.00E+002
15   .    16   sort                                                  1.29E+001
14   .    15   hash_partial_groupby                                  1.29E+001
13   .    14   esp_exchange                    8(hash2):8(hash2)     1.00E+002
12   .    13   hash_partial_groupby                                  1.00E+002
11   2    12   hybrid_hash_anti_sem                                  1.65E+006
8    10   11   nested_join                                           4.96E+006
9    .    10   partition_access                                      4.96E+006
.    .    9    file_scan             fr        CUSTOMER              4.96E+006
7    .    8    esp_exchange                    8(rep-b):1            1.00E+000
6    .    7    sort_partial_aggr_ro                                  1.00E+000
5    .    6    split_top                       1:8(hash2)            1.00E+000
4    .    5    partition_access                                      1.00E+000
3    .    4    sort_partial_aggr_le                                  1.00E+000
.    .    3    file_scan                       CUSTOMER              1.35E+007
1    .    2    partition_access                                      1.50E+008
.    .    1    index_scan            fr        ORDIDX2               1.50E+008

--- SQL operation complete.
>>
>>-- now test old scan optimizer
>>control query default fso_to_use '0';

--- SQL operation complete.
>>control query default query_cache '0';

--- SQL operation complete.
>>
>>obey optdml03(q01);
>>--------------------
>>--  TPCH Query 01 --
>>--------------------
>>prepare XX from
+>SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty,
+>       CAST(SUM(l_extendedprice) AS NUMERIC(18,2)) AS sum_base_price,
+>       CAST(SUM(l_extendedprice * (1-l_discount)) AS NUMERIC(18,2)) AS sum_disc_price,
+>       CAST(SUM(l_extendedprice * (1-l_discount) * (1 + l_tax))
+>            AS NUMERIC(18,2)) AS sum_charge,
+>       AVG(l_quantity) AS avg_qty,
+>       AVG(l_extendedprice) AS avg_price,
+>       AVG(CAST(l_discount AS NUMERIC (10,3))) AS avg_disc,
+>       COUNT(*) AS count_order
+>FROM lineitem
+>WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
+>GROUP BY l_returnflag, l_linestatus
+>ORDER BY l_returnflag, l_linestatus;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

08  ROOT              7   ?               6.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.860E+003 
07  ESP_EXCHANGE      6   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.860E+003 
06  SORT_PARTIAL_GRO  5   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.757E-007   1.860E+003 
05  SORT              4   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.204E-007   1.860E+003 
04  ESP_EXCHANGE      3   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.536E-004   1.860E+003 
03  HASH_PARTIAL_GRO  2   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.343E+000   1.860E+003 
02  PARTITION_ACCESS  1   ?               5.864E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.858E+003 
01  FILE_SCAN         ?   ?   LINEITEM    5.864E+008   FUNKY_OPT_UNIQUE  1.858E+003   1.858E+003 

--- 8 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

7    .    8    root                                                  6.00E+000
6    .    7    esp_exchange                    1:8(hash2) (m)        6.00E+000
5    .    6    sort_partial_groupby                                  6.00E+000
4    .    5    sort                                                  6.00E+000
3    .    4    esp_exchange                    8(hash2):8(hash2)     6.00E+000
2    .    3    hash_partial_groupby                                  6.00E+000
1    .    2    partition_access                                      5.86E+008
.    .    1    file_scan             fr        LINEITEM              5.86E+008

--- SQL operation complete.
>>obey optdml03(q02);
>>--------------------
>>--  TPCH Query 02 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address,
+>       s_phone, s_comment
+>FROM part,supplier,partsupp, nation, region
+>WHERE p_partkey = ps_partkey
+>      AND s_suppkey = ps_suppkey
+>      AND p_size = 15
+>      AND p_type like  '%BRASS'
+>      AND s_nationkey = n_nationkey
+>      AND n_regionkey = r_regionkey
+>      AND r_name = 'EUROPE'
+>      AND ps_supplycost = (SELECT MIN(ps_supplycost)
+>                           FROM partsupp, supplier, nation, region
+>                           WHERE p_partkey = ps_partkey
+>                                 AND s_suppkey = ps_suppkey
+>                                 AND s_nationkey = n_nationkey
+>                                 AND n_regionkey = r_regionkey
+>                                 AND r_name = 'EUROPE')
+>ORDER BY s_acctbal desc, n_name, s_name, p_partkey;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

39  ROOT              38  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.024E+002 
38  FIRSTN            37  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.024E+002 
37  ESP_EXCHANGE      36  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.024E+002 
36  SORT              35  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.540E-007   1.024E+002 
35  HASH_GROUPBY      34  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.200E-004   1.024E+002 
34  HYBRID_HASH_JOIN  33  3               1.598E+004   FUNKY_OPT_UNIQUE  1.931E-003   1.024E+002 
33  HYBRID_HASH_JOIN  32  6               7.991E+004   FUNKY_OPT_UNIQUE  3.148E-003   1.024E+002 
32  HYBRID_HASH_JOIN  31  29              7.991E+004   FUNKY_OPT_UNIQUE  1.957E-002   1.024E+002 
31  PARTITION_ACCESS  30  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.245E+000 
30  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  4.245E+000   4.245E+000 
29  ESP_EXCHANGE      28  ?               7.991E+004   FUNKY_OPT_UNIQUE  2.741E-003   9.816E+001 
28  NESTED_JOIN       24  27              7.991E+004   FUNKY_OPT_UNIQUE  9.830E-004   9.815E+001 
27  SPLIT_TOP         26  ?               4.000E+000   FUNKY_OPT_UNIQUE  9.689E-004   9.496E+000 
26  PARTITION_ACCESS  25  ?               4.000E+000   FUNKY_OPT_UNIQUE  9.689E-004   9.496E+000 
25  FILE_SCAN         ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  9.495E+000   9.495E+000 
24  HYBRID_HASH_JOIN  23  9               1.997E+004   FUNKY_OPT_UNIQUE  9.324E-004   8.866E+001 
23  HYBRID_HASH_JOIN  22  12              9.989E+004   FUNKY_OPT_UNIQUE  1.567E-003   8.865E+001 
22  HYBRID_HASH_JOIN  21  18              9.989E+004   FUNKY_OPT_UNIQUE  5.027E-003   8.865E+001 
21  ESP_EXCHANGE      20  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
20  PARTITION_ACCESS  19  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
19  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
18  ESP_EXCHANGE      17  ?               9.989E+004   FUNKY_OPT_UNIQUE  2.955E-003   8.839E+001 
17  NESTED_JOIN       14  16              9.989E+004   FUNKY_OPT_UNIQUE  1.113E-003   8.839E+001 
16  PARTITION_ACCESS  15  ?               4.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.186E+001 
15  FILE_SCAN         ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  1.186E+001   1.186E+001 
14  PARTITION_ACCESS  13  ?               2.497E+004   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
13  FILE_SCAN         ?   ?   PART        2.497E+004   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
12  ESP_EXCHANGE      11  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.940E-004   1.478E-003 
11  PARTITION_ACCESS  10  ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
10  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
08  PARTITION_ACCESS  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
07  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.940E-004   1.678E-003 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
04  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
01  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 

--- 39 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

38   .    39   root                                                  1.00E+000
37   .    38   firstn                                                1.00E+000
36   .    37   esp_exchange                    1:8(hash2) (m)        1.00E+000
35   .    36   sort                                                  1.00E+000
34   .    35   hash_groupby                                          1.00E+000
33   3    34   hybrid_hash_join      u                               1.59E+004
32   6    33   hybrid_hash_join      u                               7.99E+004
31   29   32   hybrid_hash_join                                      7.99E+004
30   .    31   partition_access                                      1.00E+006
.    .    30   file_scan             fr        SUPPLIER              1.00E+006
28   .    29   esp_exchange                    8(hash2):8(hash2)     7.99E+004
24   27   28   nested_join                                           7.99E+004
26   .    27   split_top                       8(rep-n):8(hash2)     4.00E+000
25   .    26   partition_access                                      4.00E+000
.    .    25   file_scan             fr        PARTSUPP              4.00E+000
23   9    24   hybrid_hash_join      u                               1.99E+004
22   12   23   hybrid_hash_join      u                               9.98E+004
21   18   22   hybrid_hash_join                                      9.98E+004
20   .    21   esp_exchange                    8(hash2):8(hash2)     1.00E+006
19   .    20   partition_access                                      1.00E+006
.    .    19   index_scan            fr        SUIDX2                1.00E+006
17   .    18   esp_exchange                    8(hash2):8(hash2)     9.98E+004
14   16   17   nested_join                                           9.98E+004
15   .    16   partition_access                                      4.00E+000
.    .    15   file_scan             fr        PARTSUPP              4.00E+000
13   .    14   partition_access                                      2.49E+004
.    .    13   file_scan             fr        PART                  2.49E+004
11   .    12   esp_exchange                    8(rep-b):1 (m)        2.50E+001
10   .    11   partition_access                                      2.50E+001
.    .    10   file_scan             fr        NATION                2.50E+001
8    .    9    esp_exchange                    8(rep-b):1 (m)        1.00E+000
7    .    8    partition_access                                      1.00E+000
.    .    7    file_scan             fr        REGION                1.00E+000
5    .    6    esp_exchange                    8(rep-b):1 (m)        2.50E+001
4    .    5    partition_access                                      2.50E+001
.    .    4    file_scan             fr        NATION                2.50E+001
2    .    3    esp_exchange                    8(rep-b):1 (m)        1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        REGION                1.00E+000

--- SQL operation complete.
>>obey optdml03(q03);
>>--------------------
>>--  TPCH Query 03 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 10] l_orderkey, 
+>       CAST(SUM(l_extendedprice*(1-l_discount))AS NUMERIC(18,2)) AS revenue,
+>       o_orderdate, o_shippriority
+>FROM customer,orders,lineitem
+>WHERE c_mktsegment = 'BUILDING'
+>      AND c_custkey = o_custkey
+>      AND l_orderkey = o_orderkey
+>      AND o_orderdate < DATE '1995-03-15'
+>      AND l_shipdate > DATE '1995-03-15'
+>GROUP BY l_orderkey, o_orderdate, o_shippriority
+>ORDER BY revenue DESC, o_orderdate;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

14  ROOT              13  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.370E+003 
13  FIRSTN            12  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.370E+003 
12  ESP_EXCHANGE      11  ?               4.717E+007   FUNKY_OPT_UNIQUE  2.800E-009   2.370E+003 
11  SORT              10  ?               4.717E+007   FUNKY_OPT_UNIQUE  5.491E+000   2.370E+003 
10  HASH_GROUPBY      9   ?               4.717E+007   FUNKY_OPT_UNIQUE  5.888E-001   2.364E+003 
09  HYBRID_HASH_JOIN  8   6               4.717E+007   FUNKY_OPT_UNIQUE  1.959E+000   2.364E+003 
08  PARTITION_ACCESS  7   ?               3.231E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.855E+003 
07  FILE_SCAN         ?   ?   LINEITEM    3.231E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
06  HYBRID_HASH_JOIN  5   3               2.187E+007   FUNKY_OPT_UNIQUE  5.088E-001   5.071E+002 
05  PARTITION_ACCESS  4   ?               7.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.311E+002 
04  FILE_SCAN         ?   ?   ORDERS      7.291E+007   FUNKY_OPT_UNIQUE  4.311E+002   4.311E+002 
03  ESP_EXCHANGE      2   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.682E-001   7.546E+001 
02  PARTITION_ACCESS  1   ?               3.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.529E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    3.000E+006   FUNKY_OPT_UNIQUE  7.529E+001   7.529E+001 

--- 14 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

13   .    14   root                                                  4.71E+007
12   .    13   firstn                                                4.71E+007
11   .    12   esp_exchange                    1:8(hash2) (m)        4.71E+007
10   .    11   sort                                                  4.71E+007
9    .    10   hash_groupby                                          4.71E+007
8    6    9    hybrid_hash_join                                      4.71E+007
7    .    8    partition_access                                      3.23E+008
.    .    7    file_scan             fr        LINEITEM              3.23E+008
5    3    6    hybrid_hash_join      u                               2.18E+007
4    .    5    partition_access                                      7.29E+007
.    .    4    file_scan             fr        ORDERS                7.29E+007
2    .    3    esp_exchange                    8(rep-b):8(hash2)     3.00E+006
1    .    2    partition_access                                      3.00E+006
.    .    1    file_scan             fr        CUSTOMER              3.00E+006

--- SQL operation complete.
>>obey optdml03(q04);
>>--------------------
>>--  TPCH Query 04 --
>>--------------------
>>prepare XX from
+>SELECT o_orderpriority, COUNT(*) as order_count
+>FROM orders
+>WHERE o_orderdate >= DATE '1993-07-01'
+>      AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
+>      AND EXISTS (SELECT *
+>                  FROM lineitem
+>                  WHERE l_orderkey = o_orderkey
+>                        AND l_commitdate < l_receiptdate)
+>GROUP BY o_orderpriority
+>ORDER BY o_orderpriority;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               5.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.285E+003 
10  ESP_EXCHANGE      9   ?               5.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   2.285E+003 
09  SORT_PARTIAL_GRO  8   ?               5.000E+000   FUNKY_OPT_UNIQUE  9.587E-008   2.285E+003 
08  SORT              7   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.381E-008   2.285E+003 
07  ESP_EXCHANGE      6   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.343E-004   2.285E+003 
06  HASH_PARTIAL_GRO  5   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.877E-003   2.285E+003 
05  HYBRID_HASH_SEMI  4   2               5.798E+006   FUNKY_OPT_UNIQUE  5.484E-001   2.285E+003 
04  PARTITION_ACCESS  3   ?               5.798E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.308E+002 
03  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  4.308E+002   4.308E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    1.997E+008   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 11 row(s) selected.
>>
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  5.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        5.00E+000
8    .    9    sort_partial_groupby                                  5.00E+000
7    .    8    sort                                                  5.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     5.00E+000
5    .    6    hash_partial_groupby                                  5.00E+000
4    2    5    hybrid_hash_semi_joi                                  5.79E+006
3    .    4    partition_access                                      5.79E+006
.    .    3    file_scan             fr        ORDERS                5.79E+006
1    .    2    partition_access                                      1.99E+008
.    .    1    file_scan             fr        LINEITEM              1.99E+008

--- SQL operation complete.
>>obey optdml03(q05);
>>--------------------
>>--  TPCH Query 05 --
>>--------------------
>>prepare XX from
+>SELECT n_name, 
+>       CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue
+>FROM customer,orders,lineitem,supplier,nation, region
+>WHERE c_custkey = o_custkey
+>      AND o_orderkey = l_orderkey
+>      AND l_suppkey = s_suppkey
+>      AND c_nationkey= s_nationkey
+>      AND s_nationkey = n_nationkey
+>      AND n_regionkey = r_regionkey
+>      AND r_name =  'ASIA'
+>      AND o_orderdate >=  DATE '1994-01-01'
+>      AND o_orderdate <  DATE '1994-01-01' + INTERVAL '1' YEAR
+>GROUP BY n_name
+>ORDER BY revenue desc;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   2.292E+003 
28  ESP_EXCHANGE      27  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.800E-009   2.292E+003 
27  SORT              26  ?               2.500E+001   FUNKY_OPT_UNIQUE  3.854E-007   2.292E+003 
26  HASH_PARTIAL_GRO  25  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.141E-007   2.292E+003 
25  ESP_EXCHANGE      24  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.794E-004   2.292E+003 
24  HASH_PARTIAL_GRO  23  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.360E-003   2.292E+003 
23  HYBRID_HASH_JOIN  22  3               7.302E+005   FUNKY_OPT_UNIQUE  1.831E-001   2.292E+003 
22  HYBRID_HASH_JOIN  21  19              1.825E+007   FUNKY_OPT_UNIQUE  3.466E+000   2.291E+003 
21  PARTITION_ACCESS  20  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.856E+003 
20  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.856E+003   1.856E+003 
19  ESP_EXCHANGE      18  ?               4.563E+006   FUNKY_OPT_UNIQUE  9.239E-002   4.315E+002 
18  HYBRID_HASH_JOIN  17  14              4.563E+006   FUNKY_OPT_UNIQUE  1.321E-001   4.314E+002 
17  ESP_EXCHANGE      16  ?               2.281E+007   FUNKY_OPT_UNIQUE  2.262E-001   4.310E+002 
16  PARTITION_ACCESS  15  ?               2.281E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.308E+002 
15  FILE_SCAN         ?   ?   ORDERS      2.281E+007   FUNKY_OPT_UNIQUE  4.308E+002   4.308E+002 
14  ESP_EXCHANGE      13  ?               3.000E+006   FUNKY_OPT_UNIQUE  6.434E-002   2.175E-001 
13  NESTED_JOIN       10  12              3.000E+006   FUNKY_OPT_UNIQUE  2.111E-002   1.531E-001 
12  PARTITION_ACCESS  11  ?               6.000E+005   FUNKY_OPT_UNIQUE  0.000E+000   1.298E-001 
11  INDEX_SCAN        ?   ?   CUSTOMER    6.000E+005   FUNKY_OPT_UNIQUE  1.298E-001   1.298E-001 
10  ESP_EXCHANGE      9   ?               5.000E+000   FUNKY_OPT_UNIQUE  3.452E-004   2.276E-003 
09  MERGE_JOIN        5   8               5.000E+000   FUNKY_OPT_UNIQUE  1.120E-006   1.931E-003 
08  SORT              7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.675E-006   1.289E-003 
07  PARTITION_ACCESS  6   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
06  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
03  ESP_EXCHANGE      2   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.397E-002   3.265E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 

--- 29 row(s) selected.
>>
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  2.50E+001
27   .    28   esp_exchange                    1:8(hash2) (m)        2.50E+001
26   .    27   sort                                                  2.50E+001
25   .    26   hash_partial_groupby                                  2.50E+001
24   .    25   esp_exchange                    8(hash2):8(hash2)     2.50E+001
23   .    24   hash_partial_groupby                                  2.50E+001
22   3    23   hybrid_hash_join      u                               7.30E+005
21   19   22   hybrid_hash_join                                      1.82E+007
20   .    21   partition_access                                      5.99E+008
.    .    20   file_scan             fr        LINEITEM              5.99E+008
18   .    19   esp_exchange                    8(hash2):8(hash2)     4.56E+006
17   14   18   hybrid_hash_join                                      4.56E+006
16   .    17   esp_exchange                    8(hash2):8(hash2)     2.28E+007
15   .    16   partition_access                                      2.28E+007
.    .    15   file_scan             fr        ORDERS                2.28E+007
13   .    14   esp_exchange                    8(hash2):8(hash2)     3.00E+006
10   12   13   nested_join                                           3.00E+006
11   .    12   partition_access                                      6.00E+005
.    .    11   index_scan            fr        CUIDX2                6.00E+005
9    .    10   esp_exchange                    8(rep-b):1            5.00E+000
5    8    9    merge_join                                            5.00E+000
7    .    8    sort                                                  2.50E+001
6    .    7    partition_access                                      2.50E+001
.    .    6    file_scan             fr        NATION                2.50E+001
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SUIDX2                1.00E+006

--- SQL operation complete.
>>obey optdml03(q06);
>>--------------------
>>--  TPCH Query 06 --
>>--------------------
>>prepare XX from
+>SELECT CAST(SUM(l_extendedprice*l_discount) AS NUMERIC(18,2)) AS revenue
+>FROM lineitem
+>WHERE l_shipdate >= DATE '1994-01-01'
+>      AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+>      AND l_discount BETWEEN .06 - 0.01 AND .06 + 0.01
+>      AND l_quantity < 24;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

06  ROOT              5   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.853E+003 
04  SPLIT_TOP         3   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   1.853E+003 
03  PARTITION_ACCESS  2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   1.853E+003 
02  SORT_PARTIAL_AGG  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.396E-002   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    1.143E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 6 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

5    .    6    root                                                  1.00E+000
4    .    5    sort_partial_aggr_ro                                  1.00E+000
3    .    4    split_top                       1:8(hash2)            1.00E+000
2    .    3    partition_access                                      1.00E+000
1    .    2    sort_partial_aggr_le                                  1.00E+000
.    .    1    file_scan                       LINEITEM              1.14E+007

--- SQL operation complete.
>>obey optdml03(q07);
>>--------------------
>>--  TPCH Query 07 --
>>--------------------
>>prepare XX from
+>SELECT supp_nation, cust_nation, yr, sum(volume) as revenue
+>   FROM
+>    (SELECT n1.n_name as supp_nation, n2.n_name as cust_nation,
+>             extract(year from l_shipdate) as yr,
+>       CAST(l_extendedprice*(1-l_discount) AS NUMERIC(18,2)) as volume
+>     FROM supplier,lineitem,orders,customer, nation n1, nation n2
+>     WHERE s_suppkey = l_suppkey
+>      AND o_orderkey = l_orderkey
+>      AND c_custkey = o_custkey
+>      AND s_nationkey = n1.n_nationkey
+>      AND c_nationkey = n2.n_nationkey
+>      AND ((n1.n_name  = 'FRANCE' AND
+>            n2.n_name = 'GERMANY')
+>          OR (n1.n_name = 'GERMANY'  AND
+>            n2.n_name = 'FRANCE'))
+>      AND l_shipdate BETWEEN  DATE '1995-01-01' AND  DATE '1996-12-31') as shipping
+>
+>GROUP BY supp_nation, cust_nation, yr
+>ORDER BY supp_nation, cust_nation, yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               8.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.897E+003 
28  ESP_EXCHANGE      27  ?               8.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   1.897E+003 
27  SORT_PARTIAL_GRO  26  ?               8.000E+000   FUNKY_OPT_UNIQUE  1.332E-007   1.897E+003 
26  SORT              25  ?               8.000E+000   FUNKY_OPT_UNIQUE  7.839E-008   1.897E+003 
25  ESP_EXCHANGE      24  ?               8.000E+000   FUNKY_OPT_UNIQUE  2.240E-004   1.897E+003 
24  HASH_PARTIAL_GRO  23  ?               8.000E+000   FUNKY_OPT_UNIQUE  1.428E-003   1.897E+003 
23  HYBRID_HASH_JOIN  22  3               5.828E+005   FUNKY_OPT_UNIQUE  2.608E-001   1.897E+003 
22  HYBRID_HASH_JOIN  21  19              2.188E+007   FUNKY_OPT_UNIQUE  1.441E+000   1.897E+003 
21  PARTITION_ACCESS  20  ?               1.821E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
20  FILE_SCAN         ?   ?   LINEITEM    1.821E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
19  ESP_EXCHANGE      18  ?               1.800E+007   FUNKY_OPT_UNIQUE  4.806E-001   4.138E+001 
18  HYBRID_HASH_JOIN  17  15              1.800E+007   FUNKY_OPT_UNIQUE  6.983E-001   4.090E+001 
17  PARTITION_ACCESS  16  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+001 
16  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.634E+001   3.634E+001 
15  HYBRID_HASH_JOIN  14  11              1.200E+006   FUNKY_OPT_UNIQUE  6.316E-002   3.859E+000 
14  ESP_EXCHANGE      13  ?               1.500E+007   FUNKY_OPT_UNIQUE  1.571E-001   3.792E+000 
13  PARTITION_ACCESS  12  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+000 
12  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  3.634E+000   3.634E+000 
11  ESP_EXCHANGE      10  ?               2.000E+000   FUNKY_OPT_UNIQUE  8.546E-004   4.143E-003 
10  HYBRID_HASH_JOIN  9   6               2.000E+000   FUNKY_OPT_UNIQUE  7.267E-006   3.289E-003 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.430E-004   1.627E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
04  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
03  ESP_EXCHANGE      2   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.397E-002   3.265E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 

--- 29 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  8.00E+000
27   .    28   esp_exchange                    1:8(hash2) (m)        8.00E+000
26   .    27   sort_partial_groupby                                  8.00E+000
25   .    26   sort                                                  8.00E+000
24   .    25   esp_exchange                    8(hash2):8(hash2)     8.00E+000
23   .    24   hash_partial_groupby                                  8.00E+000
22   3    23   hybrid_hash_join      u                               5.82E+005
21   19   22   hybrid_hash_join                                      2.18E+007
20   .    21   partition_access                                      1.82E+008
.    .    20   file_scan             fr        LINEITEM              1.82E+008
18   .    19   esp_exchange                    8(hash2):8(hash2)     1.80E+007
17   15   18   hybrid_hash_join                                      1.80E+007
16   .    17   partition_access                                      1.50E+008
.    .    16   index_scan            fr        ORDIDX2               1.50E+008
14   11   15   hybrid_hash_join                                      1.20E+006
13   .    14   esp_exchange                    8(hash2):8(hash2)     1.50E+007
12   .    13   partition_access                                      1.50E+007
.    .    12   index_scan            fr        CUIDX2                1.50E+007
10   .    11   esp_exchange                    8(rep-b):8(hash2)     2.00E+000
9    6    10   hybrid_hash_join                                      2.00E+000
8    .    9    esp_exchange                    8(hash2):1 (m)        2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    8(rep-b):1 (m)        2.50E+001
4    .    5    partition_access                                      2.50E+001
.    .    4    file_scan             fr        NATION                2.50E+001
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SUIDX2                1.00E+006

--- SQL operation complete.
>>obey optdml03(q08);
>>--------------------
>>--  TPCH Query 08 --
>>--------------------
>>prepare XX from
+>SELECT yr, sum(CASE when nation = 'BRAZIL'
+>                  then volume
+>                   else 0 end)
+>              / sum(volume) as mkt_share
+>  FROM
+>    (SELECT extract(year from o_orderdate) as yr,
+>       CAST(l_extendedprice*(1-l_discount) AS NUMERIC(16,2)) as volume,
+>          n2.n_name as nation
+>     FROM part,supplier,lineitem,orders,customer, nation n1, nation  n2,region
+>     WHERE p_partkey = l_partkey
+>      AND s_suppkey = l_suppkey
+>      AND l_orderkey = o_orderkey
+>      AND o_custkey = c_custkey
+>      AND c_nationkey = n1.n_nationkey
+>      AND n1.n_regionkey = r_regionkey
+>      AND r_name = 'AMERICA'
+>      AND s_nationkey = n2.n_nationkey
+>      AND o_orderdate BETWEEN  DATE '1995-01-01' AND  DATE '1996-12-31'
+>      AND p_type ='ECONOMY ANODIZED STEEL')  all_nations
+>GROUP BY yr
+>ORDER BY yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

37  ROOT              36  ?               2.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.373E+003 
36  ESP_EXCHANGE      35  ?               2.000E+000   FUNKY_OPT_UNIQUE  2.359E-004   2.373E+003 
35  SORT              34  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.666E-008   2.373E+003 
34  HASH_PARTIAL_GRO  33  ?               2.000E+000   FUNKY_OPT_UNIQUE  8.467E-008   2.373E+003 
33  ESP_EXCHANGE      32  ?               2.000E+000   FUNKY_OPT_UNIQUE  1.372E-004   2.373E+003 
32  HASH_PARTIAL_GRO  31  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.649E-004   2.373E+003 
31  HYBRID_HASH_JOIN  30  3               2.432E+005   FUNKY_OPT_UNIQUE  2.920E-003   2.373E+003 
30  HYBRID_HASH_JOIN  29  26              2.432E+005   FUNKY_OPT_UNIQUE  6.379E-003   2.373E+003 
29  ESP_EXCHANGE      28  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
28  PARTITION_ACCESS  27  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
27  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
26  ESP_EXCHANGE      25  ?               2.432E+005   FUNKY_OPT_UNIQUE  4.719E-003   2.372E+003 
25  HYBRID_HASH_JOIN  24  6               2.432E+005   FUNKY_OPT_UNIQUE  9.342E-003   2.372E+003 
24  HYBRID_HASH_JOIN  23  9               1.216E+006   FUNKY_OPT_UNIQUE  1.576E-002   2.372E+003 
23  HYBRID_HASH_JOIN  22  19              1.216E+006   FUNKY_OPT_UNIQUE  6.767E-002   2.372E+003 
22  ESP_EXCHANGE      21  ?               1.500E+007   FUNKY_OPT_UNIQUE  1.571E-001   3.792E+000 
21  PARTITION_ACCESS  20  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+000 
20  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  3.634E+000   3.634E+000 
19  ESP_EXCHANGE      18  ?               1.216E+006   FUNKY_OPT_UNIQUE  2.311E-002   2.369E+003 
18  HYBRID_HASH_JOIN  17  15              1.216E+006   FUNKY_OPT_UNIQUE  2.251E-001   2.368E+003 
17  PARTITION_ACCESS  16  ?               4.560E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.309E+002 
16  FILE_SCAN         ?   ?   ORDERS      4.560E+007   FUNKY_OPT_UNIQUE  4.309E+002   4.309E+002 
15  HYBRID_HASH_JOIN  14  12              3.996E+006   FUNKY_OPT_UNIQUE  3.912E+000   1.937E+003 
14  PARTITION_ACCESS  13  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
13  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.857E+003   1.857E+003 
12  ESP_EXCHANGE      11  ?               1.333E+005   FUNKY_OPT_UNIQUE  7.690E-003   7.653E+001 
11  PARTITION_ACCESS  10  ?               1.333E+005   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
10  FILE_SCAN         ?   ?   PART        1.333E+005   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  1.940E-004   1.478E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   7.671E-004 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   6.406E-004 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  6.406E-004   6.406E-004 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 37 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

36   .    37   root                                                  2.00E+000
35   .    36   esp_exchange                    1:8(hash2) (m)        2.00E+000
34   .    35   sort                                                  2.00E+000
33   .    34   hash_partial_groupby                                  2.00E+000
32   .    33   esp_exchange                    8(hash2):8(hash2)     2.00E+000
31   .    32   hash_partial_groupby                                  2.00E+000
30   3    31   hybrid_hash_join      u                               2.43E+005
29   26   30   hybrid_hash_join                                      2.43E+005
28   .    29   esp_exchange                    8(hash2):8(hash2)     1.00E+006
27   .    28   partition_access                                      1.00E+006
.    .    27   index_scan            fr        SUIDX2                1.00E+006
25   .    26   esp_exchange                    8(hash2):8(hash2)     2.43E+005
24   6    25   hybrid_hash_join      u                               2.43E+005
23   9    24   hybrid_hash_join      u                               1.21E+006
22   19   23   hybrid_hash_join                                      1.21E+006
21   .    22   esp_exchange                    8(hash2):8(hash2)     1.50E+007
20   .    21   partition_access                                      1.50E+007
.    .    20   index_scan            fr        CUIDX2                1.50E+007
18   .    19   esp_exchange                    8(hash2):8(hash2)     1.21E+006
17   15   18   hybrid_hash_join                                      1.21E+006
16   .    17   partition_access                                      4.56E+007
.    .    16   file_scan             fr        ORDERS                4.56E+007
14   12   15   hybrid_hash_join      u                               3.99E+006
13   .    14   partition_access                                      5.99E+008
.    .    13   file_scan             fr        LINEITEM              5.99E+008
11   .    12   esp_exchange                    8(rep-b):8(hash2)     1.33E+005
10   .    11   partition_access                                      1.33E+005
.    .    10   file_scan             fr        PART                  1.33E+005
8    .    9    esp_exchange                    8(rep-b):1 (m)        2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    8(rep-b):1 (m)        1.00E+000
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q09);
>>--------------------
>>--  TPCH Query 09 --
>>--------------------
>>
>>prepare XX from
+>SELECT nation, yr, sum(amount) as sum_profit
+>  from  (SELECT n_name as nation, EXTRACT (YEAR from o_orderdate) as yr,
+>                l_extendedprice*(1-l_discount)-ps_supplycost*l_quantity as amount
+>FROM part,supplier,lineitem,partsupp,orders, nation
+>WHERE s_suppkey  = l_suppkey
+>      AND ps_suppkey = l_suppkey
+>      AND ps_partkey = l_partkey
+>      AND p_partkey  = l_partkey
+>      AND o_orderkey = l_orderkey
+>      AND p_name like '%green%'
+>      AND s_nationkey = n_nationkey) as profit
+>GROUP BY nation, yr
+>ORDER BY nation, yr DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

28  ROOT              27  ?               1.750E+002   FUNKY_OPT_UNIQUE  0.000E+000   2.357E+003 
27  ESP_EXCHANGE      26  ?               1.750E+002   FUNKY_OPT_UNIQUE  2.800E-009   2.357E+003 
26  SORT_PARTIAL_GRO  25  ?               1.750E+002   FUNKY_OPT_UNIQUE  2.447E-006   2.357E+003 
25  SORT              24  ?               1.750E+002   FUNKY_OPT_UNIQUE  4.645E-006   2.357E+003 
24  ESP_EXCHANGE      23  ?               1.750E+002   FUNKY_OPT_UNIQUE  1.888E-004   2.357E+003 
23  HASH_PARTIAL_GRO  22  ?               1.750E+002   FUNKY_OPT_UNIQUE  7.346E-002   2.357E+003 
22  HYBRID_HASH_JOIN  21  3               3.746E+007   FUNKY_OPT_UNIQUE  4.991E-001   2.357E+003 
21  HYBRID_HASH_JOIN  20  17              3.746E+007   FUNKY_OPT_UNIQUE  9.294E-001   2.357E+003 
20  ESP_EXCHANGE      19  ?               1.500E+008   FUNKY_OPT_UNIQUE  1.486E+000   3.783E+001 
19  PARTITION_ACCESS  18  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.634E+001 
18  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.634E+001   3.634E+001 
17  HYBRID_HASH_JOIN  16  14              3.746E+007   FUNKY_OPT_UNIQUE  4.578E+000   2.318E+003 
16  PARTITION_ACCESS  15  ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
15  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.857E+003   1.857E+003 
14  ESP_EXCHANGE      13  ?               5.000E+006   FUNKY_OPT_UNIQUE  6.266E-001   4.563E+002 
13  HYBRID_HASH_JOIN  12  6               5.000E+006   FUNKY_OPT_UNIQUE  5.532E-002   4.556E+002 
12  ESP_EXCHANGE      11  ?               5.000E+006   FUNKY_OPT_UNIQUE  7.044E-002   4.553E+002 
11  HYBRID_HASH_JOIN  10  8               5.000E+006   FUNKY_OPT_UNIQUE  4.191E-001   4.553E+002 
10  PARTITION_ACCESS  9   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
09  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
08  PARTITION_ACCESS  7   ?               1.250E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
07  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 
06  ESP_EXCHANGE      5   ?               1.000E+006   FUNKY_OPT_UNIQUE  1.077E-002   2.533E-001 
05  PARTITION_ACCESS  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.425E-001 
04  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  2.425E-001   2.425E-001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 28 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

27   .    28   root                                                  1.75E+002
26   .    27   esp_exchange                    1:8(hash2) (m)        1.75E+002
25   .    26   sort_partial_groupby                                  1.75E+002
24   .    25   sort                                                  1.75E+002
23   .    24   esp_exchange                    8(hash2):8(hash2)     1.75E+002
22   .    23   hash_partial_groupby                                  1.75E+002
21   3    22   hybrid_hash_join      u                               3.74E+007
20   17   21   hybrid_hash_join                                      3.74E+007
19   .    20   esp_exchange                    8(hash2):8(hash2)     1.50E+008
18   .    19   partition_access                                      1.50E+008
.    .    18   index_scan            fr        ORDIDX3               1.50E+008
16   14   17   hybrid_hash_join                                      3.74E+007
15   .    16   partition_access                                      5.99E+008
.    .    15   file_scan             fr        LINEITEM              5.99E+008
13   .    14   esp_exchange                    8(rep-b):8(hash2)     5.00E+006
12   6    13   hybrid_hash_join      u                               5.00E+006
11   .    12   esp_exchange                    8(hash2):8(hash2)     5.00E+006
10   8    11   hybrid_hash_join                                      5.00E+006
9    .    10   partition_access                                      8.00E+007
.    .    9    file_scan             fr        PARTSUPP              8.00E+007
7    .    8    partition_access                                      1.25E+006
.    .    7    file_scan             fr        PART                  1.25E+006
5    .    6    esp_exchange                    8(hash2):8(hash2)     1.00E+006
4    .    5    partition_access                                      1.00E+006
.    .    4    index_scan            fr        SUIDX2                1.00E+006
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q10);
>>--------------------
>>--  TPCH Query 10 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 20] c_custkey, c_name,
+>       CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue,
+>       c_acctbal,  n_name, c_address, c_phone, c_comment
+>FROM customer,orders,lineitem, nation
+>WHERE c_custkey = o_custkey
+>      AND l_orderkey = o_orderkey
+>      AND o_orderdate >=  DATE '1993-10-01'
+>      AND o_orderdate <  DATE '1993-10-01' + INTERVAL '3' MONTH
+>      AND l_returnflag = 'R'
+>      AND c_nationkey = n_nationkey
+>GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name,
+>         c_address, c_comment
+>ORDER BY revenue DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.363E+003 
17  FIRSTN            16  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   2.363E+003 
16  ESP_EXCHANGE      15  ?               5.723E+006   FUNKY_OPT_UNIQUE  2.800E-009   2.363E+003 
15  SORT              14  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.203E+000   2.363E+003 
14  HASH_GROUPBY      13  ?               5.723E+006   FUNKY_OPT_UNIQUE  2.234E-001   2.362E+003 
13  HYBRID_HASH_JOIN  12  3               5.723E+006   FUNKY_OPT_UNIQUE  2.178E-001   2.362E+003 
12  HYBRID_HASH_JOIN  11  9               5.723E+006   FUNKY_OPT_UNIQUE  4.065E-001   2.361E+003 
11  PARTITION_ACCESS  10  ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.556E+001 
10  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  7.556E+001   7.556E+001 
09  ESP_EXCHANGE      8   ?               5.723E+006   FUNKY_OPT_UNIQUE  8.057E-002   2.285E+003 
08  HYBRID_HASH_JOIN  7   5               5.723E+006   FUNKY_OPT_UNIQUE  7.729E-001   2.285E+003 
07  PARTITION_ACCESS  6   ?               1.479E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
06  FILE_SCAN         ?   ?   LINEITEM    1.479E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.307E+002 
04  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  4.307E+002   4.307E+002 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  3.694E-004   1.654E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  0.000E+000   1.284E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  1.284E-003   1.284E-003 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  5.72E+006
16   .    17   firstn                                                5.72E+006
15   .    16   esp_exchange                    1:8(hash2) (m)        5.72E+006
14   .    15   sort                                                  5.72E+006
13   .    14   hash_groupby                                          5.72E+006
12   3    13   hybrid_hash_join      u                               5.72E+006
11   9    12   hybrid_hash_join                                      5.72E+006
10   .    11   partition_access                                      1.50E+007
.    .    10   file_scan             fr        CUSTOMER              1.50E+007
8    .    9    esp_exchange                    8(hash2):8(hash2)     5.72E+006
7    5    8    hybrid_hash_join      u                               5.72E+006
6    .    7    partition_access                                      1.47E+008
.    .    6    file_scan             fr        LINEITEM              1.47E+008
4    .    5    partition_access                                      5.79E+006
.    .    4    file_scan             fr        ORDERS                5.79E+006
2    .    3    esp_exchange                    8(rep-b):1 (m)        2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml03(q11);
>>--------------------
>>--  TPCH Query 11 --
>>--------------------
>>prepare XX from
+>SELECT ps_partkey, SUM(ps_supplycost * ps_availqty) AS ps_value
+>FROM partsupp,supplier,nation
+>WHERE ps_suppkey = s_suppkey
+>      AND s_nationkey = n_nationkey
+>      AND n_name = 'GERMANY'
+>GROUP BY ps_partkey
+>HAVING SUM(ps_supplycost*ps_availqty) >
+>       (SELECT SUM(ps_supplycost*ps_availqty) * 0.000001
+>        FROM partsupp,supplier,nation
+>        WHERE ps_suppkey = s_suppkey
+>              AND s_nationkey = n_nationkey
+>              AND n_name = 'GERMANY')
+>ORDER BY ps_value DESC;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

29  ROOT              28  ?               1.074E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.579E+002 
28  ESP_EXCHANGE      27  ?               1.074E+006   FUNKY_OPT_UNIQUE  2.800E-009   7.579E+002 
27  SORT              26  ?               1.074E+006   FUNKY_OPT_UNIQUE  5.313E-002   7.579E+002 
26  HYBRID_HASH_JOIN  25  14              1.074E+006   FUNKY_OPT_UNIQUE  3.908E-002   7.578E+002 
25  HASH_GROUPBY      24  ?               3.223E+006   FUNKY_OPT_UNIQUE  4.167E-002   3.790E+002 
24  HYBRID_HASH_JOIN  23  21              3.223E+006   FUNKY_OPT_UNIQUE  5.386E-001   3.790E+002 
23  PARTITION_ACCESS  22  ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.784E+002 
22  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.784E+002   3.784E+002 
21  ESP_EXCHANGE      20  ?               4.028E+004   FUNKY_OPT_UNIQUE  1.827E-002   2.453E-002 
20  NESTED_JOIN       17  19              4.028E+004   FUNKY_OPT_UNIQUE  9.874E-005   6.257E-003 
19  PARTITION_ACCESS  18  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.748E-003 
18  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.748E-003   4.748E-003 
17  ESP_EXCHANGE      16  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
16  PARTITION_ACCESS  15  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
15  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
14  ESP_EXCHANGE      13  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.827E-004   3.787E+002 
13  SORT_PARTIAL_AGG  12  ?               1.000E+000   FUNKY_OPT_UNIQUE  7.126E-008   3.787E+002 
12  ESP_EXCHANGE      11  ?               1.000E+000   FUNKY_OPT_UNIQUE  6.302E-005   3.787E+002 
11  SORT_PARTIAL_AGG  10  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.184E-003   3.787E+002 
10  HYBRID_HASH_JOIN  9   7               3.223E+006   FUNKY_OPT_UNIQUE  3.799E-001   3.787E+002 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
08  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
07  ESP_EXCHANGE      6   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.827E-002   2.453E-002 
06  NESTED_JOIN       3   5               4.028E+004   FUNKY_OPT_UNIQUE  9.874E-005   6.257E-003 
05  PARTITION_ACCESS  4   ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.748E-003 
04  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.748E-003   4.748E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
01  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 

--- 29 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

28   .    29   root                                                  1.07E+006
27   .    28   esp_exchange                    1:8(hash2) (m)        1.07E+006
26   .    27   sort                                                  1.07E+006
25   14   26   hybrid_hash_join                                      1.07E+006
24   .    25   hash_groupby                                          3.22E+006
23   21   24   hybrid_hash_join      u                               3.22E+006
22   .    23   partition_access                                      8.00E+007
.    .    22   file_scan             fr        PARTSUPP              8.00E+007
20   .    21   esp_exchange                    8(rep-b):8(hash2)     4.02E+004
17   19   20   nested_join                                           4.02E+004
18   .    19   partition_access                                      4.02E+004
.    .    18   index_scan            fr        SUIDX2                4.02E+004
16   .    17   esp_exchange                    8(rep-b):1 (m)        1.00E+000
15   .    16   partition_access                                      1.00E+000
.    .    15   file_scan             fr        NATION                1.00E+000
13   .    14   esp_exchange                    8(rep-b):1            1.00E+000
12   .    13   sort_partial_aggr_ro                                  1.00E+000
11   .    12   esp_exchange                    1:8(hash2)            1.00E+000
10   .    11   sort_partial_aggr_le                                  1.00E+000
9    7    10   hybrid_hash_join      u                               3.22E+006
8    .    9    partition_access                                      8.00E+007
.    .    8    file_scan             fr        PARTSUPP              8.00E+007
6    .    7    esp_exchange                    8(rep-b):8(hash2)     4.02E+004
3    5    6    nested_join                                           4.02E+004
4    .    5    partition_access                                      4.02E+004
.    .    4    index_scan            fr        SUIDX2                4.02E+004
2    .    3    esp_exchange                    8(rep-b):1 (m)        1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        NATION                1.00E+000

--- SQL operation complete.
>>obey optdml03(q12);
>>--------------------
>>--  TPCH Query 12 --
>>--------------------
>>prepare XX from
+>SELECT l_shipmode,
+>       SUM(CASE WHEN o_orderpriority ='1-URGENT'
+>                     OR o_orderpriority ='2-HIGH'
+>                THEN 1
+>                ELSE 0
+>           END) AS high_line_count,
+>        SUM(CASE WHEN o_orderpriority <> '1-URGENT'
+>                      AND o_orderpriority <> '2-HIGH'
+>                 THEN 1
+>                 ELSE 0
+>            END) AS low_line_count
+>FROM orders,lineitem
+>where o_orderkey = l_orderkey
+>      AND l_shipmode in ('MAIL','SHIP')
+>      AND l_commitdate < l_receiptdate
+>      AND l_shipdate < l_commitdate
+>      AND l_receiptdate >=  DATE '1994-01-01'
+>      AND l_receiptdate <  DATE '1994-01-01' + INTERVAL '1' YEAR
+>GROUP BY l_shipmode
+>ORDER BY l_shipmode;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               2.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.285E+003 
10  ESP_EXCHANGE      9   ?               2.000E+000   FUNKY_OPT_UNIQUE  2.800E-009   2.285E+003 
09  SORT_PARTIAL_GRO  8   ?               2.000E+000   FUNKY_OPT_UNIQUE  1.019E-007   2.285E+003 
08  SORT              7   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.785E-008   2.285E+003 
07  ESP_EXCHANGE      6   ?               2.000E+000   FUNKY_OPT_UNIQUE  1.428E-004   2.285E+003 
06  HASH_PARTIAL_GRO  5   ?               2.000E+000   FUNKY_OPT_UNIQUE  3.314E-003   2.285E+003 
05  HYBRID_HASH_JOIN  4   2               2.892E+006   FUNKY_OPT_UNIQUE  5.457E-001   2.285E+003 
04  PARTITION_ACCESS  3   ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   4.312E+002 
03  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  4.312E+002   4.312E+002 
02  PARTITION_ACCESS  1   ?               2.892E+006   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    2.892E+006   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 11 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  2.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        2.00E+000
8    .    9    sort_partial_groupby                                  2.00E+000
7    .    8    sort                                                  2.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     2.00E+000
5    .    6    hash_partial_groupby                                  2.00E+000
4    2    5    hybrid_hash_join                                      2.89E+006
3    .    4    partition_access                                      1.50E+008
.    .    3    file_scan             fr        ORDERS                1.50E+008
1    .    2    partition_access                                      2.89E+006
.    .    1    file_scan             fr        LINEITEM              2.89E+006

--- SQL operation complete.
>>obey optdml03(q13);
>>--------------------
>>--  TPCH Query 13 --
>>--------------------
>>prepare XX from
+>
+>SELECT yr, sum(revenue) as revenue
+>  FROM
+>    (SELECT extract(year from o_orderdate) as yr,
+>       CAST(l_extendedprice * (1-l_discount) AS NUMERIC(18,2)) as revenue
+>FROM lineitem, orders
+>WHERE o_orderkey = l_orderkey
+>      AND o_clerk = 'Clerk#000000088'
+>      AND l_returnflag = 'R') as performance
+>GROUP BY yr
+>ORDER BY yr;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

11  ROOT              10  ?               7.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   4.314E+002 
10  ESP_EXCHANGE      9   ?               7.000E+000   FUNKY_OPT_UNIQUE  2.364E-004   4.314E+002 
09  SORT_PARTIAL_GRO  8   ?               7.000E+000   FUNKY_OPT_UNIQUE  6.233E-008   4.314E+002 
08  SORT              7   ?               7.000E+000   FUNKY_OPT_UNIQUE  3.666E-008   4.314E+002 
07  ESP_EXCHANGE      6   ?               7.000E+000   FUNKY_OPT_UNIQUE  1.047E-004   4.314E+002 
06  HASH_PARTIAL_GRO  5   ?               7.000E+000   FUNKY_OPT_UNIQUE  1.760E-006   4.314E+002 
05  NESTED_JOIN       2   4               1.481E+003   FUNKY_OPT_UNIQUE  1.722E-005   4.314E+002 
04  PARTITION_ACCESS  3   ?               9.873E-001   FUNKY_OPT_UNIQUE  0.000E+000   7.151E-001 
03  FILE_SCAN         ?   ?   LINEITEM    9.873E-001   FUNKY_OPT_UNIQUE  7.151E-001   7.151E-001 
02  PARTITION_ACCESS  1   ?               1.500E+003   FUNKY_OPT_UNIQUE  0.000E+000   4.307E+002 
01  FILE_SCAN         ?   ?   ORDERS      1.500E+003   FUNKY_OPT_UNIQUE  4.307E+002   4.307E+002 

--- 11 row(s) selected.
>>
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

10   .    11   root                                                  7.00E+000
9    .    10   esp_exchange                    1:8(hash2) (m)        7.00E+000
8    .    9    sort_partial_groupby                                  7.00E+000
7    .    8    sort                                                  7.00E+000
6    .    7    esp_exchange                    8(hash2):8(hash2)     7.00E+000
5    .    6    hash_partial_groupby                                  7.00E+000
2    4    5    nested_join                                           1.48E+003
3    .    4    partition_access                                      9.87E-001
.    .    3    file_scan             fr        LINEITEM              9.87E-001
1    .    2    partition_access                                      1.50E+003
.    .    1    file_scan             fr        ORDERS                1.50E+003

--- SQL operation complete.
>>obey optdml03(q14);
>>--------------------
>>--  TPCH Query 14 --
>>--------------------
>>prepare XX from
+>SELECT 100.00 * SUM(CASE WHEN p_type = 'promo' -- p_type like 'PROMO%'
+>                         THEN (l_extendedprice * (1-l_discount))
+>                         ELSE 0
+>                    END) / 
+>        SUM((l_extendedprice*(1-l_discount))) AS promo_revenue
+>FROM  lineitem, part
+>WHERE l_partkey = p_partkey
+>      AND l_shipdate >=  DATE '1995-09-01'
+>      AND l_shipdate <  DATE '1995-09-01' + INTERVAL '1' MONTH;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

10  ROOT              9   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.930E+003 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.215E-008   1.930E+003 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.454E-005   1.930E+003 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  8.107E-003   1.930E+003 
06  HYBRID_HASH_JOIN  5   3               7.720E+006   FUNKY_OPT_UNIQUE  2.248E-001   1.930E+003 
05  PARTITION_ACCESS  4   ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.666E+001 
04  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  7.666E+001   7.666E+001 
03  ESP_EXCHANGE      2   ?               7.720E+006   FUNKY_OPT_UNIQUE  1.213E-001   1.853E+003 
02  PARTITION_ACCESS  1   ?               7.720E+006   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    7.720E+006   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 10 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

9    .    10   root                                                  1.00E+000
8    .    9    sort_partial_aggr_ro                                  1.00E+000
7    .    8    esp_exchange                    1:8(hash2)            1.00E+000
6    .    7    sort_partial_aggr_le                                  1.00E+000
5    3    6    hybrid_hash_join                                      7.72E+006
4    .    5    partition_access                                      2.00E+007
.    .    4    file_scan             fr        PART                  2.00E+007
2    .    3    esp_exchange                    8(hash2):8(hash2)     7.72E+006
1    .    2    partition_access                                      7.72E+006
.    .    1    file_scan             fr        LINEITEM              7.72E+006

--- SQL operation complete.
>>obey optdml03(q15);
>>--------------------
>>--  TPCH Query 15 --
>>--------------------
>>create view revenue (supplier_no, total_revenue) AS
+>     SELECT l_suppkey, SUM(l_extendedprice * (1-l_discount))
+>     FROM lineitem
+>     WHERE l_shipdate >= DATE '1996-01-01'
+>       AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
+>     GROUP BY l_suppkey;

--- SQL operation complete.
>>
>>prepare XX from
+>SELECT s_suppkey, s_name, s_address, s_phone, total_revenue
+>FROM  supplier, revenue
+>WHERE s_suppkey = supplier_no
+>  AND total_revenue = (SELECT MAX(total_revenue)
+>                       FROM revenue)
+>ORDER BY s_suppkey;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

21  ROOT              20  ?               3.333E+005   FUNKY_OPT_UNIQUE  0.000E+000   3.711E+003 
20  ESP_EXCHANGE      19  ?               3.333E+005   FUNKY_OPT_UNIQUE  2.800E-009   3.711E+003 
19  SORT              18  ?               3.333E+005   FUNKY_OPT_UNIQUE  3.576E-002   3.711E+003 
18  HYBRID_HASH_JOIN  17  15              3.333E+005   FUNKY_OPT_UNIQUE  1.729E-002   3.711E+003 
17  PARTITION_ACCESS  16  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.239E+000 
16  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  4.239E+000   4.239E+000 
15  HYBRID_HASH_JOIN  14  9               3.333E+005   FUNKY_OPT_UNIQUE  7.304E-003   3.707E+003 
14  HASH_PARTIAL_GRO  13  ?               1.000E+006   FUNKY_OPT_UNIQUE  8.709E-002   1.853E+003 
13  ESP_EXCHANGE      12  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.842E-002   1.853E+003 
12  HASH_PARTIAL_GRO  11  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.944E-002   1.853E+003 
11  PARTITION_ACCESS  10  ?               2.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
10  FILE_SCAN         ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.682E-004   1.853E+003 
08  SORT_PARTIAL_AGG  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.853E+003 
07  ESP_EXCHANGE      6   ?               1.000E+000   FUNKY_OPT_UNIQUE  6.685E-005   1.853E+003 
06  SORT_PARTIAL_AGG  5   ?               1.000E+000   FUNKY_OPT_UNIQUE  8.575E-004   1.853E+003 
05  HASH_PARTIAL_GRO  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  8.651E-002   1.853E+003 
04  ESP_EXCHANGE      3   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.842E-002   1.853E+003 
03  HASH_PARTIAL_GRO  2   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.944E-002   1.853E+003 
02  PARTITION_ACCESS  1   ?               2.291E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
01  FILE_SCAN         ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 

--- 21 row(s) selected.
>>
>>drop view revenue;

--- SQL operation complete.
>>
>>--prepare XX from
>>--SELECT s_suppkey,s_name,s_address,s_phone, total_revenue
>>--FROM supplier, (SELECT l_suppkey, SUM(l_extendedprice * (1-l_discount))
>>--                FROM lineitem
>>--                WHERE l_shipdate >=  DATE '1996-01-01'
>>--                      AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
>>--                GROUP BY l_suppkey) revenue(supplier_no, total_revenue)
>>--WHERE s_suppkey = supplier_no
>>--      AND total_revenue = (SELECT MAX(total_revenue)
>>--                           FROM (SELECT l_suppkey,
>>--                                        sum(l_extendedprice * (1-l_discount))
>>--                                 FROM lineitem
>>--                                 WHERE l_shipdate >=  DATE '1996-01-01'
>>--                                 AND l_shipdate <  DATE '1996-01-01' + INTERVAL '3' MONTH
>>--                                 GROUP BY l_suppkey)
>>--                           revenue(supplier_no, total_revenue))
>>--ORDER BY s_suppkey;
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

20   .    21   root                                                  3.33E+005
19   .    20   esp_exchange                    1:8(hash2) (m)        3.33E+005
18   .    19   sort                                                  3.33E+005
17   15   18   hybrid_hash_join      u                               3.33E+005
16   .    17   partition_access                                      1.00E+006
.    .    16   file_scan             fr        SUPPLIER              1.00E+006
14   9    15   hybrid_hash_join      u                               3.33E+005
13   .    14   hash_partial_groupby                                  1.00E+006
12   .    13   esp_exchange                    8(hash2):8(hash2)     1.48E+007
11   .    12   hash_partial_groupby                                  1.48E+007
10   .    11   partition_access                                      2.29E+007
.    .    10   file_scan             fr        LINEITEM              2.29E+007
8    .    9    esp_exchange                    8(rep-b):1            1.00E+000
7    .    8    sort_partial_aggr_ro                                  1.00E+000
6    .    7    esp_exchange                    1:8(hash2)            1.00E+000
5    .    6    sort_partial_aggr_le                                  1.00E+000
4    .    5    hash_partial_groupby                                  1.00E+006
3    .    4    esp_exchange                    8(hash2):8(hash2)     1.48E+007
2    .    3    hash_partial_groupby                                  1.48E+007
1    .    2    partition_access                                      2.29E+007
.    .    1    file_scan             fr        LINEITEM              2.29E+007

--- SQL operation complete.
>>obey optdml03(q16);
>>--------------------
>>--  TPCH Query 16 --
>>--------------------
>>prepare XX from
+>SELECT p_brand, p_type, p_size, COUNT(DISTINCT ps_suppkey) AS supplier_cnt
+>FROM partsupp, part
+>WHERE p_partkey = ps_partkey
+>      AND p_brand <> 'Brand#45'
+>      AND p_type NOT LIKE 'MEDIUM POLISHED%'
+>      AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
+>      AND ps_suppkey NOT IN (SELECT s_suppkey
+>                             FROM supplier
+>                             WHERE s_comment LIKE
+>                                  '%Better Business Bureau%Complaints%')
+>GROUP BY p_brand, p_type, p_size
+>ORDER BY supplier_cnt DESC, p_brand, p_type, p_size;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               2.840E+004   FUNKY_OPT_UNIQUE  0.000E+000   1.073E+002 
17  ESP_EXCHANGE      16  ?               2.840E+004   FUNKY_OPT_UNIQUE  2.800E-009   1.073E+002 
16  SORT              15  ?               2.840E+004   FUNKY_OPT_UNIQUE  1.679E-003   1.073E+002 
15  HASH_PARTIAL_GRO  14  ?               2.840E+004   FUNKY_OPT_UNIQUE  4.870E-004   1.073E+002 
14  ESP_EXCHANGE      13  ?               2.840E+004   FUNKY_OPT_UNIQUE  3.699E-004   1.073E+002 
13  HASH_PARTIAL_GRO  12  ?               2.840E+004   FUNKY_OPT_UNIQUE  2.361E-002   1.073E+002 
12  HASH_PARTIAL_GRO  11  ?               1.125E+007   FUNKY_OPT_UNIQUE  1.849E-001   1.073E+002 
11  ESP_EXCHANGE      10  ?               1.125E+007   FUNKY_OPT_UNIQUE  6.625E-002   1.071E+002 
10  HASH_PARTIAL_GRO  9   ?               1.125E+007   FUNKY_OPT_UNIQUE  4.624E-002   1.070E+002 
09  HYBRID_HASH_ANTI  8   3               1.125E+007   FUNKY_OPT_UNIQUE  1.923E-001   1.070E+002 
08  HYBRID_HASH_JOIN  7   5               1.200E+007   FUNKY_OPT_UNIQUE  4.002E-001   1.026E+002 
07  PARTITION_ACCESS  6   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.566E+001 
06  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  2.566E+001   2.566E+001 
05  PARTITION_ACCESS  4   ?               3.001E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.655E+001 
04  FILE_SCAN         ?   ?   PART        3.001E+006   FUNKY_OPT_UNIQUE  7.655E+001   7.655E+001 
03  ESP_EXCHANGE      2   ?               6.250E+004   FUNKY_OPT_UNIQUE  3.724E-003   4.232E+000 
02  PARTITION_ACCESS  1   ?               6.250E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.228E+000 
01  FILE_SCAN         ?   ?   SUPPLIER    6.250E+004   FUNKY_OPT_UNIQUE  4.228E+000   4.228E+000 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  2.83E+004
16   .    17   esp_exchange                    1:8(hash2) (m)        2.83E+004
15   .    16   sort                                                  2.83E+004
14   .    15   hash_partial_groupby                                  2.83E+004
13   .    14   esp_exchange                    8(hash2):8(hash2)     2.83E+004
12   .    13   hash_partial_groupby                                  2.83E+004
11   .    12   hash_partial_groupby                                  1.12E+007
10   .    11   esp_exchange                    8(hash2):8(hash2)     1.12E+007
9    .    10   hash_partial_groupby                                  1.12E+007
8    3    9    hybrid_hash_anti_sem                                  1.12E+007
7    5    8    hybrid_hash_join                                      1.20E+007
6    .    7    partition_access                                      8.00E+007
.    .    6    index_scan            fr        PSIDX1                8.00E+007
4    .    5    partition_access                                      3.00E+006
.    .    4    file_scan             fr        PART                  3.00E+006
2    .    3    esp_exchange                    8(rep-b):8(hash2)     6.25E+004
1    .    2    partition_access                                      6.25E+004
.    .    1    file_scan             fr        SUPPLIER              6.25E+004

--- SQL operation complete.
>>obey optdml03(q17);
>>--------------------
>>--  TPCH Query 17 --
>>--------------------
>>prepare XX from
+>SELECT CAST((SUM(l_extendedprice)/7.0) AS NUMERIC(18,2)) AS avg_yearly
+>FROM lineitem, part
+>WHERE p_partkey = l_partkey
+>      AND p_brand = 'Brand#23'
+>      AND p_container = 'MED BOX'
+>      AND l_quantity < (SELECT 0.2*avg(l_quantity)
+>                        FROM lineitem 
+>                        WHERE l_partkey = p_partkey);

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

15  ROOT              14  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   3.795E+003 
14  SORT_PARTIAL_AGG  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  5.268E-008   3.795E+003 
13  ESP_EXCHANGE      12  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.982E-005   3.795E+003 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.638E-003   3.795E+003 
11  HASH_GROUPBY      10  ?               6.000E+006   FUNKY_OPT_UNIQUE  1.455E-001   3.795E+003 
10  HYBRID_HASH_JOIN  9   7               1.800E+007   FUNKY_OPT_UNIQUE  3.429E+000   3.795E+003 
09  PARTITION_ACCESS  8   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.856E+003 
08  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.856E+003   1.856E+003 
07  ESP_EXCHANGE      6   ?               6.007E+005   FUNKY_OPT_UNIQUE  5.865E-002   1.935E+003 
06  HYBRID_HASH_JOIN  5   3               6.007E+005   FUNKY_OPT_UNIQUE  2.546E+000   1.935E+003 
05  PARTITION_ACCESS  4   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.855E+003 
04  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
03  ESP_EXCHANGE      2   ?               2.004E+004   FUNKY_OPT_UNIQUE  1.346E-003   7.652E+001 
02  PARTITION_ACCESS  1   ?               2.004E+004   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
01  FILE_SCAN         ?   ?   PART        2.004E+004   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 

--- 15 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

14   .    15   root                                                  1.00E+000
13   .    14   sort_partial_aggr_ro                                  1.00E+000
12   .    13   esp_exchange                    1:8(hash2)            1.00E+000
11   .    12   sort_partial_aggr_le                                  1.00E+000
10   .    11   hash_groupby                                          6.00E+006
9    7    10   hybrid_hash_join                                      1.80E+007
8    .    9    partition_access                                      5.99E+008
.    .    8    file_scan             fr        LINEITEM              5.99E+008
6    .    7    esp_exchange                    8(rep-b):8(hash2)     6.00E+005
5    3    6    hybrid_hash_join      u                               6.00E+005
4    .    5    partition_access                                      5.99E+008
.    .    4    file_scan             fr        LINEITEM              5.99E+008
2    .    3    esp_exchange                    8(rep-b):8(hash2)     2.00E+004
1    .    2    partition_access                                      2.00E+004
.    .    1    file_scan             fr        PART                  2.00E+004

--- SQL operation complete.
>>obey optdml03(q18);
>>--------------------
>>--  TPCH Query 18 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
+>                   SUM(l_quantity)
+>FROM customer, orders, lineitem
+>WHERE o_orderkey IN (SELECT l_orderkey
+>                     FROM lineitem
+>                     GROUP BY l_orderkey HAVING SUM(l_quantity) >  300)
+>      AND c_custkey = o_custkey
+>      AND o_orderkey = l_orderkey
+>GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+>ORDER BY o_totalprice DESC, o_orderdate;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.235E+003 
17  FIRSTN            16  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.235E+003 
16  ESP_EXCHANGE      15  ?               4.994E+007   FUNKY_OPT_UNIQUE  2.800E-009   4.235E+003 
15  SORT              14  ?               4.994E+007   FUNKY_OPT_UNIQUE  8.167E+000   4.235E+003 
14  HYBRID_HASH_JOIN  13  2               4.994E+007   FUNKY_OPT_UNIQUE  8.793E-001   4.227E+003 
13  ESP_EXCHANGE      12  ?               4.994E+007   FUNKY_OPT_UNIQUE  9.255E-001   4.151E+003 
12  HYBRID_HASH_JOIN  11  9               4.994E+007   FUNKY_OPT_UNIQUE  1.357E+000   4.150E+003 
11  PARTITION_ACCESS  10  ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   4.315E+002 
10  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  4.315E+002   4.315E+002 
09  HYBRID_HASH_JOIN  8   5               4.994E+007   FUNKY_OPT_UNIQUE  9.701E-001   3.717E+003 
08  PARTITION_ACCESS  7   ?               1.498E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.858E+003 
07  SORT_GROUPBY      6   ?               1.498E+008   FUNKY_OPT_UNIQUE  2.416E+000   1.858E+003 
06  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
05  PARTITION_ACCESS  4   ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.857E+003 
04  SORT_GROUPBY      3   ?               4.994E+007   FUNKY_OPT_UNIQUE  1.902E+000   1.857E+003 
03  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  1.855E+003   1.855E+003 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.539E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  7.539E+001   7.539E+001 

--- 18 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  4.99E+007
16   .    17   firstn                                                4.99E+007
15   .    16   esp_exchange                    1:8(hash2) (m)        4.99E+007
14   .    15   sort                                                  4.99E+007
13   2    14   hybrid_hash_join                                      4.99E+007
12   .    13   esp_exchange                    8(hash2):8(hash2)     4.99E+007
11   9    12   hybrid_hash_join                                      4.99E+007
10   .    11   partition_access                                      1.50E+008
.    .    10   file_scan             fr        ORDERS                1.50E+008
8    5    9    hybrid_hash_join                                      4.99E+007
7    .    8    partition_access                                      1.49E+008
6    .    7    sort_groupby                                          1.49E+008
.    .    6    file_scan                       LINEITEM              5.99E+008
4    .    5    partition_access                                      4.99E+007
3    .    4    sort_groupby                                          4.99E+007
.    .    3    file_scan                       LINEITEM              5.99E+008
1    .    2    partition_access                                      1.50E+007
.    .    1    file_scan             fr        CUSTOMER              1.50E+007

--- SQL operation complete.
>>obey optdml03(q19);
>>--------------------
>>--  TPCH Query 19 --
>>--------------------
>>prepare XX from
+>SELECT CAST(SUM(l_extendedprice*(1-l_discount)) AS NUMERIC(18,2)) AS revenue
+>FROM lineitem, part
+>WHERE 
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#24'
+>    AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
+>    AND l_quantity >= 1 AND l_quantity <= 1 + 10
+>    AND p_size BETWEEN 1 AND 5
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   )
+> OR
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#16'
+>    AND p_container IN ( 'MED CASE', 'MED BOX', 'MED PACK', 'MED PKG' )
+>    AND l_quantity >= 10 AND l_quantity <= 10 + 10
+>    AND p_size BETWEEN 1 AND 10
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   )
+> OR
+>   (    p_partkey = l_partkey
+>    AND p_brand = 'Brand#42'
+>    AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
+>    AND l_quantity >= 20 AND l_quantity <= 20 + 10
+>    AND p_size BETWEEN 1 AND 15
+>    AND l_shipmode IN ( 'AIR', 'AIR REG' )
+>    AND l_shipinstruct = 'DELIVER IN PERSON' 
+>   );

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

10  ROOT              9   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.931E+003 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   1.931E+003 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  6.685E-005   1.931E+003 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.768E-005   1.931E+003 
06  HYBRID_HASH_JOIN  5   2               2.380E+004   FUNKY_OPT_UNIQUE  5.678E-001   1.931E+003 
05  ESP_EXCHANGE      4   ?               4.281E+007   FUNKY_OPT_UNIQUE  7.346E-001   1.854E+003 
04  PARTITION_ACCESS  3   ?               4.281E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
03  FILE_SCAN         ?   ?   LINEITEM    4.281E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
02  PARTITION_ACCESS  1   ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   7.665E+001 
01  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  7.665E+001   7.665E+001 

--- 10 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

9    .    10   root                                                  1.00E+000
8    .    9    sort_partial_aggr_ro                                  1.00E+000
7    .    8    esp_exchange                    1:8(hash2)            1.00E+000
6    .    7    sort_partial_aggr_le                                  1.00E+000
5    2    6    hybrid_hash_join                                      2.38E+004
4    .    5    esp_exchange                    8(hash2):8(hash2)     4.28E+007
3    .    4    partition_access                                      4.28E+007
.    .    3    file_scan             fr        LINEITEM              4.28E+007
1    .    2    partition_access                                      2.00E+007
.    .    1    file_scan             fr        PART                  2.00E+007

--- SQL operation complete.
>>obey optdml03(q20);
>>--------------------
>>--  TPCH Query 20 --
>>--------------------
>>prepare XX from
+>SELECT s_name, s_address
+>FROM supplier, nation
+>WHERE s_suppkey IN (SELECT ps_suppkey
+>                    FROM partsupp
+>                    WHERE ps_partkey IN (SELECT p_partkey
+>                                         FROM part
+>                                         WHERE p_name LIKE 'forest%')
+>                      AND ps_availqty > (SELECT 0.5 * sum(l_quantity)
+>                                         FROM lineitem
+>                                         WHERE l_partkey = ps_partkey
+>                                           AND l_suppkey = ps_suppkey
+>                                           AND l_shipdate >= DATE '1994-01-01'
+>                                           AND l_shipdate <  DATE '1994-01-01'
+>                                                              + INTERVAL '1' YEAR)
+>                     )
+>  AND s_nationkey = n_nationkey
+>  AND n_name = 'CANADA'
+>ORDER by s_name;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   2.314E+003 
21  ESP_EXCHANGE      20  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.800E-009   2.314E+003 
20  SORT              19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.888E-003   2.314E+003 
19  HYBRID_HASH_SEMI  18  12              4.028E+004   FUNKY_OPT_UNIQUE  5.476E-003   2.314E+003 
18  NESTED_JOIN       15  17              4.028E+004   FUNKY_OPT_UNIQUE  4.158E-004   4.234E+000 
17  PARTITION_ACCESS  16  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.232E+000 
16  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.232E+000   4.232E+000 
15  ESP_EXCHANGE      14  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
14  PARTITION_ACCESS  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
13  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
12  ESP_EXCHANGE      11  ?               1.898E+006   FUNKY_OPT_UNIQUE  1.351E-002   2.309E+003 
11  HASH_GROUPBY      10  ?               1.898E+006   FUNKY_OPT_UNIQUE  3.585E-002   2.309E+003 
10  HYBRID_HASH_JOIN  9   7               5.696E+006   FUNKY_OPT_UNIQUE  3.982E-001   2.309E+003 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   3.783E+002 
08  FILE_SCAN         ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  3.783E+002   3.783E+002 
07  ESP_EXCHANGE      6   ?               5.696E+006   FUNKY_OPT_UNIQUE  8.020E-002   1.931E+003 
06  HYBRID_HASH_JOIN  5   3               5.696E+006   FUNKY_OPT_UNIQUE  4.990E-001   1.930E+003 
05  PARTITION_ACCESS  4   ?               9.114E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.853E+003 
04  FILE_SCAN         ?   ?   LINEITEM    9.114E+007   FUNKY_OPT_UNIQUE  1.853E+003   1.853E+003 
03  ESP_EXCHANGE      2   ?               1.250E+006   FUNKY_OPT_UNIQUE  7.022E-002   7.659E+001 
02  PARTITION_ACCESS  1   ?               1.250E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.652E+001 
01  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  7.652E+001   7.652E+001 

--- 22 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

21   .    22   root                                                  4.02E+004
20   .    21   esp_exchange                    1:8(hash2) (m)        4.02E+004
19   .    20   sort                                                  4.02E+004
18   12   19   hybrid_hash_semi_joi                                  4.02E+004
15   17   18   nested_join                                           4.02E+004
16   .    17   partition_access                                      4.02E+004
.    .    16   file_scan             fr        SUPPLIER              4.02E+004
14   .    15   esp_exchange                    8(rep-b):1 (m)        1.00E+000
13   .    14   partition_access                                      1.00E+000
.    .    13   file_scan             fr        NATION                1.00E+000
11   .    12   esp_exchange                    8(hash2):8(hash2)     1.89E+006
10   .    11   hash_groupby                                          1.89E+006
9    7    10   hybrid_hash_join                                      5.69E+006
8    .    9    partition_access                                      8.00E+007
.    .    8    file_scan             fr        PARTSUPP              8.00E+007
6    .    7    esp_exchange                    8(hash2):8(hash2)     5.69E+006
5    3    6    hybrid_hash_join                                      5.69E+006
4    .    5    partition_access                                      9.11E+007
.    .    4    file_scan             fr        LINEITEM              9.11E+007
2    .    3    esp_exchange                    8(rep-b):8(hash2)     1.25E+006
1    .    2    partition_access                                      1.25E+006
.    .    1    file_scan             fr        PART                  1.25E+006

--- SQL operation complete.
>>obey optdml03(q21);
>>--------------------
>>--  TPCH Query 21 --
>>--------------------
>>prepare XX from
+>SELECT [FIRST 100] s_name, count(*) as numwait
+>FROM supplier, lineitem l1, part, orders, nation
+>WHERE s_suppkey = l1.l_suppkey
+>  AND l1.l_partkey = p_partkey
+>  AND o_orderkey = l1.l_orderkey
+>  AND o_orderstatus = 'F'
+>  AND l1.l_receiptdate > l1.l_commitdate
+>  AND EXISTS (SELECT *
+>              FROM lineitem l2
+>              WHERE l2.l_orderkey = l1.l_orderkey
+>                AND l2.l_suppkey <> l1.l_suppkey)
+>  AND NOT EXISTS (SELECT *
+>                  FROM lineitem l3
+>                  WHERE l3.l_orderkey = l1.l_orderkey
+>                    AND l3.l_suppkey <> l1.l_suppkey
+>                    AND l3.l_receiptdate > l3.l_commitdate)
+>  AND s_nationkey = n_nationkey
+>  AND n_name = 'SAUDI ARABIA'
+>GROUP BY s_name
+>ORDER BY numwait DESC, s_name;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.358E+003 
30  FIRSTN            29  ?               1.000E+006   FUNKY_OPT_UNIQUE  0.000E+000   4.358E+003 
29  ESP_EXCHANGE      28  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.800E-009   4.358E+003 
28  SORT              27  ?               1.000E+006   FUNKY_OPT_UNIQUE  6.661E-002   4.358E+003 
27  HASH_PARTIAL_GRO  26  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.570E-002   4.358E+003 
26  ESP_EXCHANGE      25  ?               1.231E+006   FUNKY_OPT_UNIQUE  6.349E-003   4.358E+003 
25  HASH_PARTIAL_GRO  24  ?               1.231E+006   FUNKY_OPT_UNIQUE  4.304E-003   4.358E+003 
24  HYBRID_HASH_JOIN  23  21              1.308E+006   FUNKY_OPT_UNIQUE  6.558E-002   4.358E+003 
23  PARTITION_ACCESS  22  ?               2.000E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.826E+000 
22  INDEX_SCAN        ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  4.826E+000   4.826E+000 
21  ESP_EXCHANGE      20  ?               1.308E+006   FUNKY_OPT_UNIQUE  2.526E-002   4.353E+003 
20  HYBRID_HASH_SEMI  19  3               1.308E+006   FUNKY_OPT_UNIQUE  9.847E+000   4.353E+003 
19  HYBRID_HASH_ANTI  18  5               1.308E+006   FUNKY_OPT_UNIQUE  7.514E-001   4.145E+003 
18  HYBRID_HASH_JOIN  17  7               3.925E+006   FUNKY_OPT_UNIQUE  2.680E-001   2.290E+003 
17  HYBRID_HASH_JOIN  16  14              8.049E+006   FUNKY_OPT_UNIQUE  9.099E-001   1.859E+003 
16  PARTITION_ACCESS  15  ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
15  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
14  ESP_EXCHANGE      13  ?               4.028E+004   FUNKY_OPT_UNIQUE  6.677E-003   4.240E+000 
13  NESTED_JOIN       10  12              4.028E+004   FUNKY_OPT_UNIQUE  2.658E-004   4.234E+000 
12  PARTITION_ACCESS  11  ?               4.028E+004   FUNKY_OPT_UNIQUE  0.000E+000   4.232E+000 
11  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  4.232E+000   4.232E+000 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.264E-004   1.409E-003 
09  PARTITION_ACCESS  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   1.283E-003 
08  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  1.283E-003   1.283E-003 
07  PARTITION_ACCESS  6   ?               7.307E+007   FUNKY_OPT_UNIQUE  0.000E+000   4.309E+002 
06  FILE_SCAN         ?   ?   ORDERS      7.307E+007   FUNKY_OPT_UNIQUE  4.309E+002   4.309E+002 
05  PARTITION_ACCESS  4   ?               1.997E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.854E+003 
04  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  1.854E+003   1.854E+003 
03  ESP_EXCHANGE      2   ?               5.994E+008   FUNKY_OPT_UNIQUE  5.934E+000   1.982E+002 
02  PARTITION_ACCESS  1   ?               5.994E+008   FUNKY_OPT_UNIQUE  0.000E+000   1.923E+002 
01  INDEX_SCAN        ?   ?   LINEITEM)   5.994E+008   FUNKY_OPT_UNIQUE  1.923E+002   1.923E+002 

--- 31 row(s) selected.
>>
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

30   .    31   root                                                  1.00E+006
29   .    30   firstn                                                1.00E+006
28   .    29   esp_exchange                    1:8(hash2) (m)        1.00E+006
27   .    28   sort                                                  1.00E+006
26   .    27   hash_partial_groupby                                  1.00E+006
25   .    26   esp_exchange                    8(hash2):8(hash2)     1.23E+006
24   .    25   hash_partial_groupby                                  1.23E+006
23   21   24   hybrid_hash_join                                      1.30E+006
22   .    23   partition_access                                      2.00E+007
.    .    22   index_scan            fr        PAIDX1                2.00E+007
20   .    21   esp_exchange                    8(hash2):8(hash2)     1.30E+006
19   3    20   hybrid_hash_semi_joi                                  1.30E+006
18   5    19   hybrid_hash_anti_sem                                  1.30E+006
17   7    18   hybrid_hash_join                                      3.92E+006
16   14   17   hybrid_hash_join      u                               8.04E+006
15   .    16   partition_access                                      1.99E+008
.    .    15   file_scan             fr        LINEITEM              1.99E+008
13   .    14   esp_exchange                    8(rep-b):8(hash2)     4.02E+004
10   12   13   nested_join                                           4.02E+004
11   .    12   partition_access                                      4.02E+004
.    .    11   file_scan             fr        SUPPLIER              4.02E+004
9    .    10   esp_exchange                    8(rep-b):1 (m)        1.00E+000
8    .    9    partition_access                                      1.00E+000
.    .    8    file_scan             fr        NATION                1.00E+000
6    .    7    partition_access                                      7.30E+007
.    .    6    file_scan             fr        ORDERS                7.30E+007
4    .    5    partition_access                                      1.99E+008
.    .    4    file_scan             fr        LINEITEM              1.99E+008
2    .    3    esp_exchange                    8(hash2):8(hash2)     5.99E+008
1    .    2    partition_access                                      5.99E+008
.    .    1    index_scan            fr        LIIDX3                5.99E+008

--- SQL operation complete.
>>obey optdml03(q22);
>>--------------------
>>--  TPCH Query 22 --
>>--------------------
>>prepare XX from
+>SELECT cntrycode, count(*) AS numcust, sum(c_acctbal) AS totacctbal
+>FROM (SELECT SUBSTRING(c_phone FROM 1 FOR 2) AS cntrycode, c_acctbal
+>      FROM customer
+>      WHERE SUBSTRING(c_phone FROM 1 FOR 2) IN ('13','17','18','23','29','30','31')
+>        AND c_acctbal > (SELECT AVG(c_acctbal)
+>                         FROM customer
+>                         WHERE c_acctbal > 0.00
+>                           AND SUBSTRING (c_phone FROM 1 FOR 2) IN
+>                                               ('13','17','18','23','29','30','31'))
+>        AND NOT EXISTS (SELECT *
+>                        FROM orders
+>                        WHERE o_custkey = c_custkey)
+>       ) AS custsale
+>GROUP BY cntrycode
+>ORDER BY cntrycode;

--- SQL command prepared.
>>
>>execute explainIt;

S   OPERATOR          LC  RC  TAB_NAME    CARDINAL     (EXPR)            OP_COST      TOT_COST
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               1.000E+002   FUNKY_OPT_UNIQUE  0.000E+000   1.873E+002 
17  ESP_EXCHANGE      16  ?               1.000E+002   FUNKY_OPT_UNIQUE  2.800E-009   1.873E+002 
16  SORT              15  ?               1.300E+001   FUNKY_OPT_UNIQUE  1.123E-007   1.873E+002 
15  HASH_PARTIAL_GRO  14  ?               1.300E+001   FUNKY_OPT_UNIQUE  6.311E-007   1.873E+002 
14  ESP_EXCHANGE      13  ?               1.000E+002   FUNKY_OPT_UNIQUE  1.256E-004   1.873E+002 
13  HASH_PARTIAL_GRO  12  ?               1.000E+002   FUNKY_OPT_UNIQUE  1.755E-003   1.873E+002 
12  HYBRID_HASH_ANTI  11  2               1.653E+006   FUNKY_OPT_UNIQUE  4.132E-001   1.873E+002 
11  NESTED_JOIN       8   10              4.960E+006   FUNKY_OPT_UNIQUE  2.430E-002   1.507E+002 
10  PARTITION_ACCESS  9   ?               4.960E+006   FUNKY_OPT_UNIQUE  0.000E+000   7.532E+001 
09  FILE_SCAN         ?   ?   CUSTOMER    4.960E+006   FUNKY_OPT_UNIQUE  7.532E+001   7.532E+001 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.682E-004   7.536E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.068E-008   7.536E+001 
06  SPLIT_TOP         5   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   7.536E+001 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.187E-007   7.536E+001 
04  SORT_PARTIAL_AGG  3   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.281E-002   7.536E+001 
03  FILE_SCAN         ?   ?   CUSTOMER    1.352E+007   FUNKY_OPT_UNIQUE  7.533E+001   7.533E+001 
02  PARTITION_ACCESS  1   ?               1.500E+008   FUNKY_OPT_UNIQUE  0.000E+000   3.619E+001 
01  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  3.619E+001   3.619E+001 

--- 18 row(s) selected.
>>explain options 'f' xx;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

17   .    18   root                                                  1.00E+002
16   .    17   esp_exchange                    1:8(hash2) (m)        1.00E+002
15   .    16   sort                                                  1.29E+001
14   .    15   hash_partial_groupby                                  1.29E+001
13   .    14   esp_exchange                    8(hash2):8(hash2)     1.00E+002
12   .    13   hash_partial_groupby                                  1.00E+002
11   2    12   hybrid_hash_anti_sem                                  1.65E+006
8    10   11   nested_join                                           4.96E+006
9    .    10   partition_access                                      4.96E+006
.    .    9    file_scan             fr        CUSTOMER              4.96E+006
7    .    8    esp_exchange                    8(rep-b):1            1.00E+000
6    .    7    sort_partial_aggr_ro                                  1.00E+000
5    .    6    split_top                       1:8(hash2)            1.00E+000
4    .    5    partition_access                                      1.00E+000
3    .    4    sort_partial_aggr_le                                  1.00E+000
.    .    3    file_scan                       CUSTOMER              1.35E+007
1    .    2    partition_access                                      1.50E+008
.    .    1    index_scan            fr        ORDIDX2               1.50E+008

--- SQL operation complete.
>>
>>?section stopLog
>>---------------------------
>>--  Stop logging output. --
>>---------------------------
>>LOG;
