泽拓昆仑Klustron VS Oracle TPC-H性能对比

Klustron大约 17 分钟

泽拓昆仑Klustron VS Oracle TPC-H性能对比

测试结果总览

通过TPC-H测试工具，分别对Klustron v1.4.1和Oracle v19.14.0.0.0版本进行测试，获取性能结果。

数据量为100G时，Klustron总耗时为307.9s，Oracle总耗时为782.8s，Klustron的性能是Oracle的2.5倍。
数据量为200G时，Klustron总耗时为1631.57s，Oracle总耗时为2599.11s，Klustron的性能Oracle的1.5倍。
数据量为500G时，Klustron总耗时为10197.26s（q21查询异常），Oracle总耗时为5827.13s，Oracle的性能是Klustron的1.7倍。

测试环境

服务器规格配置

共三台机器：192.168.0.17、192.168.0.18、192.168.0.19，每台机器的配置如下：

操作系统：CentOS Linux release 8.5.2111
CPU：AMD Ryzen 9 7950X 16-Core Processor，hread(s) per core: 2，32核
内存：128GB
磁盘：nvme 1.8T *1
网络：Speed: 10000Mb/s

软件版本

软件名称	软件版本
Klustron	v1.4.1
Oracle	v19.14.0.0.0
TPC-H	v3.0.0

集群环境

Klustron

节点类型	IP	PORT
计算节点	192.168.0.19	23001
Storage-shard1-Master	192.168.0.17	33501
Storage-shard2-Master	192.168.0.18	33503
Storage-shard3-Master	192.168.0.19	33505
Cluster_mgr	192.168.0.17/18/19	23501
Metedata_cluster	192.168.0.17/18/19	23301
XPanel	192.168.0.18	10024

集群说明：

计算节点：部署一个计算节点。
存储节点：3个shard，每一个shard为单主，三个shard的单主分别分布在这三台机器上。
管理节点和元数据集群：管理集群有三台机器组成，为3个节点，1主两备。

Oracle

节点类型	IP	PORT
单节点实例	192.168.0.20	监听端口1521

配置参数

Klustron

计算节点：

alter system set statement_timeout=6000000;
alter system set mysql_read_timeout=1200;
alter system set mysql_write_timeout=1200;
alter system set lock_timeout=1200000;
alter system set log_min_duration_statement=1200000;
alter system set effective_cache_size = '48GB';
alter system set work_mem  = '5GB';
alter system set wal_buffers='64MB';
alter system set extension.aggregate_ignore_work_mem=false;
alter system set enable_nestloop=false;
alter system set extension.max_custom_indexscan_parallelism=16;
alter system set extension.custom_hashjoin_parallelism=32;

注意：调整后需重启计算节点。

存储节点：

mysql -h 192.168.0.17 -P 33501 -upgx -ppgx_pwd
mysql -h 192.168.0.18 -P 33503 -upgx -ppgx_pwd
mysql -h 192.168.0.19 -P 33505 -upgx -ppgx_pwd

分别设置如下参数：

set persist innodb_buffer_pool_size=48*1024*1024*1024;
set persist lock_wait_timeout=1200;
set persist innodb_lock_wait_timeout=1200;    
set persist fullsync_timeout=1200000; 
set persist enable_fullsync=false;
set persist innodb_flush_log_at_trx_commit=2;
set persist sync_binlog=0;
set persist max_binlog_size=1*1024*1024*1024;

Oracle

实例节点执行，设置96G SGA

alter system set sga_max_size=98304M scope=spfile;
alter system set sga_target=98304M scope=spfile;

设置大表开启并行查询

alter table LINEITEM parallel(degree 24);
alter table PART parallel(degree 8);
alter table CUSTOMER parallel(degree 8);
alter table ORDERS parallel(degree 8);
alter table PARTSUPP parallel(degree 8);

测试结果

TPC-H-100G

TPC-H 数据量100G，测试结果均为测试两次择优耗时较短的一次，单位：秒。

