TPC-H Benchmark

TPC-H是一个决策支持基准（Decision Support Benchmark），它由一套面向业务的特别查询和并发数据修改组成。查询和填充数据库的数据具有广泛的行业相关性。这个基准测试演示了检查大量数据、执行高度复杂的查询并回答关键业务问题的决策支持系统。TPC-H报告的性能指标称为TPC-H每小时复合查询性能指标(QphH@Size)，反映了系统处理查询能力的多个方面。这些方面包括执行查询时所选择的数据库大小，由单个流提交查询时的查询处理能力，以及由多个并发用户提交查询时的查询吞吐量。

本文档主要介绍 Doris 在 TPC-H 100G 测试集上的性能表现。

注1：包括 TPC-H 在内的标准测试集通常和实际业务场景差距较大，并且部分测试会针对测试集进行参数调优。所以标准测试集的测试结果仅能反映数据库在特定场景下的性能表现。建议用户使用实际业务数据进行进一步的测试。

注2：本文档涉及的操作都在 CentOS 7.x 上进行测试。

注 3: Doris 从 1.2.2 版本开始，为了减少内存占用，默认关闭了 Page Cache，会对性能有一定影响，所以在进行性能测试时请在 be.conf 添加 disable_storage_page_cache=false 来打开 Page Cache。

在 TPC-H 标准测试数据集上的 22 个查询上，我们基于 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04 版本进行了对别测试， Apache Doris 1.2.0-rc01上相对 Apache Doris 1.1.3 整体性能提升了将近 3 倍，相对于 Apache Doris 0.15.0 RC04 ,性能提升了将近 11 倍 。

1. 硬件环境

2. 软件环境

• Doris部署 3BE 1FE；
• 内核版本：Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
• 操作系统版本：CentOS 7.8
• Doris 软件版本： Apache Doris 1.2.0-rc01、 Apache Doris 1.1.3 、 Apache Doris 0.15.0 RC04
• JDK：openjdk version “11.0.14” 2022-01-18

3. 测试数据量

整个测试模拟生成 TPCH 100G 的数据分别导入到 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04 版本进行测试，下面是表的相关说明及数据量。

4. 测试SQL

TPCH 22 个测试查询语句 ： TPCH-Query-SQL

注意：

以上 SQL 中的以下四个参数在 Apache Doris 0.15.0 RC04 中不存在，在执行的时候，去掉：

1. 1. enable_vectorized_engine=true,
2. 2. batch_size=4096,
3. 3. disable_join_reorder=false
4. 4. enable_projection=true

5. 测试结果

这里我们使用 Apache Doris 1.2.0-rc01， Apache Doris 1.1.3 及 Apache Doris 0.15.0 RC04 版本进行对比测试，测试结果如下：

结果说明

• 测试结果对应的数据集为scale 100, 约6亿条。
• 测试环境配置为用户常用配置，云服务器4台，16核 64G SSD，1 FE 3 BE 部署。
• 选用用户常见配置测试以降低用户选型评估成本，但整个测试过程中不会消耗如此多的硬件资源。
• Apache Doris 0.15 RC04 在 TPC-H 测试中 Q14 执行失败，无法完成查询。

6. 环境准备

请先参照 官方文档 进行 Doris 的安装部署，以获得一个正常运行中的 Doris 集群（至少包含 1 FE 1 BE，推荐 1 FE 3 BE）。

7. 数据准备

7.1 下载安装 TPC-H 数据生成工具

执行以下脚本下载并编译 tpch-tools 工具。

1. sh build-tpch-dbgen.sh

安装成功后，将在 TPC-H_Tools_v3.0.0/ 目录下生成 dbgen 二进制文件。

7.2 生成 TPC-H 测试集

执行以下脚本生成 TPC-H 数据集：

1. sh gen-tpch-data.sh

注1：通过 sh gen-tpch-data.sh -h 查看脚本帮助。

