TPC-H

$cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

$uname -a
Linux testdriver 3.10.0-1160.24.1.el7.x86_64 #1 SMP Thu Apr 8 19:51:47 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

$cat /proc/cpuinfo
processor : 31
vendor_id : GenuineIntel
cpu family : 6
model  : 85
model name : Intel(R) Xeon(R) Platinum 8269CY CPU @ 2.50GHz
Stepping : 7
microcode : 0x1
cpu MHz  : 2500.002
cache size : 36608 KB
physical id : 0
siblings : 32
core id  : 15
cpu cores : 16
apicid  : 31
initial apicid : 31
fpu  : yes
fpu_exception : yes
cpuid level : 22
wp  : yes
flags  : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl nonstop_tsc eagerfpu pni pclmulqdq monitor ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat avx512_vnni
bogomips : 5000.00
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:

$free -g
             total        used        free      shared  buff/cache   available
Mem:            123          41           2           0          79          81
Swap:             0           0           0

$./observer -V
observer (OceanBase Database V3.2.3)

REVISION: 20220125174436-b765b7557c36d62bcd42597142fe0ee34bd21f6e
BUILD_BRANCH: 3_2_x_release
BUILD_TIME: Jan 25 2022 17:56:14
BUILD_FLAGS: RelWithDebInfo
BUILD_INFO:

Copyright (c) 2011-2020 Alipay Inc.

ALTER SYSTEM SET enable_merge_by_turn= False;
ALTER SYSTEM SET trace_log_slow_query_watermark='100s';
ALTER SYSTEM SET max_kept_major_version_number=1;
ALTER SYSTEM SET enable_sql_operator_dump=True;
ALTER SYSTEM SET _hash_area_size='3g' tenant='tpch_oracle';
ALTER SYSTEM SET memstore_limit_percentage=50;
ALTER SYSTEM SET enable_clog_persistence_compress = true;
ALTER SYSTEM SET _bloom_filter_ratio=10;
ALTER SYSTEM SET clog_persistence_compress_func = "zstd_1.0";
ALTER SYSTEM SET minor_freeze_times=5;
ALTER SYSTEM SET cache_wash_threshold='30g';
ALTER SYSTEM SET _pushdown_storage_level=3 tenant all;
ALTER SYSTEM SET _rowsets_enabled=true tenant all;
ALTER SYSTEM SET _parallel_server_sleep_time=10;
ALTER SYSTEM SET merge_thread_count=64;
ALTER SYSTEM SET cpu_quota_concurrency=4;
ALTER SYSTEM SET server_data_copy_out_concurrency=1000;
ALTER SYSTEM SET server_data_copy_in_concurrency=1000;
ALTER SYSTEM SET memory_chunk_cache_size =0;
ALTER SYSTEM SET minor_freeze_times=200;
ALTER SYSTEM SET clog_transport_compress_all=false;
ALTER SYSTEM SET trx_try_wait_lock_timeout='0ms';
ALTER SYSTEM SET large_query_threshold='1s';
ALTER SYSTEM SET syslog_io_bandwidth_limit='30m';
ALTER SYSTEM SET enable_async_syslog=true;
ALTER SYSTEM SET merger_warm_up_duration_time='0';
ALTER SYSTEM SET merger_switch_leader_duration_time='0';
ALTER SYSTEM SET large_query_worker_percentage=10;
ALTER SYSTEM SET minor_compact_trigger = 2;
ALTER SYSTEM SET builtin_db_data_verify_cycle = 0;
ALTER SYSTEM SET micro_block_merge_verify_level=0;
ALTER SYSTEM SET freeze_trigger_percentage=50;
ALTER SYSTEM SET sys_bkgd_io_low_percentage=70;
ALTER SYSTEM SET _mini_merge_concurrency = 5;
ALTER SYSTEM SET enable_sql_audit=false;
ALTER SYSTEM SET enable_perf_event=false;
ALTER SYSTEM SET syslog_level='PERF';
ALTER SYSTEM SET max_syslog_file_count=100;
ALTER SYSTEM SET enable_syslog_recycle='True';
SET GLOBAL _groupby_nopushdown_cut_ratio=1;
SET GLOBAL _nlj_batching_enabled=true;
SET GLOBAL ob_sql_work_area_percentage=80;
SET GLOBAL optimizer_use_sql_plan_baselines = true;
SET GLOBAL optimizer_capture_sql_plan_baselines = true;
SET GLOBAL ob_query_timeout=36000000000;
SET GLOBAL ob_trx_timeout=36000000000;
SET GLOBAL max_allowed_packet=67108864;
SET GLOBAL secure_file_priv="";
SET GLOBAL parallel_servers_target=624;

