-- @@@ 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 SF100 test run. --
>>-------------------------------------------------
>>control query default ARKCMP_FAKE_HW 'ON';

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

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

--- 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 'MAXIMUM';

--- 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.
>>
>>control query default DP2_MAX_READ_PER_ACCESS_IN_KB '56';

--- SQL operation complete.
>>control query default DP2_MESSAGE_BUFFER_SIZE '32';

--- 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 optdml02(q01);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

09  ROOT              8   ?               6.000E+000   FUNKY_OPT_UNIQUE  5.411E-007   5.063E+002 
08  ESP_EXCHANGE      7   ?               6.000E+000   FUNKY_OPT_UNIQUE  3.650E-003   5.063E+002 
07  SORT_PARTIAL_GRO  6   ?               6.000E+000   FUNKY_OPT_UNIQUE  8.616E-006   5.063E+002 
06  SORT              5   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.742E-002   5.063E+002 
05  ESP_EXCHANGE      4   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.644E-003   5.063E+002 
04  SPLIT_TOP         3   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.973E-002   5.063E+002 
03  PARTITION_ACCESS  2   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.973E-002   5.063E+002 
02  HASH_PARTIAL_GRO  1   ?               6.000E+000   FUNKY_OPT_UNIQUE  3.726E+002   5.063E+002 
01  INDEX_SCAN        ?   ?   LINEITEM    5.864E+008   FUNKY_OPT_UNIQUE  1.336E+002   1.336E+002 

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

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

8    .    9    root                                                  6.00E+000
7    .    8    esp_exchange                    1:4(hash2)            6.00E+000
6    .    7    sort_partial_groupby                                  6.00E+000
5    .    6    sort                                                  6.00E+000
4    .    5    esp_exchange                    4(hash2):4(range)     6.00E+000
3    .    4    split_top                       4(range):72(range)    6.00E+000
2    .    3    partition_access                                      6.00E+000
1    .    2    hash_partial_groupby                                  6.00E+000
.    .    1    index_scan                      LX3                   5.86E+008

--- SQL operation complete.
>>obey optdml02(q02);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

42  ROOT              41  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.010E-007   2.053E+001 
41  FIRSTN            40  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.053E+001 
40  ESP_EXCHANGE      39  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.777E-001   2.053E+001 
39  SORT              38  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   2.036E+001 
38  HASH_GROUPBY      37  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.043E-001   2.034E+001 
37  HYBRID_HASH_JOIN  36  3               1.598E+004   FUNKY_OPT_UNIQUE  5.463E-002   2.013E+001 
36  HYBRID_HASH_JOIN  35  6               7.991E+004   FUNKY_OPT_UNIQUE  6.028E-002   2.007E+001 
35  HYBRID_HASH_JOIN  34  31              7.991E+004   FUNKY_OPT_UNIQUE  7.449E-001   2.000E+001 
34  SPLIT_TOP         33  ?               1.000E+006   FUNKY_OPT_UNIQUE  4.373E+000   5.041E+000 
33  PARTITION_ACCESS  32  ?               1.000E+006   FUNKY_OPT_UNIQUE  4.373E+000   5.041E+000 
32  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  6.671E-001   6.671E-001 
31  ESP_EXCHANGE      30  ?               7.991E+004   FUNKY_OPT_UNIQUE  1.844E-001   1.425E+001 
30  NESTED_JOIN       26  29              7.991E+004   FUNKY_OPT_UNIQUE  8.697E-004   1.407E+001 
29  SPLIT_TOP         28  ?               4.000E+000   FUNKY_OPT_UNIQUE  1.625E+000   3.868E+000 
28  PARTITION_ACCESS  27  ?               4.000E+000   FUNKY_OPT_UNIQUE  1.625E+000   3.868E+000 
27  INDEX_SCAN        ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  2.242E+000   2.242E+000 
26  HYBRID_HASH_JOIN  25  9               1.997E+004   FUNKY_OPT_UNIQUE  6.828E-002   1.020E+001 
25  HYBRID_HASH_JOIN  24  12              9.989E+004   FUNKY_OPT_UNIQUE  7.532E-002   1.012E+001 
24  HYBRID_HASH_JOIN  23  20              9.989E+004   FUNKY_OPT_UNIQUE  7.643E-001   1.004E+001 
23  SPLIT_TOP         22  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.061E+000 
22  PARTITION_ACCESS  21  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.061E+000 
21  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  5.275E-001   5.275E-001 
20  ESP_EXCHANGE      19  ?               9.989E+004   FUNKY_OPT_UNIQUE  2.097E-001   8.236E+000 
19  NESTED_JOIN       15  18              9.989E+004   FUNKY_OPT_UNIQUE  1.087E-003   8.026E+000 
18  SPLIT_TOP         17  ?               4.000E+000   FUNKY_OPT_UNIQUE  2.018E+000   4.437E+000 
17  PARTITION_ACCESS  16  ?               4.000E+000   FUNKY_OPT_UNIQUE  2.018E+000   4.437E+000 
16  INDEX_SCAN        ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  2.419E+000   2.419E+000 
15  SPLIT_TOP         14  ?               2.497E+004   FUNKY_OPT_UNIQUE  4.050E-002   3.587E+000 
14  PARTITION_ACCESS  13  ?               2.497E+004   FUNKY_OPT_UNIQUE  4.050E-002   3.587E+000 
13  INDEX_SCAN        ?   ?   PART        2.497E+004   FUNKY_OPT_UNIQUE  3.547E+000   3.547E+000 
12  ESP_EXCHANGE      11  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   1.078E-002 
11  PARTITION_ACCESS  10  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.342E-003 
10  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  7.905E-003   7.905E-003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   6.879E-003 
08  PARTITION_ACCESS  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.438E-003 
07  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  4.001E-003   4.001E-003 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   1.078E-002 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.343E-003 
04  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  7.907E-003   7.907E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.442E-003   6.881E-003 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   4.438E-003 
01  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  4.001E-003   4.001E-003 

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

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

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

--- SQL operation complete.
>>obey optdml02(q03);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

17  ROOT              16  ?               4.717E+007   FUNKY_OPT_UNIQUE  1.264E+000   1.435E+003 
16  FIRSTN            15  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.433E+003 
15  ESP_EXCHANGE      14  ?               4.717E+007   FUNKY_OPT_UNIQUE  1.744E-001   1.433E+003 
14  SORT              13  ?               4.717E+007   FUNKY_OPT_UNIQUE  8.181E+002   1.433E+003 
13  HYBRID_HASH_JOIN  12  8               4.717E+007   FUNKY_OPT_UNIQUE  7.208E+001   6.154E+002 
12  SPLIT_TOP         11  ?               7.291E+007   FUNKY_OPT_UNIQUE  5.463E+001   3.812E+002 
11  PARTITION_ACCESS  10  ?               7.291E+007   FUNKY_OPT_UNIQUE  5.463E+001   3.812E+002 
10  SORT_GROUPBY      9   ?               7.291E+007   FUNKY_OPT_UNIQUE  1.012E+002   3.266E+002 
09  FILE_SCAN         ?   ?   LINEITEM    3.231E+008   FUNKY_OPT_UNIQUE  2.253E+002   2.253E+002 
08  ESP_EXCHANGE      7   ?               2.187E+007   FUNKY_OPT_UNIQUE  2.849E+001   1.623E+002 
07  HYBRID_HASH_JOIN  6   3               2.187E+007   FUNKY_OPT_UNIQUE  5.312E+001   1.338E+002 
06  SPLIT_TOP         5   ?               7.291E+007   FUNKY_OPT_UNIQUE  4.884E+001   7.123E+001 
05  PARTITION_ACCESS  4   ?               7.291E+007   FUNKY_OPT_UNIQUE  4.884E+001   7.123E+001 
04  INDEX_SCAN        ?   ?   ORDERS      7.291E+007   FUNKY_OPT_UNIQUE  2.238E+001   2.238E+001 
03  SPLIT_TOP         2   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.317E+000   9.721E+000 
02  PARTITION_ACCESS  1   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.317E+000   9.721E+000 
01  FILE_SCAN         ?   ?   CUSTOMER    3.000E+006   FUNKY_OPT_UNIQUE  8.403E+000   8.403E+000 

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

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

16   .    17   root                                                  4.71E+007
15   .    16   firstn                                                4.71E+007
14   .    15   esp_exchange                    1:4(range)            4.71E+007
13   .    14   sort                                                  4.71E+007
12   8    13   hybrid_hash_join                                      4.71E+007
11   .    12   split_top                       4(range):64(range)    7.29E+007
10   .    11   partition_access                                      7.29E+007
9    .    10   sort_groupby                                          7.29E+007
.    .    9    file_scan                       LINEITEM              3.23E+008
7    .    8    esp_exchange                    4(range):4(range)     2.18E+007
6    3    7    hybrid_hash_join      u                               2.18E+007
5    .    6    split_top                       4(range):64(range)    7.29E+007
4    .    5    partition_access                                      7.29E+007
.    .    4    index_scan            fr        OX2                   7.29E+007
2    .    3    split_top                       4(range):64(range)    3.00E+006
1    .    2    partition_access                                      3.00E+006
.    .    1    file_scan             fr        CUSTOMER              3.00E+006

--- SQL operation complete.
>>obey optdml02(q04);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               5.000E+000   FUNKY_OPT_UNIQUE  1.401E-007   6.142E+002 
12  ESP_EXCHANGE      11  ?               5.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   6.142E+002 
11  SORT_PARTIAL_GRO  10  ?               5.000E+000   FUNKY_OPT_UNIQUE  4.854E-006   6.142E+002 
10  SORT              9   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   6.142E+002 
09  ESP_EXCHANGE      8   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.591E-004   6.142E+002 
08  HASH_PARTIAL_GRO  7   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.843E+000   6.142E+002 
07  HYBRID_HASH_SEMI  6   3               7.730E+006   FUNKY_OPT_UNIQUE  2.418E+002   6.124E+002 
06  SPLIT_TOP         5   ?               5.798E+006   FUNKY_OPT_UNIQUE  4.240E+000   6.180E+001 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  4.240E+000   6.180E+001 
04  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  5.756E+001   5.756E+001 
03  SPLIT_TOP         2   ?               1.997E+008   FUNKY_OPT_UNIQUE  8.661E+001   3.087E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  8.661E+001   3.087E+002 
01  FILE_SCAN         ?   ?   LINEITEM    1.997E+008   FUNKY_OPT_UNIQUE  2.221E+002   2.221E+002 

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

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

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

--- SQL operation complete.
>>obey optdml02(q05);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.066E-006   5.417E+002 
30  ESP_EXCHANGE      29  ?               2.500E+001   FUNKY_OPT_UNIQUE  3.654E-003   5.417E+002 
29  SORT              28  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.742E-002   5.417E+002 
28  HASH_PARTIAL_GRO  27  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.069E-005   5.417E+002 
27  ESP_EXCHANGE      26  ?               2.500E+001   FUNKY_OPT_UNIQUE  6.671E-004   5.417E+002 
26  HASH_PARTIAL_GRO  25  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.197E-001   5.417E+002 
25  HYBRID_HASH_JOIN  24  4               7.302E+005   FUNKY_OPT_UNIQUE  3.055E+001   5.415E+002 
24  NESTED_JOIN       20  23              1.825E+007   FUNKY_OPT_UNIQUE  1.986E-001   5.134E+002 
23  SPLIT_TOP         22  ?               4.000E+000   FUNKY_OPT_UNIQUE  3.340E+002   4.483E+002 
22  PARTITION_ACCESS  21  ?               4.000E+000   FUNKY_OPT_UNIQUE  3.340E+002   4.483E+002 
21  FILE_SCAN         ?   ?   LINEITEM    4.000E+000   FUNKY_OPT_UNIQUE  1.143E+002   1.143E+002 
20  HYBRID_HASH_JOIN  19  16              4.563E+006   FUNKY_OPT_UNIQUE  1.827E+001   6.488E+001 
19  SPLIT_TOP         18  ?               2.281E+007   FUNKY_OPT_UNIQUE  1.170E+001   3.859E+001 
18  PARTITION_ACCESS  17  ?               2.281E+007   FUNKY_OPT_UNIQUE  1.170E+001   3.859E+001 
17  INDEX_SCAN        ?   ?   ORDERS      2.281E+007   FUNKY_OPT_UNIQUE  2.688E+001   2.688E+001 
16  ESP_EXCHANGE      15  ?               3.000E+006   FUNKY_OPT_UNIQUE  5.258E+000   8.074E+000 
15  NESTED_JOIN       11  14              3.000E+006   FUNKY_OPT_UNIQUE  2.611E-002   2.816E+000 
14  SPLIT_TOP         13  ?               6.000E+005   FUNKY_OPT_UNIQUE  1.086E+000   2.773E+000 
13  PARTITION_ACCESS  12  ?               6.000E+005   FUNKY_OPT_UNIQUE  1.086E+000   2.773E+000 
12  INDEX_SCAN        ?   ?   CUSTOMER    6.000E+005   FUNKY_OPT_UNIQUE  1.686E+000   1.686E+000 
11  HYBRID_HASH_JOIN  10  7               5.000E+000   FUNKY_OPT_UNIQUE  3.072E-005   1.680E-002 
10  ESP_EXCHANGE      9   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.021E-003   1.036E-002 
09  PARTITION_ACCESS  8   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.343E-003 
08  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  7.907E-003   7.907E-003 
07  ESP_EXCHANGE      6   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.000E-003   6.439E-003 
06  PARTITION_ACCESS  5   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   4.438E-003 
05  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  4.001E-003   4.001E-003 
04  ESP_EXCHANGE      3   ?               1.000E+006   FUNKY_OPT_UNIQUE  2.225E+000   2.746E+000 
03  SPLIT_TOP         2   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.205E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.205E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  1.342E-001   1.342E-001 

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

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

30   .    31   root                                                  2.50E+001
29   .    30   esp_exchange                    1:4(hash2)            2.50E+001
28   .    29   sort                                                  2.50E+001
27   .    28   hash_partial_groupby                                  2.50E+001
26   .    27   esp_exchange                    4(hash2):4(range)     2.50E+001
25   .    26   hash_partial_groupby                                  2.50E+001
24   4    25   hybrid_hash_join      u                               7.30E+005
20   23   24   nested_join                                           1.82E+007
22   .    23   split_top                       4(rep-n):64(range)    4.00E+000
21   .    22   partition_access                                      4.00E+000
.    .    21   file_scan             fr        LINEITEM              4.00E+000
19   16   20   hybrid_hash_join                                      4.56E+006
18   .    19   split_top                       4(range):64(range)    2.28E+007
17   .    18   partition_access                                      2.28E+007
.    .    17   index_scan            fr        OX2                   2.28E+007
15   .    16   esp_exchange                    4(range):4(hash2)     3.00E+006
11   14   15   nested_join                                           3.00E+006
13   .    14   split_top                       4(rep-n):12(range)    6.00E+005
12   .    13   partition_access                                      6.00E+005
.    .    12   index_scan            fr        CX1                   6.00E+005
10   7    11   hybrid_hash_join      u                               5.00E+000
9    .    10   esp_exchange                    4(hash2):1            2.50E+001
8    .    9    partition_access                                      2.50E+001
.    .    8    file_scan             fr        NATION                2.50E+001
6    .    7    esp_exchange                    4(hash2):1            1.00E+000
5    .    6    partition_access                                      1.00E+000
.    .    5    file_scan             fr        REGION                1.00E+000
3    .    4    esp_exchange                    4(rep-b):4(range)     1.00E+006
2    .    3    split_top                       4(range):12(range)    1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SX1                   1.00E+006

--- SQL operation complete.
>>obey optdml02(q06);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

08  ROOT              7   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   2.333E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.036E-001   2.333E+001 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   2.303E+001 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.591E-002   2.302E+001 
04  SPLIT_TOP         3   ?               5.717E+006   FUNKY_OPT_UNIQUE  4.101E+000   2.295E+001 
03  PARTITION_ACCESS  2   ?               5.717E+006   FUNKY_OPT_UNIQUE  4.101E+000   2.295E+001 
02  SORT_PARTIAL_AGG  1   ?               5.717E+006   FUNKY_OPT_UNIQUE  1.518E-001   1.885E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    1.143E+007   FUNKY_OPT_UNIQUE  1.869E+001   1.869E+001 

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

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

7    .    8    root                                                  1.00E+000
6    .    7    sort_partial_aggr_ro                                  1.00E+000
5    .    6    esp_exchange                    1:4(range)            1.00E+000
4    .    5    sort_partial_aggr_no                                  1.00E+000
3    .    4    split_top                       4(range):72(range)    5.71E+006
2    .    3    partition_access                                      5.71E+006
1    .    2    sort_partial_aggr_le                                  5.71E+006
.    .    1    index_scan                      LX3 (m)               1.14E+007

--- SQL operation complete.
>>obey optdml02(q07);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.280E-004   8.454E+002 
30  ESP_EXCHANGE      29  ?               2.924E+003   FUNKY_OPT_UNIQUE  1.526E-002   8.454E+002 
29  SORT_PARTIAL_GRO  28  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.396E-003   8.454E+002 
28  SORT              27  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.785E-002   8.454E+002 
27  ESP_EXCHANGE      26  ?               2.924E+003   FUNKY_OPT_UNIQUE  4.715E-003   8.454E+002 
26  HASH_PARTIAL_GRO  25  ?               2.924E+003   FUNKY_OPT_UNIQUE  4.452E-001   8.454E+002 
25  HYBRID_HASH_JOIN  24  4               5.828E+005   FUNKY_OPT_UNIQUE  4.265E+001   8.449E+002 
24  ESP_EXCHANGE      23  ?               1.457E+007   FUNKY_OPT_UNIQUE  4.733E+001   7.670E+002 
23  HYBRID_HASH_JOIN  22  19              1.457E+007   FUNKY_OPT_UNIQUE  2.156E+002   7.196E+002 
22  SPLIT_TOP         21  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.299E+002 
21  PARTITION_ACCESS  20  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.299E+002 
20  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  5.313E+001   5.313E+001 
19  ESP_EXCHANGE      18  ?               1.457E+007   FUNKY_OPT_UNIQUE  3.769E+001   3.744E+002 
18  HYBRID_HASH_JOIN  17  14              1.457E+007   FUNKY_OPT_UNIQUE  1.228E+002   3.367E+002 
17  SPLIT_TOP         16  ?               1.821E+008   FUNKY_OPT_UNIQUE  1.445E+002   2.124E+002 
16  PARTITION_ACCESS  15  ?               1.821E+008   FUNKY_OPT_UNIQUE  1.445E+002   2.124E+002 
15  INDEX_SCAN        ?   ?   LINEITEM    1.821E+008   FUNKY_OPT_UNIQUE  6.793E+001   6.793E+001 
14  ESP_EXCHANGE      13  ?               8.000E+004   FUNKY_OPT_UNIQUE  1.724E+000   1.869E+000 
13  NESTED_JOIN       9   12              8.000E+004   FUNKY_OPT_UNIQUE  2.786E-003   1.441E-001 
12  SPLIT_TOP         11  ?               4.000E+004   FUNKY_OPT_UNIQUE  6.595E-002   1.245E-001 
11  PARTITION_ACCESS  10  ?               4.000E+004   FUNKY_OPT_UNIQUE  6.595E-002   1.245E-001 
10  INDEX_SCAN        ?   ?   SUPPLIER    4.000E+004   FUNKY_OPT_UNIQUE  5.855E-002   5.855E-002 
09  HYBRID_HASH_JOIN  8   6               2.000E+000   FUNKY_OPT_UNIQUE  1.112E-003   1.670E-002 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.340E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  7.903E-003   7.903E-003 
06  PARTITION_ACCESS  5   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.340E-003 
05  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  7.903E-003   7.903E-003 
04  ESP_EXCHANGE      3   ?               1.500E+007   FUNKY_OPT_UNIQUE  2.817E+001   3.573E+001 
03  SPLIT_TOP         2   ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.559E+000 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.559E+000 
01  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.804E+000   1.804E+000 

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

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

