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 3.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_mysql';
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 ob_query_timeout=14400000000;
SET GLOBAL ob_trx_timeout=10000000000;
SET ob_query_timeout=14400000000;
SET 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;

CREATE TABLEGROUP IF NOT EXISTS tpch_tg_SF_TPC_USER_lineitem_order_group BINDING true PARTITION BY KEY 1 PARTITIONS 96;
CREATE TABLEGROUP IF NOT EXISTS tpch_tg_SF_TPC_USER_partsupp_part BINDING true PARTITION BY KEY 1 PARTITIONS 96;

DROP TABLE IF EXISTS LINEITEM;
CREATE TABLE lineitem (
    l_orderkey BIGINT NOT NULL,
    l_partkey BIGINT NOT NULL,
    l_suppkey INTEGER NOT NULL,
    l_linenumber INTEGER NOT NULL,
    l_quantity NUMBER(12,2) NOT NULL,
    l_extendedprice NUMBER(12,2) NOT NULL,
    l_discount NUMBER(12,2) NOT NULL,
    l_tax NUMBER(12,2) 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)
)row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
PARTITION BY KEY(l_orderkey) PARTITIONS 96;

DROP TABLE IF EXISTS ORDERS;
CREATE TABLE orders (
    o_orderkey BIGINT NOT NULL,
    o_custkey BIGINT NOT NULL,
    o_orderstatus char(1) DEFAULT NULL,
    o_totalprice NUMBER(12,2) DEFAULT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority char(15) DEFAULT NULL,
    o_clerk char(15) DEFAULT NULL,
    o_shippriority INTEGER DEFAULT NULL,
    o_comment varchar(79) DEFAULT NULL,
PRIMARY KEY (o_orderkey)
)row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
PARTITION BY KEY(o_orderkey) PARTITIONS 96;