注2：数据会以 .tbl 为后缀生成在 tpch-data/ 目录下。文件总大小约100GB。生成时间可能在数分钟到1小时不等。

注3：默认生成 100G 的标准测试数据集

7.3 建表

7.3.1 准备 doris-cluster.conf 文件

在调用导入脚本前，需要将 FE 的 ip 端口等信息写在 doris-cluster.conf 文件中。

文件位置在 ${DORIS_HOME}/tools/tpch-tools/conf/ 目录下。

文件内容包括 FE 的 ip，HTTP 端口，用户名，密码以及待导入数据的 DB 名称：

1. # Any of FE host
2. export FE_HOST='127.0.0.1'
3. # http_port in fe.conf
4. export FE_HTTP_PORT=8030
5. # query_port in fe.conf
6. export FE_QUERY_PORT=9030
7. # Doris username
8. export USER='root'
9. # Doris password
10. export PASSWORD=''
11. # The database where TPC-H tables located
12. export DB='tpch1'

7.3.2 执行以下脚本生成创建 TPC-H 表

1. sh create-tpch-tables.sh

或者复制 create-tpch-tables.sql 中的建表语句，在 Doris 中执行。

7.4 导入数据

通过下面的命令执行数据导入：

1. sh ./load-tpch-data.sh

7.5 检查导入数据

执行下面的 SQL 语句检查导入的数据与上面的数据量是一致。

1. select count(*)  from  lineitem;
2. select count(*)  from  orders;
3. select count(*)  from  partsupp;
4. select count(*)  from  part;
5. select count(*)  from  customer;
6. select count(*)  from  supplier;
7. select count(*)  from  nation;
8. select count(*)  from  region;
9. select count(*)  from  revenue0;

7.6 查询测试

7.6.1 执行查询脚本

执行上面的测试 SQL 或者 执行下面的命令

1. ./run-tpch-queries.sh

注意：

1. 目前Doris的查询优化器和统计信息功能还不完善，所以我们在TPC-H中重写了一些查询以适应Doris的执行框架，但不影响结果的正确性
2. Doris 新的查询优化器将在后续的版本中发布
3. 执行查询之前设置 set mem_exec_limit=8G

7.6.2 单个 SQL 执行

下面是测试时使用的 SQL 语句，你也可以从代码库里获取最新的 SQL 。最新测试查询语句地址：TPC-H 测试查询语句

1. --Q1
2. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=false) */
3. l_returnflag,
4. l_linestatus,
5. sum(l_quantity) as sum_qty,
6. sum(l_extendedprice) as sum_base_price,
7. sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
8. sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
9. avg(l_quantity) as avg_qty,
10. avg(l_extendedprice) as avg_price,
11. avg(l_discount) as avg_disc,
12. count(*) as count_order
13. from
14. lineitem
15. where
16. l_shipdate <= date '1998-12-01' - interval '90' day
17. group by
18. l_returnflag,
19. l_linestatus
20. order by
21. l_returnflag,
22. l_linestatus;

24. --Q2
25. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
26. s_acctbal,
27. s_name,
28. n_name,
29. p_partkey,
30. p_mfgr,
31. s_address,
32. s_phone,
33. s_comment
34. from
35. partsupp join
36. (
37. select
38. ps_partkey as a_partkey,
39. min(ps_supplycost) as a_min
40. from
41. partsupp,
42. part,
43. supplier,
44. nation,
45. region
46. where
47. p_partkey = ps_partkey
48. and s_suppkey = ps_suppkey
49. and s_nationkey = n_nationkey
50. and n_regionkey = r_regionkey
51. and r_name = 'EUROPE'
52. and p_size = 15
53. and p_type like '%BRASS'
54. group by a_partkey
55. ) A on ps_partkey = a_partkey and ps_supplycost=a_min ,
56. part,
57. supplier,
58. nation,
59. region
60. where
61. p_partkey = ps_partkey
62. and s_suppkey = ps_suppkey
63. and p_size = 15
64. and p_type like '%BRASS'
65. and s_nationkey = n_nationkey
66. and n_regionkey = r_regionkey
67. and r_name = 'EUROPE'