30   .    31   root                                                  2.92E+003
29   .    30   esp_exchange                    1:4(hash2)            2.92E+003
28   .    29   sort_partial_groupby                                  2.92E+003
27   .    28   sort                                                  2.92E+003
26   .    27   esp_exchange                    4(hash2):4(hash2)     2.92E+003
25   .    26   hash_partial_groupby                                  2.92E+003
24   4    25   hybrid_hash_join                                      5.82E+005
23   .    24   esp_exchange                    4(hash2):4(range)     1.45E+007
22   19   23   hybrid_hash_join                                      1.45E+007
21   .    22   split_top                       4(range):64(range)    1.50E+008
20   .    21   partition_access                                      1.50E+008
.    .    20   file_scan             fr        ORDERS                1.50E+008
18   .    19   esp_exchange                    4(range):4(range)     1.45E+007
17   14   18   hybrid_hash_join                                      1.45E+007
16   .    17   split_top                       4(range):72(range)    1.82E+008
15   .    16   partition_access                                      1.82E+008
.    .    15   index_scan            fr        LX3 (m)               1.82E+008
13   .    14   esp_exchange                    4(rep-b):1            8.00E+004
9    12   13   nested_join                                           8.00E+004
11   .    12   split_top                       1:12(range)           4.00E+004
10   .    11   partition_access                                      4.00E+004
.    .    10   index_scan            fr        SX1                   4.00E+004
8    6    9    hybrid_hash_join                                      2.00E+000
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    partition_access                                      2.50E+001
.    .    5    file_scan             fr        NATION                2.50E+001
3    .    4    esp_exchange                    4(hash2):4(range)     1.50E+007
2    .    3    split_top                       4(range):12(range)    1.50E+007
1    .    2    partition_access                                      1.50E+007
.    .    1    index_scan            fr        CX1                   1.50E+007

--- SQL operation complete.
>>obey optdml02(q08);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

40  ROOT              39  ?               7.310E+002   FUNKY_OPT_UNIQUE  1.247E-005   1.842E+002 
39  ESP_EXCHANGE      38  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.627E-003   1.842E+002 
38  SORT_PARTIAL_GRO  37  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.493E-004   1.842E+002 
37  SORT              36  ?               7.310E+002   FUNKY_OPT_UNIQUE  1.806E-002   1.842E+002 
36  ESP_EXCHANGE      35  ?               7.310E+002   FUNKY_OPT_UNIQUE  9.353E-004   1.842E+002 
35  HASH_PARTIAL_GRO  34  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.229E-002   1.842E+002 
34  HYBRID_HASH_JOIN  33  3               2.432E+005   FUNKY_OPT_UNIQUE  1.832E-001   1.842E+002 
33  HYBRID_HASH_JOIN  32  29              2.432E+005   FUNKY_OPT_UNIQUE  9.039E-001   1.840E+002 
32  SPLIT_TOP         31  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.061E+000 
31  PARTITION_ACCESS  30  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.061E+000 
30  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  5.275E-001   5.275E-001 
29  ESP_EXCHANGE      28  ?               2.432E+005   FUNKY_OPT_UNIQUE  3.950E-001   1.820E+002 
28  HYBRID_HASH_JOIN  27  6               2.432E+005   FUNKY_OPT_UNIQUE  8.312E-001   1.816E+002 
27  HYBRID_HASH_JOIN  26  9               1.216E+006   FUNKY_OPT_UNIQUE  9.160E-001   1.808E+002 
26  HYBRID_HASH_JOIN  25  22              1.216E+006   FUNKY_OPT_UNIQUE  1.443E+001   1.799E+002 
25  SPLIT_TOP         24  ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.559E+000 
24  PARTITION_ACCESS  23  ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.559E+000 
23  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.804E+000   1.804E+000 
22  ESP_EXCHANGE      21  ?               1.216E+006   FUNKY_OPT_UNIQUE  4.784E+000   1.581E+002 
21  HYBRID_HASH_JOIN  20  17              1.216E+006   FUNKY_OPT_UNIQUE  4.472E+001   1.533E+002 
20  SPLIT_TOP         19  ?               4.560E+007   FUNKY_OPT_UNIQUE  2.697E+001   5.419E+001 
19  PARTITION_ACCESS  18  ?               4.560E+007   FUNKY_OPT_UNIQUE  2.697E+001   5.419E+001 
18  INDEX_SCAN        ?   ?   ORDERS      4.560E+007   FUNKY_OPT_UNIQUE  2.721E+001   2.721E+001 
17  ESP_EXCHANGE      16  ?               3.996E+006   FUNKY_OPT_UNIQUE  1.434E+001   5.454E+001 
16  NESTED_JOIN       12  15              3.996E+006   FUNKY_OPT_UNIQUE  3.595E-002   4.019E+001 
15  SPLIT_TOP         14  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.303E+001   4.003E+001 
14  PARTITION_ACCESS  13  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.303E+001   4.003E+001 
13  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  2.699E+001   2.699E+001 
12  SPLIT_TOP         11  ?               1.333E+005   FUNKY_OPT_UNIQUE  6.271E-002   1.239E-001 
11  PARTITION_ACCESS  10  ?               1.333E+005   FUNKY_OPT_UNIQUE  6.271E-002   1.239E-001 
10  INDEX_SCAN        ?   ?   PART        1.333E+005   FUNKY_OPT_UNIQUE  6.123E-002   6.123E-002 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.011E-003   1.035E-002 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.342E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  7.905E-003   7.905E-003 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.442E-003   6.881E-003 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   4.438E-003 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  4.001E-003   4.001E-003 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   1.078E-002 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.340E-003 
01  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  7.903E-003   7.903E-003 

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

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

39   .    40   root                                                  7.30E+002
38   .    39   esp_exchange                    1:4(hash2)            7.30E+002
37   .    38   sort_partial_groupby                                  7.30E+002
36   .    37   sort                                                  7.30E+002
35   .    36   esp_exchange                    4(hash2):4(range)     7.30E+002
34   .    35   hash_partial_groupby                                  7.30E+002
33   3    34   hybrid_hash_join      u                               2.43E+005
32   29   33   hybrid_hash_join                                      2.43E+005
31   .    32   split_top                       4(range):72(range)    1.00E+006
30   .    31   partition_access                                      1.00E+006
.    .    30   file_scan             fr        SUPPLIER              1.00E+006
28   .    29   esp_exchange                    4(range):4(range)     2.43E+005
27   6    28   hybrid_hash_join      u                               2.43E+005
26   9    27   hybrid_hash_join      u                               1.21E+006
25   22   26   hybrid_hash_join                                      1.21E+006
24   .    25   split_top                       4(range):12(range)    1.50E+007
23   .    24   partition_access                                      1.50E+007
.    .    23   index_scan            fr        CX1                   1.50E+007
21   .    22   esp_exchange                    4(rep-b):4(range)     1.21E+006
20   17   21   hybrid_hash_join                                      1.21E+006
19   .    20   split_top                       4(range):64(range)    4.56E+007
18   .    19   partition_access                                      4.56E+007
.    .    18   index_scan            fr        OX2                   4.56E+007
16   .    17   esp_exchange                    4(rep-b):4(range)     3.99E+006
12   15   16   nested_join                                           3.99E+006
14   .    15   split_top                       4(rep-n):72(range)    2.99E+001
13   .    14   partition_access                                      2.99E+001
.    .    13   index_scan            fr        LX2                   2.99E+001
11   .    12   split_top                       4(range):72(range)    1.33E+005
10   .    11   partition_access                                      1.33E+005
.    .    10   index_scan            fr        PX1                   1.33E+005
8    .    9    esp_exchange                    4(range):1            2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    4(rep-b):1            1.00E+000
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    4(rep-b):1            2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml02(q09);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               6.015E+004   FUNKY_OPT_UNIQUE  2.712E-003   1.045E+003 
30  ESP_EXCHANGE      29  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.512E-001   1.045E+003 
29  SORT_PARTIAL_GRO  28  ?               6.015E+004   FUNKY_OPT_UNIQUE  4.201E-002   1.045E+003 
28  SORT              27  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.949E-001   1.045E+003 
27  ESP_EXCHANGE      26  ?               6.015E+004   FUNKY_OPT_UNIQUE  5.343E-002   1.045E+003 
26  HASH_PARTIAL_GRO  25  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.748E+001   1.045E+003 
25  HYBRID_HASH_JOIN  24  3               3.746E+007   FUNKY_OPT_UNIQUE  2.821E+001   1.027E+003 
24  HYBRID_HASH_JOIN  23  20              3.746E+007   FUNKY_OPT_UNIQUE  2.357E+002   9.994E+002 
23  SPLIT_TOP         22  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.247E+002 
22  PARTITION_ACCESS  21  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.247E+002 
21  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  4.790E+001   4.790E+001 
20  HYBRID_HASH_JOIN  19  7               3.746E+007   FUNKY_OPT_UNIQUE  3.176E+001   6.392E+002 
19  ESP_EXCHANGE      18  ?               3.746E+007   FUNKY_OPT_UNIQUE  5.520E+001   6.052E+002 
18  HYBRID_HASH_JOIN  17  14              3.746E+007   FUNKY_OPT_UNIQUE  2.615E+002   5.500E+002 
17  SPLIT_TOP         16  ?               8.000E+007   FUNKY_OPT_UNIQUE  5.394E+001   6.601E+001 
16  PARTITION_ACCESS  15  ?               8.000E+007   FUNKY_OPT_UNIQUE  5.394E+001   6.601E+001 
15  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  1.206E+001   1.206E+001 
14  NESTED_JOIN       10  13              3.746E+007   FUNKY_OPT_UNIQUE  3.370E-001   2.226E+002 
13  SPLIT_TOP         12  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.279E+002   2.128E+002 
12  PARTITION_ACCESS  11  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.279E+002   2.128E+002 
11  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  8.492E+001   8.492E+001 
10  SPLIT_TOP         9   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.432E+000 
09  PARTITION_ACCESS  8   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.432E+000 
08  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  8.867E+000   8.867E+000 
07  ESP_EXCHANGE      6   ?               1.000E+006   FUNKY_OPT_UNIQUE  2.225E+000   2.746E+000 
06  SPLIT_TOP         5   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.205E-001 
05  PARTITION_ACCESS  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.205E-001 
04  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  1.342E-001   1.342E-001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   1.078E-002 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.340E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  7.903E-003   7.903E-003 

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

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

30   .    31   root                                                  6.01E+004
29   .    30   esp_exchange                    1:4(hash2)            6.01E+004
28   .    29   sort_partial_groupby                                  6.01E+004
27   .    28   sort                                                  6.01E+004
26   .    27   esp_exchange                    4(hash2):4(range)     6.01E+004
25   .    26   hash_partial_groupby                                  6.01E+004
24   3    25   hybrid_hash_join      u                               3.74E+007
23   20   24   hybrid_hash_join                                      3.74E+007
22   .    23   split_top                       4(range):64(range)    1.50E+008
21   .    22   partition_access                                      1.50E+008
.    .    21   file_scan             fr        ORDERS                1.50E+008
19   7    20   hybrid_hash_join      u                               3.74E+007
18   .    19   esp_exchange                    4(range):4(range)     3.74E+007
17   14   18   hybrid_hash_join                                      3.74E+007
16   .    17   split_top                       4(range):72(range)    8.00E+007
15   .    16   partition_access                                      8.00E+007
.    .    15   index_scan            fr        PSX2                  8.00E+007
10   13   14   nested_join                                           3.74E+007
12   .    13   split_top                       4(rep-n):72(range)    2.99E+001
11   .    12   partition_access                                      2.99E+001
.    .    11   index_scan            fr        LX2                   2.99E+001
9    .    10   split_top                       4(range):72(range)    1.25E+006
8    .    9    partition_access                                      1.25E+006
.    .    8    file_scan             fr        PART                  1.25E+006
6    .    7    esp_exchange                    4(rep-b):4(range)     1.00E+006
5    .    6    split_top                       4(range):12(range)    1.00E+006
4    .    5    partition_access                                      1.00E+006
.    .    4    index_scan            fr        SX1                   1.00E+006
2    .    3    esp_exchange                    4(rep-b):1            2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml02(q10);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.743E+000   8.612E+002 
21  FIRSTN            20  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   8.595E+002 
20  ESP_EXCHANGE      19  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.744E-001   8.595E+002 
19  SORT              18  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.345E+002   8.593E+002 
18  HASH_GROUPBY      17  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.535E+002   7.247E+002 
17  HYBRID_HASH_JOIN  16  3               5.723E+006   FUNKY_OPT_UNIQUE  4.310E+000   5.712E+002 
16  HYBRID_HASH_JOIN  15  12              5.723E+006   FUNKY_OPT_UNIQUE  1.557E+001   5.669E+002 
15  SPLIT_TOP         14  ?               1.500E+007   FUNKY_OPT_UNIQUE  7.011E+001   8.088E+001 
14  PARTITION_ACCESS  13  ?               1.500E+007   FUNKY_OPT_UNIQUE  7.011E+001   8.088E+001 
13  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.077E+001   1.077E+001 
12  ESP_EXCHANGE      11  ?               5.723E+006   FUNKY_OPT_UNIQUE  7.947E+000   4.707E+002 
11  HYBRID_HASH_JOIN  10  7               5.723E+006   FUNKY_OPT_UNIQUE  1.041E+002   4.628E+002 
10  SPLIT_TOP         9   ?               1.479E+008   FUNKY_OPT_UNIQUE  9.908E+001   3.224E+002 
09  PARTITION_ACCESS  8   ?               1.479E+008   FUNKY_OPT_UNIQUE  9.908E+001   3.224E+002 
08  FILE_SCAN         ?   ?   LINEITEM    1.479E+008   FUNKY_OPT_UNIQUE  2.233E+002   2.233E+002 
07  ESP_EXCHANGE      6   ?               5.798E+006   FUNKY_OPT_UNIQUE  7.059E+000   3.668E+001 
06  SPLIT_TOP         5   ?               5.798E+006   FUNKY_OPT_UNIQUE  2.987E+000   2.962E+001 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  2.987E+000   2.962E+001 
04  INDEX_SCAN        ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  2.664E+001   2.664E+001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   1.078E-002 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   8.340E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  7.903E-003   7.903E-003 

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

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

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

--- SQL operation complete.
>>obey optdml02(q11);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

33  ROOT              32  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.571E-002   2.577E+001 
32  ESP_EXCHANGE      31  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.744E-001   2.575E+001 
31  SORT              30  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.987E+000   2.558E+001 
30  HYBRID_HASH_JOIN  29  15              1.074E+006   FUNKY_OPT_UNIQUE  1.418E+000   2.359E+001 
29  HASH_PARTIAL_GRO  28  ?               3.223E+006   FUNKY_OPT_UNIQUE  2.998E+000   1.404E+001 
28  ESP_EXCHANGE      27  ?               3.223E+006   FUNKY_OPT_UNIQUE  1.394E+000   1.105E+001 
27  HASH_PARTIAL_GRO  26  ?               3.223E+006   FUNKY_OPT_UNIQUE  7.732E-001   9.655E+000 
26  NESTED_JOIN       22  25              3.223E+006   FUNKY_OPT_UNIQUE  2.841E-002   8.882E+000 
25  SPLIT_TOP         24  ?               8.000E+001   FUNKY_OPT_UNIQUE  5.352E+000   8.779E+000 
24  PARTITION_ACCESS  23  ?               8.000E+001   FUNKY_OPT_UNIQUE  5.352E+000   8.779E+000 
23  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+001   FUNKY_OPT_UNIQUE  3.427E+000   3.427E+000 
22  NESTED_JOIN       18  21              4.028E+004   FUNKY_OPT_UNIQUE  3.508E-004   7.415E-002 
21  SPLIT_TOP         20  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   6.345E-002 
20  PARTITION_ACCESS  19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   6.345E-002 
19  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  3.805E-002   3.805E-002 
18  ESP_EXCHANGE      17  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.000E-003   1.034E-002 
17  PARTITION_ACCESS  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   8.341E-003 
16  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  7.904E-003   7.904E-003 
15  ESP_EXCHANGE      14  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   8.128E+000 
14  SORT_PARTIAL_AGG  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.080E-006   8.126E+000 
13  ESP_EXCHANGE      12  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   8.126E+000 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.069E-002   8.125E+000 
11  NESTED_JOIN       7   10              3.223E+006   FUNKY_OPT_UNIQUE  2.841E-002   8.114E+000 
10  SPLIT_TOP         9   ?               8.000E+001   FUNKY_OPT_UNIQUE  4.617E+000   8.012E+000 
09  PARTITION_ACCESS  8   ?               8.000E+001   FUNKY_OPT_UNIQUE  4.617E+000   8.012E+000 
08  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+001   FUNKY_OPT_UNIQUE  3.394E+000   3.394E+000 
07  NESTED_JOIN       3   6               4.028E+004   FUNKY_OPT_UNIQUE  3.508E-004   7.414E-002 
06  SPLIT_TOP         5   ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   6.345E-002 
05  PARTITION_ACCESS  4   ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   6.345E-002 
04  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  3.805E-002   3.805E-002 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.997E-003   1.033E-002 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   8.341E-003 
01  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  7.904E-003   7.904E-003 

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

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