DROP TABLE IF EXISTS PARTSUPP;
CREATE TABLE partsupp (
    ps_partkey BIGINT NOT NULL,
    ps_suppkey INTEGER NOT NULL,
    ps_availqty INTEGER DEFAULT NULL,
    ps_supplycost NUMBER(12,2) DEFAULT NULL,
    ps_comment varchar(199) DEFAULT NULL,
    PRIMARY KEY (ps_partkey, ps_suppkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part
PARTITION BY KEY(ps_partkey) PARTITIONS 96;

DROP TABLE IF EXISTS PART;
CREATE TABLE part (
  p_partkey BIGINT 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 INTEGER DEFAULT NULL,
  p_container char(10) DEFAULT NULL,
  p_retailprice NUMBER(12,2) DEFAULT NULL,
  p_comment varchar(23) DEFAULT NULL,
  PRIMARY KEY (p_partkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part
PARTITION BY KEY(p_partkey) PARTITIONS 96;

DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE customer (
  c_custkey BIGINT NOT NULL,
  c_name varchar(25) DEFAULT NULL,
  c_address varchar(40) DEFAULT NULL,
  c_nationkey INTEGER DEFAULT NULL,
  c_phone char(15) DEFAULT NULL,
  c_acctbal NUMBER(12,2) DEFAULT NULL,
  c_mktsegment char(10) DEFAULT NULL,
  c_comment varchar(117) DEFAULT NULL,
  PRIMARY KEY (c_custkey)) row_format = condensed
PARTITION BY KEY(c_custkey) PARTITIONS 96;

DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE supplier (
  s_suppkey INTEGER NOT NULL,
  s_name char(25) DEFAULT NULL,
  s_address varchar(40) DEFAULT NULL,
  s_nationkey INTEGER DEFAULT NULL,
  s_phone char(15) DEFAULT NULL,
  s_acctbal NUMBER(12,2) DEFAULT NULL,
  s_comment varchar(101) DEFAULT NULL,
  PRIMARY KEY (s_suppkey)
) row_format = condensed  PARTITION BY KEY(s_suppkey) PARTITIONS 96;

DROP TABLE IF EXISTS NATION;
CREATE TABLE nation (
  n_nationkey INTEGER NOT NULL,
  n_name char(25) DEFAULT NULL,
  n_regionkey INTEGER DEFAULT NULL,
  n_comment varchar(152) DEFAULT NULL,
  PRIMARY KEY (n_nationkey)
) row_format = condensed;

DROP TABLE IF EXISTS REGION;
CREATE TABLE region (
  r_regionkey INTEGER NOT NULL,
  r_name char(25) DEFAULT NULL,
  r_comment varchar(152) DEFAULT NULL,
  PRIMARY KEY (r_regionkey)
) row_format = condensed;

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 /*+ TPCH_Q2  PARALLEL(96)  */ * from(
select
        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 = 45
        and p_type like '%STEEL'
        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)
limit 100;

select /*+ TPCH_Q3   PARALLEL(96) */
 l_orderkey,
 sum(l_extendedprice * (1 - l_discount)) 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
limit 10;

SELECT  /*+   TPCH_Q4 PARALLEL(96)   */ 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_Q4 parallel(256)  */ 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(96) */
        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 c_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'EUROPE'
        and o_orderdate >= date '1995-01-01'
        and o_orderdate < date '1995-01-01' + interval '1' year
group by
        n_name
order by
        revenue desc;

select /*+   TPCH_Q6 PARALLEL(96) */
 sum(l_extendedprice * l_discount) 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;

select /*+   TPCH_Q7 PARALLEL(96) */
 supp_nation,
 cust_nation,
 l_year,
 sum(volume) as revenue
from
 (
  select
   n1.n_name as supp_nation,
   n2.n_name as cust_nation,
   extract(year from l_shipdate) 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 date '1995-01-01' and date '1996-12-31'
 ) as shipping
group by
 supp_nation,
 cust_nation,
 l_year
order by
 supp_nation,
 cust_nation,
 l_year;

select /*+   TPCH_Q8 PARALLEL(96) */
 o_year,
 sum(case
  when nation = 'BRAZIL' then volume
  else 0
 end) / sum(volume) as mkt_share
from
 (
  select
   extract(year from o_orderdate) 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 date '1995-01-01' and date '1996-12-31'
   and p_type = 'ECONOMY ANODIZED STEEL'
 ) as all_nations
group by
 o_year
order by
 o_year;

select  /*+ TPCH_Q9 PARALLEL(96) */
        nation,
        o_year,
        sum(amount) as sum_profit
from
        (
                select
                        n_name as nation,
                        extract(year from o_orderdate) 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 = ps_partkey
                        and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
                        and p_name like '%indian%'
        ) profit
group by
        nation,
        o_year
order by
        nation,
        o_year desc;

SELECT * FROM (SELECT /*+   TPCH_Q10 PARALLEL(96) */ *
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)
) limit 20;

select /*+   TPCH_Q11 PARALLEL(96) */
 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 = 'GERMANY'
group by
 ps_partkey having
  sum(ps_supplycost * ps_availqty) > (
   select
    sum(ps_supplycost * ps_availqty) * 0.0000100000
   from
    partsupp,
    supplier,
    nation
   where
    ps_suppkey = s_suppkey
    and s_nationkey = n_nationkey
    and n_name = 'GERMANY'
  )
order by
 value desc;

select /*+   TPCH_Q12 PARALLEL(96) */
 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;

SELECT  /*+   TPCH_Q13 PARALLEL(96) */ 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(96) */
 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
  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;


select /*+   TPCH_Q15 PARALLEL(96) */
 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(96) */
 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(96) */
 sum(l_extendedprice) / 7.0 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
 );

select /*+  TPCH_Q18  PARALLEL(96) */
 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
limit 100;

select /*+   TPCH_Q19 PARALLEL(96) */
 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(96) */
 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;

select * from (
select /*+ PARALLEL(96) */
        s_name,
        count(*) as 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 = 'CHINA'
group by
        s_name
order by
        numwait desc,
        s_name
)
limit 100;

select /*+   TPCH_Q22 PARALLEL(96) */
 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', '31', '23', '29', '30', '18', '17')
   and c_acctbal > (
    select
     avg(c_acctbal)
    from
     customer
    where
     c_acctbal > 0.00
     and substring(c_phone from 1 for 2) in
      ('13', '31', '23', '29', '30', '18', '17')
   )
   and not exists (
    select
     *
    from
     orders
    where
     o_custkey = c_custkey
   )
 ) as custsale
group by
 cntrycode
order by
 cntrycode;