一、tpc概述
TPC（Transaction Processing Performance Council，事务处理性能委员会）是一个非盈利组织，负责制定和维护数据库系统性能测试基准，帮助用户评估数据库系统的性能和性价比。TPC 有多个测试基准，其中最常见的是 TPC-C、TPC-H 和 TPC-DS，它们分别针对不同的应用场景和数据库类型。
二、tpcc和tpch概述
TPC-C定义：TPC-C 是一个在线事务处理（OLTP）基准测试，主要用于评估数据库系统在高并发事务处理场景下的性能。
应用场景：模拟一个典型的订单处理系统，例如零售商店或批发业务。它包含多种事务类型，如订单创建、订单查询、库存更新等。
测试内容：
（1）事务类型：包括新订单事务、支付事务、订单状态查询事务、库存水平事务和发货事务。
（2）性能指标：主要衡量每分钟处理的事务数量（TPM，Transactions Per Minute），同时要求系统能够保证数据的完整性和一致性。
（3）并发性：模拟多个用户同时操作数据库，测试系统的并发处理能力。
适用范围：适用于评估关系型数据库（如 MySQL、Oracle、SQL Server 等）在 OLTP 场景下的性能，也常用于测试分布式数据库和内存数据库的事务处理能力。
TPC-H定义：TPC-H 是一个决策支持（DSS）基准测试，主要用于评估数据库系统在复杂查询处理场景下的性能。
应用场景：模拟一个企业级的数据仓库环境，用于支持商业智能（BI）和数据分析任务。
测试内容：
（1）查询类型：包含 22 个复杂的 SQL 查询，涉及数据聚合、连接查询、子查询、排序等操作。
（2）性能指标：主要衡量查询的响应时间和吞吐量，即单位时间内可以完成的查询数量。
（3）数据量：测试数据量通常较大，从几百 GB 到数 TB 不等，用于评估数据库在大规模数据处理场景下的性能。
适用范围：适用于评估关系型数据库（如 PostgreSQL、Teradata 等）在数据仓库和数据分析场景下的性能，也常用于测试 MPP（大规模并行处理）数据库和列存储数据库的性能。
三、tpcc安装部署和测试
3.1 benchmarksql部署
3.1.1上传benchmarksql压缩包并解压
3.1.2在benchmarksql-5.0/src/client/jTPCC.java中配置达梦的参数
3.1.3修改benchmarksql-5.0/run/funcs.sh，如下，增加红色部分即达梦的参数
function setCP()
{
case "$(getProp db)" in
firebird)
cp="../lib/firebird/*:../lib/*"
;;
dameng)
cp="../lib/dameng/*:../lib/*"
;;
oracle)
cp="../lib/oracle/*"
if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
# ----
# Make sure that the properties file does have db= and the value
# is a database, we support.
# ----
case "$(getProp db)" in
firebird|oracle|postgres|dameng)
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 1
;;
esac
3.1.4在lib库中添加dameng目录，放置driver包
3.1.5修改benchmarksql-5.0/run/runDatabaseBuild.sh，去掉AFTER_LOAD中的extraHistID
原因：TPC-C 标准测试不允许在 HISTORY 表上创建额外的索引（如 hist_id 的唯一索引），因为这会人为优化某些查询，导致测试结果不公正。HISTORY 表的设计初衷是 无索引，以模拟真实 OLTP 场景的写入压力。如果保留 extraHistID，可能会使某些查询（如按 hist_id 查找）性能虚高，偏离真实基准。自己测着玩应该就没事。
3.1.6在/benchmarksql-5.0/run中编辑props.dm文件，用于达梦压测参数
修改ip地址、端口号、用户名和密码
3.2数据用户、表准备
3.2.1连接dm数据库
3.2.2创建表空间
CREATE TABLESPACE BENCHMARKSQL1 DATAFILE 'BENCHMARKSQL1.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL2.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL3.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL4.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL5.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL6.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL7.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL8.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL9.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL10.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL11.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL12.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL13.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL14.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL15.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL16.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL17.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL18.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL19.dbf' SIZE 1000;
ALTER TABLESPACE BENCHMARKSQL1 ADD DATAFILE 'BENCHMARKSQL20.dbf' SIZE 1000;
这里为了最大化IO性能，创建了很多数据文件
3.2.3创建用户并给予权限
CREATE USER "BENCHMARKSQL" IDENTIFIED BY "Sysdba%_025" DEFAULT TABLESPACE "BENCHMARKSQL1";
GRANT DBA TO BENCHMARKSQL;
3.2.4调整 ROLL 表空间的数据文件和重做日志文件大小
alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 10000;
alter database resize logfile 'DAMENG01.log' to 40000;
alter database resize logfile 'DAMENG02.log' to 40000;
ROLL 表空间扩容：解决回滚段不足问题。
日志文件扩容：减少日志切换频率，提升 OLTP 稳定性。
这两个只能往大改
3.2.5创建数据库对象
create table BENCHMARKSQL.bmsql_config (
cfg_name varchar(30) cluster primary key,
cfg_value varchar(50)
);
create table BENCHMARKSQL.bmsql_warehouse (
w_id integer not null,
w_ytd decimal(22,2),
w_tax float,
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
cluster primary key(w_id)
)STORAGE(FILLFACTOR 1);
create table BENCHMARKSQL.bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(22,2),
d_tax float,
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
cluster primary key(d_w_id, d_id)
)STORAGE(FILLFACTOR 1);
create table BENCHMARKSQL.bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount float,
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim float,
c_balance float,
c_ytd_payment float,
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500),
cluster primary key(c_w_id, c_d_id, c_id)
);
create table BENCHMARKSQL.bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount float,
h_data varchar(24)
)storage(branch(32,32),without counter);
create table BENCHMARKSQL.bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt float,
o_all_local float,
o_entry_d timestamp,
cluster primary key(o_w_id, o_d_id, o_id)
)storage(without counter);
create table BENCHMARKSQL.bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null,
cluster primary key(no_w_id, no_d_id, no_o_id)
)storage(without counter);
create table BENCHMARKSQL.bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount float,
ol_supply_w_id integer,
ol_quantity float,
ol_dist_info char(24),
cluster primary key(ol_w_id, ol_d_id, ol_o_id, ol_number)
)storage(without counter);
create table BENCHMARKSQL.bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity float,
s_ytd float,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24),
cluster primary key(s_w_id, s_i_id)
);
create table BENCHMARKSQL.bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price float,
i_data varchar(50),
i_im_id integer,
cluster primary key(i_id)
);
3.3装载数据
进入到benchmarksql-5.0/run目录
./runLoader.sh props.dm
之后可以用以下命令一次性核对 TPC-C 基准数据量是否符合官方公式，从而确认 BenchmarkSQL 数据加载是否成功、是否完整，这条 SQL 用 UNION ALL 把 10 条统计查询拼成了一张一列多行的结果表
select count(*) from "BENCHMARKSQL"."BMSQL_CUSTOMER" union all
select count(*) from "BENCHMARKSQL"."BMSQL_DISTRICT" union all
select count(*) from "BENCHMARKSQL"."BMSQL_ITEM" union all
select count(*) from "BENCHMARKSQL"."BMSQL_NEW_ORDER" union all
select count(*) from "BENCHMARKSQL"."BMSQL_OORDER" union all
select count(*) from "BENCHMARKSQL"."BMSQL_ORDER_LINE" union all
select count(*) from "BENCHMARKSQL"."BMSQL_STOCK" union all
select count(*) from "BENCHMARKSQL"."BMSQL_WAREHOUSE" union all
select count(*) from "BENCHMARKSQL"."BMSQL_HISTORY" union all
select count("C_PAYMENT_CNT") from "BENCHMARKSQL"."BMSQL_CUSTOMER";
3.4测试
./runBenchmark.sh props.dm
结果：
结果分析：
|
指标名 |
含义 |
典型输出样例 |
如何解读 |
|
tpmC |
每分钟完成的新订单事务数（New-Order） |
Measured tpmC (NewOrders) = 56415.11 |
越大越好，直接反映系统 OLTP 吞吐量 |
|
tpmTOTAL |
每分钟完成的全部事务数（含 Payment、Order-Status…） |
Measured tpmTOTAL = 125365.22 |
可验算比例：tpmC ≈ tpmTOTAL × newOrderWeight% |
|
Transaction Count |
整个测试阶段完成的总事务数 |
Transaction Count = 1253693 |
结合 runMins 可反推 tpmTOTAL |
我们也可以测一下创建索引后结果会有什么变化
创建索引：
create index ndx_customer_name on BENCHMARKSQL.BMSQL_customer (c_w_id, c_d_id, c_last, c_first);
create or replace procedure BENCHMARKSQL.createsequence
as
n int;
stmt1 varchar(200);
begin
select count(*)+1 into n from BMSQL_history;
if(n != 1) then
select max(hist_id) + 1 into n from BMSQL_history;
end if;
PRINT n;
stmt1:='create sequence hist_id_seq start with '||n||' MAXVALUE 9223372036854775807 CACHE 50000;';
EXECUTE IMMEDIATE stmt1;
end;
/
call BENCHMARKSQL.createsequence;
alter table BENCHMARKSQL.BMSQL_history modify hist_id integer default (BENCHMARKSQL.hist_id_seq.nextval);
结果：
这表明加了索引对tpcc的结果影响不大，这和Jmeter不同，原因在于tpcc的设计刻意让“读”走主键，避免被额外索引放大性能，而且tpcc中读写的占比接近1：1。JMeter中原来无索引，只能全表扫，加了索引后，直接走索引范围扫，响应时间从几百毫秒降到几毫秒，提升爆炸。
四、tpch安装部署
4.1部署tpch
4.1.1上传并解压unzip tpc-h-tool-3.0.1.zip
4.1.2在/tpchv301/dbgen目录下编辑配置文件
vi makefile.suite
修改部分如下
4.1.3编译
make -f makefile.suite
4.1.4会生成qgen、dists.dss和dbgen共3个程序，可以将其复制到其他目录使用
4.2数据生成
./dbgen -vf -s 20
其中20表示生成20G的数据
4.3建表
--CUSTOMER表
CREATE HUGE TABLE CUSTOMER
(
C_CUSTKEY BIGINT 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)
);
--LINEITEM表
CREATE HUGE TABLE LINEITEM
(
L_ORDERKEY BIGINT NOT NULL,--10T的话，该列类型要为BIGINT
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)
);
--ORDERS表
CREATE HUGE TABLE ORDERS
(
O_ORDERKEY BIGINT NOT NULL,--10T的话，该列类型要为BIGINT
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)
);
--PART表
CREATE HUGE TABLE PART
(
P_PARTKEY BIGINT 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)
);
--PARTSUPP表
CREATE HUGE TABLE PARTSUPP
(
PS_PARTKEY BIGINT 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)
);
--SUPPLIER表
CREATE HUGE TABLE SUPPLIER
(
S_SUPPKEY BIGINT 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)
);
--REGION表
CREATE TABLE REGION
(
R_REGIONKEY INT NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152) NOT NULL
);
--NATION表
CREATE TABLE NATION
(
N_NATIONKEY INT NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INT NOT NULL,
N_COMMENT VARCHAR(152) NOT NULL
);
4.4数据装载
4.4.1进入待装载的数据文件目录创建脚本load_data.sh
vi load_data.sh
脚本内容如下（user_id/user_pw为数据库用户及密码、ip_in为数据库IP和端口、dm_bin为数据库执行码目录，用于调用dmfldr）：
#!/bin/bash
curr=$(cd "$(dirname "$0")";pwd)
user_id="SYSDBA"
user_pw="Sysdba%_025"
ip_in="10.76.11.238:5236"
dm_bin="/data/test3/dmdbms/bin"
export LD_LIBRARY_PATH=$dm_bin:$LD_LIBRARY_PATH
dmfldr_parain="LAN_MODE=TRUE IGNORE_AIMLESS_DATA=TRUE LOG_LEVEL=0 LOB_AS_VARCHAR=TRUE APPEND_OPTION=0 READ_ROWS=100000 BUFFER_NODE_SIZE=20 BDTA_SIZE=5000 ROW_SEPERATOR=X '0A' CHARACTER_CODE='UTF-8' FIELD_SEPERATOR='|' MODE='IN' COMMIT_OPTION=0 SINGLE_FILE=TRUE SILENT=TRUE ERRORS=10000 RECONN=300 RECONN_TIME=20"
#load table
mkdir -p ${curr}/log
rm -rf ${curr}/log/*
for tablename in customer lineitem nation orders partsupp part region supplier
do
(nohup ${dm_bin}/dmfldr userid=${user_id}/${user_pw}@$ip_in TABLE=${tablename} data=\'${curr}/${tablename}.tbl*\' log=\'${curr}/log/${tablename}.log\' $dmfldr_parain LOG_LEVEL=1 >/dev/null 2>&1 &) &
done
4.4.2给脚本授权
chmod 755 load_data.sh
4.4.3执行脚本导入数据
sh load_data.sh
4.4.4查看log文件可以得到相关数据
|
表名 |
文本行数 |
文本大小 |
加载耗时(ms) |
|
CUSTOMER |
30000000 |
513M |
19748.859 |
|
LINEITEM |
119994608 |
17.58G |
213747.660 |
|
NATION |
25 |
256K |
4.444 |
|
ORDERS |
30000000 |
3.56G |
213751.636 |
|
PARTSUPP |
16000000 |
2.27G |
209753.004 |
|
PART |
4000000 |
545M |
13750.960 |
|
REGION |
5 |
256K |
7.340 |
|
SUPPLIER |
2000000 |
34M |
1101.480 |
4.5更新统计信息
统计信息的正确性，对执行计划的影响很大。
--更新统计信息
--CUSTOMER表
stat 100 on CUSTOMER(C_CUSTKEY) ;
stat 100 on CUSTOMER(C_NAME) ;
stat 100 on CUSTOMER(C_ADDRESS) ;
stat 100 on CUSTOMER(C_NATIONKEY) ;
stat 100 on CUSTOMER(C_PHONE) ;
stat 100 on CUSTOMER(C_ACCTBAL) ;
stat 100 on CUSTOMER(C_MKTSEGMENT) ;
stat 100 on CUSTOMER(C_COMMENT) ;
--LINEITEM表
sp_set_para_value(1,'SORT_BUF_GLOBAL_SIZE',10240);--ys的参数排序空间不足需要调整
stat 100 on LINEITEM(L_ORDERKEY) ;
sp_set_para_value(1,'SORT_BUF_GLOBAL_SIZE',1000);--ys的参数排序空间不足需要调整
stat 100 on LINEITEM(L_PARTKEY) ;
stat 100 on LINEITEM(L_SUPPKEY) ;
stat 100 on LINEITEM(L_LINENUMBER) ;
stat 100 on LINEITEM(L_QUANTITY) ;
stat 100 on LINEITEM(L_EXTENDEDPRICE) ;
stat 100 on LINEITEM(L_DISCOUNT) ;
stat 100 on LINEITEM(L_TAX) ;
stat 100 on LINEITEM(L_RETURNFLAG) ;
stat 100 on LINEITEM(L_LINESTATUS) ;
stat 100 on LINEITEM(L_SHIPDATE) ;
stat 100 on LINEITEM(L_COMMITDATE) ;
stat 100 on LINEITEM(L_RECEIPTDATE) ;
stat 100 on LINEITEM(L_SHIPINSTRUCT) ;
stat 100 on LINEITEM(L_SHIPMODE) ;
--NATION表
stat 100 on NATION(N_NATIONKEY) ;
stat 100 on NATION(N_NAME) ;
stat 100 on NATION(N_REGIONKEY) ;
stat 100 on NATION(N_COMMENT) ;
--ORDERS表
sp_set_para_value(1,'SORT_BUF_GLOBAL_SIZE',10240);--ys的参数排序空间不足需要调整
stat 100 on ORDERS(O_ORDERKEY) ;
sp_set_para_value(1,'SORT_BUF_GLOBAL_SIZE',1000);--ys的参数排序空间不足需要调整
stat 100 on ORDERS(O_CUSTKEY) ;
stat 100 on ORDERS(O_ORDERSTATUS) ;
stat 100 on ORDERS(O_TOTALPRICE) ;
stat 100 on ORDERS(O_ORDERDATE) ;
stat 100 on ORDERS(O_ORDERPRIORITY) ;
stat 100 on ORDERS(O_CLERK) ;
stat 100 on ORDERS(O_SHIPPRIORITY) ;
stat 100 on ORDERS(O_COMMENT) ;
--PART表
stat 100 on PART(P_PARTKEY) ;
stat 100 on PART(P_NAME) ;
stat 100 on PART(P_MFGR) ;
stat 100 on PART(P_BRAND) ;
stat 100 on PART(P_TYPE) ;
stat 100 on PART(P_SIZE) ;
stat 100 on PART(P_CONTAINER) ;
stat 100 on PART(P_RETAILPRICE) ;
--PARTSUPP表
stat 100 on PARTSUPP(PS_PARTKEY) ;
stat 100 on PARTSUPP(PS_SUPPKEY) ;
stat 100 on PARTSUPP(PS_AVAILQTY) ;
stat 100 on PARTSUPP(PS_SUPPLYCOST) ;
--REGION表
stat 100 on REGION(R_REGIONKEY) ;
stat 100 on REGION(R_NAME) ;
stat 100 on REGION(R_COMMENT) ;
--SUPPLIER表
stat 100 on SUPPLIER(S_SUPPKEY) ;
stat 100 on SUPPLIER(S_NAME) ;
stat 100 on SUPPLIER(S_ADDRESS) ;
stat 100 on SUPPLIER(S_NATIONKEY) ;
stat 100 on SUPPLIER(S_PHONE) ;
stat 100 on SUPPLIER(S_ACCTBAL) ;
4.6测试结果
--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 '104' day (3)
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 = 48
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
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 = 'AMERICA'
)
order by
s_acctbal desc,
n_name ,
s_name ,
p_partkey;
--Q3
select
/*+ dpc(1 l_bro) */
top 10 l_orderkey ,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate ,
o_shippriority
from
customer,
orders ,
lineitem
where
c_mktsegment = 'HOUSEHOLD'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-10'
and l_shipdate > date '1995-03-10'
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 '1994-08-01'
and o_orderdate < date '1994-08-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 = '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;
--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.02 - 0.01 and 0.02 + 0.01
and l_quantity < 25;
--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 = 'ARGENTINA'
and n2.n_name = 'JAPAN'
)
or
(
n1.n_name = 'JAPAN'
and n2.n_name = 'ARGENTINA'
)
)
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 = 'JAPAN' 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 = 'ASIA'
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31'
and p_type = 'LARGE POLISHED 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 '%olive%'
) 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-06-01'
and o_orderdate < date '1993-06-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 value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'ROMANIA'
group by
ps_partkey
having
sum(ps_supplycost * ps_availqty) >
(
select
sum(ps_supplycost * ps_availqty) * 0.0000001000
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'ROMANIA'
)
order by
value 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 ('RAIL', 'SHIP')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1996-01-01'
and l_receiptdate < date '1996-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 '%express%accounts%'
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 '1996-09-01'
and l_shipdate < date '1996-09-01' + interval '1' month;
--Q15 run sql
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;
--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#54'
and p_type not like 'ECONOMY POLISHED%'
and p_size in (15, 23, 13, 24, 41, 7, 42, 1)
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#44'
and p_container = 'LG JAR'
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) > 315
)
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 /*+DPC(3 L_DIS_R_DIS)*/
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem,
part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 9
and l_quantity <= 9 + 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 >= 11
and l_quantity <= 11 + 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#41'
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 'khaki%'
)
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 = 'IRAN'
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 = 'ETHIOPIA'
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 ('30', '40', '22', '21', '27', '26', '35')
and c_acctbal >
(
select
avg(c_acctbal)
from
customer
where
c_acctbal > 0.00
and substring(c_phone
from
1 for 2) in ('30', '40', '22', '21', '27', '26', '35')
)
and not exists
(
select * from orders where o_custkey = c_custkey
)
) as custsale
group by
cntrycode
order by
cntrycode;
结果数据：
|
语句 |
查询对象 |
耗时 |
|
Q1 |
价格汇总报表查询 |
0:01:00.447 |
|
Q2 |
最小成本供应商查询 |
0:00:14.088 |
|
Q3 |
发货优先级查询 |
0:01:01.277 |
|
Q4 |
订单优先级查询 |
0:00:39.072 |
|
Q5 |
本地供应商数量查询 |
0:00:43.374 |
|
Q6 |
收入变化预测查询 |
0:00:25.161 |
|
Q7 |
批量出货查询 |
0:01:05.830 |
|
Q8 |
全国市场占有率查询 |
0:00:43.843 |
|
Q9 |
产品类型利润指标查询 |
0:03:42.418 |
|
Q10 |
退货项报表查询 |
0:00:44.723 |
|
Q11 |
重要库存鉴定查询 |
0:00:04.976 |
|
Q12 |
发货方式及订单优先级查询 |
0:00:41.411 |
|
Q13 |
客户分布查询 |
0:13:25.120 |
|
Q14 |
提升效果查询 |
0:00:25.259 |
|
Q15 |
顶级供应商查询 |
0:00:26.010 |
|
Q16 |
零件/供应商关系查询 |
0:00:13.040 |
|
Q17 |
小批量订单收入查询 |
0:18:06.691 |
|
Q18 |
大批量客户查询 |
0:16:34.848 |
|
Q19 |
折现收入查询 |
0:02:14.945 |
|
Q20 |
潜在部件提升查询 |
0:07:37.398 |
|
Q21 |
保持订单等待的供应商查询 |
0:02:26.086 |
|
Q22 |
全球销售机会查询 |
0:00:11.812 |
扫一扫
1123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