32   .    33   root                                                  1.07E+006
31   .    32   esp_exchange                    1:4(hash2)            1.07E+006
30   .    31   sort                                                  1.07E+006
29   15   30   hybrid_hash_join                                      1.07E+006
28   .    29   hash_partial_groupby                                  3.22E+006
27   .    28   esp_exchange                    4(hash2):4(hash2)     3.22E+006
26   .    27   hash_partial_groupby                                  3.22E+006
22   25   26   nested_join                                           3.22E+006
24   .    25   split_top                       4(rep-n):72(range)    8.00E+001
23   .    24   partition_access                                      8.00E+001
.    .    23   index_scan            fr        PSX1                  8.00E+001
18   21   22   nested_join                                           4.02E+004
20   .    21   split_top                       4(rep-n):12(range)    4.02E+004
19   .    20   partition_access                                      4.02E+004
.    .    19   index_scan            fr        SX1                   4.02E+004
17   .    18   esp_exchange                    4(hash2):1            1.00E+000
16   .    17   partition_access                                      1.00E+000
.    .    16   file_scan             fr        NATION                1.00E+000
14   .    15   esp_exchange                    4(rep-b):1            1.00E+000
13   .    14   sort_partial_aggr_ro                                  1.00E+000
12   .    13   esp_exchange                    1:4(hash2)            1.00E+000
11   .    12   sort_partial_aggr_le                                  1.00E+000
7    10   11   nested_join                                           3.22E+006
9    .    10   split_top                       4(rep-n):72(range)    8.00E+001
8    .    9    partition_access                                      8.00E+001
.    .    8    index_scan            fr        PSX1                  8.00E+001
3    6    7    nested_join                                           4.02E+004
5    .    6    split_top                       4(rep-n):12(range)    4.02E+004
4    .    5    partition_access                                      4.02E+004
.    .    4    index_scan            fr        SX1                   4.02E+004
2    .    3    esp_exchange                    4(hash2):1            1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        NATION                1.00E+000

--- SQL operation complete.
>>obey optdml02(q12);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

14  ROOT              13  ?               2.000E+000   FUNKY_OPT_UNIQUE  6.338E-008   1.901E+002 
13  ESP_EXCHANGE      12  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   1.901E+002 
12  SORT_PARTIAL_GRO  11  ?               2.000E+000   FUNKY_OPT_UNIQUE  4.874E-006   1.901E+002 
11  SORT              10  ?               2.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   1.901E+002 
10  ESP_EXCHANGE      9   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.589E-004   1.901E+002 
09  HASH_PARTIAL_GRO  8   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.092E-001   1.901E+002 
08  HYBRID_HASH_JOIN  7   4               2.892E+006   FUNKY_OPT_UNIQUE  1.027E+002   1.895E+002 
07  SPLIT_TOP         6   ?               1.500E+008   FUNKY_OPT_UNIQUE  5.752E+001   7.518E+001 
06  PARTITION_ACCESS  5   ?               1.500E+008   FUNKY_OPT_UNIQUE  5.752E+001   7.518E+001 
05  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  1.766E+001   1.766E+001 
04  ESP_EXCHANGE      3   ?               2.892E+006   FUNKY_OPT_UNIQUE  3.895E+000   1.325E+001 
03  SPLIT_TOP         2   ?               2.892E+006   FUNKY_OPT_UNIQUE  1.885E+000   9.358E+000 
02  PARTITION_ACCESS  1   ?               2.892E+006   FUNKY_OPT_UNIQUE  1.885E+000   9.358E+000 
01  INDEX_SCAN        ?   ?   LINEITEM    2.892E+006   FUNKY_OPT_UNIQUE  7.472E+000   7.472E+000 

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

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

13   .    14   root                                                  2.00E+000
12   .    13   esp_exchange                    1:4(hash2)            2.00E+000
11   .    12   sort_partial_groupby                                  2.00E+000
10   .    11   sort                                                  2.00E+000
9    .    10   esp_exchange                    4(hash2):4(range)     2.00E+000
8    .    9    hash_partial_groupby                                  2.00E+000
7    4    8    hybrid_hash_join                                      2.89E+006
6    .    7    split_top                       4(range):12(range)    1.50E+008
5    .    6    partition_access                                      1.50E+008
.    .    5    index_scan            fr        OX4                   1.50E+008
3    .    4    esp_exchange                    4(range):4(range)     2.89E+006
2    .    3    split_top                       4(range):98(range)    2.89E+006
1    .    2    partition_access                                      2.89E+006
.    .    1    index_scan            fr        LX5 (m)               2.89E+006

--- SQL operation complete.
>>obey optdml02(q13);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               1.481E+003   FUNKY_OPT_UNIQUE  2.527E-005   1.235E+000 
12  ESP_EXCHANGE      11  ?               1.481E+003   FUNKY_OPT_UNIQUE  5.830E-003   1.235E+000 
11  SORT_PARTIAL_GRO  10  ?               1.481E+003   FUNKY_OPT_UNIQUE  9.014E-004   1.229E+000 
10  SORT              9   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.879E-002   1.228E+000 
09  ESP_EXCHANGE      8   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.210E-003   1.210E+000 
08  HASH_PARTIAL_GRO  7   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.361E-003   1.208E+000 
07  NESTED_JOIN       3   6               1.481E+003   FUNKY_OPT_UNIQUE  2.599E-005   1.207E+000 
06  SPLIT_TOP         5   ?               9.873E-001   FUNKY_OPT_UNIQUE  1.543E-001   1.184E+000 
05  PARTITION_ACCESS  4   ?               9.873E-001   FUNKY_OPT_UNIQUE  1.543E-001   1.184E+000 
04  FILE_SCAN         ?   ?   LINEITEM    9.873E-001   FUNKY_OPT_UNIQUE  1.029E+000   1.029E+000 
03  SPLIT_TOP         2   ?               1.500E+003   FUNKY_OPT_UNIQUE  7.101E-003   2.327E-002 
02  PARTITION_ACCESS  1   ?               1.500E+003   FUNKY_OPT_UNIQUE  7.101E-003   2.327E-002 
01  INDEX_SCAN        ?   ?   ORDERS      1.500E+003   FUNKY_OPT_UNIQUE  1.617E-002   1.617E-002 

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

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

12   .    13   root                                                  1.48E+003
11   .    12   esp_exchange                    1:4(hash2)            1.48E+003
10   .    11   sort_partial_groupby                                  1.48E+003
9    .    10   sort                                                  1.48E+003
8    .    9    esp_exchange                    4(hash2):4(range)     1.48E+003
7    .    8    hash_partial_groupby                                  1.48E+003
3    6    7    nested_join                                           1.48E+003
5    .    6    split_top                       4(rep-n):64(range)    9.87E-001
4    .    5    partition_access                                      9.87E-001
.    .    4    file_scan             fr        LINEITEM              9.87E-001
2    .    3    split_top                       4(range):16(range)    1.50E+003
1    .    2    partition_access                                      1.50E+003
.    .    1    index_scan            fr        OX1                   1.50E+003

--- SQL operation complete.
>>obey optdml02(q14);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

12  ROOT              11  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   7.645E+001 
11  SORT_PARTIAL_AGG  10  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.104E-006   7.645E+001 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.123E-004   7.645E+001 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  5.125E-002   7.645E+001 
08  HYBRID_HASH_JOIN  7   4               7.720E+006   FUNKY_OPT_UNIQUE  2.086E+001   7.640E+001 
07  SPLIT_TOP         6   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.945E+001   2.806E+001 
06  PARTITION_ACCESS  5   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.945E+001   2.806E+001 
05  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  8.615E+000   8.615E+000 
04  ESP_EXCHANGE      3   ?               7.720E+006   FUNKY_OPT_UNIQUE  1.137E+001   2.756E+001 
03  SPLIT_TOP         2   ?               7.720E+006   FUNKY_OPT_UNIQUE  5.838E+000   1.618E+001 
02  PARTITION_ACCESS  1   ?               7.720E+006   FUNKY_OPT_UNIQUE  5.838E+000   1.618E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    7.720E+006   FUNKY_OPT_UNIQUE  1.034E+001   1.034E+001 

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

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

11   .    12   root                                                  1.00E+000
10   .    11   sort_partial_aggr_ro                                  1.00E+000
9    .    10   esp_exchange                    1:4(range)            1.00E+000
8    .    9    sort_partial_aggr_le                                  1.00E+000
7    4    8    hybrid_hash_join                                      7.72E+006
6    .    7    split_top                       4(range):72(range)    2.00E+007
5    .    6    partition_access                                      2.00E+007
.    .    5    file_scan             fr        PART                  2.00E+007
3    .    4    esp_exchange                    4(range):4(range)     7.72E+006
2    .    3    split_top                       4(range):72(range)    7.72E+006
1    .    2    partition_access                                      7.72E+006
.    .    1    index_scan            fr        LX3 (m)               7.72E+006

--- SQL operation complete.
>>obey optdml02(q15);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

24  ROOT              23  ?               3.333E+005   FUNKY_OPT_UNIQUE  3.818E-002   1.077E+002 
23  ESP_EXCHANGE      22  ?               3.333E+005   FUNKY_OPT_UNIQUE  1.744E-001   1.076E+002 
22  SORT              21  ?               3.333E+005   FUNKY_OPT_UNIQUE  7.069E-001   1.075E+002 
21  HYBRID_HASH_JOIN  20  17              3.333E+005   FUNKY_OPT_UNIQUE  9.916E-001   1.067E+002 
20  SPLIT_TOP         19  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.086E+000   2.635E+000 
19  PARTITION_ACCESS  18  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.086E+000   2.635E+000 
18  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  5.491E-001   5.491E-001 
17  HYBRID_HASH_JOIN  16  10              3.333E+005   FUNKY_OPT_UNIQUE  1.048E+000   1.031E+002 
16  HASH_PARTIAL_GRO  15  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.036E+001   5.146E+001 
15  ESP_EXCHANGE      14  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.488E+000   4.110E+001 
14  HASH_PARTIAL_GRO  13  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.343E+000   3.561E+001 
13  SPLIT_TOP         12  ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   3.027E+001 
12  PARTITION_ACCESS  11  ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   3.027E+001 
11  INDEX_SCAN        ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.481E+001   1.481E+001 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   5.067E+001 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   5.067E+001 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   5.067E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.319E-003   5.067E+001 
06  HASH_PARTIAL_GRO  5   ?               1.000E+006   FUNKY_OPT_UNIQUE  1.036E+001   5.067E+001 
05  ESP_EXCHANGE      4   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.488E+000   4.030E+001 
04  HASH_PARTIAL_GRO  3   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.343E+000   3.481E+001 
03  SPLIT_TOP         2   ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   2.947E+001 
02  PARTITION_ACCESS  1   ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   2.947E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.401E+001   1.401E+001 

--- 24 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
---- ---- ---- --------------------  --------  --------------------  ---------

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

--- SQL operation complete.
>>obey optdml02(q16);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

20  ROOT              19  ?               2.707E+004   FUNKY_OPT_UNIQUE  1.616E-003   1.993E+002 
19  ESP_EXCHANGE      18  ?               2.707E+004   FUNKY_OPT_UNIQUE  9.197E-002   1.993E+002 
18  SORT              17  ?               2.707E+004   FUNKY_OPT_UNIQUE  1.573E-001   1.992E+002 
17  HASH_PARTIAL_GRO  16  ?               2.707E+004   FUNKY_OPT_UNIQUE  8.069E-002   1.990E+002 
16  ESP_EXCHANGE      15  ?               2.707E+004   FUNKY_OPT_UNIQUE  3.292E-002   1.989E+002 
15  HASH_PARTIAL_GRO  14  ?               2.707E+004   FUNKY_OPT_UNIQUE  7.371E+000   1.989E+002 
14  HASH_GROUPBY      13  ?               1.080E+007   FUNKY_OPT_UNIQUE  3.933E+001   1.915E+002 
13  HYBRID_HASH_ANTI  12  4               1.080E+007   FUNKY_OPT_UNIQUE  7.734E+000   1.522E+002 
12  ESP_EXCHANGE      11  ?               1.152E+007   FUNKY_OPT_UNIQUE  2.314E+001   1.440E+002 
11  HYBRID_HASH_JOIN  10  7               1.152E+007   FUNKY_OPT_UNIQUE  5.688E+001   1.209E+002 
10  SPLIT_TOP         9   ?               8.000E+007   FUNKY_OPT_UNIQUE  4.126E+001   5.008E+001 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  4.126E+001   5.008E+001 
08  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  8.819E+000   8.819E+000 
07  SPLIT_TOP         6   ?               2.881E+006   FUNKY_OPT_UNIQUE  3.623E+000   1.413E+001 
06  PARTITION_ACCESS  5   ?               2.881E+006   FUNKY_OPT_UNIQUE  3.623E+000   1.413E+001 
05  FILE_SCAN         ?   ?   PART        2.881E+006   FUNKY_OPT_UNIQUE  1.050E+001   1.050E+001 
04  ESP_EXCHANGE      3   ?               6.250E+004   FUNKY_OPT_UNIQUE  8.624E-002   6.538E-001 
03  SPLIT_TOP         2   ?               6.250E+004   FUNKY_OPT_UNIQUE  4.569E-002   5.676E-001 
02  PARTITION_ACCESS  1   ?               6.250E+004   FUNKY_OPT_UNIQUE  4.569E-002   5.676E-001 
01  FILE_SCAN         ?   ?   SUPPLIER    6.250E+004   FUNKY_OPT_UNIQUE  5.219E-001   5.219E-001 

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

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

19   .    20   root                                                  2.70E+004
18   .    19   esp_exchange                    1:4(hash2)            2.70E+004
17   .    18   sort                                                  2.70E+004
16   .    17   hash_partial_groupby                                  2.70E+004
15   .    16   esp_exchange                    4(hash2):4(hash2)     2.70E+004
14   .    15   hash_partial_groupby                                  2.70E+004
13   .    14   hash_groupby                                          1.08E+007
12   4    13   hybrid_hash_anti_sem                                  1.08E+007
11   .    12   esp_exchange                    4(hash2):4(range)     1.15E+007
10   7    11   hybrid_hash_join                                      1.15E+007
9    .    10   split_top                       4(range):72(range)    8.00E+007
8    .    9    partition_access                                      8.00E+007
.    .    8    index_scan            fr        PSX2                  8.00E+007
6    .    7    split_top                       4(range):72(range)    2.88E+006
5    .    6    partition_access                                      2.88E+006
.    .    5    file_scan             fr        PART                  2.88E+006
3    .    4    esp_exchange                    4(hash2):4(range)     6.25E+004
2    .    3    split_top                       4(range):72(range)    6.25E+004
1    .    2    partition_access                                      6.25E+004
.    .    1    file_scan             fr        SUPPLIER              6.25E+004

--- SQL operation complete.
>>obey optdml02(q17);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   1.170E+002 
17  SORT_PARTIAL_AGG  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.072E-006   1.170E+002 
16  ESP_EXCHANGE      15  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.132E-004   1.170E+002 
15  SORT_PARTIAL_AGG  14  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.991E-002   1.170E+002 
14  SORT_GROUPBY      13  ?               6.000E+006   FUNKY_OPT_UNIQUE  1.771E+001   1.169E+002 
13  NESTED_JOIN       9   12              1.800E+007   FUNKY_OPT_UNIQUE  1.619E-001   9.927E+001 
12  SPLIT_TOP         11  ?               2.997E+001   FUNKY_OPT_UNIQUE  5.385E+001   8.060E+001 
11  PARTITION_ACCESS  10  ?               2.997E+001   FUNKY_OPT_UNIQUE  5.385E+001   8.060E+001 
10  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  2.674E+001   2.674E+001 
09  SORT              8   ?               6.007E+005   FUNKY_OPT_UNIQUE  3.957E+000   1.851E+001 
08  ESP_EXCHANGE      7   ?               6.007E+005   FUNKY_OPT_UNIQUE  2.302E+000   1.455E+001 
07  NESTED_JOIN       3   6               6.007E+005   FUNKY_OPT_UNIQUE  5.404E-003   1.225E+001 
06  SPLIT_TOP         5   ?               2.997E+001   FUNKY_OPT_UNIQUE  2.001E+000   6.625E+000 
05  PARTITION_ACCESS  4   ?               2.997E+001   FUNKY_OPT_UNIQUE  2.001E+000   6.625E+000 
04  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  4.623E+000   4.623E+000 
03  SPLIT_TOP         2   ?               2.004E+004   FUNKY_OPT_UNIQUE  2.754E-002   5.623E+000 
02  PARTITION_ACCESS  1   ?               2.004E+004   FUNKY_OPT_UNIQUE  2.754E-002   5.623E+000 
01  INDEX_SCAN        ?   ?   PART        2.004E+004   FUNKY_OPT_UNIQUE  5.596E+000   5.596E+000 

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

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

17   .    18   root                                                  1.00E+000
16   .    17   sort_partial_aggr_ro                                  1.00E+000
15   .    16   esp_exchange                    1:4(hash2)            1.00E+000
14   .    15   sort_partial_aggr_le                                  1.00E+000
13   .    14   sort_groupby                                          6.00E+006
9    12   13   nested_join                                           1.80E+007
11   .    12   split_top                       4(rep-n):72(range)    2.99E+001
10   .    11   partition_access                                      2.99E+001
.    .    10   index_scan            fr        LX2                   2.99E+001
8    .    9    sort                                                  6.00E+005
7    .    8    esp_exchange                    4(hash2):4(range)     6.00E+005
3    6    7    nested_join                                           6.00E+005
5    .    6    split_top                       4(rep-n):72(range)    2.99E+001
4    .    5    partition_access                                      2.99E+001
.    .    4    index_scan            fr        LX2                   2.99E+001
2    .    3    split_top                       4(range):72(range)    2.00E+004
1    .    2    partition_access                                      2.00E+004
.    .    1    index_scan            fr        PX1                   2.00E+004