ALTER SYSTEM SET ob_enable_batched_multi_statement='false';
SET GLOBAL NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SET GLOBAL NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
SET GLOBAL NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
SET GLOBAL ob_query_timeout=14400000000;
SET GLOBAL ob_trx_timeout=10000000000;
SET GLOBAL secure_file_priv="";
SET GLOBAL autocommit=1;
SET GLOBAL optimizer_use_sql_plan_baselines = true;
SET GLOBAL optimizer_capture_sql_plan_baselines = true;
SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
SET ob_query_timeout=7200000000;
SET ob_trx_timeout=10000000000;

CREATE TABLEGROUP tpch_tg_SF_TPC_USER_lineitem_order_group BINDING true PARTITION BY HASH PARTITIONS 96;
CREATE TABLEGROUP tpch_tg_SF_TPC_USER_partsupp_part BINDING true PARTITION BY HASH PARTITIONS 96;

delimiter //;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='LINEITEM';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE lineitem (
    l_orderkey number NOT NULL,
    l_partkey number NOT NULL,
    l_suppkey number NOT NULL,
    l_linenumber number NOT NULL,
    l_quantity number NOT NULL,
    l_extendedprice number NOT NULL,
    l_discount number NOT NULL,
    l_tax number NOT NULL,
    l_returnflag char(1) DEFAULT NULL,
    l_linestatus char(1) DEFAULT NULL,
    l_shipdate date NOT NULL,
    l_commitdate date DEFAULT NULL,
    l_receiptdate date DEFAULT NULL,
    l_shipinstruct char(25) DEFAULT NULL,
    l_shipmode char(10) DEFAULT NULL,
    l_comment varchar(44) DEFAULT NULL,
