"TPC-H官方文档：数据库OLAP基准测试标准版3.0.0"

TPC-H

需积分: 33 77 浏览量更新于2024-04-11 收藏 1.7MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

TPC-H（决策支持）标准规范第3.0.0版是由Transaction Processing Performance Council（TPC）制定的用于数据库OLAP基准测试的官方文档。该标准规范包含了一系列测试指标和规则，用于评估决策支持系统在处理大规模数据集时的性能。TPC-H基准测试的目的是衡量系统在执行复杂查询和报表生成等决策支持任务时的效率和稳定性。 TPC-H基准测试是由TPC于1993年推出的，经过多年的发展和改进，目前已经发展到第3.0.0版。该基准测试的设计受到了广泛认可和赞誉，被广泛应用于数据库系统的性能评估和比较之中。通过执行TPC-H基准测试，用户可以了解系统在处理复杂查询和大数据集时的实际表现，从而为系统优化和性能提升提供参考依据。 TPC-H基准测试的规范包括了数据生成、查询模板、性能度量等多个方面的内容。数据生成包括了数据规模、数据分布和数据加载等内容，用户可以根据自身需求选择合适的数据集进行测试。查询模板包括了22个标准查询，涵盖了各种不同的决策支持场景，用户可以根据需求选择合适的查询进行性能测试。性能度量包括了各种性能指标，如查询执行时间、吞吐量、响应时间等，用户可以通过这些指标来评估系统的性能表现。在执行TPC-H基准测试时，用户需要按照规范的要求来进行测试，并记录测试过程中的各种数据和指标。测试结果将会被提交给TPC进行审核和认证，通过TPC认证的测试结果将可以被公开发布和比较。通过执行TPC-H基准测试，用户可以了解系统在处理决策支持任务时的真实性能表现，为系统优化和性能提升提供依据。总的来说，TPC-H基准测试是一种权威和有效的数据库OLAP性能评估方法，通过执行TPC-H基准测试，用户可以了解系统在处理复杂查询和大数据集时的性能表现，为系统优化和性能提升提供依据。TPC-H基准测试规范的不断更新和完善将继续推动数据库系统性能评估的发展，为用户提供更好的性能比较和选择参考。TPC-H基准测试对于数据库系统的性能评估和比较具有重要意义，为用户提供了一种客观、标准和有效的性能评估方法。

资源详情

资源推荐

TPC Benchmark

H Standard Specification Revision 3.0.0 Page 16

C_NAME

variable text, size 25

C_ADDRESS

variable text, size 40

C_NATIONKEY

Identifier

Foreign Key to N_NATIONKEY

C_PHONE

fixed text, size 15

C_ACCTBAL

Decimal

C_MKTSEGMENT

fixed text, size 10

C_COMMENT

variable text, size 117

Primary Key: C_CUSTKEY

ORDERS Table Layout

Column Name

Datatype Requirements

Comment

O_ORDERKEY

Identifier

SF*1,500,000 are sparsely populated

O_CUSTKEY

Identifier

Foreign Key to C_CUSTKEY

O_ORDERSTATUS

fixed text, size 1

O_TOTALPRICE

Decimal

O_ORDERDATE

Date

O_ORDERPRIORITY

fixed text, size 15

O_CLERK

fixed text, size 15

O_SHIPPRIORITY

Integer

O_COMMENT

variable text, size 79

Primary Key: O_ORDERKEY

Comment: Orders are not present for all customers. In fact, one-third of the customers do not have any order in

the database. The orders are assigned at random to two-thirds of the customers (see Clause 4: ). The purpose of

this is to exercise the capabilities of the DBMS to handle "dead data" when joining two or more tables.

LINEITEM Table Layout

Column Name

Datatype Requirements

Comment

L_ORDERKEY

identifier

Foreign Key to O_ORDERKEY

L_PARTKEY

identifier

Foreign key to P_PARTKEY, first part of the

compound Foreign Key to (PS_PARTKEY,

PS_SUPPKEY) with L_SUPPKEY

L_SUPPKEY

Identifier

Foreign key to S_SUPPKEY, second part of the