69. order by
70. s_acctbal desc,
71. n_name,
72. s_name,
73. p_partkey
74. limit 100;

76. --Q3
77. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true, runtime_filter_wait_time_ms=10000) */
78. l_orderkey,
79. sum(l_extendedprice * (1 - l_discount)) as revenue,
80. o_orderdate,
81. o_shippriority
82. from
83. (
84. select l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey from
85. lineitem join orders
86. where l_orderkey = o_orderkey
87. and o_orderdate < date '1995-03-15'
88. and l_shipdate > date '1995-03-15'
89. ) t1 join customer c
90. on c.c_custkey = t1.o_custkey
91. where c_mktsegment = 'BUILDING'
92. group by
93. l_orderkey,
94. o_orderdate,
95. o_shippriority
96. order by
97. revenue desc,
98. o_orderdate
99. limit 10;

101. --Q4
102. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
103. o_orderpriority,
104. count(*) as order_count
105. from
106. (
107. select
108. *
109. from
110. lineitem
111. where l_commitdate < l_receiptdate
112. ) t1
113. right semi join orders
114. on t1.l_orderkey = o_orderkey
115. where
116. o_orderdate >= date '1993-07-01'
117. and o_orderdate < date '1993-07-01' + interval '3' month
118. group by
119. o_orderpriority
120. order by
121. o_orderpriority;

123. --Q5
124. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
125. n_name,
126. sum(l_extendedprice * (1 - l_discount)) as revenue
127. from
128. customer,
129. orders,
130. lineitem,
131. supplier,
132. nation,
133. region
134. where
135. c_custkey = o_custkey
136. and l_orderkey = o_orderkey
137. and l_suppkey = s_suppkey
138. and c_nationkey = s_nationkey
139. and s_nationkey = n_nationkey
140. and n_regionkey = r_regionkey
141. and r_name = 'ASIA'
142. and o_orderdate >= date '1994-01-01'
143. and o_orderdate < date '1994-01-01' + interval '1' year
144. group by
145. n_name
146. order by
147. revenue desc;

149. --Q6
150. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
151. sum(l_extendedprice * l_discount) as revenue
152. from
153. lineitem
154. where
155. l_shipdate >= date '1994-01-01'
156. and l_shipdate < date '1994-01-01' + interval '1' year
157. and l_discount between .06 - 0.01 and .06 + 0.01
158. and l_quantity < 24;