--- SQL operation complete.
>>obey optdml02(q18);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               4.994E+007   FUNKY_OPT_UNIQUE  3.348E+000   2.568E+003 
21  FIRSTN            20  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.564E+003 
20  ESP_EXCHANGE      19  ?               4.994E+007   FUNKY_OPT_UNIQUE  1.744E-001   2.564E+003 
19  SORT              18  ?               4.994E+007   FUNKY_OPT_UNIQUE  9.458E+002   2.564E+003 
18  HYBRID_HASH_JOIN  17  3               4.994E+007   FUNKY_OPT_UNIQUE  5.101E+001   1.618E+003 
17  ESP_EXCHANGE      16  ?               4.994E+007   FUNKY_OPT_UNIQUE  8.212E+001   1.546E+003 
16  HYBRID_HASH_JOIN  15  11              4.994E+007   FUNKY_OPT_UNIQUE  2.740E+002   1.463E+003 
15  SPLIT_TOP         14  ?               1.498E+008   FUNKY_OPT_UNIQUE  8.860E+001   4.958E+002 
14  PARTITION_ACCESS  13  ?               1.498E+008   FUNKY_OPT_UNIQUE  8.860E+001   4.958E+002 
13  SORT_GROUPBY      12  ?               1.498E+008   FUNKY_OPT_UNIQUE  2.012E+002   4.072E+002 
12  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  2.059E+002   2.059E+002 
11  HYBRID_HASH_JOIN  10  7               4.994E+007   FUNKY_OPT_UNIQUE  1.484E+002   6.943E+002 
10  SPLIT_TOP         9   ?               1.500E+008   FUNKY_OPT_UNIQUE  1.123E+002   1.668E+002 
09  PARTITION_ACCESS  8   ?               1.500E+008   FUNKY_OPT_UNIQUE  1.123E+002   1.668E+002 
08  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  5.443E+001   5.443E+001 
07  SPLIT_TOP         6   ?               4.994E+007   FUNKY_OPT_UNIQUE  2.166E+001   3.795E+002 
06  PARTITION_ACCESS  5   ?               4.994E+007   FUNKY_OPT_UNIQUE  2.166E+001   3.795E+002 
04  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  2.059E+002   2.059E+002 
03  SPLIT_TOP         2   ?               1.500E+007   FUNKY_OPT_UNIQUE  1.449E+001   2.270E+001 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  1.449E+001   2.270E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  8.208E+000   8.208E+000 

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

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

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

--- SQL operation complete.
>>obey optdml02(q19);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   4.492E+002 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   4.492E+002 
11  ESP_EXCHANGE      10  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.123E-004   4.492E+002 
10  SORT_PARTIAL_AGG  9   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.954E-005   4.492E+002 
09  HYBRID_HASH_JOIN  8   4               2.380E+004   FUNKY_OPT_UNIQUE  6.872E+001   4.492E+002 
08  ESP_EXCHANGE      7   ?               4.281E+007   FUNKY_OPT_UNIQUE  6.673E+001   3.245E+002 
07  SPLIT_TOP         6   ?               4.281E+007   FUNKY_OPT_UNIQUE  3.546E+001   2.578E+002 
06  PARTITION_ACCESS  5   ?               4.281E+007   FUNKY_OPT_UNIQUE  3.546E+001   2.578E+002 
05  FILE_SCAN         ?   ?   LINEITEM    4.281E+007   FUNKY_OPT_UNIQUE  2.223E+002   2.223E+002 
04  ESP_EXCHANGE      3   ?               2.000E+007   FUNKY_OPT_UNIQUE  3.288E+001   5.669E+001 
03  SPLIT_TOP         2   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.827E+001   2.381E+001 
02  PARTITION_ACCESS  1   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.827E+001   2.381E+001 
01  INDEX_SCAN        ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  5.532E+000   5.532E+000 

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

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

12   .    13   root                                                  1.00E+000
11   .    12   sort_partial_aggr_ro                                  1.00E+000
10   .    11   esp_exchange                    1:4(hash2)            1.00E+000
9    .    10   sort_partial_aggr_le                                  1.00E+000
8    4    9    hybrid_hash_join                                      2.38E+004
7    .    8    esp_exchange                    4(hash2):4(range)     4.28E+007
6    .    7    split_top                       4(range):64(range)    4.28E+007
5    .    6    partition_access                                      4.28E+007
.    .    5    file_scan             fr        LINEITEM              4.28E+007
3    .    4    esp_exchange                    4(hash2):4(range)     2.00E+007
2    .    3    split_top                       4(range):72(range)    2.00E+007
1    .    2    partition_access                                      2.00E+007
.    .    1    index_scan            fr        PX1                   2.00E+007

--- SQL operation complete.
>>obey optdml02(q20);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

26  ROOT              25  ?               7.649E+004   FUNKY_OPT_UNIQUE  6.246E-003   3.714E+002 
25  ESP_EXCHANGE      24  ?               7.649E+004   FUNKY_OPT_UNIQUE  1.744E-001   3.714E+002 
24  SORT              23  ?               7.649E+004   FUNKY_OPT_UNIQUE  2.115E-001   3.712E+002 
23  HYBRID_HASH_SEMI  22  15              7.649E+004   FUNKY_OPT_UNIQUE  1.706E+000   3.710E+002 
22  ESP_EXCHANGE      21  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.543E-001   1.010E+000 
21  NESTED_JOIN       17  20              4.028E+004   FUNKY_OPT_UNIQUE  1.403E-003   7.557E-001 
20  SPLIT_TOP         19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.108E-001   7.460E-001 
19  PARTITION_ACCESS  18  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.108E-001   7.460E-001 
18  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  5.352E-001   5.352E-001 
17  PARTITION_ACCESS  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   8.341E-003 
16  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  7.904E-003   7.904E-003 
15  HYBRID_HASH_JOIN  14  11              1.898E+006   FUNKY_OPT_UNIQUE  1.185E+002   3.683E+002 
14  SPLIT_TOP         13  ?               8.000E+007   FUNKY_OPT_UNIQUE  4.761E+001   5.880E+001 
13  PARTITION_ACCESS  12  ?               8.000E+007   FUNKY_OPT_UNIQUE  4.761E+001   5.880E+001 
12  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  1.118E+001   1.118E+001 
11  HASH_PARTIAL_GRO  10  ?               5.696E+006   FUNKY_OPT_UNIQUE  9.024E+000   1.912E+002 
10  ESP_EXCHANGE      9   ?               5.696E+006   FUNKY_OPT_UNIQUE  2.038E+000   1.822E+002 
09  HASH_PARTIAL_GRO  8   ?               5.696E+006   FUNKY_OPT_UNIQUE  3.519E+000   1.801E+002 
08  HYBRID_HASH_JOIN  7   4               5.696E+006   FUNKY_OPT_UNIQUE  6.563E+001   1.766E+002 
07  SPLIT_TOP         6   ?               9.114E+007   FUNKY_OPT_UNIQUE  6.145E+001   9.948E+001 
06  PARTITION_ACCESS  5   ?               9.114E+007   FUNKY_OPT_UNIQUE  6.145E+001   9.948E+001 
05  INDEX_SCAN        ?   ?   LINEITEM    9.114E+007   FUNKY_OPT_UNIQUE  3.803E+001   3.803E+001 
04  ESP_EXCHANGE      3   ?               1.250E+006   FUNKY_OPT_UNIQUE  2.353E+000   1.178E+001 
03  SPLIT_TOP         2   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.432E+000 
02  PARTITION_ACCESS  1   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.432E+000 
01  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  8.867E+000   8.867E+000 

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

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

25   .    26   root                                                  7.64E+004
24   .    25   esp_exchange                    1:4(range)            7.64E+004
23   .    24   sort                                                  7.64E+004
22   15   23   hybrid_hash_semi_joi                                  7.64E+004
21   .    22   esp_exchange                    4(range):1            4.02E+004
17   20   21   nested_join                                           4.02E+004
19   .    20   split_top                       1:72(range)           4.02E+004
18   .    19   partition_access                                      4.02E+004
.    .    18   file_scan             fr        SUPPLIER              4.02E+004
16   .    17   partition_access                                      1.00E+000
.    .    16   file_scan             fr        NATION                1.00E+000
14   11   15   hybrid_hash_join                                      1.89E+006
13   .    14   split_top                       4(range):72(range)    8.00E+007
12   .    13   partition_access                                      8.00E+007
.    .    12   index_scan            fr        PSX1                  8.00E+007
10   .    11   hash_partial_groupby                                  5.69E+006
9    .    10   esp_exchange                    4(range):4(range)     5.69E+006
8    .    9    hash_partial_groupby                                  5.69E+006
7    4    8    hybrid_hash_join                                      5.69E+006
6    .    7    split_top                       4(range):72(range)    9.11E+007
5    .    6    partition_access                                      9.11E+007
.    .    5    index_scan            fr        LX3 (m)               9.11E+007
3    .    4    esp_exchange                    4(rep-b):4(range)     1.25E+006
2    .    3    split_top                       4(range):72(range)    1.25E+006
1    .    2    partition_access                                      1.25E+006
.    .    1    file_scan             fr        PART                  1.25E+006

--- SQL operation complete.
>>obey optdml02(q21);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

35  ROOT              34  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.022E-008   2.436E+003 
34  FIRSTN            33  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.436E+003 
33  ESP_EXCHANGE      32  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.654E-003   2.436E+003 
32  SORT              31  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   2.436E+003 
31  HASH_PARTIAL_GRO  30  ?               1.000E+000   FUNKY_OPT_UNIQUE  5.794E-006   2.436E+003 
30  ESP_EXCHANGE      29  ?               1.000E+000   FUNKY_OPT_UNIQUE  6.600E-004   2.436E+003 
29  HASH_PARTIAL_GRO  28  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.448E-006   2.436E+003 
28  HYBRID_HASH_JOIN  27  24              1.000E+000   FUNKY_OPT_UNIQUE  1.332E+001   2.436E+003 
27  SPLIT_TOP         26  ?               2.000E+007   FUNKY_OPT_UNIQUE  8.743E+000   1.696E+001 
26  PARTITION_ACCESS  25  ?               2.000E+007   FUNKY_OPT_UNIQUE  8.743E+000   1.696E+001 
25  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  8.225E+000   8.225E+000 
24  ESP_EXCHANGE      23  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.637E-003   2.405E+003 
23  HYBRID_HASH_ANTI  22  3               1.000E+000   FUNKY_OPT_UNIQUE  2.725E+002   2.405E+003 
22  HYBRID_HASH_JOIN  21  18              3.925E+006   FUNKY_OPT_UNIQUE  5.622E+001   1.808E+003 
21  SPLIT_TOP         20  ?               7.307E+007   FUNKY_OPT_UNIQUE  3.168E+001   8.416E+001 
20  PARTITION_ACCESS  19  ?               7.307E+007   FUNKY_OPT_UNIQUE  3.168E+001   8.416E+001 
19  FILE_SCAN         ?   ?   ORDERS      7.307E+007   FUNKY_OPT_UNIQUE  5.247E+001   5.247E+001 
18  MERGE_SEMI_JOIN   15  17              8.049E+006   FUNKY_OPT_UNIQUE  1.640E+002   1.667E+003 
17  PARTITION_ACCESS  16  ?               5.994E+008   FUNKY_OPT_UNIQUE  3.070E+002   9.087E+002 
16  FILE_SCAN         ?   ?   LINEITEM)   5.994E+008   FUNKY_OPT_UNIQUE  6.016E+002   6.016E+002 
15  SORT              14  ?               8.049E+006   FUNKY_OPT_UNIQUE  7.707E+001   5.950E+002 
14  HYBRID_HASH_JOIN  13  10              8.049E+006   FUNKY_OPT_UNIQUE  1.339E+002   5.179E+002 
13  SPLIT_TOP         12  ?               1.997E+008   FUNKY_OPT_UNIQUE  1.181E+002   3.832E+002 
12  PARTITION_ACCESS  11  ?               1.997E+008   FUNKY_OPT_UNIQUE  1.181E+002   3.832E+002 
11  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  2.650E+002   2.650E+002 
10  ESP_EXCHANGE      9   ?               4.028E+004   FUNKY_OPT_UNIQUE  5.770E-001   1.283E+000 
09  NESTED_JOIN       5   8               4.028E+004   FUNKY_OPT_UNIQUE  1.403E-003   7.068E-001 
08  SPLIT_TOP         7   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.630E-001   6.970E-001 
07  PARTITION_ACCESS  6   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.630E-001   6.970E-001 
06  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  5.340E-001   5.340E-001 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.370E-004   8.341E-003 
04  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  7.904E-003   7.904E-003 
03  SPLIT_TOP         2   ?               1.997E+008   FUNKY_OPT_UNIQUE  1.023E+002   3.251E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  1.023E+002   3.251E+002 
01  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  2.227E+002   2.227E+002 

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

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

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

--- SQL operation complete.
>>obey optdml02(q22);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

19  ROOT              18  ?               4.960E+006   FUNKY_OPT_UNIQUE  1.209E-001   4.318E+002 
18  ESP_EXCHANGE      17  ?               4.960E+006   FUNKY_OPT_UNIQUE  1.744E-001   4.317E+002 
17  SORT              16  ?               4.960E+006   FUNKY_OPT_UNIQUE  4.216E+001   4.315E+002 
16  HASH_GROUPBY      15  ?               4.960E+006   FUNKY_OPT_UNIQUE  5.268E+001   3.894E+002 
15  ESP_EXCHANGE      14  ?               7.440E+007   FUNKY_OPT_UNIQUE  9.691E+001   3.367E+002 
14  MERGE_ANTI_SEMI_  11  13              7.440E+007   FUNKY_OPT_UNIQUE  7.591E+001   2.398E+002 
13  PARTITION_ACCESS  12  ?               1.500E+008   FUNKY_OPT_UNIQUE  6.503E+001   8.933E+001 
12  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  2.429E+001   2.429E+001 
11  SORT              10  ?               4.960E+006   FUNKY_OPT_UNIQUE  4.117E+001   7.457E+001 
10  HYBRID_HASH_JOIN  9   6               4.960E+006   FUNKY_OPT_UNIQUE  6.549E+000   3.339E+001 
09  SPLIT_TOP         8   ?               1.488E+007   FUNKY_OPT_UNIQUE  9.983E+000   1.802E+001 
08  PARTITION_ACCESS  7   ?               1.488E+007   FUNKY_OPT_UNIQUE  9.983E+000   1.802E+001 
07  FILE_SCAN         ?   ?   CUSTOMER    1.488E+007   FUNKY_OPT_UNIQUE  8.044E+000   8.044E+000 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   8.879E+000 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   8.877E+000 
04  SPLIT_TOP         3   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.473E-002   8.877E+000 
03  PARTITION_ACCESS  2   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.473E-002   8.877E+000 
02  SORT_PARTIAL_AGG  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.593E-001   8.832E+000 
01  FILE_SCAN         ?   ?   CUSTOMER    1.352E+007   FUNKY_OPT_UNIQUE  8.472E+000   8.472E+000 

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

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

18   .    19   root                                                  4.96E+006
17   .    18   esp_exchange                    1:4(hash2)            4.96E+006
16   .    17   sort                                                  4.96E+006
15   .    16   hash_groupby                                          4.96E+006
14   .    15   esp_exchange                    4(hash2):4(range)     7.44E+007
11   13   14   merge_anti_semi_join                                  7.44E+007
12   .    13   partition_access                                      1.50E+008
.    .    12   index_scan            fr        OX2                   1.50E+008
10   .    11   sort                                                  4.96E+006
9    6    10   hybrid_hash_join                                      4.96E+006
8    .    9    split_top                       4(range):64(range)    1.48E+007
7    .    8    partition_access                                      1.48E+007
.    .    7    file_scan             fr        CUSTOMER              1.48E+007
5    .    6    esp_exchange                    4(rep-b):1            1.00E+000
4    .    5    sort_partial_aggr_ro                                  1.00E+000
3    .    4    split_top                       1:64(range)           1.00E+000
2    .    3    partition_access                                      1.00E+000
1    .    2    sort_partial_aggr_le                                  1.00E+000
.    .    1    file_scan                       CUSTOMER              1.35E+007

--- 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 optdml02(q01);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

09  ROOT              8   ?               6.000E+000   FUNKY_OPT_UNIQUE  5.411E-007   5.063E+002 
08  ESP_EXCHANGE      7   ?               6.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   5.063E+002 
07  SORT_PARTIAL_GRO  6   ?               6.000E+000   FUNKY_OPT_UNIQUE  8.616E-006   5.063E+002 
06  SORT              5   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.742E-002   5.063E+002 
05  ESP_EXCHANGE      4   ?               6.000E+000   FUNKY_OPT_UNIQUE  2.643E-003   5.063E+002 
04  SPLIT_TOP         3   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.972E-002   5.063E+002 
03  PARTITION_ACCESS  2   ?               6.000E+000   FUNKY_OPT_UNIQUE  1.972E-002   5.063E+002 
02  HASH_PARTIAL_GRO  1   ?               6.000E+000   FUNKY_OPT_UNIQUE  3.726E+002   5.063E+002 
01  INDEX_SCAN        ?   ?   LINEITEM    5.864E+008   FUNKY_OPT_UNIQUE  1.336E+002   1.336E+002 

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

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

8    .    9    root                                                  6.00E+000
7    .    8    esp_exchange                    1:4(hash2)            6.00E+000
6    .    7    sort_partial_groupby                                  6.00E+000
5    .    6    sort                                                  6.00E+000
4    .    5    esp_exchange                    4(hash2):4(range)     6.00E+000
3    .    4    split_top                       4(range):72(range)    6.00E+000
2    .    3    partition_access                                      6.00E+000
1    .    2    hash_partial_groupby                                  6.00E+000
.    .    1    index_scan                      LX3                   5.86E+008

--- SQL operation complete.
>>obey optdml02(q02);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

