TPC-H Benchmark

TPC-H is a decision support benchmark (Decision Support Benchmark), which consists of a set of business-oriented special query and concurrent data modification. The data that is queried and populates the database has broad industry relevance. This benchmark demonstrates a decision support system that examines large amounts of data, executes highly complex queries, and answers key business questions. The performance index reported by TPC-H is called TPC-H composite query performance index per hour (QphH@Size), which reflects multiple aspects of the system’s ability to process queries. These aspects include the database size chosen when executing the query, the query processing capability when the query is submitted by a single stream, and the query throughput when the query is submitted by many concurrent users.

This document mainly introduces the performance of Doris on the TPC-H 100G test set.

Note 1: The standard test set including TPC-H is usually far from the actual business scenario, and some tests will perform parameter tuning for the test set. Therefore, the test results of the standard test set can only reflect the performance of the database in a specific scenario. We suggest users use actual business data for further testing.

Note 2: The operations involved in this document are all tested on CentOS 7.x.

On 22 queries on the TPC-H standard test data set, we conducted a comparison test based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 versions. Compared with Apache Doris 1.1.3, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 3 times, and by nearly 11 times compared with Apache Doris 0.15.0 RC04.

1. Hardware Environment

2. Software Environment

• Doris Deployed 3BEs and 1FE
• Kernel Version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
• OS version: CentOS 7.8
• Doris software version: 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. Test Data Volume

The TPCH 100G data generated by the simulation of the entire test are respectively imported into Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 for testing. The following is the relevant description and data volume of the table.

4. Test SQL

TPCH 22 test query statements ： TPCH-Query-SQL

Notice:

The following four parameters in the above SQL do not exist in Apache Doris 0.15.0 RC04. When executing, please remove:

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

5. Test Results

Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 for comparative testing. In the test, we use Query Time(ms) as the main performance indicator. The test results are as follows:

• Result Description
• The data set corresponding to the test results is scale 100, about 600 million.
• The test environment is configured as the user’s common configuration, with 4 cloud servers, 16-core 64G SSD, and 1 FE 3 BEs deployment.
• Select the user’s common configuration test to reduce the cost of user selection and evaluation, but the entire test process will not consume so many hardware resources.
• Apache Doris 0.15 RC04 failed to execute Q14 in the TPC-H test, unable to complete the query.

6. Environmental Preparation

Please refer to the official document to install and deploy Doris to obtain a normal running Doris cluster (at least 1 FE 1 BE, 1 FE 3 BE is recommended).

7. Data Preparation

7.1 Download and Install TPC-H Data Generation Tool

Execute the following script to download and compile the tpch-tools tool.

1. sh build-tpch-dbgen.sh

After successful installation, the dbgen binary will be generated under the TPC-H_Tools_v3.0.0/ directory.

7.2 Generating the TPC-H Test Set

Execute the following script to generate the TPC-H dataset:

1. sh gen-tpch-data.sh

Note 1: Check the script help via sh gen-tpch-data.sh -h.

Note 2: The data will be generated under the tpch-data/ directory with the suffix .tbl. The total file size is about 100GB and may need a few minutes to an hour to generate.

Note 3: A standard test data set of 100G is generated by default.

7.3 Create Table

7.3.1 Prepare the doris-cluster.conf File

Before import the script, you need to write the FE’s ip port and other information in the doris-cluster.conf file.

The file is located under ${DORIS_HOME}/tools/tpch-tools/conf/ .

The content of the file includes FE’s ip, HTTP port, user name, password and the DB name of the data to be imported:

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'

Execute the Following Script to Generate and Create TPC-H Table

1. sh create-tpch-tables.sh

Or copy the table creation statement in create-tpch-tables.sql and excute it in Doris.

7.4 Import Data

Please perform data import with the following command:

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

7.5 Check Imported Data

Execute the following SQL statement to check that the imported data is consistent with the above data.

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 Query Test

7.6.1 Executing Query Scripts

Execute the above test SQL or execute the following command

1. ./run-tpch-queries.sh

Notice:

1. At present, the query optimizer and statistics functions of Doris are not so perfect, so we rewrite some queries in TPC-H to adapt to the execution framework of Doris, but it does not affect the correctness of the results
2. Doris’ new query optimizer will be released in future versions
3. Set set mem_exec_limit=8G before executing the query

7.6.2 Single SQL Execution

The following is the SQL statement used in the test, you can also get the latest SQL from the code base.

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;