160. --Q7
161. select /*+SET_VAR(exec_mem_limit=458589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
162. supp_nation,
163. cust_nation,
164. l_year,
165. sum(volume) as revenue
166. from
167. (
168. select
169. n1.n_name as supp_nation,
170. n2.n_name as cust_nation,
171. extract(year from l_shipdate) as l_year,
172. l_extendedprice * (1 - l_discount) as volume
173. from
174. supplier,
175. lineitem,
176. orders,
177. customer,
178. nation n1,
179. nation n2
180. where
181. s_suppkey = l_suppkey
182. and o_orderkey = l_orderkey
183. and c_custkey = o_custkey
184. and s_nationkey = n1.n_nationkey
185. and c_nationkey = n2.n_nationkey
186. and (
187. (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
188. or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
189. )
190. and l_shipdate between date '1995-01-01' and date '1996-12-31'
191. ) as shipping
192. group by
193. supp_nation,
194. cust_nation,
195. l_year
196. order by
197. supp_nation,
198. cust_nation,
199. l_year;

201. --Q8

203. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
204. o_year,
205. sum(case
206. when nation = 'BRAZIL' then volume
207. else 0
208. end) / sum(volume) as mkt_share
209. from
210. (
211. select
212. extract(year from o_orderdate) as o_year,
213. l_extendedprice * (1 - l_discount) as volume,
214. n2.n_name as nation
215. from
216. lineitem,
217. orders,
218. customer,
219. supplier,
220. part,
221. nation n1,
222. nation n2,
223. region
224. where
225. p_partkey = l_partkey
226. and s_suppkey = l_suppkey
227. and l_orderkey = o_orderkey
228. and o_custkey = c_custkey
229. and c_nationkey = n1.n_nationkey
230. and n1.n_regionkey = r_regionkey
231. and r_name = 'AMERICA'
232. and s_nationkey = n2.n_nationkey
233. and o_orderdate between date '1995-01-01' and date '1996-12-31'
234. and p_type = 'ECONOMY ANODIZED STEEL'
235. ) as all_nations
236. group by
237. o_year
238. order by
239. o_year;

241. --Q9
242. select/*+SET_VAR(exec_mem_limit=37179869184, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true, enable_remove_no_conjuncts_runtime_filter_policy=true, runtime_filter_wait_time_ms=100000) */
243. nation,
244. o_year,
245. sum(amount) as sum_profit
246. from
247. (
248. select
249. n_name as nation,
250. extract(year from o_orderdate) as o_year,
251. l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
252. from
253. lineitem join orders on o_orderkey = l_orderkey
254. join[shuffle] part on p_partkey = l_partkey
255. join[shuffle] partsupp on ps_partkey = l_partkey
256. join[shuffle] supplier on s_suppkey = l_suppkey
257. join[broadcast] nation on s_nationkey = n_nationkey
258. where
259. ps_suppkey = l_suppkey and
260. p_name like '%green%'
261. ) as profit
262. group by
263. nation,
264. o_year
265. order by
266. nation,
267. o_year desc;

269. --Q10

271. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */
272. c_custkey,
273. c_name,
274. sum(t1.l_extendedprice * (1 - t1.l_discount)) as revenue,
275. c_acctbal,
276. n_name,
277. c_address,
278. c_phone,
279. c_comment
280. from
281. customer,
282. (
283. select o_custkey,l_extendedprice,l_discount from lineitem, orders
284. where l_orderkey = o_orderkey
285. and o_orderdate >= date '1993-10-01'
286. and o_orderdate < date '1993-10-01' + interval '3' month
287. and l_returnflag = 'R'
288. ) t1,
289. nation
290. where
291. c_custkey = t1.o_custkey
292. and c_nationkey = n_nationkey
293. group by
294. c_custkey,
295. c_name,
296. c_acctbal,
297. c_phone,
298. n_name,
299. c_address,
300. c_comment
301. order by
302. revenue desc
303. limit 20;

305. --Q11
306. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
307. ps_partkey,
308. sum(ps_supplycost * ps_availqty) as value
309. from
310. partsupp,
311. (
312. select s_suppkey
313. from supplier, nation
314. where s_nationkey = n_nationkey and n_name = 'GERMANY'
315. ) B
316. where
317. ps_suppkey = B.s_suppkey
318. group by
319. ps_partkey having
320. sum(ps_supplycost * ps_availqty) > (
321. select
322. sum(ps_supplycost * ps_availqty) * 0.000002
323. from
324. partsupp,
325. (select s_suppkey
326. from supplier, nation
327. where s_nationkey = n_nationkey and n_name = 'GERMANY'
328. ) A
329. where
330. ps_suppkey = A.s_suppkey
331. )
332. order by
333. value desc;

335. --Q12

337. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
338. l_shipmode,
339. sum(case
340. when o_orderpriority = '1-URGENT'
341. or o_orderpriority = '2-HIGH'
342. then 1
343. else 0
344. end) as high_line_count,
345. sum(case
346. when o_orderpriority <> '1-URGENT'
347. and o_orderpriority <> '2-HIGH'
348. then 1
349. else 0
350. end) as low_line_count
351. from
352. orders,
353. lineitem
354. where
355. o_orderkey = l_orderkey
356. and l_shipmode in ('MAIL', 'SHIP')
357. and l_commitdate < l_receiptdate
358. and l_shipdate < l_commitdate
359. and l_receiptdate >= date '1994-01-01'
360. and l_receiptdate < date '1994-01-01' + interval '1' year
361. group by
362. l_shipmode
363. order by
364. l_shipmode;

366. --Q13
367. select /*+SET_VAR(exec_mem_limit=45899345920, parallel_fragment_exec_instance_num=16, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
368. c_count,
369. count(*) as custdist
370. from
371. (
372. select
373. c_custkey,
374. count(o_orderkey) as c_count
375. from
376. orders right outer join customer on
377. c_custkey = o_custkey
378. and o_comment not like '%special%requests%'
379. group by
380. c_custkey
381. ) as c_orders
382. group by
383. c_count
384. order by
385. custdist desc,
386. c_count desc;

388. --Q14

390. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true, runtime_filter_mode=OFF) */
391. 100.00 * sum(case
392. when p_type like 'PROMO%'
393. then l_extendedprice * (1 - l_discount)
394. else 0
395. end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
396. from
397. part,
398. lineitem
399. where
400. l_partkey = p_partkey
401. and l_shipdate >= date '1995-09-01'
402. and l_shipdate < date '1995-09-01' + interval '1' month;

404. --Q15
405. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
406. s_suppkey,
407. s_name,
408. s_address,
409. s_phone,
410. total_revenue
411. from
412. supplier,
413. revenue0
414. where
415. s_suppkey = supplier_no
416. and total_revenue = (
417. select
418. max(total_revenue)
419. from
420. revenue0
421. )
422. order by
423. s_suppkey;

425. --Q16
426. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
427. p_brand,
428. p_type,
429. p_size,
430. count(distinct ps_suppkey) as supplier_cnt
431. from
432. partsupp,
433. part
434. where
435. p_partkey = ps_partkey
436. and p_brand <> 'Brand#45'
437. and p_type not like 'MEDIUM POLISHED%'
438. and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
439. and ps_suppkey not in (
440. select
441. s_suppkey
442. from
443. supplier
444. where
445. s_comment like '%Customer%Complaints%'
446. )
447. group by
448. p_brand,
449. p_type,
450. p_size
451. order by
452. supplier_cnt desc,
453. p_brand,
454. p_type,
455. p_size;

457. --Q17
458. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */
459. sum(l_extendedprice) / 7.0 as avg_yearly
460. from
461. lineitem join [broadcast]
462. part p1 on p1.p_partkey = l_partkey
463. where
464. p1.p_brand = 'Brand#23'
465. and p1.p_container = 'MED BOX'
466. and l_quantity < (
467. select
468. 0.2 * avg(l_quantity)
469. from
470. lineitem join [broadcast]
471. part p2 on p2.p_partkey = l_partkey
472. where
473. l_partkey = p1.p_partkey
474. and p2.p_brand = 'Brand#23'
475. and p2.p_container = 'MED BOX'
476. );

478. --Q18

480. select /*+SET_VAR(exec_mem_limit=45899345920, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
481. c_name,
482. c_custkey,
483. t3.o_orderkey,
484. t3.o_orderdate,
485. t3.o_totalprice,
486. sum(t3.l_quantity)
487. from
488. customer join
489. (
490. select * from
491. lineitem join
492. (
493. select * from
494. orders left semi join
495. (
496. select
497. l_orderkey
498. from
499. lineitem
500. group by
501. l_orderkey having sum(l_quantity) > 300
502. ) t1
503. on o_orderkey = t1.l_orderkey
504. ) t2
505. on t2.o_orderkey = l_orderkey
506. ) t3
507. on c_custkey = t3.o_custkey
508. group by
509. c_name,
510. c_custkey,
511. t3.o_orderkey,
512. t3.o_orderdate,
513. t3.o_totalprice
514. order by
515. t3.o_totalprice desc,
516. t3.o_orderdate
517. limit 100;

519. --Q19

521. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=true) */
522. sum(l_extendedprice* (1 - l_discount)) as revenue
523. from
524. lineitem,
525. part
526. where
527. (
528. p_partkey = l_partkey
529. and p_brand = 'Brand#12'
530. and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
531. and l_quantity >= 1 and l_quantity <= 1 + 10
532. and p_size between 1 and 5
533. and l_shipmode in ('AIR', 'AIR REG')
534. and l_shipinstruct = 'DELIVER IN PERSON'
535. )
536. or
537. (
538. p_partkey = l_partkey
539. and p_brand = 'Brand#23'
540. and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
541. and l_quantity >= 10 and l_quantity <= 10 + 10
542. and p_size between 1 and 10
543. and l_shipmode in ('AIR', 'AIR REG')
544. and l_shipinstruct = 'DELIVER IN PERSON'
545. )
546. or
547. (
548. p_partkey = l_partkey
549. and p_brand = 'Brand#34'
550. and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
551. and l_quantity >= 20 and l_quantity <= 20 + 10
552. and p_size between 1 and 15
553. and l_shipmode in ('AIR', 'AIR REG')
554. and l_shipinstruct = 'DELIVER IN PERSON'
555. );

557. --Q20
558. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true, runtime_bloom_filter_size=551943) */
559. s_name, s_address from
560. supplier left semi join
561. (
562. select * from
563. (
564. select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q
565. from lineitem
566. where l_shipdate >= date '1994-01-01'
567. and l_shipdate < date '1994-01-01' + interval '1' year
568. group by l_partkey,l_suppkey
569. ) t2 join
570. (
571. select ps_partkey, ps_suppkey, ps_availqty
572. from partsupp left semi join part
573. on ps_partkey = p_partkey and p_name like 'forest%'
574. ) t1
575. on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey
576. and t1.ps_availqty > t2.l_q
577. ) t3
578. on s_suppkey = t3.ps_suppkey
579. join nation
580. where s_nationkey = n_nationkey
581. and n_name = 'CANADA'
582. order by s_name;