QUERY ID	Klustron v1.4.1	Oracle v19.14.0.0.0
Q1	4.48	45.05
Q2	8.05	4.74
Q3	10.09	54.17
Q4	5.06	46.85
Q5	22.33	52.39
Q6	2.95	25.43
Q7	12.36	25.84
Q8	31.36	32.17
Q9	29.43	48.51
Q10	10.61	216.21
Q11	2.6	7.64
Q12	6.99	56.11
Q13	15.73	8.24
Q14	4.04	29.28
Q15	7.57	13.24
Q16	3.76	7.47
Q17	15.41	19.26
Q18	39.05	27.5
Q19	8.37	8.27
Q20	13.57	11.63
Q21	47.8	32.42
Q22	6.29	10.38
总耗时	307.9	782.8

TPC-H-200G

TPC-H 数据量200G，测试结果均为测试两次择优耗时较短的一次，单位：秒。

QUERY ID	Klustron v1.4.1	Oracle v19.14.0.0.0
Q1	44.39	88.94
Q2	26.95	2.89
Q3	55.93	109.86
Q4	56.83	105.72
Q5	114.12	111.43
Q6	45.47	89.17
Q7	70.03	110.53
Q8	112.17	110.3
Q9	111.07	274.54
Q10	63.72	358
Q11	14.99	14.81
Q12	64.27	110.69
Q13	33.62	20.17
Q14	53.79	96.76
Q15	102.97	94.3
Q16	17.49	15.72
Q17	96.64	191
Q18	177.66	241.72
Q19	52.49	97.65
Q20	78.37	98.05
Q21	218.36	236.74
Q22	20.24	20.12
总耗时	1631.57	2599.11

)

TPC-H-500G

TPC-H 数据量500G，测试结果均为测试两次择优耗时较短的一次，单位：秒。

QUERY ID	Klustron v1.4.1	Oracle v19.14.0.0.0
Q1	88.79	218.84
Q2	167.71	22.07
Q3	424.11	275.89
Q4	162.17	261.36
Q5	465.63	299.21
Q6	132.41	235.60
Q7	209.25	349.81
Q8	467.98	292.20
Q9	717.72	565.74
Q10	266.68	214.90
Q11	135.14	62.61
Q12	128.81	277.72
Q13	178.35	65.85
Q14	174.23	239.02
Q15	330.5	231.78
Q16	641.42	70.62
Q17	445.27	474.54
Q18	4191.07	449.45
Q19	163.6	240.43
Q20	610.63	290.03
Q21		637.36
Q22	95.79	52.1
总耗时	10197.26	5827.13

)

建表语句

Klustron

create TABLEGROUP tpch10 partitions 3 (
        partition tpch10_auto_p0 with(hash=0,shard=1),
        partition tpch10_auto_p1 with(hash=1,shard=2),
        partition tpch10_auto_p2 with(hash=2,shard=3)
);

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152),
          PRIMARY KEY (n_nationkey)) WITH(SHARD=all);


CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152),
          PRIMARY KEY (r_regionkey)) WITH (SHARD=all);

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE double NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL,
        PRIMARY KEY (p_partkey)) PARTITION BY HASH (p_partkey) partitions 3 TABLEGROUP tpch10;



CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     double NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL,
           PRIMARY KEY (s_suppkey)) PARTITION BY HASH (s_suppkey) partitions 3 TABLEGROUP tpch10;




CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  double  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL,
           PRIMARY KEY (ps_partkey, ps_suppkey)) PARTITION BY HASH (ps_partkey) partitions 3 TABLEGROUP tpch10;




CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     double NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL,
           PRIMARY KEY(c_custkey)) PARTITION BY HASH (C_CUSTKEY) partitions 3 TABLEGROUP tpch10;


CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     double NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL,
         PRIMARY KEY(o_orderkey)) PARTITION BY HASH (o_orderkey) partitions 3 TABLEGROUP tpch10;



CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    double NOT NULL,
                             L_EXTENDEDPRICE  double NOT NULL,
                             L_DISCOUNT    double NOT NULL,
                             L_TAX         double NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL,
           PRIMARY KEY(l_orderkey, l_linenumber)) PARTITION BY HASH (l_orderkey) partitions 3 TABLEGROUP tpch10;

Oracle

