TPC BENCHMARK (TM) H (779138), страница 27
Текст из файла (страница 27)
Verify that the versionand the release numbers match the benchmark specification.9.2.4.8 Verify that storage and processing elements that are not included in the priced configuration are physically removedor made inaccessible during the performance test.9.2.4.9 Verify that the database load time is measured according to the requirements.9.2.5Clause 5 Related Items9.2.5.1 Verify that the driver meets the requirements of Clause 5.2 and Clause 6.3.9.2.5.2 Verify that the execution rules are followed for the power test.9.2.5.3 Verify that the queries are executed against the test database.9.2.5.4 Verify that the execution rules are followed for the throughput test.9.2.5.5 Verify that a single stream is used for refresh functions in the throughput test and that the required number of refreshfunction pairs is executed according to the execution rules.9.2.5.6 Verify that the query sequencing rules are followed.9.2.5.7 Verify that the measurement interval for the throughput test is measured as required.9.2.5.8 Verify that the method used to measure the timing intervals is compliant.9.2.5.9 Verify that the metrics are computed as required.
Note whether Clause 5.4.1.4 concerning the ratio between the longest and the shortest timing intervals had to be applied.9.2.5.10 Verify that the reported metrics are repeatable.9.2.6Clause 6 Related Items9.2.6.1 Verify that the composition of the SUT is compliant and that its components will be commercially available software or hardware products according to clause 7 of the Pricing Specification.9.2.6.2 Note whether an implementation specific layer is used and verify its compliance with Clause 6.2.4.9.2.6.3 Verify that the driver's implementation is compliant.9.2.6.4 Verify that any profile-directed optimization performed by the test sponsor conforms to the requirements of Clause5.2.9.9.2.7Clause 8 Related ItemsTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 1219.2.7.1 Verify that major portions of the full disclosure report are accurate and comply with the reporting requirements.
Thisincludes:The executive summary;The numerical quantity summary;The diagrams of both measured and priced configurations;The block diagram illustrating the database load process.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 12210: GLOBAL DEFINITIONSE ___________________________Externally Documented means that the documentation is available to any customer who has purchased the SUT, i.e.
noadditional condition such as a Non Disclosure Agreement (NDA) is required.F ___________________________Foreign KeyA Foreign Key (Foreign Key Constraint) is a column or combination of columns used to establish and enforce a linkbetween the data in two tables. A link is created between two tables by adding the column or columns that hold one table'sPrimary Key values to the other table. This column becomes a Foreign Key in the second table. May also be referred to asa foreign key constraint.P____________________________Primary KeyA Primary Key (Primary Key Constraint) is one or more columns that uniquely identifies a row. None of the columns thatare part of the Primary Key may be nullable. A table must have no more than one Primary Key.R ___________________________Referential IntegrityReferential Integrity is a data property whereby a Foreign Key in one table has a corresponding Primary key in a differenttable.round(x,m)Rounding a number x to a decimal precision of m is defined as:1)x+5*power(10,-m-1), call it y2)y*power(10,m), call it z3)truncate z to an integer value, call it q;4)q/power(10,m) to obtain the rounded value.Rounding Examplesround(45.897,1)y=45.897+0.05=45.947z=459.47q=459z=45.9round(45.213,1)y=45.213+0.05=45.263z=452.63q=452z=45.2round(45.897,0)y=45.897+0.5=46.397z=46.397q=46z=46TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 123Appendix A: ORDERED SETSFollowing are the ordered sets that must be used for sequencing query execution as described in Clause 5.3.5.
Theyare adapted from Moses and Oakford, Tables of Random Permutations, Stanford University Press, 1963. pp. 52-53.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22Power Test014 2 9 20 6 17 18 8 21 13 3 22 16 4 11 15 1 10 19 5 7 12Throughput Test121 3 18 5 11 7 6 20 17 12 16 15 13 10 2 8 14 19 9 22 1 426 17 14 16 19 10 9 2 15 8 5 22 12 7 13 18 1 4 20 3 11 2138 5 4 6 17 7 1 18 22 14 9 10 15 11 20 2 21 19 13 16 12 345 21 14 19 15 17 12 6 4 9 8 16 11 2 10 18 1 13 7 22 3 20521 15 4 6 7 16 19 18 14 22 11 13 3 1 2 5 8 20 12 17 10 9610 3 15 13 6 8 9 7 4 11 22 18 12 1 5 16 2 14 19 20 17 21718 8 20 21 2 4 22 17 1 11 9 19 3 13 5 7 10 16 6 14 15 12819 1 15 17 5 8 9 12 14 7 4 3 20 16 6 22 10 13 2 21 18 1198 13 2 20 17 3 6 21 18 11 19 10 15 4 22 1 7 12 9 14 5 16106 15 18 17 12 1 7 2 22 13 21 10 14 9 3 16 20 19 11 4 8 51115 14 18 17 10 20 16 11 1 8 4 22 5 12 3 9 21 2 13 6 19 7121 7 16 17 18 22 12 6 8 9 11 4 2 5 20 21 13 10 19 3 14 151321 17 7 3 1 10 12 22 9 16 6 11 2 4 5 14 8 20 13 18 15 19142 9 5 4 18 1 20 15 16 17 7 21 13 14 19 8 22 11 10 3 12 61516 9 17 8 14 11 10 12 6 21 7 3 15 5 22 20 1 13 19 2 4 18161 3 6 5 2 16 14 22 17 20 4 9 10 11 15 8 12 19 18 13 7 21173 16 5 11 21 9 2 15 10 18 17 7 8 19 14 13 1 4 22 20 6 121814 4 13 5 21 11 8 6 3 17 2 20 1 19 10 9 12 18 15 7 22 16194 12 22 14 5 15 16 2 8 10 17 9 21 7 3 6 13 18 11 20 19 12016 15 14 13 4 22 18 19 7 1 12 17 5 10 20 3 9 21 11 2 6 82120 14 21 12 15 17 4 19 13 10 11 1 16 5 18 7 8 22 9 6 3 22216 14 13 2 21 10 11 4 1 22 18 12 19 5 7 8 6 3 15 20 9 172318 15 9 14 12 2 8 11 22 21 16 1 6 17 5 10 19 4 20 13 3 7247 3 10 14 13 21 18 6 20 4 9 8 22 15 2 1 5 12 19 17 11 162518 1 13 7 16 10 14 2 19 5 21 11 22 15 8 17 20 3 4 12 6 92613 2 22 5 11 21 20 14 7 10 4 9 19 18 6 3 1 8 15 12 17 162714 17 21 8 2 9 6 4 5 13 22 7 15 3 1 18 16 11 10 12 20 192810 22 1 12 13 18 21 20 2 14 16 7 15 3 4 17 5 19 6 8 9 112910 8 9 18 12 6 1 5 20 11 17 22 16 3 13 2 15 21 14 19 7 4307 17 22 5 3 10 13 18 9 1 14 15 21 19 16 12 8 6 11 20 4 2312 9 21 3 4 7 1 11 16 5 20 19 18 8 17 13 10 12 15 6 14 223215 12 8 4 22 13 16 17 18 3 7 5 6 1 9 11 21 10 14 20 19 23315 16 2 11 17 7 5 14 20 4 21 3 10 9 12 8 13 6 18 19 22 1341 13 11 3 4 21 6 14 15 22 18 9 7 5 10 20 12 16 17 8 19 23514 17 22 20 8 16 5 10 1 13 2 21 12 9 4 18 3 7 6 19 15 11369 17 7 4 5 13 21 18 11 3 22 1 6 16 20 14 15 10 8 2 12 193713 14 5 22 19 11 9 6 18 15 8 10 7 4 17 16 3 1 12 2 21 203820 5 4 14 11 1 6 16 8 22 7 3 2 12 21 19 17 13 10 15 18 9393 7 14 15 6 5 21 20 18 10 4 16 19 1 13 9 8 17 11 12 22 24013 15 17 1 22 11 3 4 7 20 14 21 9 8 2 18 16 6 10 12 5 19TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 124Appendix B: APPROVED QUERY VARIANTSFollowing are the approved TPC-H query variants as of the publication date of this version of the specification.
Asnew query variants may be approved on an on-going basis, implementers are encouraged to obtain a copy of the latest list of approved query variants from the TPC office (see cover page for coordinates).Some query variants include statements that create temporary tables. In these statements, column data types are designated in angle brackets (e.g., <Integer>) and refer to the list of data types specified in Clause 1.3.1.- This appendix is also available in machine readable format To obtain a copy of the machine-readable appendices, please contact the TPC (see cover page).Q8Variant A (approved 11-Feb-1998)This variant replaces the CASE statement from the Functional Query Definition with equivalent DECODE() syntax.The justification for this variant was Clause 2.2.4.3 (d)), which allows for vendor-specific syntax that, while notSQL-92, provides a simple and direct mapping to approved SQL-92 syntax.selecto_year,sum(decode(nation, ‘[NATION]’, volume, 0)) / sum(volume) as mkt_sharefrom(selectextract(year from o_orderdate) as o_year,l_extendedprice * (1 - l_discount) as volume,n2.n_name as nationfrompart,supplier,lineitem,orders,customer,nation n1,nation n2,regionwherep_partkey = l_partkeyand s_suppkey = l_suppkeyand l_orderkey = o_orderkeyand o_custkey = c_custkeyand c_nationkey = n1.n_nationkeyand n1.n_regionkey = r_regionkeyand r_name = '[REGION]'and s_nationkey = n2.n_nationkeyand o_orderdate between date '1995-01-01' and date '1996-12-31'and p_type = '[TYPE]’TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 125) all_nationsgroup byo_yearorder byo_year;Q12Variant A (approved 11-Feb-1998)This variant replaces the CASE statement from the Functional Query Definition with equivalent DECODE() syntax.The justification for this variant was Clause 2.2.4.3 (d), which allows for vendor-specific syntax that, while notSQL-92, provides a simple and direct mapping to approved SQL-92 syntax.selectl_shipmode,sum(decode(o_orderpriority, '1-URGENT', 1, '2-HIGH', 1, 0)) ashigh_line_count,sum(decode(o_orderpriority, '1-URGENT', 0, '2-HIGH', 0, 1)) aslow_line_countfromorders,lineitemwhereo_orderkey = l_orderkeyand l_shipmode in ('[SHIPMODE1]', '[SHIPMODE2]')and l_commitdate < l_receiptdateand l_shipdate < l_commitdateand l_receiptdate >= date '[DATE]'and l_receiptdate < date '[DATE]' + interval '1' yeargroup byl_shipmodeorder byl_shipmode;Q13Variant A (approved 5 March 1998)This variant was required by a vendor which did not support two aggregates in a nested table expression.create view orders_per_cust[STREAM_ID] (custkey, ordercount) asselectc_custkey,count(o_orderkey)fromcustomer left outer join orders onc_custkey = o_custkeyand o_comment not like '%[WORD1]%[WORD2]%'group byc_custkey;selectTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 126ordercount,count(*) as custdistfromorders_per_cust[STREAM_ID]group byordercountorder bycustdist desc,ordercount desc;drop view orders_per_cust[STREAM_ID];Q14Variant A (approved 5 March 1998)This variant replaces the CASE statement with the equivalent DECODE() syntax.select100.00 * sum(decode(substring(p_type from 1 for 5), 'PROMO',l_extendedprice * (1-l_discount), 0)) /sum(l_extendedprice * (1-l_discount)) as promo_revenuefromlineitem,partwherel_partkey = p_partkeyand l_shipdate >= date '[DATE]'and l_shipdate < date '[DATE]' + interval '1' month;Q15Variant A (approved 11-Feb-1998)This variant was approved because it contains new SQL syntax that is relevant to the benchmark.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