primary key(l_orderkey, l_linenumber)
)   COMPRESS FOR QUERY LOW
    tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
    partition by hash (l_orderkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='ORDERS';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE orders (
    o_orderkey number NOT NULL,
    o_custkey number NOT NULL,
    o_orderstatus char(1) DEFAULT NULL,
    o_totalprice number DEFAULT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority char(15) DEFAULT NULL,
    o_clerk char(15) DEFAULT NULL,
    o_shippriority number DEFAULT NULL,
    o_comment varchar(79) DEFAULT NULL,
PRIMARY KEY (o_orderkey)
)   COMPRESS FOR QUERY LOW
    tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
    partition by hash(o_orderkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='PARTSUPP';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE partsupp (
    ps_partkey number NOT NULL,
    ps_suppkey number NOT NULL,
    ps_availqty number DEFAULT NULL,
    ps_supplycost number DEFAULT NULL,
    ps_comment varchar(199) DEFAULT NULL,
    PRIMARY KEY (ps_partkey, ps_suppkey))
    COMPRESS FOR QUERY LOW
    tablegroup tpch_tg_SF_TPC_USER_partsupp_part
    partition by hash(ps_partkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='PART';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE part (
  p_partkey number NOT NULL,
  p_name varchar(55) DEFAULT NULL,
  p_mfgr char(25) DEFAULT NULL,
  p_brand char(10) DEFAULT NULL,
  p_type varchar(25) DEFAULT NULL,
  p_size number DEFAULT NULL,
  p_container char(10) DEFAULT NULL,
  p_retailprice number DEFAULT NULL,
  p_comment varchar(23) DEFAULT NULL,
  PRIMARY KEY (p_partkey))
  COMPRESS FOR QUERY LOW
  tablegroup tpch_tg_SF_TPC_USER_partsupp_part
  partition by hash(p_partkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='CUSTOMER';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE customer (
  c_custkey number NOT NULL,
  c_name varchar(25) DEFAULT NULL,
  c_address varchar(40) DEFAULT NULL,
  c_nationkey number DEFAULT NULL,
  c_phone char(15) DEFAULT NULL,
  c_acctbal number DEFAULT NULL,
  c_mktsegment char(10) DEFAULT NULL,
  c_comment varchar(117) DEFAULT NULL,
  PRIMARY KEY (c_custkey))
  COMPRESS FOR QUERY LOW
  partition by hash(c_custkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='SUPPLIER';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE supplier (
  s_suppkey number NOT NULL,
  s_name char(25) DEFAULT NULL,
  s_address varchar(40) DEFAULT NULL,
  s_nationkey number DEFAULT NULL,
  s_phone char(15) DEFAULT NULL,
  s_acctbal number DEFAULT NULL,
  s_comment varchar(101) DEFAULT NULL,
  PRIMARY KEY (s_suppkey)
) COMPRESS FOR QUERY LOW
partition by hash(s_suppkey) partitions 96';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='NATION';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE nation (
  n_nationkey number NOT NULL,
  n_name char(25) DEFAULT NULL,
  n_regionkey number DEFAULT NULL,
  n_comment varchar(152) DEFAULT NULL,
  PRIMARY KEY (n_nationkey)) COMPRESS FOR QUERY LOW';
  END IF;
END//;
DECLARE v1 INT;
BEGIN
  select count(*) into v1 from all_tables where table_name='REGION';
  IF (v1 = 0) then
    execute immediate 'CREATE TABLE region (
  r_regionkey number NOT NULL,
  r_name char(25) DEFAULT NULL,
  r_comment varchar(152) DEFAULT NULL,
  PRIMARY KEY (r_regionkey)) COMPRESS FOR QUERY LOW';
  END IF;
END//;

select /*+   TPCH_Q1 PARALLEL(96) */
 l_returnflag,
 l_linestatus,
 sum(l_quantity) as sum_qty,
 sum(l_extendedprice) as sum_base_price,
 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
 avg(l_quantity) as avg_qty,
 avg(l_extendedprice) as avg_price,
 avg(l_discount) 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;

SELECT * FROM (SELECT /*+   TPCH_Q2 PARALLEL(96)  */  
     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 = 30 and
      p_type like '%STEEL' and
      s_nationkey = n_nationkey and
      n_regionkey = r_regionkey and
      r_name = 'ASIA' 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 = 'ASIA' )
order by
  s_acctbal desc,
  n_name,
  s_name,
  p_partkey)
WHERE rownum <= 100;

SELECT * FROM (
 SELECT /*+   TPCH_Q3 PARALLEL(96) */ *
 from (
   SELECT
     l_orderkey,
              o_orderdate,
              o_shippriority,
              sum(l_extendedprice * (1 - l_discount)) as revenue
        from
       customer,
     orders,
     lineitem
        where
       c_mktsegment = 'BUILDING' and
              c_custkey = o_custkey and
              l_orderkey = o_orderkey and
              o_orderdate <  '1995-03-15' and
              l_shipdate >  '1995-03-15'
        group by
       l_orderkey,
     o_orderdate,
     o_shippriority
        order by
       revenue desc,
     o_orderdate)
)
WHERE ROWNUM <= 10;

SELECT  /*+   TPCH_Q4 PARALLEL(cpu_num)  no_unnest */
            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;

SELECT  /*+   TPCH_Q5 parallel(256)  */ N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION
WHERE C_CUSTKEY = O_CUSTKEY AND
      L_ORDERKEY = O_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;

SELECT /*+   TPCH_Q6 parallel(256) */ sum(l_extendedprice * l_discount) as revenue
from lineitem
where l_shipdate >= DATE'1994-01-01' and
      l_shipdate < DATE'1994-01-01' + interval  '12' month and
      l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24;

SELECT /*+   TPCH_Q7 parallel(256) */  supp_nation, cust_nation, l_year, sum(volume) as revenue
from ( SELECT n1.n_name as supp_nation,
              n2.n_name as cust_nation,
              to_number (to_char(l_shipdate, 'yyyy')) as l_year,
              l_extendedprice * (1 - l_discount) 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  '1995-01-01' and '1996-12-31' ) shipping
group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year;

SELECT  /*+   TPCH_Q8 parallel(256)  */  o_year, sum(case when nation='BRAZIL' then volume else 0 end )/ sum(volume) as mkt_share
from ( SELECT TO_CHAR(o_orderdate,'YYYY') as o_year,
              l_extendedprice * (1 - l_discount) 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 '1995-01-01' and '1996-12-31' and
             p_type = 'ECONOMY ANODIZED STEEL' ) all_nations
 group by o_year
 order by o_year;

SELECT  /*+   TPCH_Q9 parallel(256) */ NATION, O_YEAR, SUM(AMOUNT) AS SUM_PROFIT
FROM (SELECT N_NAME AS NATION,
             TO_CHAR(O_ORDERDATE, 'YYYY') AS O_YEAR,
             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
            S_NATIONKEY = N_NATIONKEY AND
            P_NAME LIKE '%%green%%') PROFIT
GROUP BY NATION, O_YEAR ORDER BY NATION, O_YEAR DESC;

SELECT * FROM (SELECT /*+   TPCH_Q10 parallel(256) */ *
from  ( SELECT c_custkey,
               c_name,
               sum(l_extendedprice * (1 - l_discount)) 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)
) WHERE ROWNUM <=20;

SELECT /*+   TPCH_Q11 parallel(256) */ ps_partkey,
       Sum(ps_supplycost * ps_availqty) AS value
FROM   partsupp,
       supplier,
       nation
WHERE  ps_suppkey = s_suppkey
       AND s_nationkey = n_nationkey
       AND n_name = 'MOZAMBIQUE'
GROUP  BY ps_partkey
HAVING Sum(ps_supplycost * ps_availqty) > (SELECT
       Sum(ps_supplycost * ps_availqty) * 0.0001000000
                                           FROM   partsupp,
                                                  supplier,
                                                  nation
                                           WHERE  ps_suppkey = s_suppkey
                                                  AND s_nationkey = n_nationkey
                                                  AND n_name = 'MOZAMBIQUE')
ORDER  BY value DESC;

SELECT /*+   TPCH_Q12 parallel(256)  */ 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'1995-09-01' + interval '1' year
 GROUP BY L_SHIPMODE
 ORDER BY L_SHIPMODE;

SELECT  /*+   TPCH_Q13 parallel(256) */ c_count, count(*) as custdist
from ( SELECT c_custkey, count(o_orderkey)  as  c_count
       from customer left join orders on c_custkey = o_custkey and o_comment not like '%special%requests%'
       group by c_custkey ) c_orders
group by c_count
order by custdist desc, c_count desc;

SELECT  /*+   TPCH_Q14 parallel(256) */ 100.00 * sum(case when 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;

CREATE VIEW revenue0
(supplier_no, total_revenue)
AS
  SELECT /*+ parallel(256) */  l_suppkey,
         SUM(l_extendedprice * ( 1 - l_discount ))
  FROM   lineitem
  WHERE  l_shipdate >= DATE '1997-07-01'
         AND l_shipdate < DATE '1997-07-01' + interval '3' month
  GROUP  BY l_suppkey;

SELECT /*+   TPCH_Q15 parallel(256) */ s_suppkey,
       s_name,
       s_address,
       s_phone,
       total_revenue
FROM   supplier,
       revenue0
WHERE  s_suppkey = supplier_no
       AND total_revenue = (SELECT Max(total_revenue)
                            FROM   revenue0)
ORDER  BY s_suppkey;

drop view revenue0;

SELECT /*+   TPCH_Q16 parallel(256) */  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 '%customer%complaints%' )
group by p_brand, p_type, p_size
order by supplier_cnt desc, p_brand, p_type, p_size;

SELECT  /*+   TPCH_Q17 parallel(256) */ Sum(l_extendedprice) / 7.0 AS avg_yearly
FROM   lineitem,
       part
WHERE  p_partkey = l_partkey
       AND p_brand = 'Brand#44'
       AND p_container = 'WRAP PKG'
       AND l_quantity < (SELECT 0.2 * Avg(l_quantity)
                         FROM   lineitem
                         WHERE  l_partkey = p_partkey);

SELECT * FROM (
SELECT /*+   TPCH_Q18 parallel(256) */ 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) > 314 ) 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
 ) WHERE ROWNUM <=100;