CREATE TABLE NATION  ( N_NATIONKEY  number NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  number NOT NULL,
                            N_COMMENT    VARCHAR2(152),
          PRIMARY KEY (n_nationkey));


CREATE TABLE REGION  ( R_REGIONKEY  number NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR2(152),
          PRIMARY KEY (r_regionkey));

CREATE TABLE PART  ( P_PARTKEY     number NOT NULL,
                          P_NAME        VARCHAR2(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR2(25) NOT NULL,
                          P_SIZE        number NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE binary_double NOT NULL,
                          P_COMMENT     VARCHAR2(23) NOT NULL,
        PRIMARY KEY (p_partkey)) PARTITION BY HASH (p_partkey) partitions 3;



CREATE TABLE SUPPLIER ( S_SUPPKEY     number NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR2(40) NOT NULL,
                             S_NATIONKEY   number NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     binary_double NOT NULL,
                             S_COMMENT     VARCHAR2(101) NOT NULL,
           PRIMARY KEY (s_suppkey)) PARTITION BY HASH (s_suppkey) partitions 3;




CREATE TABLE PARTSUPP ( PS_PARTKEY     number NOT NULL,
                             PS_SUPPKEY     number NOT NULL,
                             PS_AVAILQTY    number NOT NULL,
                             PS_SUPPLYCOST  binary_double  NOT NULL,
                             PS_COMMENT     VARCHAR2(199) NOT NULL,
           PRIMARY KEY (ps_partkey, ps_suppkey)) PARTITION BY HASH (ps_partkey) partitions 3;




CREATE TABLE CUSTOMER ( C_CUSTKEY     number NOT NULL,
                             C_NAME        VARCHAR2(25) NOT NULL,
                             C_ADDRESS     VARCHAR2(40) NOT NULL,
                             C_NATIONKEY   number NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     binary_double NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR2(117) NOT NULL,
           PRIMARY KEY(c_custkey)) PARTITION BY HASH (C_CUSTKEY) partitions 3;


CREATE TABLE ORDERS  ( O_ORDERKEY       number NOT NULL,
                           O_CUSTKEY        number NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     binary_double NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   number NOT NULL,
                           O_COMMENT        VARCHAR2(79) NOT NULL,
         PRIMARY KEY(o_orderkey)) PARTITION BY HASH (o_orderkey) partitions 3;



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    binary_double NOT NULL,
                             L_EXTENDEDPRICE  binary_double NOT NULL,
                             L_DISCOUNT    binary_double NOT NULL,
                             L_TAX         binary_double NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR2(44) NOT NULL,
           PRIMARY KEY(l_orderkey, l_linenumber)) PARTITION BY HASH (l_orderkey) partitions 3;

收集统计信息

Klustron

Klustron采用如下语句收集统计信息：

Klustron执行的语句：

analyze nation;
analyze region;
analyze part;
analyze supplier;
analyze partsupp;
analyze customer;
analyze orders;
analyze lineitem;
analyze lineitem(l_shipmode,l_receiptdate,l_returnflag,l_linestatus,l_orderkey,l_suppkey,l_discount,l_quantity,l_partkey);
analyze supplier(s_suppkey,s_nationkey);
analyze part(p_size,p_type,p_partkey);
analyze partsupp(ps_suppkey,ps_supplycost);
analyze nation(n_regionkey,n_nationkey,n_name);
analyze region(r_name,r_regionkey);
analyze customer(c_mktsegment,c_custkey,c_nationkey,c_name);
analyze orders(o_custkey,o_orderkey,o_orderdate,o_orderpriority,o_totalprice);

Oracle

EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','REGION',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','NATION',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','LINEITEM',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','CUSTOMER',estimate_percent=>50,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','PART',estimate_percent=>50,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','PARTSUPP',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','ORDERS',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('ASHAN','SUPPLIER',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>16,cascade=>TRUE);

查询语句

Klustron

#Q1:
select
  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 '91' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus
LIMIT 1;
 
 
 
#Q2:
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 = 6
  and p_type like '%BRASS'
  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
LIMIT 100;
 
 
 
#Q3:
select
  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-17'
  and l_shipdate > date '1995-03-17'