42  ROOT              41  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.010E-007   2.739E+001 
41  FIRSTN            40  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.739E+001 
40  ESP_EXCHANGE      39  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.777E-001   2.739E+001 
39  SORT              38  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   2.721E+001 
38  HASH_GROUPBY      37  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.043E-001   2.719E+001 
37  HYBRID_HASH_JOIN  36  3               1.598E+004   FUNKY_OPT_UNIQUE  5.463E-002   2.699E+001 
36  HYBRID_HASH_JOIN  35  6               7.991E+004   FUNKY_OPT_UNIQUE  6.028E-002   2.693E+001 
35  HYBRID_HASH_JOIN  34  31              7.991E+004   FUNKY_OPT_UNIQUE  7.449E-001   2.686E+001 
34  SPLIT_TOP         33  ?               1.000E+006   FUNKY_OPT_UNIQUE  4.373E+000   5.012E+000 
33  PARTITION_ACCESS  32  ?               1.000E+006   FUNKY_OPT_UNIQUE  4.373E+000   5.012E+000 
32  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  6.389E-001   6.389E-001 
31  ESP_EXCHANGE      30  ?               7.991E+004   FUNKY_OPT_UNIQUE  1.844E-001   2.112E+001 
30  NESTED_JOIN       26  29              7.991E+004   FUNKY_OPT_UNIQUE  8.697E-004   2.094E+001 
29  SPLIT_TOP         28  ?               4.000E+000   FUNKY_OPT_UNIQUE  1.625E+000   7.310E+000 
28  PARTITION_ACCESS  27  ?               4.000E+000   FUNKY_OPT_UNIQUE  1.625E+000   7.310E+000 
27  INDEX_SCAN        ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  5.684E+000   5.684E+000 
26  HYBRID_HASH_JOIN  25  9               1.997E+004   FUNKY_OPT_UNIQUE  6.828E-002   1.363E+001 
25  HYBRID_HASH_JOIN  24  12              9.989E+004   FUNKY_OPT_UNIQUE  7.532E-002   1.355E+001 
24  HYBRID_HASH_JOIN  23  20              9.989E+004   FUNKY_OPT_UNIQUE  7.643E-001   1.347E+001 
23  SPLIT_TOP         22  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.172E+000 
22  PARTITION_ACCESS  21  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.172E+000 
21  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  6.389E-001   6.389E-001 
20  ESP_EXCHANGE      19  ?               9.989E+004   FUNKY_OPT_UNIQUE  2.097E-001   1.154E+001 
19  NESTED_JOIN       15  18              9.989E+004   FUNKY_OPT_UNIQUE  1.087E-003   1.133E+001 
18  SPLIT_TOP         17  ?               4.000E+000   FUNKY_OPT_UNIQUE  2.018E+000   7.749E+000 
17  PARTITION_ACCESS  16  ?               4.000E+000   FUNKY_OPT_UNIQUE  2.018E+000   7.749E+000 
16  INDEX_SCAN        ?   ?   PARTSUPP    4.000E+000   FUNKY_OPT_UNIQUE  5.730E+000   5.730E+000 
15  SPLIT_TOP         14  ?               2.497E+004   FUNKY_OPT_UNIQUE  4.050E-002   3.587E+000 
14  PARTITION_ACCESS  13  ?               2.497E+004   FUNKY_OPT_UNIQUE  4.050E-002   3.587E+000 
13  INDEX_SCAN        ?   ?   PART        2.497E+004   FUNKY_OPT_UNIQUE  3.547E+000   3.547E+000 
12  ESP_EXCHANGE      11  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   6.893E-003 
11  PARTITION_ACCESS  10  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.453E-003 
10  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  4.016E-003   4.016E-003 
09  ESP_EXCHANGE      8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   6.777E-003 
08  PARTITION_ACCESS  7   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.336E-003 
07  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  3.899E-003   3.899E-003 
06  ESP_EXCHANGE      5   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   6.893E-003 
05  PARTITION_ACCESS  4   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.453E-003 
04  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  4.016E-003   4.016E-003 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   6.777E-003 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.336E-003 
01  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  3.899E-003   3.899E-003 

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

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

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

--- SQL operation complete.
>>obey optdml02(q03);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

17  ROOT              16  ?               4.717E+007   FUNKY_OPT_UNIQUE  1.264E+000   1.464E+003 
16  FIRSTN            15  ?               4.717E+007   FUNKY_OPT_UNIQUE  0.000E+000   1.463E+003 
15  ESP_EXCHANGE      14  ?               4.717E+007   FUNKY_OPT_UNIQUE  1.744E-001   1.463E+003 
14  SORT              13  ?               4.717E+007   FUNKY_OPT_UNIQUE  8.181E+002   1.463E+003 
13  HYBRID_HASH_JOIN  12  8               4.717E+007   FUNKY_OPT_UNIQUE  7.208E+001   6.450E+002 
12  SPLIT_TOP         11  ?               7.291E+007   FUNKY_OPT_UNIQUE  5.463E+001   4.103E+002 
11  PARTITION_ACCESS  10  ?               7.291E+007   FUNKY_OPT_UNIQUE  5.463E+001   4.103E+002 
10  SORT_GROUPBY      9   ?               7.291E+007   FUNKY_OPT_UNIQUE  1.012E+002   3.557E+002 
09  FILE_SCAN         ?   ?   LINEITEM    3.231E+008   FUNKY_OPT_UNIQUE  2.544E+002   2.544E+002 
08  ESP_EXCHANGE      7   ?               2.187E+007   FUNKY_OPT_UNIQUE  2.849E+001   1.627E+002 
07  HYBRID_HASH_JOIN  6   3               2.187E+007   FUNKY_OPT_UNIQUE  5.312E+001   1.342E+002 
06  SPLIT_TOP         5   ?               7.291E+007   FUNKY_OPT_UNIQUE  4.884E+001   7.123E+001 
05  PARTITION_ACCESS  4   ?               7.291E+007   FUNKY_OPT_UNIQUE  4.884E+001   7.123E+001 
04  INDEX_SCAN        ?   ?   ORDERS      7.291E+007   FUNKY_OPT_UNIQUE  2.238E+001   2.238E+001 
03  SPLIT_TOP         2   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.317E+000   1.011E+001 
02  PARTITION_ACCESS  1   ?               3.000E+006   FUNKY_OPT_UNIQUE  1.317E+000   1.011E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    3.000E+006   FUNKY_OPT_UNIQUE  8.799E+000   8.799E+000 

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

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

16   .    17   root                                                  4.71E+007
15   .    16   firstn                                                4.71E+007
14   .    15   esp_exchange                    1:4(range)            4.71E+007
13   .    14   sort                                                  4.71E+007
12   8    13   hybrid_hash_join                                      4.71E+007
11   .    12   split_top                       4(range):64(range)    7.29E+007
10   .    11   partition_access                                      7.29E+007
9    .    10   sort_groupby                                          7.29E+007
.    .    9    file_scan                       LINEITEM              3.23E+008
7    .    8    esp_exchange                    4(range):4(range)     2.18E+007
6    3    7    hybrid_hash_join      u                               2.18E+007
5    .    6    split_top                       4(range):64(range)    7.29E+007
4    .    5    partition_access                                      7.29E+007
.    .    4    index_scan            fr        OX2                   7.29E+007
2    .    3    split_top                       4(range):64(range)    3.00E+006
1    .    2    partition_access                                      3.00E+006
.    .    1    file_scan             fr        CUSTOMER              3.00E+006

--- SQL operation complete.
>>obey optdml02(q04);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               5.000E+000   FUNKY_OPT_UNIQUE  1.401E-007   6.329E+002 
12  ESP_EXCHANGE      11  ?               5.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   6.329E+002 
11  SORT_PARTIAL_GRO  10  ?               5.000E+000   FUNKY_OPT_UNIQUE  4.854E-006   6.329E+002 
10  SORT              9   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   6.329E+002 
09  ESP_EXCHANGE      8   ?               5.000E+000   FUNKY_OPT_UNIQUE  6.591E-004   6.328E+002 
08  HASH_PARTIAL_GRO  7   ?               5.000E+000   FUNKY_OPT_UNIQUE  1.843E+000   6.328E+002 
07  HYBRID_HASH_SEMI  6   3               7.730E+006   FUNKY_OPT_UNIQUE  2.418E+002   6.310E+002 
06  SPLIT_TOP         5   ?               5.798E+006   FUNKY_OPT_UNIQUE  4.240E+000   6.170E+001 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  4.240E+000   6.170E+001 
04  FILE_SCAN         ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  5.746E+001   5.746E+001 
03  SPLIT_TOP         2   ?               1.997E+008   FUNKY_OPT_UNIQUE  8.661E+001   3.275E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  8.661E+001   3.275E+002 
01  FILE_SCAN         ?   ?   LINEITEM    1.997E+008   FUNKY_OPT_UNIQUE  2.409E+002   2.409E+002 

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

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

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

--- SQL operation complete.
>>obey optdml02(q05);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.066E-006   6.218E+002 
30  ESP_EXCHANGE      29  ?               2.500E+001   FUNKY_OPT_UNIQUE  3.648E-003   6.218E+002 
29  SORT              28  ?               2.500E+001   FUNKY_OPT_UNIQUE  1.742E-002   6.218E+002 
28  HASH_PARTIAL_GRO  27  ?               2.500E+001   FUNKY_OPT_UNIQUE  4.069E-005   6.218E+002 
27  ESP_EXCHANGE      26  ?               2.500E+001   FUNKY_OPT_UNIQUE  6.655E-004   6.218E+002 
26  HASH_PARTIAL_GRO  25  ?               2.500E+001   FUNKY_OPT_UNIQUE  2.197E-001   6.218E+002 
25  HYBRID_HASH_JOIN  24  4               7.302E+005   FUNKY_OPT_UNIQUE  3.055E+001   6.216E+002 
24  NESTED_JOIN       20  23              1.825E+007   FUNKY_OPT_UNIQUE  1.986E-001   5.933E+002 
23  SPLIT_TOP         22  ?               4.000E+000   FUNKY_OPT_UNIQUE  3.220E+002   5.277E+002 
22  PARTITION_ACCESS  21  ?               4.000E+000   FUNKY_OPT_UNIQUE  3.220E+002   5.277E+002 
21  FILE_SCAN         ?   ?   LINEITEM    4.000E+000   FUNKY_OPT_UNIQUE  2.056E+002   2.056E+002 
20  HYBRID_HASH_JOIN  19  16              4.563E+006   FUNKY_OPT_UNIQUE  1.827E+001   6.541E+001 
19  SPLIT_TOP         18  ?               2.281E+007   FUNKY_OPT_UNIQUE  1.170E+001   3.859E+001 
18  PARTITION_ACCESS  17  ?               2.281E+007   FUNKY_OPT_UNIQUE  1.170E+001   3.859E+001 
17  INDEX_SCAN        ?   ?   ORDERS      2.281E+007   FUNKY_OPT_UNIQUE  2.688E+001   2.688E+001 
16  ESP_EXCHANGE      15  ?               3.000E+006   FUNKY_OPT_UNIQUE  5.258E+000   8.605E+000 
15  NESTED_JOIN       11  14              3.000E+006   FUNKY_OPT_UNIQUE  2.611E-002   3.346E+000 
14  SPLIT_TOP         13  ?               6.000E+005   FUNKY_OPT_UNIQUE  1.086E+000   3.308E+000 
13  PARTITION_ACCESS  12  ?               6.000E+005   FUNKY_OPT_UNIQUE  1.086E+000   3.308E+000 
12  INDEX_SCAN        ?   ?   CUSTOMER    6.000E+005   FUNKY_OPT_UNIQUE  2.221E+000   2.221E+000 
11  HYBRID_HASH_JOIN  10  7               5.000E+000   FUNKY_OPT_UNIQUE  3.072E-005   1.281E-002 
10  ESP_EXCHANGE      9   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.021E-003   6.475E-003 
09  PARTITION_ACCESS  8   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.453E-003 
08  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  4.016E-003   4.016E-003 
07  ESP_EXCHANGE      6   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.997E-003   6.334E-003 
06  PARTITION_ACCESS  5   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.336E-003 
05  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  3.899E-003   3.899E-003 
04  ESP_EXCHANGE      3   ?               1.000E+006   FUNKY_OPT_UNIQUE  2.225E+000   2.740E+000 
03  SPLIT_TOP         2   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.144E-001 
02  PARTITION_ACCESS  1   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.144E-001 
01  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  1.281E-001   1.281E-001 

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

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

30   .    31   root                                                  2.50E+001
29   .    30   esp_exchange                    1:4(hash2)            2.50E+001
28   .    29   sort                                                  2.50E+001
27   .    28   hash_partial_groupby                                  2.50E+001
26   .    27   esp_exchange                    4(hash2):4(range)     2.50E+001
25   .    26   hash_partial_groupby                                  2.50E+001
24   4    25   hybrid_hash_join      u                               7.30E+005
20   23   24   nested_join                                           1.82E+007
22   .    23   split_top                       4(rep-n):64(range)    4.00E+000
21   .    22   partition_access                                      4.00E+000
.    .    21   file_scan             fr        LINEITEM              4.00E+000
19   16   20   hybrid_hash_join                                      4.56E+006
18   .    19   split_top                       4(range):64(range)    2.28E+007
17   .    18   partition_access                                      2.28E+007
.    .    17   index_scan            fr        OX2                   2.28E+007
15   .    16   esp_exchange                    4(range):4(hash2)     3.00E+006
11   14   15   nested_join                                           3.00E+006
13   .    14   split_top                       4(rep-n):12(range)    6.00E+005
12   .    13   partition_access                                      6.00E+005
.    .    12   index_scan            fr        CX1                   6.00E+005
10   7    11   hybrid_hash_join      u                               5.00E+000
9    .    10   esp_exchange                    4(hash2):1            2.50E+001
8    .    9    partition_access                                      2.50E+001
.    .    8    file_scan             fr        NATION                2.50E+001
6    .    7    esp_exchange                    4(hash2):1            1.00E+000
5    .    6    partition_access                                      1.00E+000
.    .    5    file_scan             fr        REGION                1.00E+000
3    .    4    esp_exchange                    4(rep-b):4(range)     1.00E+006
2    .    3    split_top                       4(range):12(range)    1.00E+006
1    .    2    partition_access                                      1.00E+006
.    .    1    index_scan            fr        SX1                   1.00E+006

--- SQL operation complete.
>>obey optdml02(q06);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

08  ROOT              7   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   2.333E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.036E-001   2.333E+001 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   2.303E+001 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.591E-002   2.302E+001 
04  SPLIT_TOP         3   ?               5.717E+006   FUNKY_OPT_UNIQUE  4.101E+000   2.295E+001 
03  PARTITION_ACCESS  2   ?               5.717E+006   FUNKY_OPT_UNIQUE  4.101E+000   2.295E+001 
02  SORT_PARTIAL_AGG  1   ?               5.717E+006   FUNKY_OPT_UNIQUE  1.518E-001   1.885E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    1.143E+007   FUNKY_OPT_UNIQUE  1.869E+001   1.869E+001 

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

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

7    .    8    root                                                  1.00E+000
6    .    7    sort_partial_aggr_ro                                  1.00E+000
5    .    6    esp_exchange                    1:4(range)            1.00E+000
4    .    5    sort_partial_aggr_no                                  1.00E+000
3    .    4    split_top                       4(range):72(range)    5.71E+006
2    .    3    partition_access                                      5.71E+006
1    .    2    sort_partial_aggr_le                                  5.71E+006
.    .    1    index_scan                      LX3 (m)               1.14E+007

--- SQL operation complete.
>>obey optdml02(q07);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.280E-004   8.596E+002 
30  ESP_EXCHANGE      29  ?               2.924E+003   FUNKY_OPT_UNIQUE  1.526E-002   8.596E+002 
29  SORT_PARTIAL_GRO  28  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.396E-003   8.596E+002 
28  SORT              27  ?               2.924E+003   FUNKY_OPT_UNIQUE  2.785E-002   8.596E+002 
27  ESP_EXCHANGE      26  ?               2.924E+003   FUNKY_OPT_UNIQUE  4.714E-003   8.596E+002 
26  HASH_PARTIAL_GRO  25  ?               2.924E+003   FUNKY_OPT_UNIQUE  4.452E-001   8.596E+002 
25  HYBRID_HASH_JOIN  24  4               5.828E+005   FUNKY_OPT_UNIQUE  4.265E+001   8.591E+002 
24  ESP_EXCHANGE      23  ?               1.457E+007   FUNKY_OPT_UNIQUE  4.733E+001   7.811E+002 
23  HYBRID_HASH_JOIN  22  19              1.457E+007   FUNKY_OPT_UNIQUE  2.156E+002   7.338E+002 
22  SPLIT_TOP         21  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.441E+002 
21  PARTITION_ACCESS  20  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.441E+002 
20  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  6.725E+001   6.725E+001 
19  ESP_EXCHANGE      18  ?               1.457E+007   FUNKY_OPT_UNIQUE  3.769E+001   3.744E+002 
18  HYBRID_HASH_JOIN  17  14              1.457E+007   FUNKY_OPT_UNIQUE  1.228E+002   3.367E+002 
17  SPLIT_TOP         16  ?               1.821E+008   FUNKY_OPT_UNIQUE  1.445E+002   2.124E+002 
16  PARTITION_ACCESS  15  ?               1.821E+008   FUNKY_OPT_UNIQUE  1.445E+002   2.124E+002 
15  INDEX_SCAN        ?   ?   LINEITEM    1.821E+008   FUNKY_OPT_UNIQUE  6.793E+001   6.793E+001 
14  ESP_EXCHANGE      13  ?               8.000E+004   FUNKY_OPT_UNIQUE  1.724E+000   1.866E+000 
13  NESTED_JOIN       9   12              8.000E+004   FUNKY_OPT_UNIQUE  2.786E-003   1.416E-001 
12  SPLIT_TOP         11  ?               4.000E+004   FUNKY_OPT_UNIQUE  6.595E-002   1.297E-001 
11  PARTITION_ACCESS  10  ?               4.000E+004   FUNKY_OPT_UNIQUE  6.595E-002   1.297E-001 
10  INDEX_SCAN        ?   ?   SUPPLIER    4.000E+004   FUNKY_OPT_UNIQUE  6.382E-002   6.382E-002 
09  HYBRID_HASH_JOIN  8   6               2.000E+000   FUNKY_OPT_UNIQUE  1.112E-003   8.923E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.449E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  4.013E-003   4.013E-003 
06  PARTITION_ACCESS  5   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.449E-003 
05  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  4.013E-003   4.013E-003 
04  ESP_EXCHANGE      3   ?               1.500E+007   FUNKY_OPT_UNIQUE  2.817E+001   3.579E+001 
03  SPLIT_TOP         2   ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.623E+000 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.623E+000 
01  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.867E+000   1.867E+000 

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

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

30   .    31   root                                                  2.92E+003
29   .    30   esp_exchange                    1:4(hash2)            2.92E+003
28   .    29   sort_partial_groupby                                  2.92E+003
27   .    28   sort                                                  2.92E+003
26   .    27   esp_exchange                    4(hash2):4(hash2)     2.92E+003
25   .    26   hash_partial_groupby                                  2.92E+003
24   4    25   hybrid_hash_join                                      5.82E+005
23   .    24   esp_exchange                    4(hash2):4(range)     1.45E+007
22   19   23   hybrid_hash_join                                      1.45E+007
21   .    22   split_top                       4(range):64(range)    1.50E+008
20   .    21   partition_access                                      1.50E+008
.    .    20   file_scan             fr        ORDERS                1.50E+008
18   .    19   esp_exchange                    4(range):4(range)     1.45E+007
17   14   18   hybrid_hash_join                                      1.45E+007
16   .    17   split_top                       4(range):72(range)    1.82E+008
15   .    16   partition_access                                      1.82E+008
.    .    15   index_scan            fr        LX3 (m)               1.82E+008
13   .    14   esp_exchange                    4(rep-b):1            8.00E+004
9    12   13   nested_join                                           8.00E+004
11   .    12   split_top                       1:12(range)           4.00E+004
10   .    11   partition_access                                      4.00E+004
.    .    10   index_scan            fr        SX1                   4.00E+004
8    6    9    hybrid_hash_join                                      2.00E+000
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    partition_access                                      2.50E+001
.    .    5    file_scan             fr        NATION                2.50E+001
3    .    4    esp_exchange                    4(hash2):4(range)     1.50E+007
2    .    3    split_top                       4(range):12(range)    1.50E+007
1    .    2    partition_access                                      1.50E+007
.    .    1    index_scan            fr        CX1                   1.50E+007