584. --Q21
585. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */
586. s_name, count(*) as numwait
587. from
588. lineitem l2 right semi join
589. (
590. select * from
591. lineitem l3 right anti join
592. (
593. select * from
594. orders join lineitem l1 on l1.l_orderkey = o_orderkey and o_orderstatus = 'F'
595. join
596. (
597. select * from
598. supplier join nation
599. where s_nationkey = n_nationkey
600. and n_name = 'SAUDI ARABIA'
601. ) t1
602. where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate
603. ) t2
604. on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey  and l3.l_receiptdate > l3.l_commitdate
605. ) t3
606. on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey

608. group by
609. t3.s_name
610. order by
611. numwait desc,
612. t3.s_name
613. limit 100;

615. --Q22

617. with tmp as (select
618. avg(c_acctbal) as av
619. from
620. customer
621. where
622. c_acctbal > 0.00
623. and substring(c_phone, 1, 2) in
624. ('13', '31', '23', '29', '30', '18', '17'))

626. select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4,runtime_bloom_filter_size=4194304) */
627. cntrycode,
628. count(*) as numcust,
629. sum(c_acctbal) as totacctbal
630. from
631. (
632. select
633. substring(c_phone, 1, 2) as cntrycode,
634. c_acctbal
635. from
636. orders right anti join customer c on  o_custkey = c.c_custkey join tmp on c.c_acctbal > tmp.av
637. where
638. substring(c_phone, 1, 2) in
639. ('13', '31', '23', '29', '30', '18', '17')
640. ) as custsale
641. group by
642. cntrycode
643. order by
644. cntrycode;