group by
  l_orderkey,
  o_orderdate,
  o_shippriority
order by
  revenue desc,
  o_orderdate
LIMIT 10; 
 
 
#Q4:
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1995-07-01'
  and o_orderdate < date '1995-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
LIMIT 1;
 
 
 
#Q5:
select
  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 = 'AFRICA'
  and o_orderdate >= date '1997-01-01'
  and o_orderdate < date '1997-01-01' + interval '1' year
group by
  n_name
order by
  revenue desc
LIMIT 1;
 
 
 
#Q6:
select
  sum(l_extendedprice * l_discount) as revenue
from
  lineitem
where
  l_shipdate >= date '1997-01-01'
  and l_shipdate < date '1997-01-01' + interval '1' year
  and l_discount between 0.05 - 0.01 and 0.05 + 0.01
  and l_quantity < 25
LIMIT 1;
 
 
 
#Q7:
select
  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 = 'IRAN' and n2.n_name = 'PERU')
        or (n1.n_name = 'PERU' and n2.n_name = 'IRAN')
      )
      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
LIMIT 1; 
 
 
#Q8:
select
  o_year,
  sum(case
    when nation = 'PERU' 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 COPPER'
  ) as all_nations
group by
  o_year
order by
  o_year
LIMIT 1; 
 
 
#Q9:
select
  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 = l_partkey
      and o_orderkey = l_orderkey
      and s_nationkey = n_nationkey
      and p_name like '%pink%'
  ) as profit
group by
  nation,
  o_year
order by
  nation,
  o_year desc
LIMIT 1;
 
 
 
#Q10:
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-05-01'
  and o_orderdate < date '1993-05-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; 
 
 
#Q11:
select
  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
LIMIT 1; 
 
 
#Q12:
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 = 'MAIL' or l_shipmode = 'FOB')
  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
LIMIT 1;
 
 
 
#Q13:
select
  c_count,
  count(*) as custdist
from
  (
    select
      c_custkey,
      count(o_orderkey)
    from
      customer left outer join orders on
        c_custkey = o_custkey
        and o_comment not like '%special%requests%'
    group by
      c_custkey
  ) as c_orders (c_custkey, c_count)
group by
  c_count
order by
  custdist desc,
  c_count desc
LIMIT 1; 
 
 
#Q14:
select
  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 '1994-10-01'
  and l_shipdate < date '1994-10-01' + interval '1' month
LIMIT 1; 
 
 
#Q15:
create view revenue0 (supplier_no, total_revenue) as
  select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
  from
    lineitem
  where
    l_shipdate >= date '1994-04-01'
    and l_shipdate < date '1994-04-01' + interval '3' month
  group by
    l_suppkey;


select
  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
LIMIT 1;

drop view revenue0; 
 
 
#Q16:
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#11'
  and p_type not like 'STANDARD BRUSHED%'
  and p_size in (9, 21, 24, 30, 36, 50, 27, 39)
  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
LIMIT 1; 
 
 
#Q17:
select
  sum(l_extendedprice) / 7.0 as avg_yearly
from
  lineitem,
  part,
  (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
  p_partkey = l_partkey
  and agg_partkey = l_partkey
  and p_brand = 'Brand#11'
  and p_container = 'SM CAN'
  and l_quantity < avg_quantity
LIMIT 1;
 
 
 
#Q18:
select
  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) > 312
  )
  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; 
 
 
#Q19:
select
  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 >= 5 and l_quantity <= 5 + 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#33'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and l_quantity >= 18 and l_quantity <= 18 + 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#45'
    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'
  )
LIMIT 1; 
 
 
#Q20:
select
  s_name,
  s_address
from
  supplier,
  nation
where
  s_suppkey in (
    select
      ps_suppkey
    from
      partsupp,
      (
        select
          l_partkey agg_partkey,
          l_suppkey agg_suppkey,
          0.5 * sum(l_quantity) AS agg_quantity
        from
          lineitem
        where
          l_shipdate >= date '1995-01-01'
          and l_shipdate < date '1995-01-01' + interval '1' year
        group by
          l_partkey,
          l_suppkey
      ) agg_lineitem
    where
      agg_partkey = ps_partkey
      and agg_suppkey = ps_suppkey
      and ps_partkey in (
        select
          p_partkey
        from
          part
        where
          p_name like 'hot%'
      )
      and ps_availqty > agg_quantity
  )
  and s_nationkey = n_nationkey
  and n_name = 'KENYA'