--- SQL operation complete.
>>obey optdml02(q08);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

40  ROOT              39  ?               7.310E+002   FUNKY_OPT_UNIQUE  1.247E-005   2.144E+002 
39  ESP_EXCHANGE      38  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.616E-003   2.144E+002 
38  SORT_PARTIAL_GRO  37  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.493E-004   2.143E+002 
37  SORT              36  ?               7.310E+002   FUNKY_OPT_UNIQUE  1.806E-002   2.143E+002 
36  ESP_EXCHANGE      35  ?               7.310E+002   FUNKY_OPT_UNIQUE  9.321E-004   2.143E+002 
35  HASH_PARTIAL_GRO  34  ?               7.310E+002   FUNKY_OPT_UNIQUE  4.229E-002   2.143E+002 
34  HYBRID_HASH_JOIN  33  3               2.432E+005   FUNKY_OPT_UNIQUE  1.832E-001   2.143E+002 
33  HYBRID_HASH_JOIN  32  29              2.432E+005   FUNKY_OPT_UNIQUE  9.039E-001   2.141E+002 
32  SPLIT_TOP         31  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.172E+000 
31  PARTITION_ACCESS  30  ?               1.000E+006   FUNKY_OPT_UNIQUE  5.338E-001   1.172E+000 
30  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  6.389E-001   6.389E-001 
29  ESP_EXCHANGE      28  ?               2.432E+005   FUNKY_OPT_UNIQUE  3.950E-001   2.120E+002 
28  HYBRID_HASH_JOIN  27  6               2.432E+005   FUNKY_OPT_UNIQUE  8.312E-001   2.116E+002 
27  HYBRID_HASH_JOIN  26  9               1.216E+006   FUNKY_OPT_UNIQUE  9.160E-001   2.108E+002 
26  HYBRID_HASH_JOIN  25  22              1.216E+006   FUNKY_OPT_UNIQUE  1.443E+001   2.099E+002 
25  SPLIT_TOP         24  ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.623E+000 
24  PARTITION_ACCESS  23  ?               1.500E+007   FUNKY_OPT_UNIQUE  5.755E+000   7.623E+000 
23  INDEX_SCAN        ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.867E+000   1.867E+000 
22  ESP_EXCHANGE      21  ?               1.216E+006   FUNKY_OPT_UNIQUE  4.784E+000   1.880E+002 
21  HYBRID_HASH_JOIN  20  17              1.216E+006   FUNKY_OPT_UNIQUE  4.472E+001   1.832E+002 
20  SPLIT_TOP         19  ?               4.560E+007   FUNKY_OPT_UNIQUE  2.697E+001   5.419E+001 
19  PARTITION_ACCESS  18  ?               4.560E+007   FUNKY_OPT_UNIQUE  2.697E+001   5.419E+001 
18  INDEX_SCAN        ?   ?   ORDERS      4.560E+007   FUNKY_OPT_UNIQUE  2.721E+001   2.721E+001 
17  ESP_EXCHANGE      16  ?               3.996E+006   FUNKY_OPT_UNIQUE  1.434E+001   8.446E+001 
16  NESTED_JOIN       12  15              3.996E+006   FUNKY_OPT_UNIQUE  3.595E-002   7.011E+001 
15  SPLIT_TOP         14  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.303E+001   6.995E+001 
14  PARTITION_ACCESS  13  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.303E+001   6.995E+001 
13  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  5.692E+001   5.692E+001 
12  SPLIT_TOP         11  ?               1.333E+005   FUNKY_OPT_UNIQUE  6.271E-002   1.184E-001 
11  PARTITION_ACCESS  10  ?               1.333E+005   FUNKY_OPT_UNIQUE  6.271E-002   1.184E-001 
10  INDEX_SCAN        ?   ?   PART        1.333E+005   FUNKY_OPT_UNIQUE  5.574E-002   5.574E-002 
09  ESP_EXCHANGE      8   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.011E-003   6.465E-003 
08  PARTITION_ACCESS  7   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.453E-003 
07  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  4.016E-003   4.016E-003 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   6.777E-003 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.336E-003 
04  FILE_SCAN         ?   ?   REGION      1.000E+000   FUNKY_OPT_UNIQUE  3.899E-003   3.899E-003 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   6.890E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.449E-003 
01  FILE_SCAN         ?   ?   NATION)     2.500E+001   FUNKY_OPT_UNIQUE  4.013E-003   4.013E-003 

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

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

39   .    40   root                                                  7.30E+002
38   .    39   esp_exchange                    1:4(hash2)            7.30E+002
37   .    38   sort_partial_groupby                                  7.30E+002
36   .    37   sort                                                  7.30E+002
35   .    36   esp_exchange                    4(hash2):4(range)     7.30E+002
34   .    35   hash_partial_groupby                                  7.30E+002
33   3    34   hybrid_hash_join      u                               2.43E+005
32   29   33   hybrid_hash_join                                      2.43E+005
31   .    32   split_top                       4(range):72(range)    1.00E+006
30   .    31   partition_access                                      1.00E+006
.    .    30   file_scan             fr        SUPPLIER              1.00E+006
28   .    29   esp_exchange                    4(range):4(range)     2.43E+005
27   6    28   hybrid_hash_join      u                               2.43E+005
26   9    27   hybrid_hash_join      u                               1.21E+006
25   22   26   hybrid_hash_join                                      1.21E+006
24   .    25   split_top                       4(range):12(range)    1.50E+007
23   .    24   partition_access                                      1.50E+007
.    .    23   index_scan            fr        CX1                   1.50E+007
21   .    22   esp_exchange                    4(rep-b):4(range)     1.21E+006
20   17   21   hybrid_hash_join                                      1.21E+006
19   .    20   split_top                       4(range):64(range)    4.56E+007
18   .    19   partition_access                                      4.56E+007
.    .    18   index_scan            fr        OX2                   4.56E+007
16   .    17   esp_exchange                    4(rep-b):4(range)     3.99E+006
12   15   16   nested_join                                           3.99E+006
14   .    15   split_top                       4(rep-n):72(range)    2.99E+001
13   .    14   partition_access                                      2.99E+001
.    .    13   index_scan            fr        LX2                   2.99E+001
11   .    12   split_top                       4(range):72(range)    1.33E+005
10   .    11   partition_access                                      1.33E+005
.    .    10   index_scan            fr        PX1                   1.33E+005
8    .    9    esp_exchange                    4(range):1            2.50E+001
7    .    8    partition_access                                      2.50E+001
.    .    7    file_scan             fr        NATION                2.50E+001
5    .    6    esp_exchange                    4(rep-b):1            1.00E+000
4    .    5    partition_access                                      1.00E+000
.    .    4    file_scan             fr        REGION                1.00E+000
2    .    3    esp_exchange                    4(rep-b):1            2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml02(q09);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

31  ROOT              30  ?               6.015E+004   FUNKY_OPT_UNIQUE  2.712E-003   1.049E+003 
30  ESP_EXCHANGE      29  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.512E-001   1.049E+003 
29  SORT_PARTIAL_GRO  28  ?               6.015E+004   FUNKY_OPT_UNIQUE  4.201E-002   1.049E+003 
28  SORT              27  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.949E-001   1.049E+003 
27  ESP_EXCHANGE      26  ?               6.015E+004   FUNKY_OPT_UNIQUE  5.343E-002   1.049E+003 
26  HASH_PARTIAL_GRO  25  ?               6.015E+004   FUNKY_OPT_UNIQUE  1.748E+001   1.049E+003 
25  HYBRID_HASH_JOIN  24  3               3.746E+007   FUNKY_OPT_UNIQUE  2.821E+001   1.031E+003 
24  HYBRID_HASH_JOIN  23  20              3.746E+007   FUNKY_OPT_UNIQUE  2.357E+002   1.003E+003 
23  SPLIT_TOP         22  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.388E+002 
22  PARTITION_ACCESS  21  ?               1.500E+008   FUNKY_OPT_UNIQUE  7.685E+001   1.388E+002 
21  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  6.203E+001   6.203E+001 
20  HYBRID_HASH_JOIN  19  7               3.746E+007   FUNKY_OPT_UNIQUE  3.176E+001   6.292E+002 
19  ESP_EXCHANGE      18  ?               3.746E+007   FUNKY_OPT_UNIQUE  5.520E+001   5.952E+002 
18  HYBRID_HASH_JOIN  17  14              3.746E+007   FUNKY_OPT_UNIQUE  2.615E+002   5.400E+002 
17  SPLIT_TOP         16  ?               8.000E+007   FUNKY_OPT_UNIQUE  5.394E+001   6.705E+001 
16  PARTITION_ACCESS  15  ?               8.000E+007   FUNKY_OPT_UNIQUE  5.394E+001   6.705E+001 
15  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  1.310E+001   1.310E+001 
14  NESTED_JOIN       10  13              3.746E+007   FUNKY_OPT_UNIQUE  3.370E-001   2.116E+002 
13  SPLIT_TOP         12  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.242E+002   2.018E+002 
12  PARTITION_ACCESS  11  ?               2.997E+001   FUNKY_OPT_UNIQUE  1.242E+002   2.018E+002 
11  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  7.758E+001   7.758E+001 
10  SPLIT_TOP         9   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.459E+000 
09  PARTITION_ACCESS  8   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.459E+000 
08  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  8.894E+000   8.894E+000 
07  ESP_EXCHANGE      6   ?               1.000E+006   FUNKY_OPT_UNIQUE  2.225E+000   2.740E+000 
06  SPLIT_TOP         5   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.144E-001 
05  PARTITION_ACCESS  4   ?               1.000E+006   FUNKY_OPT_UNIQUE  3.863E-001   5.144E-001 
04  INDEX_SCAN        ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  1.281E-001   1.281E-001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   6.890E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.449E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  4.013E-003   4.013E-003 

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

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

30   .    31   root                                                  6.01E+004
29   .    30   esp_exchange                    1:4(hash2)            6.01E+004
28   .    29   sort_partial_groupby                                  6.01E+004
27   .    28   sort                                                  6.01E+004
26   .    27   esp_exchange                    4(hash2):4(range)     6.01E+004
25   .    26   hash_partial_groupby                                  6.01E+004
24   3    25   hybrid_hash_join      u                               3.74E+007
23   20   24   hybrid_hash_join                                      3.74E+007
22   .    23   split_top                       4(range):64(range)    1.50E+008
21   .    22   partition_access                                      1.50E+008
.    .    21   file_scan             fr        ORDERS                1.50E+008
19   7    20   hybrid_hash_join      u                               3.74E+007
18   .    19   esp_exchange                    4(range):4(range)     3.74E+007
17   14   18   hybrid_hash_join                                      3.74E+007
16   .    17   split_top                       4(range):72(range)    8.00E+007
15   .    16   partition_access                                      8.00E+007
.    .    15   index_scan            fr        PSX2                  8.00E+007
10   13   14   nested_join                                           3.74E+007
12   .    13   split_top                       4(rep-n):72(range)    2.99E+001
11   .    12   partition_access                                      2.99E+001
.    .    11   index_scan            fr        LX2                   2.99E+001
9    .    10   split_top                       4(range):72(range)    1.25E+006
8    .    9    partition_access                                      1.25E+006
.    .    8    file_scan             fr        PART                  1.25E+006
6    .    7    esp_exchange                    4(rep-b):4(range)     1.00E+006
5    .    6    split_top                       4(range):12(range)    1.00E+006
4    .    5    partition_access                                      1.00E+006
.    .    4    index_scan            fr        SX1                   1.00E+006
2    .    3    esp_exchange                    4(rep-b):1            2.50E+001
1    .    2    partition_access                                      2.50E+001
.    .    1    file_scan             fr        NATION                2.50E+001

--- SQL operation complete.
>>obey optdml02(q10);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.743E+000   8.733E+002 
21  FIRSTN            20  ?               5.723E+006   FUNKY_OPT_UNIQUE  0.000E+000   8.716E+002 
20  ESP_EXCHANGE      19  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.744E-001   8.716E+002 
19  SORT              18  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.345E+002   8.714E+002 
18  HASH_GROUPBY      17  ?               5.723E+006   FUNKY_OPT_UNIQUE  1.535E+002   7.369E+002 
17  HYBRID_HASH_JOIN  16  3               5.723E+006   FUNKY_OPT_UNIQUE  4.310E+000   5.833E+002 
16  HYBRID_HASH_JOIN  15  12              5.723E+006   FUNKY_OPT_UNIQUE  1.557E+001   5.790E+002 
15  SPLIT_TOP         14  ?               1.500E+007   FUNKY_OPT_UNIQUE  7.011E+001   8.107E+001 
14  PARTITION_ACCESS  13  ?               1.500E+007   FUNKY_OPT_UNIQUE  7.011E+001   8.107E+001 
13  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.095E+001   1.095E+001 
12  ESP_EXCHANGE      11  ?               5.723E+006   FUNKY_OPT_UNIQUE  7.947E+000   4.827E+002 
11  HYBRID_HASH_JOIN  10  7               5.723E+006   FUNKY_OPT_UNIQUE  1.041E+002   4.747E+002 
10  SPLIT_TOP         9   ?               1.479E+008   FUNKY_OPT_UNIQUE  9.908E+001   3.343E+002 
09  PARTITION_ACCESS  8   ?               1.479E+008   FUNKY_OPT_UNIQUE  9.908E+001   3.343E+002 
08  FILE_SCAN         ?   ?   LINEITEM    1.479E+008   FUNKY_OPT_UNIQUE  2.352E+002   2.352E+002 
07  ESP_EXCHANGE      6   ?               5.798E+006   FUNKY_OPT_UNIQUE  7.059E+000   3.668E+001 
06  SPLIT_TOP         5   ?               5.798E+006   FUNKY_OPT_UNIQUE  2.987E+000   2.962E+001 
05  PARTITION_ACCESS  4   ?               5.798E+006   FUNKY_OPT_UNIQUE  2.987E+000   2.962E+001 
04  INDEX_SCAN        ?   ?   ORDERS      5.798E+006   FUNKY_OPT_UNIQUE  2.664E+001   2.664E+001 
03  ESP_EXCHANGE      2   ?               2.500E+001   FUNKY_OPT_UNIQUE  2.440E-003   6.890E-003 
02  PARTITION_ACCESS  1   ?               2.500E+001   FUNKY_OPT_UNIQUE  4.368E-004   4.449E-003 
01  FILE_SCAN         ?   ?   NATION      2.500E+001   FUNKY_OPT_UNIQUE  4.013E-003   4.013E-003 

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

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

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

--- SQL operation complete.
>>obey optdml02(q11);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

33  ROOT              32  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.571E-002   3.114E+001 
32  ESP_EXCHANGE      31  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.744E-001   3.113E+001 
31  SORT              30  ?               1.074E+006   FUNKY_OPT_UNIQUE  1.987E+000   3.095E+001 
30  HYBRID_HASH_JOIN  29  15              1.074E+006   FUNKY_OPT_UNIQUE  1.418E+000   2.896E+001 
29  HASH_PARTIAL_GRO  28  ?               3.223E+006   FUNKY_OPT_UNIQUE  2.998E+000   1.671E+001 
28  ESP_EXCHANGE      27  ?               3.223E+006   FUNKY_OPT_UNIQUE  1.394E+000   1.372E+001 
27  HASH_PARTIAL_GRO  26  ?               3.223E+006   FUNKY_OPT_UNIQUE  7.732E-001   1.232E+001 
26  NESTED_JOIN       22  25              3.223E+006   FUNKY_OPT_UNIQUE  2.841E-002   1.155E+001 
25  SPLIT_TOP         24  ?               8.000E+001   FUNKY_OPT_UNIQUE  5.352E+000   1.146E+001 
24  PARTITION_ACCESS  23  ?               8.000E+001   FUNKY_OPT_UNIQUE  5.352E+000   1.146E+001 
23  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+001   FUNKY_OPT_UNIQUE  6.108E+000   6.108E+000 
22  NESTED_JOIN       18  21              4.028E+004   FUNKY_OPT_UNIQUE  3.508E-004   6.271E-002 
21  SPLIT_TOP         20  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   5.592E-002 
20  PARTITION_ACCESS  19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   5.592E-002 
19  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  3.051E-002   3.051E-002 
18  ESP_EXCHANGE      17  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.997E-003   6.437E-003 
17  PARTITION_ACCESS  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.439E-003 
16  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  4.002E-003   4.002E-003 
15  ESP_EXCHANGE      14  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   1.083E+001 
14  SORT_PARTIAL_AGG  13  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.080E-006   1.082E+001 
13  ESP_EXCHANGE      12  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   1.082E+001 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.069E-002   1.082E+001 
11  NESTED_JOIN       7   10              3.223E+006   FUNKY_OPT_UNIQUE  2.841E-002   1.081E+001 
10  SPLIT_TOP         9   ?               8.000E+001   FUNKY_OPT_UNIQUE  4.617E+000   1.072E+001 
09  PARTITION_ACCESS  8   ?               8.000E+001   FUNKY_OPT_UNIQUE  4.617E+000   1.072E+001 
08  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+001   FUNKY_OPT_UNIQUE  6.108E+000   6.108E+000 
07  NESTED_JOIN       3   6               4.028E+004   FUNKY_OPT_UNIQUE  3.508E-004   6.271E-002 
06  SPLIT_TOP         5   ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   5.592E-002 
05  PARTITION_ACCESS  4   ?               4.028E+004   FUNKY_OPT_UNIQUE  2.540E-002   5.592E-002 
04  INDEX_SCAN        ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  3.051E-002   3.051E-002 
03  ESP_EXCHANGE      2   ?               1.000E+000   FUNKY_OPT_UNIQUE  1.997E-003   6.437E-003 
02  PARTITION_ACCESS  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.439E-003 
01  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  4.002E-003   4.002E-003 

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

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