SELECT /*+   TPCH_Q19 parallel(256) */ Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#12'
         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#23'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             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#34'
             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' );

SELECT /*+   TPCH_Q20 parallel(256) */ 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 'green%')
                            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
                                                          '1993-01-01'
                                                      AND l_shipdate < DATE
                                                          '1993-01-01' + interval '1' year
                                              ))
       AND s_nationkey = n_nationkey
       AND n_name = 'ALGERIA'
ORDER  BY s_name;

SELECT * FROM (
SELECT /*+   TPCH_Q21 parallel(256) */ *
FROM   (SELECT s_name,
               Count(*) numwait
        FROM   supplier,
               lineitem l1,
               orders,
               nation
        WHERE  s_suppkey = l1.l_suppkey
               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) x
) WHERE ROWNUM <= 100;

SELECT  /*+   TPCH_Q22 parallel(256) */ cntrycode,
       Count(*)       AS numcust,
       Sum(c_acctbal) AS totacctbal
FROM   (SELECT Substr(c_phone, 1, 2) AS cntrycode,
               c_acctbal
        FROM   customer
        WHERE  Substr(c_phone, 1, 2) IN ( '13', '31', '23', '29',
                                          '30', '18', '17' )
               AND c_acctbal > (SELECT Avg(c_acctbal)
                                FROM   customer
                                WHERE  c_acctbal > 0.00
                                       AND Substr(c_phone, 1, 2) IN (
                                           '13', '31', '23', '29',
                                           '30', '18', '17' ))
               AND NOT EXISTS (SELECT *
                               FROM   orders
                               WHERE  o_custkey = c_custkey)) custsale
GROUP  BY cntrycode
ORDER  BY cntrycode;