tpch测试步骤

Tpch测试步骤

1：上传dbgen工具并解压
装载1g的数据
[root@dm1 tpch_dbgen]# ./dbgen -s 1

2：检查并修改控制文件

修改控制文件

3：建表
CREATE HUGE TABLE CUSTOMER
(
C_CUSTKEY INT NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INT NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL FLOAT NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL,
PRIMARY KEY (C_CUSTKEY)
)
DISTRIBUTED BY (C_CUSTKEY);

CREATE HUGE TABLE LINEITEM
(
L_ORDERKEY BIGINT NOT NULL,
L_PARTKEY INT NOT NULL,
L_SUPPKEY INT NOT NULL,
L_LINENUMBER INT NOT NULL,
L_QUANTITY FLOAT NOT NULL,
L_EXTENDEDPRICE FLOAT NOT NULL,
L_DISCOUNT FLOAT NOT NULL,
L_TAX FLOAT 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)
)
DISTRIBUTED BY(L_ORDERKEY);

CREATE HUGE TABLE NATION
(
N_NATIONKEY INT NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INT NOT NULL,
N_COMMENT VARCHAR(152) NOT NULL,
PRIMARY KEY (N_NATIONKEY)
)DISTRIBUTED BY (N_NATIONKEY);

CREATE HUGE TABLE ORDERS
(
O_ORDERKEY BIGINT NOT NULL,
O_CUSTKEY INT NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE FLOAT 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)
)
DISTRIBUTED BY (O_ORDERKEY);

CREATE HUGE TABLE PART
(
P_PARTKEY INT 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 INT NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE FLOAT NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL,
PRIMARY KEY (P_PARTKEY)
)
DISTRIBUTED BY (P_PARTKEY);

CREATE HUGE TABLE PARTSUPP
(
PS_PARTKEY INT NOT NULL,
PS_SUPPKEY INT NOT NULL,
PS_AVAILQTY INT NOT NULL,
PS_SUPPLYCOST FLOAT NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL,
PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
)
DISTRIBUTED BY (PS_PARTKEY);

CREATE HUGE TABLE REGION
(
R_REGIONKEY INT NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152) NOT NULL,
PRIMARY KEY (R_REGIONKEY)
)
DISTRIBUTED BY (R_REGIONKEY);

CREATE HUGE TABLE SUPPLIER
(
S_SUPPKEY INT NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INT NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL FLOAT NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL,
PRIMARY KEY (S_SUPPKEY)
)
DISTRIBUTED BY (S_SUPPKEY);

4：加载数据

[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/customer.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/lineitem.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/nation.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/orders.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/part.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/partsupp.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/region.ctrl’
[root@dm1 bin]# ./dmfldr userid=SYSDBA/SYSDBA:5236 control=’/dm8/tpch/linux/supplier.ctrl’

5：更新统计信息
–删除表上所有列的统计信息
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘CUSTOMER’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘LINEITEM’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘NATION’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘ORDERS’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘PART’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘PARTSUPP’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘REGION’);
CALL SP_TAB_COL_STAT_DEINIT (‘SYSDBA’, ‘SUPPLIER’);
–删除表的统计信息
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘CUSTOMER’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘LINEITEM’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘NATION’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘ORDERS’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘PART’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘PARTSUPP’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘REGION’);
CALL SP_TAB_STAT_DEINIT (‘SYSDBA’, ‘SUPPLIER’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘REGION’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘NATION’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘PART’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘PARTSUPP’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘SUPPLIER’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘CUSTOMER’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘ORDERS’);
SP_TAB_STAT_INIT(‘SYSDBA’,‘LINEITEM’);
–更新统计信息
STAT 100 ON REGION(R_NAME);
STAT 100 ON REGION(R_REGIONKEY);
STAT 100 ON NATION(N_NAME);
STAT 100 ON NATION(N_NATIONKEY) ;
STAT 100 ON NATION(N_REGIONKEY) ;
STAT 100 ON SUPPLIER(S_SUPPKEY) ;
STAT 100 ON SUPPLIER(S_NATIONKEY);
STAT 100 ON SUPPLIER(S_COMMENT);

STAT 100 ON PART(P_SIZE);
STAT 100 ON PART(P_BRAND);
STAT 100 ON PART(P_TYPE);
STAT 100 ON PART(P_NAME);
STAT 100 ON PART(P_PARTKEY);
STAT 100 ON PART(P_CONTAINER);

STAT 100 ON PARTSUPP(PS_SUPPKEY);
STAT 100 ON PARTSUPP(PS_PARTKEY);

STAT 100 ON ORDERS(O_ORDERKEY);
STAT 100 ON ORDERS(O_ORDERDATE);
STAT 100 ON ORDERS(O_ORDERSTATUS);
STAT 100 ON ORDERS(O_ORDERPRIORITY);
STAT 100 ON ORDERS(O_CUSTKEY);
STAT 100 ON ORDERS(O_COMMENT);

STAT 100 ON LINEITEM(L_SUPPKEY);
STAT 100 ON LINEITEM(L_PARTKEY);
STAT 100 ON LINEITEM(L_ORDERKEY);
STAT 100 ON LINEITEM(L_SHIPDATE);
STAT 100 ON LINEITEM(L_SHIPMODE);
STAT 100 ON LINEITEM(L_COMMITDATE);
STAT 100 ON LINEITEM(L_RECEIPTDATE);
STAT 100 ON LINEITEM(L_RETURNFLAG);
STAT 100 ON LINEITEM(L_LINESTATUS);
STAT 100 ON LINEITEM(L_QUANTITY);
STAT 100 ON LINEITEM(L_SHIPINSTRUCT);

STAT 100 ON CUSTOMER(C_CUSTKEY);
STAT 100 ON CUSTOMER(C_MKTSEGMENT);
STAT 100 ON CUSTOMER(C_NATIONKEY);
STAT 100 ON CUSTOMER(C_ACCTBAL);

6：测试22条sql执行时间

22条语句
–Q1(LINEITEM)
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 ‘90’ day
group by l_returnflag, l_linestatus
order by l_returnflag, l_linestatus;
–Q2
select TOP 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;
–Q3
select TOP 10
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;
–Q4
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;
–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 = ‘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;
–Q6
select
sum(l_extendedpricel_discount) as revenue
from lineitem
where l_shipdate >= ‘1994-01-01’
and l_shipdate < dateadd(year,1, ‘1994-01-01’)
and l_discount between 0.06 - 0.01
and 0.06 + 0.01
and l_quantity < 24;
–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 = ‘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;
–Q8
select
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;
–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 ‘%green%’ ) as profit
group by nation, o_year
order by nation, o_year desc;
–Q10
select top 20
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;
–Q11
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as valuess
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.0001
from partsupp, supplier, nation
where ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = ‘GERMANY’ )
order by valuess desc;
–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 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;
–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;
–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 ‘1995-09-01’
and l_shipdate < date’1995-09-01’ + interval ‘1’ month;
–Q15
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;
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;
drop view revenue;
–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#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;
–Q17
select
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 );
–Q18
select top 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;
–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 >= 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’ );
–Q20
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;
–Q21
select top 100
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 = ‘SAUDI ARABIA’
group by s_name
order by numwait desc, s_name;
–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 (‘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;

达梦云适配技术社区
https://eco.dameng.com/