32   .    33   root                                                  1.07E+006
31   .    32   esp_exchange                    1:4(hash2)            1.07E+006
30   .    31   sort                                                  1.07E+006
29   15   30   hybrid_hash_join                                      1.07E+006
28   .    29   hash_partial_groupby                                  3.22E+006
27   .    28   esp_exchange                    4(hash2):4(hash2)     3.22E+006
26   .    27   hash_partial_groupby                                  3.22E+006
22   25   26   nested_join                                           3.22E+006
24   .    25   split_top                       4(rep-n):72(range)    8.00E+001
23   .    24   partition_access                                      8.00E+001
.    .    23   index_scan            fr        PSX1                  8.00E+001
18   21   22   nested_join                                           4.02E+004
20   .    21   split_top                       4(rep-n):12(range)    4.02E+004
19   .    20   partition_access                                      4.02E+004
.    .    19   index_scan            fr        SX1                   4.02E+004
17   .    18   esp_exchange                    4(hash2):1            1.00E+000
16   .    17   partition_access                                      1.00E+000
.    .    16   file_scan             fr        NATION                1.00E+000
14   .    15   esp_exchange                    4(rep-b):1            1.00E+000
13   .    14   sort_partial_aggr_ro                                  1.00E+000
12   .    13   esp_exchange                    1:4(hash2)            1.00E+000
11   .    12   sort_partial_aggr_le                                  1.00E+000
7    10   11   nested_join                                           3.22E+006
9    .    10   split_top                       4(rep-n):72(range)    8.00E+001
8    .    9    partition_access                                      8.00E+001
.    .    8    index_scan            fr        PSX1                  8.00E+001
3    6    7    nested_join                                           4.02E+004
5    .    6    split_top                       4(rep-n):12(range)    4.02E+004
4    .    5    partition_access                                      4.02E+004
.    .    4    index_scan            fr        SX1                   4.02E+004
2    .    3    esp_exchange                    4(hash2):1            1.00E+000
1    .    2    partition_access                                      1.00E+000
.    .    1    file_scan             fr        NATION                1.00E+000

--- SQL operation complete.
>>obey optdml02(q12);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

14  ROOT              13  ?               2.000E+000   FUNKY_OPT_UNIQUE  6.338E-008   8.159E+001 
13  ESP_EXCHANGE      12  ?               2.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   8.159E+001 
12  SORT_PARTIAL_GRO  11  ?               2.000E+000   FUNKY_OPT_UNIQUE  4.874E-006   8.159E+001 
11  SORT              10  ?               2.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   8.159E+001 
10  ESP_EXCHANGE      9   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.589E-004   8.157E+001 
09  HASH_PARTIAL_GRO  8   ?               2.000E+000   FUNKY_OPT_UNIQUE  6.092E-001   8.157E+001 
08  NESTED_JOIN       3   7               2.892E+006   FUNKY_OPT_UNIQUE  5.037E-002   8.096E+001 
07  PROBE_CACHE       6   ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   7.155E+001 
06  SPLIT_TOP         5   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.811E+001   7.155E+001 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.811E+001   7.155E+001 
04  INDEX_SCAN        ?   ?   ORDERS      1.000E+000   FUNKY_OPT_UNIQUE  3.343E+001   3.343E+001 
03  SPLIT_TOP         2   ?               2.892E+006   FUNKY_OPT_UNIQUE  1.885E+000   9.358E+000 
02  PARTITION_ACCESS  1   ?               2.892E+006   FUNKY_OPT_UNIQUE  1.885E+000   9.358E+000 
01  INDEX_SCAN        ?   ?   LINEITEM    2.892E+006   FUNKY_OPT_UNIQUE  7.472E+000   7.472E+000 

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

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

13   .    14   root                                                  2.00E+000
12   .    13   esp_exchange                    1:4(hash2)            2.00E+000
11   .    12   sort_partial_groupby                                  2.00E+000
10   .    11   sort                                                  2.00E+000
9    .    10   esp_exchange                    4(hash2):4(range)     2.00E+000
8    .    9    hash_partial_groupby                                  2.00E+000
3    7    8    nested_join                                           2.89E+006
6    .    7    probe_cache                                           1.00E+000
5    .    6    split_top                       4(rep-n):12(range)    1.00E+000
4    .    5    partition_access                                      1.00E+000
.    .    4    index_scan            fr        OX4                   1.00E+000
2    .    3    split_top                       4(range):98(range)    2.89E+006
1    .    2    partition_access                                      2.89E+006
.    .    1    index_scan            fr        LX5 (m)               2.89E+006

--- SQL operation complete.
>>obey optdml02(q13);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               1.481E+003   FUNKY_OPT_UNIQUE  2.527E-005   1.794E+000 
12  ESP_EXCHANGE      11  ?               1.481E+003   FUNKY_OPT_UNIQUE  5.826E-003   1.794E+000 
11  SORT_PARTIAL_GRO  10  ?               1.481E+003   FUNKY_OPT_UNIQUE  9.014E-004   1.788E+000 
10  SORT              9   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.879E-002   1.787E+000 
09  ESP_EXCHANGE      8   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.209E-003   1.768E+000 
08  HASH_PARTIAL_GRO  7   ?               1.481E+003   FUNKY_OPT_UNIQUE  1.361E-003   1.767E+000 
07  NESTED_JOIN       3   6               1.481E+003   FUNKY_OPT_UNIQUE  2.599E-005   1.765E+000 
06  SPLIT_TOP         5   ?               9.873E-001   FUNKY_OPT_UNIQUE  1.484E-001   1.754E+000 
05  PARTITION_ACCESS  4   ?               9.873E-001   FUNKY_OPT_UNIQUE  1.484E-001   1.754E+000 
04  FILE_SCAN         ?   ?   LINEITEM    9.873E-001   FUNKY_OPT_UNIQUE  1.606E+000   1.606E+000 
03  SPLIT_TOP         2   ?               1.500E+003   FUNKY_OPT_UNIQUE  7.098E-003   1.117E-002 
02  PARTITION_ACCESS  1   ?               1.500E+003   FUNKY_OPT_UNIQUE  7.098E-003   1.117E-002 
01  INDEX_SCAN        ?   ?   ORDERS      1.500E+003   FUNKY_OPT_UNIQUE  4.074E-003   4.074E-003 

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

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

12   .    13   root                                                  1.48E+003
11   .    12   esp_exchange                    1:4(hash2)            1.48E+003
10   .    11   sort_partial_groupby                                  1.48E+003
9    .    10   sort                                                  1.48E+003
8    .    9    esp_exchange                    4(hash2):4(range)     1.48E+003
7    .    8    hash_partial_groupby                                  1.48E+003
3    6    7    nested_join                                           1.48E+003
5    .    6    split_top                       4(rep-n):64(range)    9.87E-001
4    .    5    partition_access                                      9.87E-001
.    .    4    file_scan             fr        LINEITEM              9.87E-001
2    .    3    split_top                       4(range):16(range)    1.50E+003
1    .    2    partition_access                                      1.50E+003
.    .    1    index_scan            fr        OX1                   1.50E+003

--- SQL operation complete.
>>obey optdml02(q14);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

12  ROOT              11  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   7.860E+001 
11  SORT_PARTIAL_AGG  10  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.104E-006   7.860E+001 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   7.860E+001 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  5.125E-002   7.860E+001 
08  HYBRID_HASH_JOIN  7   4               7.720E+006   FUNKY_OPT_UNIQUE  2.086E+001   7.854E+001 
07  SPLIT_TOP         6   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.945E+001   3.020E+001 
06  PARTITION_ACCESS  5   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.945E+001   3.020E+001 
05  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  1.074E+001   1.074E+001 
04  ESP_EXCHANGE      3   ?               7.720E+006   FUNKY_OPT_UNIQUE  1.137E+001   2.756E+001 
03  SPLIT_TOP         2   ?               7.720E+006   FUNKY_OPT_UNIQUE  5.838E+000   1.618E+001 
02  PARTITION_ACCESS  1   ?               7.720E+006   FUNKY_OPT_UNIQUE  5.838E+000   1.618E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    7.720E+006   FUNKY_OPT_UNIQUE  1.034E+001   1.034E+001 

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

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

11   .    12   root                                                  1.00E+000
10   .    11   sort_partial_aggr_ro                                  1.00E+000
9    .    10   esp_exchange                    1:4(range)            1.00E+000
8    .    9    sort_partial_aggr_le                                  1.00E+000
7    4    8    hybrid_hash_join                                      7.72E+006
6    .    7    split_top                       4(range):72(range)    2.00E+007
5    .    6    partition_access                                      2.00E+007
.    .    5    file_scan             fr        PART                  2.00E+007
3    .    4    esp_exchange                    4(range):4(range)     7.72E+006
2    .    3    split_top                       4(range):72(range)    7.72E+006
1    .    2    partition_access                                      7.72E+006
.    .    1    index_scan            fr        LX3 (m)               7.72E+006

--- SQL operation complete.
>>obey optdml02(q15);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

24  ROOT              23  ?               3.333E+005   FUNKY_OPT_UNIQUE  3.818E-002   1.077E+002 
23  ESP_EXCHANGE      22  ?               3.333E+005   FUNKY_OPT_UNIQUE  1.744E-001   1.077E+002 
22  SORT              21  ?               3.333E+005   FUNKY_OPT_UNIQUE  7.069E-001   1.075E+002 
21  HYBRID_HASH_JOIN  20  17              3.333E+005   FUNKY_OPT_UNIQUE  9.916E-001   1.068E+002 
20  SPLIT_TOP         19  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.086E+000   2.690E+000 
19  PARTITION_ACCESS  18  ?               1.000E+006   FUNKY_OPT_UNIQUE  2.086E+000   2.690E+000 
18  FILE_SCAN         ?   ?   SUPPLIER    1.000E+006   FUNKY_OPT_UNIQUE  6.041E-001   6.041E-001 
17  HYBRID_HASH_JOIN  16  10              3.333E+005   FUNKY_OPT_UNIQUE  1.048E+000   1.031E+002 
16  HASH_PARTIAL_GRO  15  ?               1.000E+006   FUNKY_OPT_UNIQUE  1.036E+001   5.146E+001 
15  ESP_EXCHANGE      14  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.488E+000   4.110E+001 
14  HASH_PARTIAL_GRO  13  ?               1.489E+007   FUNKY_OPT_UNIQUE  5.343E+000   3.561E+001 
13  SPLIT_TOP         12  ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   3.027E+001 
12  PARTITION_ACCESS  11  ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   3.027E+001 
11  INDEX_SCAN        ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.481E+001   1.481E+001 
10  ESP_EXCHANGE      9   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   5.067E+001 
09  SORT_PARTIAL_AGG  8   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   5.067E+001 
08  ESP_EXCHANGE      7   ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   5.067E+001 
07  SORT_PARTIAL_AGG  6   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.319E-003   5.067E+001 
06  HASH_PARTIAL_GRO  5   ?               1.000E+006   FUNKY_OPT_UNIQUE  1.036E+001   5.067E+001 
05  ESP_EXCHANGE      4   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.488E+000   4.030E+001 
04  HASH_PARTIAL_GRO  3   ?               1.489E+007   FUNKY_OPT_UNIQUE  5.343E+000   3.481E+001 
03  SPLIT_TOP         2   ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   2.947E+001 
02  PARTITION_ACCESS  1   ?               2.291E+007   FUNKY_OPT_UNIQUE  1.546E+001   2.947E+001 
01  INDEX_SCAN        ?   ?   LINEITEM    2.291E+007   FUNKY_OPT_UNIQUE  1.401E+001   1.401E+001 

--- 24 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
---- ---- ---- --------------------  --------  --------------------  ---------

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

--- SQL operation complete.
>>obey optdml02(q16);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

20  ROOT              19  ?               2.707E+004   FUNKY_OPT_UNIQUE  1.616E-003   2.007E+002 
19  ESP_EXCHANGE      18  ?               2.707E+004   FUNKY_OPT_UNIQUE  9.197E-002   2.007E+002 
18  SORT              17  ?               2.707E+004   FUNKY_OPT_UNIQUE  1.573E-001   2.006E+002 
17  HASH_PARTIAL_GRO  16  ?               2.707E+004   FUNKY_OPT_UNIQUE  8.069E-002   2.005E+002 
16  ESP_EXCHANGE      15  ?               2.707E+004   FUNKY_OPT_UNIQUE  3.292E-002   2.004E+002 
15  HASH_PARTIAL_GRO  14  ?               2.707E+004   FUNKY_OPT_UNIQUE  7.371E+000   2.004E+002 
14  HASH_GROUPBY      13  ?               1.080E+007   FUNKY_OPT_UNIQUE  3.933E+001   1.930E+002 
13  HYBRID_HASH_ANTI  12  4               1.080E+007   FUNKY_OPT_UNIQUE  7.734E+000   1.537E+002 
12  ESP_EXCHANGE      11  ?               1.152E+007   FUNKY_OPT_UNIQUE  2.314E+001   1.455E+002 
11  HYBRID_HASH_JOIN  10  7               1.152E+007   FUNKY_OPT_UNIQUE  5.688E+001   1.224E+002 
10  SPLIT_TOP         9   ?               8.000E+007   FUNKY_OPT_UNIQUE  4.126E+001   5.158E+001 
09  PARTITION_ACCESS  8   ?               8.000E+007   FUNKY_OPT_UNIQUE  4.126E+001   5.158E+001 
08  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  1.032E+001   1.032E+001 
07  SPLIT_TOP         6   ?               2.881E+006   FUNKY_OPT_UNIQUE  3.623E+000   1.413E+001 
06  PARTITION_ACCESS  5   ?               2.881E+006   FUNKY_OPT_UNIQUE  3.623E+000   1.413E+001 
05  FILE_SCAN         ?   ?   PART        2.881E+006   FUNKY_OPT_UNIQUE  1.050E+001   1.050E+001 
04  ESP_EXCHANGE      3   ?               6.250E+004   FUNKY_OPT_UNIQUE  8.624E-002   6.271E-001 
03  SPLIT_TOP         2   ?               6.250E+004   FUNKY_OPT_UNIQUE  4.569E-002   5.409E-001 
02  PARTITION_ACCESS  1   ?               6.250E+004   FUNKY_OPT_UNIQUE  4.569E-002   5.409E-001 
01  FILE_SCAN         ?   ?   SUPPLIER    6.250E+004   FUNKY_OPT_UNIQUE  4.952E-001   4.952E-001 

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

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

19   .    20   root                                                  2.70E+004
18   .    19   esp_exchange                    1:4(hash2)            2.70E+004
17   .    18   sort                                                  2.70E+004
16   .    17   hash_partial_groupby                                  2.70E+004
15   .    16   esp_exchange                    4(hash2):4(hash2)     2.70E+004
14   .    15   hash_partial_groupby                                  2.70E+004
13   .    14   hash_groupby                                          1.08E+007
12   4    13   hybrid_hash_anti_sem                                  1.08E+007
11   .    12   esp_exchange                    4(hash2):4(range)     1.15E+007
10   7    11   hybrid_hash_join                                      1.15E+007
9    .    10   split_top                       4(range):72(range)    8.00E+007
8    .    9    partition_access                                      8.00E+007
.    .    8    index_scan            fr        PSX2                  8.00E+007
6    .    7    split_top                       4(range):72(range)    2.88E+006
5    .    6    partition_access                                      2.88E+006
.    .    5    file_scan             fr        PART                  2.88E+006
3    .    4    esp_exchange                    4(hash2):4(range)     6.25E+004
2    .    3    split_top                       4(range):72(range)    6.25E+004
1    .    2    partition_access                                      6.25E+004
.    .    1    file_scan             fr        SUPPLIER              6.25E+004

--- SQL operation complete.
>>obey optdml02(q17);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

18  ROOT              17  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   1.654E+002 
17  SORT_PARTIAL_AGG  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.072E-006   1.654E+002 
16  ESP_EXCHANGE      15  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   1.654E+002 
15  SORT_PARTIAL_AGG  14  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.991E-002   1.654E+002 
14  SORT_GROUPBY      13  ?               6.000E+006   FUNKY_OPT_UNIQUE  1.771E+001   1.654E+002 
13  NESTED_JOIN       9   12              1.800E+007   FUNKY_OPT_UNIQUE  1.619E-001   1.476E+002 
12  SPLIT_TOP         11  ?               2.997E+001   FUNKY_OPT_UNIQUE  5.385E+001   1.181E+002 
11  PARTITION_ACCESS  10  ?               2.997E+001   FUNKY_OPT_UNIQUE  5.385E+001   1.181E+002 
10  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  6.431E+001   6.431E+001 
09  SORT              8   ?               6.007E+005   FUNKY_OPT_UNIQUE  3.957E+000   2.935E+001 
08  ESP_EXCHANGE      7   ?               6.007E+005   FUNKY_OPT_UNIQUE  2.302E+000   2.539E+001 
07  NESTED_JOIN       3   6               6.007E+005   FUNKY_OPT_UNIQUE  5.404E-003   2.309E+001 
06  SPLIT_TOP         5   ?               2.997E+001   FUNKY_OPT_UNIQUE  2.001E+000   1.746E+001 
05  PARTITION_ACCESS  4   ?               2.997E+001   FUNKY_OPT_UNIQUE  2.001E+000   1.746E+001 
04  INDEX_SCAN        ?   ?   LINEITEM    2.997E+001   FUNKY_OPT_UNIQUE  1.546E+001   1.546E+001 
03  SPLIT_TOP         2   ?               2.004E+004   FUNKY_OPT_UNIQUE  2.751E-002   5.623E+000 
02  PARTITION_ACCESS  1   ?               2.004E+004   FUNKY_OPT_UNIQUE  2.751E-002   5.623E+000 
01  INDEX_SCAN        ?   ?   PART        2.004E+004   FUNKY_OPT_UNIQUE  5.596E+000   5.596E+000 

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

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

17   .    18   root                                                  1.00E+000
16   .    17   sort_partial_aggr_ro                                  1.00E+000
15   .    16   esp_exchange                    1:4(hash2)            1.00E+000
14   .    15   sort_partial_aggr_le                                  1.00E+000
13   .    14   sort_groupby                                          6.00E+006
9    12   13   nested_join                                           1.80E+007
11   .    12   split_top                       4(rep-n):72(range)    2.99E+001
10   .    11   partition_access                                      2.99E+001
.    .    10   index_scan            fr        LX2                   2.99E+001
8    .    9    sort                                                  6.00E+005
7    .    8    esp_exchange                    4(hash2):4(range)     6.00E+005
3    6    7    nested_join                                           6.00E+005
5    .    6    split_top                       4(rep-n):72(range)    2.99E+001
4    .    5    partition_access                                      2.99E+001
.    .    4    index_scan            fr        LX2                   2.99E+001
2    .    3    split_top                       4(range):72(range)    2.00E+004
1    .    2    partition_access                                      2.00E+004
.    .    1    index_scan            fr        PX1                   2.00E+004

--- SQL operation complete.
>>obey optdml02(q18);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

