TPC BENCHMARK (TM) H, страница 6

For example,P_TYPE cannot be implemented as two discrete columns P_TYPE1 and P_TYPE2.1.5.13The database must allow for insertion of arbitrary data values that conform to the datatype and optional constraintdefinitions from Clause 1.3 and Clause 1.4.Comment 1: Although the refresh functions (see Clause 2.5) do not insert arbitrary values and do not modify alltables, all tables must be modifiable throughout the performance test.Comment 2: The intent of this Clause is to prevent the database schema definition from taking undue advantage ofthe limited data population of the database (see also Clause 0.2 and Clause 5.2.7).1.6Data Access Transparency Requirements1.6.1Data Access Transparency is the property of the system that removes from the query text any knowledge of thelocation and access mechanisms of partitioned data.

No finite series of tests can prove that the system supportscomplete data access transparency. The requirements below describe the minimum capabilities needed to establishthat the system provides transparent data access. An implementation that uses horizontal partitioning must meet therequirements for transparent data access described in Clause 1.6.2 and Clause 1.6.3.Comment: The intent of this Clause is to require that access to physically and/or logically partitioned data beprovided directly and transparently by services implemented by commercially available layers such as theinteractive SQL interface, the database management system (DBMS), the operating system (OS), the hardware, orany combination of these.1.6.2Each of the tables described in Clause 1.4 must be identifiable by names that have no relationship to the partitioningof tables.

All data manipulation operations in the executable query text (see Clause 2.1.1.2) must use only thesenames.1.6.3Using the names which satisfy Clause 1.6.2, any arbitrary non-TPC-H query must be able to reference any set ofrows or columns:Identifiable by any arbitrary condition supported by the underlying DBMS;Using the names described in Clause 1.6.2 and using the same data manipulation semantics and syntax forall tables.For example, the semantics and syntax used to query an arbitrary set of rows in any one table must also be usablewhen querying another arbitrary set of rows in any other table.Comment: The intent of this clause is that each TPC-H query uses general purpose mechanisms to access data in thedatabase.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 212: QUERIES AND REFRESH FUNCTIONSThis Clause describes the twenty-two decision support queries and the two database refresh functions that must beexecuted as part of the TPC-H benchmark.2.1General Requirements and Definitions for Queries2.1.1Query Overview2.1.1.1 Each query is defined by the following components:The business question, which illustrates the business context in which the query could be used;The functional query definition, which defines, using the SQL-92 language, the function to be performedby the query;The substitution parameters, which describe how to generate the values needed to complete the querysyntax;The query validation, which describes how to validate the query against the qualification database.2.1.1.2 For each query, the test sponsor must create an implementation of the functional query definition, referred to as theexecutable query text.2.1.2Functional Query Definitions2.1.2.1 The functional query definitions are written in the SQL-92 language (ISO/IEC 9075:1992), annotated wherenecessary to specify the number of rows to be returned.

They define the function that each executable query textmust perform against the test database (see Clause 4.1.1).2.1.2.2 If an executable query text, with the exception of its substitution parameters, is not identical to the specifiedfunctional query definition it must satisfy the compliance requirements of Clause 2.2.2.1.2.3 When a functional query definition includes the creation of a new entity (e.g., cursor, view, or table) somemechanism must be used to ensure that newly created entities do not interfere with other execution streams and arenot shared between multiple execution streams (see Clause 5.1.2.3).Functional query definitions in this document (as well as QGEN, see Clause 2.1.4) achieve this separation byappending a text-token to the new entity name.

This text-token is expressed in upper case letters and enclosed insquare brackets (i.e., [STREAM_ID]). This text-token, whenever found in the functional query definition, must bereplaced by a unique stream identification number (starting with 0) to complete the executable query text.Comment: Once an identification number has been generated and assigned to a given query stream, the sameidentification number must be used for that query stream for the duration of the test.2.1.2.4 When a functional query definition includes the creation of a table, the datatype specification of the columns usesthe <datatype> notation.