order by
  s_name
LIMIT 1;
 
 
 
#Q21:
select
  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 = 'IRAQ'
group by
  s_name
order by
  numwait desc,
  s_name
LIMIT 100; 
 
 
#Q22:
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
        ('24', '27', '34', '23', '29', '15', '13')
      and c_acctbal > (
        select
          avg(c_acctbal)
        from
          customer
        where
          c_acctbal > 0.00
          and substring(c_phone from 1 for 2) in
            ('24', '27', '34', '23', '29', '15', '13')
      )
      and not exists (
        select
          *
        from
          orders
        where
          o_custkey = c_custkey
      )
  ) as custsale
group by
  cntrycode
order by
  cntrycode
LIMIT 1;

Oracle

#Q1:
select
  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 '91' day
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus
offset 0 rows fetch next 1 rows only;


#Q2:
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 = 6
  and p_type like '%BRASS'
  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
offset 0 rows fetch next 100 rows only;


#Q3:
select
  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-17'
  and l_shipdate > date '1995-03-17'
group by
  l_orderkey,
  o_orderdate,
  o_shippriority
order by
  revenue desc,
  o_orderdate
offset 0 rows fetch next 10 rows only;

#Q4:
select
  o_orderpriority,
  count(*) as order_count
from
  orders
where
  o_orderdate >= date '1995-07-01'
  and o_orderdate < date '1995-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
offset 0 rows fetch next 1 rows only;


#Q5:
select
  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 = 'AFRICA'
  and o_orderdate >= date '1997-01-01'
  and o_orderdate < date '1997-01-01' + interval '1' year
group by
  n_name
order by
  revenue desc
offset 0 rows fetch next 1 rows only;


#Q6:
select
  sum(l_extendedprice * l_discount) as revenue
from
  lineitem
where
  l_shipdate >= date '1997-01-01'
  and l_shipdate < date '1997-01-01' + interval '1' year
  and l_discount between 0.05 - 0.01 and 0.05 + 0.01
  and l_quantity < 25
offset 0 rows fetch next 1 rows only;


#Q7:
select
  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 = 'IRAN' and n2.n_name = 'PERU')
        or (n1.n_name = 'PERU' and n2.n_name = 'IRAN')
      )
      and l_shipdate between date '1995-01-01' and date '1996-12-31'
  ) shipping
group by
  supp_nation,
  cust_nation,
  l_year
order by
  supp_nation,
  cust_nation,
  l_year
offset 0 rows fetch next 1 rows only;

#Q8:
select
  o_year,
  sum(case
    when nation = 'PERU' 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 COPPER'
  ) all_nations
group by
  o_year
order by
  o_year
offset 0 rows fetch next 1 rows only;

#Q9:
select
  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 = l_partkey
      and o_orderkey = l_orderkey
      and s_nationkey = n_nationkey
      and p_name like '%pink%'
  ) profit
group by
  nation,
  o_year
order by
  nation,
  o_year desc
offset 0 rows fetch next 1 rows only;

#Q10:
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-05-01'
  and o_orderdate < date '1993-05-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
offset 0 rows fetch next 20 rows only;

#Q11:
select
  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
offset 0 rows fetch next 1 rows only;

#Q12:
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 = 'MAIL' or l_shipmode = 'FOB')
  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
offset 0 rows fetch next 1 rows only;

#Q13:
select
  c_count,
  count(*) as custdist