22  ROOT              21  ?               4.994E+007   FUNKY_OPT_UNIQUE  3.348E+000   2.699E+003 
21  FIRSTN            20  ?               4.994E+007   FUNKY_OPT_UNIQUE  0.000E+000   2.696E+003 
20  ESP_EXCHANGE      19  ?               4.994E+007   FUNKY_OPT_UNIQUE  1.744E-001   2.696E+003 
19  SORT              18  ?               4.994E+007   FUNKY_OPT_UNIQUE  9.458E+002   2.695E+003 
18  HYBRID_HASH_JOIN  17  3               4.994E+007   FUNKY_OPT_UNIQUE  5.101E+001   1.750E+003 
17  ESP_EXCHANGE      16  ?               4.994E+007   FUNKY_OPT_UNIQUE  8.212E+001   1.674E+003 
16  HYBRID_HASH_JOIN  15  11              4.994E+007   FUNKY_OPT_UNIQUE  2.740E+002   1.592E+003 
15  SPLIT_TOP         14  ?               1.498E+008   FUNKY_OPT_UNIQUE  8.860E+001   5.538E+002 
14  PARTITION_ACCESS  13  ?               1.498E+008   FUNKY_OPT_UNIQUE  8.860E+001   5.538E+002 
13  SORT_GROUPBY      12  ?               1.498E+008   FUNKY_OPT_UNIQUE  2.012E+002   4.652E+002 
12  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  2.639E+002   2.639E+002 
11  HYBRID_HASH_JOIN  10  7               4.994E+007   FUNKY_OPT_UNIQUE  1.484E+002   7.652E+002 
10  SPLIT_TOP         9   ?               1.500E+008   FUNKY_OPT_UNIQUE  1.123E+002   1.796E+002 
09  PARTITION_ACCESS  8   ?               1.500E+008   FUNKY_OPT_UNIQUE  1.123E+002   1.796E+002 
08  FILE_SCAN         ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  6.725E+001   6.725E+001 
07  SPLIT_TOP         6   ?               4.994E+007   FUNKY_OPT_UNIQUE  2.166E+001   4.376E+002 
06  PARTITION_ACCESS  5   ?               4.994E+007   FUNKY_OPT_UNIQUE  2.166E+001   4.376E+002 
04  FILE_SCAN         ?   ?   LINEITEM    5.994E+008   FUNKY_OPT_UNIQUE  2.639E+002   2.639E+002 
03  SPLIT_TOP         2   ?               1.500E+007   FUNKY_OPT_UNIQUE  1.449E+001   2.492E+001 
02  PARTITION_ACCESS  1   ?               1.500E+007   FUNKY_OPT_UNIQUE  1.449E+001   2.492E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    1.500E+007   FUNKY_OPT_UNIQUE  1.042E+001   1.042E+001 

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

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

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

--- SQL operation complete.
>>obey optdml02(q19);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

13  ROOT              12  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.218E-008   4.505E+002 
12  SORT_PARTIAL_AGG  11  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   4.505E+002 
11  ESP_EXCHANGE      10  ?               1.000E+000   FUNKY_OPT_UNIQUE  9.122E-004   4.505E+002 
10  SORT_PARTIAL_AGG  9   ?               1.000E+000   FUNKY_OPT_UNIQUE  7.954E-005   4.505E+002 
09  HYBRID_HASH_JOIN  8   4               2.380E+004   FUNKY_OPT_UNIQUE  6.872E+001   4.505E+002 
08  ESP_EXCHANGE      7   ?               4.281E+007   FUNKY_OPT_UNIQUE  6.673E+001   3.258E+002 
07  SPLIT_TOP         6   ?               4.281E+007   FUNKY_OPT_UNIQUE  3.546E+001   2.591E+002 
06  PARTITION_ACCESS  5   ?               4.281E+007   FUNKY_OPT_UNIQUE  3.546E+001   2.591E+002 
05  FILE_SCAN         ?   ?   LINEITEM    4.281E+007   FUNKY_OPT_UNIQUE  2.236E+002   2.236E+002 
04  ESP_EXCHANGE      3   ?               2.000E+007   FUNKY_OPT_UNIQUE  3.288E+001   5.669E+001 
03  SPLIT_TOP         2   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.827E+001   2.381E+001 
02  PARTITION_ACCESS  1   ?               2.000E+007   FUNKY_OPT_UNIQUE  1.827E+001   2.381E+001 
01  INDEX_SCAN        ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  5.532E+000   5.532E+000 

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

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

12   .    13   root                                                  1.00E+000
11   .    12   sort_partial_aggr_ro                                  1.00E+000
10   .    11   esp_exchange                    1:4(hash2)            1.00E+000
9    .    10   sort_partial_aggr_le                                  1.00E+000
8    4    9    hybrid_hash_join                                      2.38E+004
7    .    8    esp_exchange                    4(hash2):4(range)     4.28E+007
6    .    7    split_top                       4(range):64(range)    4.28E+007
5    .    6    partition_access                                      4.28E+007
.    .    5    file_scan             fr        LINEITEM              4.28E+007
3    .    4    esp_exchange                    4(hash2):4(range)     2.00E+007
2    .    3    split_top                       4(range):72(range)    2.00E+007
1    .    2    partition_access                                      2.00E+007
.    .    1    index_scan            fr        PX1                   2.00E+007

--- SQL operation complete.
>>obey optdml02(q20);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

26  ROOT              25  ?               7.649E+004   FUNKY_OPT_UNIQUE  6.246E-003   3.725E+002 
25  ESP_EXCHANGE      24  ?               7.649E+004   FUNKY_OPT_UNIQUE  1.744E-001   3.725E+002 
24  SORT              23  ?               7.649E+004   FUNKY_OPT_UNIQUE  2.115E-001   3.723E+002 
23  HYBRID_HASH_SEMI  22  15              7.649E+004   FUNKY_OPT_UNIQUE  1.706E+000   3.721E+002 
22  ESP_EXCHANGE      21  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.543E-001   9.862E-001 
21  NESTED_JOIN       17  20              4.028E+004   FUNKY_OPT_UNIQUE  1.403E-003   7.318E-001 
20  SPLIT_TOP         19  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.108E-001   7.260E-001 
19  PARTITION_ACCESS  18  ?               4.028E+004   FUNKY_OPT_UNIQUE  2.108E-001   7.260E-001 
18  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  5.152E-001   5.152E-001 
17  PARTITION_ACCESS  16  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.439E-003 
16  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  4.002E-003   4.002E-003 
15  HYBRID_HASH_JOIN  14  11              1.898E+006   FUNKY_OPT_UNIQUE  1.185E+002   3.694E+002 
14  SPLIT_TOP         13  ?               8.000E+007   FUNKY_OPT_UNIQUE  4.761E+001   5.985E+001 
13  PARTITION_ACCESS  12  ?               8.000E+007   FUNKY_OPT_UNIQUE  4.761E+001   5.985E+001 
12  INDEX_SCAN        ?   ?   PARTSUPP    8.000E+007   FUNKY_OPT_UNIQUE  1.223E+001   1.223E+001 
11  HASH_PARTIAL_GRO  10  ?               5.696E+006   FUNKY_OPT_UNIQUE  9.024E+000   1.912E+002 
10  ESP_EXCHANGE      9   ?               5.696E+006   FUNKY_OPT_UNIQUE  2.038E+000   1.822E+002 
09  HASH_PARTIAL_GRO  8   ?               5.696E+006   FUNKY_OPT_UNIQUE  3.519E+000   1.802E+002 
08  HYBRID_HASH_JOIN  7   4               5.696E+006   FUNKY_OPT_UNIQUE  6.563E+001   1.767E+002 
07  SPLIT_TOP         6   ?               9.114E+007   FUNKY_OPT_UNIQUE  6.145E+001   9.948E+001 
06  PARTITION_ACCESS  5   ?               9.114E+007   FUNKY_OPT_UNIQUE  6.145E+001   9.948E+001 
05  INDEX_SCAN        ?   ?   LINEITEM    9.114E+007   FUNKY_OPT_UNIQUE  3.803E+001   3.803E+001 
04  ESP_EXCHANGE      3   ?               1.250E+006   FUNKY_OPT_UNIQUE  2.353E+000   1.181E+001 
03  SPLIT_TOP         2   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.459E+000 
02  PARTITION_ACCESS  1   ?               1.250E+006   FUNKY_OPT_UNIQUE  5.649E-001   9.459E+000 
01  FILE_SCAN         ?   ?   PART        1.250E+006   FUNKY_OPT_UNIQUE  8.894E+000   8.894E+000 

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

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

25   .    26   root                                                  7.64E+004
24   .    25   esp_exchange                    1:4(range)            7.64E+004
23   .    24   sort                                                  7.64E+004
22   15   23   hybrid_hash_semi_joi                                  7.64E+004
21   .    22   esp_exchange                    4(range):1            4.02E+004
17   20   21   nested_join                                           4.02E+004
19   .    20   split_top                       1:72(range)           4.02E+004
18   .    19   partition_access                                      4.02E+004
.    .    18   file_scan             fr        SUPPLIER              4.02E+004
16   .    17   partition_access                                      1.00E+000
.    .    16   file_scan             fr        NATION                1.00E+000
14   11   15   hybrid_hash_join                                      1.89E+006
13   .    14   split_top                       4(range):72(range)    8.00E+007
12   .    13   partition_access                                      8.00E+007
.    .    12   index_scan            fr        PSX1                  8.00E+007
10   .    11   hash_partial_groupby                                  5.69E+006
9    .    10   esp_exchange                    4(range):4(range)     5.69E+006
8    .    9    hash_partial_groupby                                  5.69E+006
7    4    8    hybrid_hash_join                                      5.69E+006
6    .    7    split_top                       4(range):72(range)    9.11E+007
5    .    6    partition_access                                      9.11E+007
.    .    5    index_scan            fr        LX3 (m)               9.11E+007
3    .    4    esp_exchange                    4(rep-b):4(range)     1.25E+006
2    .    3    split_top                       4(range):72(range)    1.25E+006
1    .    2    partition_access                                      1.25E+006
.    .    1    file_scan             fr        PART                  1.25E+006

--- SQL operation complete.
>>obey optdml02(q21);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

35  ROOT              34  ?               1.000E+000   FUNKY_OPT_UNIQUE  4.022E-008   2.527E+003 
34  FIRSTN            33  ?               1.000E+000   FUNKY_OPT_UNIQUE  0.000E+000   2.527E+003 
33  ESP_EXCHANGE      32  ?               1.000E+000   FUNKY_OPT_UNIQUE  3.648E-003   2.527E+003 
32  SORT              31  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.741E-002   2.527E+003 
31  HASH_PARTIAL_GRO  30  ?               1.000E+000   FUNKY_OPT_UNIQUE  5.794E-006   2.527E+003 
30  ESP_EXCHANGE      29  ?               1.000E+000   FUNKY_OPT_UNIQUE  6.584E-004   2.527E+003 
29  HASH_PARTIAL_GRO  28  ?               1.000E+000   FUNKY_OPT_UNIQUE  1.448E-006   2.527E+003 
28  HYBRID_HASH_JOIN  27  24              1.000E+000   FUNKY_OPT_UNIQUE  1.332E+001   2.527E+003 
27  SPLIT_TOP         26  ?               2.000E+007   FUNKY_OPT_UNIQUE  8.743E+000   1.949E+001 
26  PARTITION_ACCESS  25  ?               2.000E+007   FUNKY_OPT_UNIQUE  8.743E+000   1.949E+001 
25  FILE_SCAN         ?   ?   PART        2.000E+007   FUNKY_OPT_UNIQUE  1.074E+001   1.074E+001 
24  ESP_EXCHANGE      23  ?               1.000E+000   FUNKY_OPT_UNIQUE  2.631E-003   2.494E+003 
23  HYBRID_HASH_ANTI  22  3               1.000E+000   FUNKY_OPT_UNIQUE  2.725E+002   2.494E+003 
22  HYBRID_HASH_JOIN  21  18              3.925E+006   FUNKY_OPT_UNIQUE  5.622E+001   1.879E+003 
21  SPLIT_TOP         20  ?               7.307E+007   FUNKY_OPT_UNIQUE  3.168E+001   9.093E+001 
20  PARTITION_ACCESS  19  ?               7.307E+007   FUNKY_OPT_UNIQUE  3.168E+001   9.093E+001 
19  FILE_SCAN         ?   ?   ORDERS      7.307E+007   FUNKY_OPT_UNIQUE  5.924E+001   5.924E+001 
18  MERGE_SEMI_JOIN   15  17              8.049E+006   FUNKY_OPT_UNIQUE  1.640E+002   1.732E+003 
17  PARTITION_ACCESS  16  ?               5.994E+008   FUNKY_OPT_UNIQUE  3.070E+002   9.553E+002 
16  FILE_SCAN         ?   ?   LINEITEM)   5.994E+008   FUNKY_OPT_UNIQUE  6.482E+002   6.482E+002 
15  SORT              14  ?               8.049E+006   FUNKY_OPT_UNIQUE  7.707E+001   6.125E+002 
14  HYBRID_HASH_JOIN  13  10              8.049E+006   FUNKY_OPT_UNIQUE  1.339E+002   5.355E+002 
13  SPLIT_TOP         12  ?               1.997E+008   FUNKY_OPT_UNIQUE  1.181E+002   4.008E+002 
12  PARTITION_ACCESS  11  ?               1.997E+008   FUNKY_OPT_UNIQUE  1.181E+002   4.008E+002 
11  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  2.826E+002   2.826E+002 
10  ESP_EXCHANGE      9   ?               4.028E+004   FUNKY_OPT_UNIQUE  5.770E-001   1.261E+000 
09  NESTED_JOIN       5   8               4.028E+004   FUNKY_OPT_UNIQUE  1.403E-003   6.841E-001 
08  SPLIT_TOP         7   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.630E-001   6.782E-001 
07  PARTITION_ACCESS  6   ?               4.028E+004   FUNKY_OPT_UNIQUE  1.630E-001   6.782E-001 
06  FILE_SCAN         ?   ?   SUPPLIER    4.028E+004   FUNKY_OPT_UNIQUE  5.152E-001   5.152E-001 
05  PARTITION_ACCESS  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.368E-004   4.439E-003 
04  FILE_SCAN         ?   ?   NATION      1.000E+000   FUNKY_OPT_UNIQUE  4.002E-003   4.002E-003 
03  SPLIT_TOP         2   ?               1.997E+008   FUNKY_OPT_UNIQUE  1.023E+002   3.432E+002 
02  PARTITION_ACCESS  1   ?               1.997E+008   FUNKY_OPT_UNIQUE  1.023E+002   3.432E+002 
01  FILE_SCAN         ?   ?   LINEITEM)   1.997E+008   FUNKY_OPT_UNIQUE  2.409E+002   2.409E+002 

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

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

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

--- SQL operation complete.
>>obey optdml02(q22);
>>--------------------
>>--  TPCD 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
--  ----------------  --  --  ----------  -----------  ----------------  -----------  -----------

19  ROOT              18  ?               4.960E+006   FUNKY_OPT_UNIQUE  1.209E-001   4.375E+002 
18  ESP_EXCHANGE      17  ?               4.960E+006   FUNKY_OPT_UNIQUE  1.744E-001   4.374E+002 
17  SORT              16  ?               4.960E+006   FUNKY_OPT_UNIQUE  4.216E+001   4.372E+002 
16  HASH_GROUPBY      15  ?               4.960E+006   FUNKY_OPT_UNIQUE  5.268E+001   3.950E+002 
15  ESP_EXCHANGE      14  ?               7.440E+007   FUNKY_OPT_UNIQUE  9.691E+001   3.423E+002 
14  MERGE_ANTI_SEMI_  11  13              7.440E+007   FUNKY_OPT_UNIQUE  7.591E+001   2.454E+002 
13  PARTITION_ACCESS  12  ?               1.500E+008   FUNKY_OPT_UNIQUE  6.503E+001   9.039E+001 
12  INDEX_SCAN        ?   ?   ORDERS      1.500E+008   FUNKY_OPT_UNIQUE  2.536E+001   2.536E+001 
11  SORT              10  ?               4.960E+006   FUNKY_OPT_UNIQUE  4.117E+001   7.916E+001 
10  HYBRID_HASH_JOIN  9   6               4.960E+006   FUNKY_OPT_UNIQUE  6.549E+000   3.798E+001 
09  SPLIT_TOP         8   ?               1.488E+007   FUNKY_OPT_UNIQUE  9.983E+000   2.039E+001 
08  PARTITION_ACCESS  7   ?               1.488E+007   FUNKY_OPT_UNIQUE  9.983E+000   2.039E+001 
07  FILE_SCAN         ?   ?   CUSTOMER    1.488E+007   FUNKY_OPT_UNIQUE  1.040E+001   1.040E+001 
06  ESP_EXCHANGE      5   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.440E-003   1.109E+001 
05  SORT_PARTIAL_AGG  4   ?               1.000E+000   FUNKY_OPT_UNIQUE  2.082E-006   1.109E+001 
04  SPLIT_TOP         3   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.473E-002   1.109E+001 
03  PARTITION_ACCESS  2   ?               1.000E+000   FUNKY_OPT_UNIQUE  4.473E-002   1.109E+001 
02  SORT_PARTIAL_AGG  1   ?               1.000E+000   FUNKY_OPT_UNIQUE  3.593E-001   1.104E+001 
01  FILE_SCAN         ?   ?   CUSTOMER    1.352E+007   FUNKY_OPT_UNIQUE  1.068E+001   1.068E+001 

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

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

18   .    19   root                                                  4.96E+006
17   .    18   esp_exchange                    1:4(hash2)            4.96E+006
16   .    17   sort                                                  4.96E+006
15   .    16   hash_groupby                                          4.96E+006
14   .    15   esp_exchange                    4(hash2):4(range)     7.44E+007
11   13   14   merge_anti_semi_join                                  7.44E+007
12   .    13   partition_access                                      1.50E+008
.    .    12   index_scan            fr        OX2                   1.50E+008
10   .    11   sort                                                  4.96E+006
9    6    10   hybrid_hash_join                                      4.96E+006
8    .    9    split_top                       4(range):64(range)    1.48E+007
7    .    8    partition_access                                      1.48E+007
.    .    7    file_scan             fr        CUSTOMER              1.48E+007
5    .    6    esp_exchange                    4(rep-b):1            1.00E+000
4    .    5    sort_partial_aggr_ro                                  1.00E+000
3    .    4    split_top                       1:64(range)           1.00E+000
2    .    3    partition_access                                      1.00E+000
1    .    2    sort_partial_aggr_le                                  1.00E+000
.    .    1    file_scan                       CUSTOMER              1.35E+007

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