The definition of <datatype> is obtained from Clause 1.3.1.2.1.2.5 Any entity created within the scope of an executable query text must also be deleted within the scope of that sameexecutable query text.2.1.2.6 A logical tablespace is a named collection of physical storage devices referenced as a single, logically contiguous,non-divisible entity.2.1.2.7 If CREATE TABLE statements are used during the execution of the queries, these CREATE TABLE statementsmay be extended only with a tablespace reference (e.g., IN <tablespacename>). A single tablespace must be used forall these tables.Comment: The allowance for tablespace syntax applies only to variants containing CREATE TABLE statements.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 222.1.2.8 All tables created during the execution of a query must meet the ACID properties defined in Clause 3: .2.1.2.9 Queries 2, 3, 10, 18 and 21 require that a given number of rows are to be returned (e.g., “Return the first 10 selectedrows”).

If N is the number of rows to be returned, the query must return exactly the first N rows unless fewer than Nrows qualify, in which case all rows must be returned. There are three permissible ways of satisfying thisrequirement. A test sponsor must select any one of them and use it consistently for all the queries that require that aspecified number of rows be returned.2.1.31.Vendor-specific control statements supported by a test sponsor’s interactive SQL interface may be used (e.g.,SET ROWCOUNT n) to limit the number of rows returned.2.Control statements recognized by the implementation specific layer (see Clause 6.2.4) and used to control aloop which fetches the rows may be used to limit the number of rows returned (e.g., while rowcount <= n).3.Vendor-specific SQL syntax may be added to the SELECT statement to limit the number of rows returned (e.g.,SELECT FIRST n).

This syntax is not classified as a minor query modification since it completes the functionalrequirements of the functional query definition and there is no standardized syntax defined. In all other respects,the query must satisfy the requirements of Clause 2.2. The syntax must deal solely with the answer set, andmust not make any additional explicit reference, for example to tables, indices, or access paths.Substitution Parameters and Output Data2.1.3.1 Each query has one or more substitution parameters. When generating executable query text a value must besupplied for each substitution parameter of that query. These values must be used to complete the executable querytext. These substitution parameters are expressed as names in uppercase and enclosed in square brackets.

Forexample, in the Pricing Summary Report Query (see Clause 2.4) the substitution parameter [DELTA], wheneverfound in the functional query definition, must be replaced by the value generated for DELTA to complete theexecutable query text.Comment 1: When dates are part of the substitution parameters, they must be expressed in a format that includesthe year, month and day in integer form, in that order (e.g., YYYY-MM-DD). The delimiter between the year,month and day is not specified.

Other date representations, for example the number of days since 1970-01-01, arespecifically not allowed.Comment 2: When a substitution parameter appears more than once in a query, a single value is generated for thatsubstitution parameter and each of its occurrences in the query must be replaced by that same value.Comment 3: Generating executable query text may also involve additional text substitution (see Clause 2.1.2.3).2.1.3.2 The term randomly selected when used in the definitions of substitution parameters means selected at randomfrom a uniform distribution over the range or list of values specified.2.1.3.3 Seeds to the random number generator used to generate substitution parameters must be selected using the followingmethod:An initial seed (seed0) is first selected as the time stamp of the end of the database load time expressed in the formatmmddhhmmss where mm is the month, dd the day, hh the hour, mm the minutes and ss the seconds.

This seed isused to seed the Power test of Run 1. Further seeds (for the Throughput test) are chosen as seed0 + 1, seed0 +2,...,seed0 + n where s is the number of throughput streams selected by the vendor. This process leads to s + 1 seedsrequired for Run 1 of a benchmark with s streams. The seeds for Run 2 can be the same as those for Run 1 (see5.3.2). However, should the test sponsor decide to use different seeds for Run 2 from those used for Run 1, thesponsor must use a selection process similar to that of Run 1. The seeds must again be of the form seed0, seed0 + 1,seed0 + 2,...., seed0 + s, where and seed0 is be the time stamp of the end of Run 1, expressed in the format definedabove.Comment 1: The intent of this Clause is to prevent performance advantage that could result from multiple streamsbeginning work with identical seeds or using seeds known in advance while providing a well-defined and unifiedmethod for seed selection.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 23Comment 2: QGEN is a utility provided by the TPC (see Clause 2.1.4) to generate executable query text.