达梦进行tpch测试方法_达梦tpch测试

1、利用dbgen 工具生成测试数据
[root@node1 tpch_dbgen]# ./dbgen -s 1
[root@node1 tpch_dbgen]# ll
total 1075056
-rw-r--r-- 1 root root 24346144 May 23 21:54 customer.tbl
-rwxr-xr-x 1 root root 124297 May 23 20:48 dbgen
-rw-r--r-- 1 root root 11815 May 23 20:48 dists.dss
-rw-r--r-- 1 root root 759863287 May 23 21:54 lineitem.tbl
-rw-r--r-- 1 root root 2224 May 23 21:54 nation.tbl
-rw-r--r-- 1 root root 171952161 May 23 21:54 orders.tbl
-rw-r--r-- 1 root root 118984616 May 23 21:54 partsupp.tbl
-rw-r--r-- 1 root root 24135125 May 23 21:54 part.tbl
-rw-r--r-- 1 root root 389 May 23 21:54 region.tbl
-rw-r--r-- 1 root root 1409184 May 23 21:54 supplier.tbl
2、创建初始表
SQL> start /tpch/create_table_dj.sql
SQL> 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)
);
executed successfully
used time: 86.033(ms). Execute id is 4000.
SQL> create huge table LINEITEM
(
L_ORDERKEY int 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)
);
executed successfully
used time: 112.850(ms). Execute id is 4001.
SQL> 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)
);
executed successfully
used time: 72.627(ms). Execute id is 4002.
SQL> create huge table ORDERS
(
O_ORDERKEY int 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)
);
executed successfully
used time: 71.845(ms). Execute id is 4003.
SQL> 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)
);
executed successfully
used time: 80.967(ms). Execute id is 4004.
SQL> 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)
);
executed successfully
used time: 71.615(ms). Execute id is 4005.
SQL> 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)
);
executed successfully
used time: 89.007(ms). Execute id is 4006.
SQL> 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)
);
executed successfully
3、利用dmfldr 进行数据加载
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/customer.ctrl\' log=\'/home/dmdba/cus.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/lineitem.ctrl\' log=\'/home/dmdba/lin.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/nation.ctrl\' log=\'/home/dmdba/nat.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/orders.ctrl\' log=\'/home/dmdba/ord.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/part.ctrl\' log=\'/home/dmdba/part.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/partsupp.ctrl\' log=\'/home/dmdba/pas.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/region.ctrl\' log=\'/home/dmdba/reg.log\'
dmfldr userid=SYSDBA/SYSDBA control=\'/tpch/supplier.ctrl\' log=\'/home/dmdba/sup.log\'
4、更新统计信息
--删除表上所有列的统计信息
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);
5、进行语句查询
--Q1(LINEITEM)
SQL> 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_extendedprice*l_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