TPC BENCHMARK (TM) H (779138), страница 8
Текст из файла (страница 8)
Theaggregate functions AVG and COUNT may be replaced with equivalent vendor-specific functions tohandle the expanded range of values (e.g., AVG_BIG and COUNT_BIG).p)Substring Scalar Functions – For queries which use the SUBSTRING() scalar function, vendor-specificsyntax may be used instead of the specified SQL 92 syntax. Replacement syntax must have equivalentsemantic behavior. For example, “SUBSTRING(C_PHONE, 1, 2)”.q)Outer Join – For outer join queries, vendor specific syntax may be used instead of the specified SQL 92syntax. Replacement syntax must have equivalent semantic behavior. For example, the join expression“CUSTOMER LEFT OUTER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY” may be replaced byadding CUSTOMER and ORDERS to the from clause and adding a specially-marked join predicate (e.g.,C_CUSTKEY *= O_CUSTKEY).2.2.3.4 The application of minor query modifications to functional query definitions or approved variants must be consistentover the query set.
For example, if a particular vendor-specific date expression or table name syntax is used in onequery, it must be used in all other queries involving date expressions or table names.2.2.3.5 The use of minor modifications to obtain executable query text must be disclosed and justified (see Clause 8.3.4.3).TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 262.2.4Approved Query Variants2.2.4.1 Approval of any new query variant is required prior to using such variant to produce compliant TPC-H results. Theapproval process is based on criteria defined in Clause 2.2.4.3.2.2.4.2 Query variants that have already been approved are listed in Appendix B of this specification.Comment: Since Appendix B is updated each time a new variant is approved, test sponsors should obtain the latestversion of this appendix prior to implementing the benchmark.2.2.4.3 The executable query text for each query in a compliant implementation must be taken from either the functionalquery definition (see Clause 2: ) or an approved query variant (see Appendix B).
Except as specifically allowed inClause 2.2.3.3, executable query text must be used in full exactly as written in the TPC-H specification. New queryvariants will be considered for approval if they meet one of the following criteria:a)The vendor cannot successfully run the executable query text against the qualification database using thefunctional query definition or an approved variant even after applying appropriate minor querymodifications as per Clause 2.2.3.b)The variant contains new or enhanced SQL syntax, relevant to the benchmark, which is defined in anApproved Committee Draft of a new ISO SQL standard.c)The variant contains syntax that brings the proposed variant closer to adherence to an ISO SQL standard.d)The variant contains minor syntax differences that have a straightforward mapping to ISO SQL syntax usedin the functional query definition and offers functionality substantially similar to the ISO SQL standard.2.2.4.4 To be approved, a proposed variant should have the following properties.
Not all of the following properties arespecifically required. Rather, the cumulative weight of each property satisfied by the proposed variant will be thedetermining factor in approving it.a)Variant is syntactical only, seeking functional compatibility and not performance gain.b)Variant is minimal and restricted to correcting a missing functionality.c)Variant is based on knowledge of the business question rather than on knowledge of the system under test(SUT) or knowledge of specific data values in the test database.d)Variant has broad applicability among different vendors.e)Variant is non procedural.f)Variant is an SQL-92 standard [ISO/IEC 9075:1992] implementation of the functional query definition.g)Variant is sponsored by a vendor who can implement it and who intends on using it in an upcomingimplementation of the benchmark.2.2.4.5 Query variants that are submitted for approval will be recorded, along with a rationale describing why they were orwere not approved.2.2.4.6 Query variants listed in Appendix B are defined using the conventions defined for functional query definitions (seeClause 2.1.2.3 through Clause 2.1.2.6).2.2.5Coding StyleImplementers may code the executable query text in any desired coding style, including:a)additional line breaks, tabs or white spaceb)choice of upper or lower case textThe coding style used must have no impact on the performance of the system under test, and must be consistentlyapplied across the entire query set.
Any coding style that differs from the functional query definitions in Clause 2:must be disclosed.Comment: This does not preclude the auditor from verifying that the coding style does not affect performance.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 272.3Query Validation2.3.1To validate the compliance of the executable query text, the following validation test must be executed by the testsponsor and the results reported in the full disclosure report:1.A qualification database must be built in a manner substantially the same as the test database (see Clause 4.1.2).2.The query validation test must be run using a qualification database that has not been modified by any updateactivity (e.g., RF1, RF2, or ACID Transaction executions).3.The query text used (see Clause 2.1.3) must be the same as that used in the performance test. The defaultsubstitution parameters provided for each query must be used. The refresh functions, RF1 and RF2, are notexecuted.4.The same driver and implementation specific layer used to execute the queries against the test database must beused for the validation of the qualification database.5.The resulting output must match the output data specified for the query validation (see Appendix C).6.Any difference between the output obtained and the query validation output must satisfy the requirements ofClause 2.1.3.5.Any query whose output differs from the query validation output to a greater degree than allowed by Clause 2.1.3.5when run against the qualification database as specified above is not compliant.Comment: The validation test, above, provides a minimum level of assurance of compliance.
The auditor mayrequest additional assurance that the query texts execute in accordance with the benchmark requirements.2.3.2No aspect of the System Under Test (e.g., system parameters and conditional software features such as those listedin Clause 5.2.7, hardware configuration, software releases, etc.), may differ between this demonstration ofcompliance and the performance test.Comment: While the intent of this validation test is that it be executed without any change to the hardwareconfiguration, building the qualification database on additional disks (i.e., disks not included in the priced system) isallowed as long as this change has no impact on the results of the demonstration of compliance.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 282.4Query DefinitionsFor each query a single example output row is shown (even though queries often produce multiple rows) along withthe column headers.
This is for illustration only. See Appendix F: for the precise validation output for each query.2.4.1Pricing Summary Report Query (Q1)This query reports the amount of business that was billed, shipped, and returned.2.4.1.1 Business QuestionThe Pricing Summary Report Query provides a summary pricing report for all lineitems shipped as of a given date.The date is within 60 - 120 days of the greatest ship date contained in the database. The query lists totals forextended price, discounted extended price, discounted extended price plus tax, average quantity, average extendedprice, and average discount. These aggregates are grouped by RETURNFLAG and LINESTATUS, and listed inascending order of RETURNFLAG and LINESTATUS.
A count of the number of lineitems in each group isincluded.2.4.1.2 Functional Query Definitionselectl_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_orderfromlineitemwherel_shipdate <= date '1998-12-01' - interval '[DELTA]' day (3)group byl_returnflag,l_linestatusorder byl_returnflag,l_linestatus;2.4.1.3 Substitution ParametersValues for the following substitution parameter must be generated and used to build the executable query text:1.DELTA is randomly selected within [60.
120].Comment: 1998-12-01 is the highest possible ship date as defined in the database population. (This is ENDDATE 30). The query will include all lineitems shipped before this date minus DELTA days. The intent is to chooseDELTA so that between 95% and 97% of the rows in the table are scanned.2.4.1.4 Query ValidationFor validation against the qualification database the query must be executed using the following values for substitution parameters and must produce the following output data:Values for substitution parameters:1.DELTA = 90.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 292.4.1.5 Sample OutputL_RETURNFLAGL_LINESTATUSSUM_QTYSUM_BASE_PRICESUM_DISC_PRICEAF37734107.0056586554400.7353758257134.87SUM_CHARGEAVG_QTYAVG_PRICEAVG_DISCCOUNT_ORDER55909065222.8325.5238273.13.0514784932.4.2Minimum Cost Supplier Query (Q2)This query finds which supplier should be selected to place an order for a given part in a given region.2.4.2.1 Business QuestionThe Minimum Cost Supplier Query finds, in a given region, for each part of a certain type and size, the supplier whocan supply it at minimum cost.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
