--- title: "TPC-H | OceanBase 文档中心" description: TPC-H TPC-H简介 TPC-H（商业智能计算测试）是美国交易处理效能委员会（ TPC，Transaction Processing Performance Council ）组织制定的用来模拟决策支持类应用的一个测试集。目前，学术界和工业界普遍采用 TPC-H 来评价决策支持技术方面应用的性能。这种商业测试可… image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS # TPC-H 更新时间：2023-07-19 13:03:06 ## TPC-H简介 TPC-H（商业智能计算测试）是美国交易处理效能委员会（ TPC，Transaction Processing Performance Council ）组织制定的用来模拟决策支持类应用的一个测试集。目前，学术界和工业界普遍采用 TPC-H 来评价决策支持技术方面应用的性能。这种商业测试可以全方位评测系统的整体商业计算综合能力，对厂商的要求更高，同时也具有普遍的商业实用意义，目前在银行信贷分析和信用卡分析、电信运营分析、税收分析、烟草行业决策分析中都有广泛的应用。 TPC-H 基准测试由 TPC-D（由 TPC 于 1994 年制定的标准，用于决策支持系统方面的测试基准）发展而来的。TPC-H 用 3NF 实现了一个数据仓库，共包含 8 个基本关系，其主要评价指标是各个查询的响应时间，即从提交查询到结果返回所需时间。TPC-H 基准测试的度量单位是每小时执行的查询数（ QphH@size ），其中 H 表示每小时系统执行复杂查询的平均次数，size 表示数据库规模的大小，它能够反映出系统在处理查询时的能力。TPC-H 是根据真实的生产运行环境来建模的，这使得它可以评估一些其他测试所不能评估的关键性能参数。总而言之，TPC 组织颁布的 TPC-H 标准满足了数据仓库领域的测试需求，并且促使各个厂商以及研究机构将该项技术推向极限。 ## 测试方案 - OceanBase 集群部署规模为 1：1：1。部署成功后，新建执行 TPC-H 测试的租户及用户（`sys` 租户是管理集群的内置系统租户，请勿直接使用该租户进行测试），将租户的 `PRIMARY_ZONE` 设置为 `RANDOM`。 - 测试数据量：100 G。 ## 测试环境 - 硬件配置 | 项目类型 | 内存 | CPU | | --- | --- | --- | | OceanBase 数据库 | 3 台 * 128 G | 3 台 * 32 核 | | TPC-H | 1 台 * 128 G | 1 台 * 32 核 | - 租户规格 ```unknow CREATE RESOURCE UNIT box1 MAX_CPU 26, MAX_MEMORY 60554378624, MAX_IOPS 128, MAX_DISK_SIZE 536870912, MAX_SESSION_NUM 64, MIN_CPU=26, MIN_MEMORY=60554378624, MIN_IOPS=128; CREATE RESOURCE POOL fpool UNIT='box1', UNIT_NUM=1, ZONE_LIST=('zone1','zone2','zone3'); CREATE TENANT tt3 RESOURCE_POOL_LIST=('fpool'), PRIMARY_ZONE='RANDOM', LOCALITY='F@zone1,F@zone2,F@zone3' SET ob_compatibility_mode='mysql',ob_tcp_invited_nodes='%'; ``` > **说明** > > > - 您需根据自身数据库的硬件配置进行动态调整。详细操作，参考 [修改租户](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000355948)。 > - 进行性能测试时，租户需配置最大可用资源。 - 软件版本 | 服务类型 | 软件版本及型号 | | --- | --- | | OceanBase 数据库 | V3.2.3 | | TPC-H | V3.0.0 | | OS | CentOS Linux release 7.9.2009 (Core) | | CPU | Intel(R) Xeon(R) Platinum 8269CY CPU @ 2.50GHz | ## 环境配置 ### 操作系统信息 ```unknow $cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) ``` ```unknow $uname -a Linux testdriver 3.10.0-1160.24.1.el7.x86_64 #1 SMP Thu Apr 8 19:51:47 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux ``` ### CPU 信息 ```unknow $cat /proc/cpuinfo processor : 31 vendor_id : GenuineIntel cpu family : 6 model : 85 model name : Intel(R) Xeon(R) Platinum 8269CY CPU @ 2.50GHz stepping : 7 microcode : 0x1 cpu MHz : 2500.002 cache size : 36608 KB physical id : 0 siblings : 32 core id : 15 cpu cores : 16 apicid : 31 initial apicid : 31 fpu : yes fpu_exception : yes cpuid level : 22 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl nonstop_tsc eagerfpu pni pclmulqdq monitor ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat avx512_vnni bogomips : 5000.00 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: ``` ### 内存信息 ```unknow $free -g total used free shared buff/cache available Mem: 123 41 2 0 79 81 Swap: 0 0 0 ``` ### OBServer 软件信息 ```unknow $./observer -V observer (OceanBase 3.2.3) REVISION: 20220125174436-b765b7557c36d62bcd42597142fe0ee34bd21f6e BUILD_BRANCH: 3_2_x_release BUILD_TIME: Jan 25 2022 17:56:14 BUILD_FLAGS: RelWithDebInfo BUILD_INFO: Copyright (c) 2011-2020 Alipay Inc. ``` ## 调优参数 请使用测试租户登录，并执行以下命令： ```unknow ALTER SYSTEM SET enable_merge_by_turn=False; ALTER SYSTEM SET trace_log_slow_query_watermark='100s'; ALTER SYSTEM SET max_kept_major_version_number=1; ALTER SYSTEM SET enable_sql_operator_dump=True; ALTER SYSTEM SET _hash_area_size='3g' tenant='tpch_mysql'; ALTER SYSTEM SET memstore_limit_percentage=50; ALTER SYSTEM SET enable_clog_persistence_compress=true; ALTER SYSTEM SET _bloom_filter_ratio=10; ALTER SYSTEM SET clog_persistence_compress_func="zstd_1.0"; ALTER SYSTEM SET minor_freeze_times=5; ALTER SYSTEM SET cache_wash_threshold='30g'; ALTER SYSTEM SET _pushdown_storage_level=3 tenant all; ALTER SYSTEM SET _rowsets_enabled=true tenant all; ALTER SYSTEM SET _parallel_server_sleep_time=10; ALTER SYSTEM SET merge_thread_count=64; ALTER SYSTEM SET cpu_quota_concurrency=4; ALTER SYSTEM SET server_data_copy_out_concurrency=1000; ALTER SYSTEM SET server_data_copy_in_concurrency=1000; ALTER SYSTEM SET memory_chunk_cache_size=0; ALTER SYSTEM SET minor_freeze_times=200; ALTER SYSTEM SET clog_transport_compress_all=false; ALTER SYSTEM SET trx_try_wait_lock_timeout='0ms'; ALTER SYSTEM SET large_query_threshold='1s'; ALTER SYSTEM SET syslog_io_bandwidth_limit='30m'; ALTER SYSTEM SET enable_async_syslog=true; ALTER SYSTEM SET merger_warm_up_duration_time='0'; ALTER SYSTEM SET merger_switch_leader_duration_time='0'; ALTER SYSTEM SET large_query_worker_percentage=10; ALTER SYSTEM SET minor_compact_trigger = 2; ALTER SYSTEM SET builtin_db_data_verify_cycle = 0; ALTER SYSTEM SET micro_block_merge_verify_level=0; ALTER SYSTEM SET freeze_trigger_percentage=50; ALTER SYSTEM SET sys_bkgd_io_low_percentage=70; ALTER SYSTEM SET _mini_merge_concurrency=5; ALTER SYSTEM SET enable_sql_audit=false; ALTER SYSTEM SET enable_perf_event=false; ALTER SYSTEM SET syslog_level='PERF'; ALTER SYSTEM SET max_syslog_file_count=100; ALTER SYSTEM SET enable_syslog_recycle='True'; SET GLOBAL _groupby_nopushdown_cut_ratio=1; SET GLOBAL _nlj_batching_enabled=true; SET GLOBAL ob_sql_work_area_percentage=80; SET GLOBAL optimizer_use_sql_plan_baselines=true; SET GLOBAL optimizer_capture_sql_plan_baselines=true; SET GLOBAL ob_query_timeout=36000000000; SET GLOBAL ob_trx_timeout=36000000000; SET GLOBAL max_allowed_packet=67108864; SET GLOBAL secure_file_priv=""; SET GLOBAL parallel_servers_target=624; ``` > **注意** > > > > 对于存储下压参数（`_pushdown_storage_level`）和 SQL 引擎向量化参数（`_rowsets_enabled`），如果非第一次设置，必须通过 `ALTER SYSTEM FLUSH PLAN CACHE GLOBAL;` 语句刷新 PLAN CACHE 才会生效。 ## Schema > **说明** > > > > 分区数设置为所有机器的 CPU 总核数，例如：3 台 32 核 的机器，则设置为 96。 ```unknow ALTER SYSTEM SET ob_enable_batched_multi_statement='false'; SET GLOBAL ob_query_timeout=14400000000; SET GLOBAL ob_trx_timeout=10000000000; SET ob_query_timeout=14400000000; SET ob_trx_timeout=10000000000; SET GLOBAL secure_file_priv=""; SET GLOBAL autocommit=1; SET GLOBAL optimizer_use_sql_plan_baselines=true; SET GLOBAL optimizer_capture_sql_plan_baselines=true; CREATE TABLEGROUP IF NOT EXISTS tpch_tg_SF_TPC_USER_lineitem_order_group BINDING true PARTITION BY KEY 1 PARTITIONS 96; CREATE TABLEGROUP IF NOT EXISTS tpch_tg_SF_TPC_USER_partsupp_part BINDING true PARTITION BY KEY 1 PARTITIONS 96; DROP TABLE IF EXISTS LINEITEM; CREATE TABLE lineitem ( l_orderkey BIGINT NOT NULL, l_partkey BIGINT NOT NULL, l_suppkey INTEGER NOT NULL, l_linenumber INTEGER NOT NULL, l_quantity NUMBER(12,2) NOT NULL, l_extendedprice NUMBER(12,2) NOT NULL, l_discount NUMBER(12,2) NOT NULL, l_tax NUMBER(12,2) NOT NULL, l_returnflag char(1) DEFAULT NULL, l_linestatus char(1) DEFAULT NULL, l_shipdate date NOT NULL, l_commitdate date DEFAULT NULL, l_receiptdate date DEFAULT NULL, l_shipinstruct char(25) DEFAULT NULL, l_shipmode char(10) DEFAULT NULL, l_comment varchar(44) DEFAULT NULL, PRIMARY KEY(l_orderkey, l_linenumber) )row_format = condensed tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group PARTITION BY KEY(l_orderkey) PARTITIONS 96; DROP TABLE IF EXISTS ORDERS; CREATE TABLE orders ( o_orderkey BIGINT NOT NULL, o_custkey BIGINT NOT NULL, o_orderstatus char(1) DEFAULT NULL, o_totalprice NUMBER(12,2) DEFAULT NULL, o_orderdate date NOT NULL, o_orderpriority char(15) DEFAULT NULL, o_clerk char(15) DEFAULT NULL, o_shippriority INTEGER DEFAULT NULL, o_comment varchar(79) DEFAULT NULL, PRIMARY KEY (o_orderkey) )row_format = condensed tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group PARTITION BY KEY(o_orderkey) PARTITIONS 96; DROP TABLE IF EXISTS PARTSUPP; CREATE TABLE partsupp ( ps_partkey BIGINT NOT NULL, ps_suppkey INTEGER NOT NULL, ps_availqty INTEGER DEFAULT NULL, ps_supplycost NUMBER(12,2) DEFAULT NULL, ps_comment varchar(199) DEFAULT NULL, PRIMARY KEY (ps_partkey, ps_suppkey)) row_format = condensed tablegroup tpch_tg_SF_TPC_USER_partsupp_part PARTITION BY KEY(ps_partkey) PARTITIONS 96; DROP TABLE IF EXISTS PART; CREATE TABLE part ( p_partkey BIGINT NOT NULL, p_name varchar(55) DEFAULT NULL, p_mfgr char(25) DEFAULT NULL, p_brand char(10) DEFAULT NULL, p_type varchar(25) DEFAULT NULL, p_size INTEGER DEFAULT NULL, p_container char(10) DEFAULT NULL, p_retailprice NUMBER(12,2) DEFAULT NULL, p_comment varchar(23) DEFAULT NULL, PRIMARY KEY (p_partkey)) row_format = condensed tablegroup tpch_tg_SF_TPC_USER_partsupp_part PARTITION BY KEY(p_partkey) PARTITIONS 96; DROP TABLE IF EXISTS CUSTOMER; CREATE TABLE customer ( c_custkey BIGINT NOT NULL, c_name varchar(25) DEFAULT NULL, c_address varchar(40) DEFAULT NULL, c_nationkey INTEGER DEFAULT NULL, c_phone char(15) DEFAULT NULL, c_acctbal NUMBER(12,2) DEFAULT NULL, c_mktsegment char(10) DEFAULT NULL, c_comment varchar(117) DEFAULT NULL, PRIMARY KEY (c_custkey)) row_format = condensed PARTITION BY KEY(c_custkey) PARTITIONS 96; DROP TABLE IF EXISTS SUPPLIER; CREATE TABLE supplier ( s_suppkey INTEGER NOT NULL, s_name char(25) DEFAULT NULL, s_address varchar(40) DEFAULT NULL, s_nationkey INTEGER DEFAULT NULL, s_phone char(15) DEFAULT NULL, s_acctbal NUMBER(12,2) DEFAULT NULL, s_comment varchar(101) DEFAULT NULL, PRIMARY KEY (s_suppkey) ) row_format = condensed PARTITION BY KEY(s_suppkey) PARTITIONS 96; DROP TABLE IF EXISTS NATION; CREATE TABLE nation ( n_nationkey INTEGER NOT NULL, n_name char(25) DEFAULT NULL, n_regionkey INTEGER DEFAULT NULL, n_comment varchar(152) DEFAULT NULL, PRIMARY KEY (n_nationkey) ) row_format = condensed; DROP TABLE IF EXISTS REGION; CREATE TABLE region ( r_regionkey INTEGER NOT NULL, r_name char(25) DEFAULT NULL, r_comment varchar(152) DEFAULT NULL, PRIMARY KEY (r_regionkey) ) row_format = condensed; ``` ## 执行 SQL > **说明** > > > > 在执行 SQL 查询之前，需要先通过 `ALTER SYSTEM MAJOR FREEZE;` 语句做合并操作。 #### Q1 ```unknow select /*+ TPCH_Q1 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q2 PARALLEL(96) */ * from( select 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 = 45 and p_type like '%STEEL' 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) limit 100; ``` #### Q3 ```unknow select /*+ TPCH_Q3 PARALLEL(96) */ 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 limit 10; ``` #### Q4 ```unknow SELECT /*+ TPCH_Q4 PARALLEL(96) */ 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;SELECT /*+ TPCH_Q4 parallel(256) */ 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 ```unknow select /*+ TPCH_Q5 PARALLEL(96) */ 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 c_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' and o_orderdate >= date '1995-01-01' and o_orderdate < date '1995-01-01' + interval '1' year group by n_name order by revenue desc; ``` #### Q6 ```unknow select /*+ TPCH_Q6 PARALLEL(96) */ sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24; ``` #### Q7 ```unknow select /*+ TPCH_Q7 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q8 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q9 PARALLEL(96) */ 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 = ps_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%indian%' ) profit group by nation, o_year order by nation, o_year desc; ``` #### Q10 ```unknow SELECT * FROM (SELECT /*+ TPCH_Q10 PARALLEL(96) */ * from ( SELECT 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) ) limit 20; ``` #### Q11 ```unknow select /*+ TPCH_Q11 PARALLEL(96) */ 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 = 'GERMANY' group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0000100000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY' ) order by value desc; ``` #### Q12 ```unknow select /*+ TPCH_Q12 PARALLEL(96) */ 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 ```unknow SELECT /*+ TPCH_Q13 PARALLEL(96) */ c_count, count(*) as custdist from ( SELECT c_custkey, count(o_orderkey) as c_count from customer left join orders on c_custkey = o_custkey and o_comment not like '%special%requests%' group by c_custkey ) c_orders group by c_count order by custdist desc, c_count desc; ``` #### Q14 ```unknow select /*+ TPCH_Q14 PARALLEL(96) */ 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 ```unknow create view revenue0 (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 /*+ TPCH_Q15 PARALLEL(96) */ 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; drop view revenue0; ``` #### Q16 ```unknow select /*+ TPCH_Q16 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q17 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q18 PARALLEL(96) */ 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 limit 100; ``` #### Q19 ```unknow select /*+ TPCH_Q19 PARALLEL(96) */ 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 ```unknow select /*+ TPCH_Q20 PARALLEL(96) */ 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 ```unknow select * from ( select /*+ PARALLEL(96) */ 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 = 'CHINA' group by s_name order by numwait desc, s_name ) limit 100; ``` #### Q22 ```unknow select /*+ TPCH_Q22 PARALLEL(96) */ 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; ``` ## 测试结果 | Query | TPCH_100G（单位：s） | | --- | --- | | Q1 | 2.91 | | Q2 | 0.93 | | Q3 | 1.95 | | Q4 | 1.05 | | Q5 | 4.74 | | Q6 | 0.57 | | Q7 | 2.85 | | Q8 | 2.13 | | Q9 | 7.86 | | Q10 | 2.58 | | Q11 | 1.11 | | Q12 | 1.74 | | Q13 | 5.07 | | Q14 | 0.75 | | Q15 | 2.01 | | Q16 | 2.58 | | Q17 | 1.14 | | Q18 | 1.56 | | Q19 | 1.62 | | Q20 | 1.92 | | Q21 | 12.06 | | Q22 | 1.53 | | **Total** | 60.66 | 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线