compound Foreign Key to (PS_PARTKEY,

TPC Benchmark

H Standard Specification Revision 3.0.0 Page 18

1.4.2 Constraints

The use of constraints is optional and limited to primary key, foreign key, check, and not null constraints. If

constraints are used, they must satisfy the following requirements:

• They must be specified using SQL. There is no specific implementation requirement. For example,

CREATE TABLE, ALTER TABLE, CREATE UNIQUE INDEX, and CREATE TRIGGER are all valid

statements;

• Constraints must be enforced either at the statement level or at the transaction level;

• All defined constraints must be enforced and validated before the load test is complete (see Clause 5.1.1.2);

1.4.2.1 The NOT NULL attribute may be used for any column.

1.4.2.2 The following columns or set of columns listed in Clause 1.4.1 as ‘Primary Key’ may be defined as primary key

constraints (using the PRIMARY KEY clause or other equivalent syntax):

• P_PARTKEY;

• S_SUPPKEY;

• PS_PARTKEY, PS_SUPPKEY;

• C_CUSTKEY;

• O_ORDERKEY;

• L_ORDERKEY, L_LINENUMBER;

• N_NATIONKEY;

• R_REGIONKEY.

Defining a primary key constraint can only be done for the columns listed above.

1.4.2.3 Columns listed in the comments of Clause 1.4.1 as ‘Foreign Key’ may be defined as foreign key constraints. There

is no specific requirement to use referential actions (e.g., RESTRICT, CASCADE, NO ACTION, etc.). If any

foreign key constraint is defined by an implementation, then all the foreign key constraints listed below must be

defined by the implementation (using the FOREIGN KEY clause or other equivalent syntax):S_NATIONKEY

(referencing N_NATIONKEY);

• PS_PARTKEY (referencing P_PARTKEY);

• PS_SUPPKEY (referencing S_SUPPKEY);

• C_NATIONKEY (referencing N_NATIONKEY);

• O_CUSTKEY (referencing C_CUSTKEY);

• L_ORDERKEY (referencing O_ORDERKEY);

• L_PARTKEY (referencing P_PARTKEY);

• L_SUPPKEY (referencing S_SUPPKEY);

• L_PARTKEY, L_SUPPKEY (referencing PS_PARTKEY, PS_SUPPKEY);

• N_REGIONKEY (referencing R_REGIONKEY);

Defining a foreign key constraint can only be done for the columns listed above.

1.4.2.4 Check Constraints: Check constraints may be defined to restrict the database contents. In order to support

evolutionary change, the check constraints must not rely on knowledge of the enumerated domains of each column.

The following list of expressions defines permissible check constraints:

1. Positive Keys

P_PARTKEY >= 0

S_SUPPKEY >= 0

C_CUSTKEY >= 0

TPC Benchmark

H Standard Specification Revision 3.0.0 Page 19

PS_PARTKEY >= 0

R_REGIONKEY >= 0

N_NATIONKEY >= 0

2. Open-interval constraints

P_SIZE >= 0

P_RETAILPRICE >= 0

PS_AVAILQTY >= 0

PS_SUPPLYCOST >= 0

O_TOTALPRICE >= 0

L_QUANTITY >= 0

L_EXTENDEDPRICE >= 0

L_TAX >= 0

3. Closed-interval constraints

L_DISCOUNT between 0.00 and 1.00

4. Multi-column constraints

L_SHIPDATE <= L_RECEIPTDATE

Comment: The constraints rely solely on the diagram provided in Clause 1.2and the description in Clause 1.4. They

are not derived from explicit knowledge of the data population specified in Clause 4.2.

1.5 Implementation Rules

1.5.1 The database shall be implemented using a commercially available database management system (DBMS).

1.5.2 The physical clustering of records within the database is allowed as long as this clustering does not alter the logical

independence of each table.

Comment: The intent of this clause is to permit flexibility in the physical design of a database while preserving a

strict logical view of all the tables.

1.5.3 At the end of the Load Test, all tables must have exactly the number of rows defined for the scale factor, SF, and the

database population, both specified in Clause 4: .

1.5.4 Horizontal partitioning of base tables or auxiliary structures created by database directives (see Clause 1.5.7) is

allowed. Groups of rows from a table or auxiliary structure may be assigned to different files, disks, or areas. If this

assignment is a function of data in the table or auxiliary structure, the assignment must be based on the value of a

partitioning field. A partitioning field must be one and only one of the following:

• A column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary key

constraint;

• A column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;

• A column having a date datatype as defined in Clause 1.3.

Some partitioning schemes require the use of directives that specify explicit values for the partitioning field. If such

directives are used they must satisfy the following conditions:

• They may not rely on any knowledge of the data stored in the table except the minimum and maximum

values of columns used for the partitioning field. The minimum and maximum values of columns are

specified in Clause 4.2.3

• Within the limitations of integer division, they must define each partition to accept an equal portion of the

range between the minimum and maximum values of the partitioning column(s). For date-based partitions,

it is permissible to partition into equally sized domains based upon an integer granularity of days, weeks,

months, or years (e.g., 30 days, 4 weeks, 1 month, 1 year, etc.). For date-based partition granularities other

TPC Benchmark

H Standard Specification Revision 3.0.0 Page 20

than days, a partition boundary may extend beyond the minimum or maximum boundaries as established in

that table’s data characteristics as defined in Clause 4.2.3.

• The directives must allow the insertion of values of the partitioning column(s) outside the range covered by

the minimum and maximum values, as required by Clause 1.5.13.

Multiple-level partitioning of base tables or auxiliary structures is allowed only if each level of partitioning satisfies

the conditions stated above and each level references only one partitioning field as defined above. If implemented,

the details of such partitioning must be disclosed.

1.5.5 Physical placement of data on durable media is not auditable. SQL DDL that explicitly partitions data vertically is

prohibited. The row must be logically presented as an atomic set of columns.

Comment: This implies that vertical partitioning which does not rely upon explicit partitioning directives is

allowed. Explicit partitioning directives are those that assign groups of columns of one row to files, disks or areas

different from those storing the other columns in that row.

1.5.6 Except as provided in Clause 1.5.7, logical replication of database objects (i.e., tables, rows, or columns) is not

allowed. The physical implementation of auxiliary data structures to the tables may involve data replication of

selected data from the tables provided that:

• All replicated data are managed by the DBMS, the operating system, or the hardware;

• All replications are transparent to all data manipulation operations;

• Data modifications are reflected in all logical copies of the replicated data by the time the updating

transaction is committed;

• All copies of replicated data maintain full ACID properties (see Clause 3: ) at all times.

1.5.7 Auxiliary data structures that constitute logical replications of data from one or more columns of a base table (e.g.,

indexes, materialized views, summary tables, structures used to enforce relational integrity constraints) must

conform to the provisions of Clause 1.5.6. The directives defining and creating these structures are subject to the

following limitations:

• Each directive may reference no more than one base table, and may not reference other auxiliary structures.

• Each directive may reference one and only one of the following:

o A column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary key

constraint;

o A column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;

o A column having a date datatype as defined in Clause 1.3.

• Each directive may contain functions or expressions on explicitly permitted columns

No directives (e.g. DDL, session options, global configuration parameters) are permitted in TPC-H scripts whose

effect is to cause the materialization of columns (or functions on columns) in auxiliary data structures other than

those columns explicitly permitted by the above limitations. Further, no directives are permitted whose effect is to

cause the materialization of columns in auxiliary data structures derived from more than one table.

Comment: Database implementations of auxiliary structures generated as a result of compliant directives usually

contain embedded pointers or references to corresponding base table rows. Database implementations that

transparently employ either ‘row IDs’ or embedded base table ‘Primary Key’ values for this purpose are equally

acceptable.

In particular, the generation of transparently embedded ‘Primary Key’ values required by auxiliary structures is a

permitted materialization of the ‘Primary Key’ column(s). ‘Primary Key’ and ‘Foreign Key’ columns are listed in

Clause 1.4.1.

剩余137页未读，继续阅读

老王笔记

粉丝: 406
资源: 11

"TPC-H官方文档：数据库OLAP基准测试标准版3.0.0"

tpc-h-tool.zip

tpc-h 国内下载

TPC-H Benchmark

tpc-e mysql

TPCH测试mysql

tpc-ds开源测试工具

推荐几款TPC-ds压测工具

建立TPCH数据库的查询语句

tpc-c 计算公式

docker安装的opengauss数据库，怎么跑tpcc，不使用pgbench等工具

tpc-usb无法编译

./tpcc.lua --mysql-host=0.0.0.0 --mysql-user=root --mysql-password=123456 --mysql-db=tpcc prepare没初始化9张表就退出了该怎么办

./tpcc.lua --mysql-host=0.0.0.0 --mysql-user=root --mysql-password=123456 --mysql-db=tpcc prepare 默认初始化多少张表

tpc-c 测算云服务器

ubuntu20.04已有sysbench，如何实现tpcc测试

TPC-486串口通信

fpc = (1 - tp[i]).cumsum(0) tpc = tp[i].cumsum(0)

TPC-ZK实验箱交通灯模拟系统

最新资源