from
  (
    select
      c_custkey,
      count(o_orderkey) as c_count
    from
      customer left outer 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
offset 0 rows fetch next 1 rows only;

#Q14:
select
  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 '1994-10-01'
  and l_shipdate < date '1994-10-01' + interval '1' month
offset 0 rows fetch next 1 rows only;

#Q15:
create view revenue0 (supplier_no, total_revenue) as
  select
    l_suppkey,
    sum(l_extendedprice * (1 - l_discount))
  from
    lineitem
  where
    l_shipdate >= date '1994-04-01'
    and l_shipdate < date '1994-04-01' + interval '3' month
  group by
    l_suppkey;

select
  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
offset 0 rows fetch next 1 rows only;


drop view revenue0;

#Q16:
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#11'
  and p_type not like 'STANDARD BRUSHED%'
  and p_size in (9, 21, 24, 30, 36, 50, 27, 39)
  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
offset 0 rows fetch next 1 rows only;

#Q17:
select
  sum(l_extendedprice) / 7.0 as avg_yearly
from
  lineitem,
  part,
  (SELECT l_partkey AS agg_partkey, 0.2 * avg(l_quantity) AS avg_quantity FROM lineitem GROUP BY l_partkey) part_agg
where
  p_partkey = l_partkey
  and agg_partkey = l_partkey
  and p_brand = 'Brand#11'
  and p_container = 'SM CAN'
  and l_quantity < avg_quantity
offset 0 rows fetch next 1 rows only;

#Q18:
select
  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) > 312
  )
  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
offset 0 rows fetch next 100 rows only;

#Q19:
select
  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 >= 5 and l_quantity <= 5 + 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#33'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and l_quantity >= 18 and l_quantity <= 18 + 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#45'
    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'
  )
offset 0 rows fetch next 1 rows only;

#Q20:
select
  s_name,
  s_address
from
  supplier,
  nation
where
  s_suppkey in (
    select
      ps_suppkey
    from
      partsupp,
      (
        select
          l_partkey agg_partkey,
          l_suppkey agg_suppkey,
          0.5 * sum(l_quantity) AS agg_quantity
        from
          lineitem
        where
          l_shipdate >= date '1995-01-01'
          and l_shipdate < date '1995-01-01' + interval '1' year
        group by
          l_partkey,
          l_suppkey
      ) agg_lineitem
    where
      agg_partkey = ps_partkey
      and agg_suppkey = ps_suppkey
      and ps_partkey in (
        select
          p_partkey
        from
          part
        where
          p_name like 'hot%'
      )
      and ps_availqty > agg_quantity
  )
  and s_nationkey = n_nationkey
  and n_name = 'KENYA'
order by
  s_name
offset 0 rows fetch next 1 rows only;

#Q21:
select
  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 = 'IRAQ'
group by
  s_name
order by
  numwait desc,
  s_name
offset 0 rows fetch next 100 rows only;

#Q22:
select
  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
        ('24', '27', '34', '23', '29', '15', '13')
      and c_acctbal > (
        select
          avg(c_acctbal)
        from
          customer
        where
          c_acctbal > 0.00
          and substr(c_phone,1,2) in
            ('24', '27', '34', '23', '29', '15', '13')
      )
      and not exists (
        select
          *
        from
          orders
        where
          o_custkey = c_custkey)
  ) custsale
group by
  cntrycode
order by
  cntrycode
offset 0 rows fetch next 1 rows only;

注意事项

oracle导入数据前，需要创建足够多的表空间，以免由于空间不足导入数据失败。
以上测试的机器磁盘不是企业级磁盘，避免不了会影响一些的性能结果。

泽拓昆仑Klustron VS Oracle TPC-H性能对比

# 泽拓昆仑Klustron VS Oracle TPC-H性能对比

# 测试结果总览

# 测试环境

# 服务器规格配置

# 软件版本

# 集群环境

# 配置参数

# 测试结果

# TPC-H-100G

# TPC-H-200G

# TPC-H-500G

# 建表语句

# Klustron

# Oracle

# 收集统计信息

# Klustron

# Oracle

# 查询语句

# Klustron

# Oracle

# 注意事项

# END

泽拓昆仑Klustron VS Oracle TPC-H性能对比

测试结果总览

测试环境

服务器规格配置

软件版本

集群环境

配置参数

测试结果

TPC-H-100G

TPC-H-200G

TPC-H-500G

建表语句

Klustron

Oracle

收集统计信息

Klustron

Oracle

查询语句

Klustron

Oracle

注意事项

END