TPC Benchmark™ H Standard Specification Revision 2.8.0 Page 1TPC BENCHMARKTM H(Decision Support)Standard SpecificationRevision 2.8.0 Transaction Processing Performance Council (TPC)Presidio of San FranciscoBuilding 572B Ruger St. (surface)P.O. Box 29920 (mail)San Francisco, CA 94129-0920Voice:415-561-6272Fax:415-561-6120Email: [email protected]© 1993 - 2008 Transaction Processing Performance CouncilTPC Benchmark™ H Standard Specification Revision 2.8.0 Page 2AcknowledgmentsThe TPC acknowledges the work and contributions of the TPC-D subcommittee member companies in developingVersion 2 of the TPC-D specification which formed the basis for TPC-H Version 1. The subcommittee included rep-resentatives from Compaq, Data General, Dell, EMC, HP, IBM, Informix, Microsoft, NCR, Oracle, Sequent, SGI,Sun, Sybase, and Unisys. The TPC also acknowledges the contribution of Jack Stephens, consultant to the TPC-Dsubcommittee, for his work on the benchmark specification and DBGEN development.TPC Membership(as of September 23, 2008)Document HistoryDateVers ion Description26 February 1999 Draft 1.0.0 Mail ballot draft for Standard Specification24 June 1999 Revision 1.1.0 First minor revision of the Specification25 April 2002 Revision 1.4.0 Clarification about Primary Keys12 July 2002 Revision 1.5.0 Additions for EOL of hardware in 8.615 July 2002 Revision 2.0.0 Mail ballot draft 3 year maintenance pricing14 August 2003 Revision 2.1.0 Adding scale factors 30TB and 100TB29 June 2005 Revision 2.2.0 Adding Pricing Specification 1.0.011 August 2005 Revision 2.3.0 Changing pricing precision to cents and proces-sor definitionAMDBull S.A.Dell Inc.ExasolFujitsuFusion IOGreenplumHewlett-PackardHitachi SWIBM Corp.IngresIntel Corporation.KickfireMicrosoft Corporation.NECNetezzaOracle CorporationParAccelFujitsu-Siemens Sun MicrosystemsSybaseTeradataUnisys CorporationVert ic aVmwareTPC Benchmark™ H Standard Specification Revision 2.8.0 Page 3TPC Benchmark™, TPC-H, QppH, QthH, and QphH are trademarks of the Transaction Processing PerformanceCouncil. All parties are granted permission to copy and distribute to any party without fee all or part of this material pro-vided that: 1) copying and distribution is done for the primary purpose of disseminating TPC material; 2) the TPCcopyright notice, the title of the publication, and its date appear, and notice is given that copying is by permission ofthe Transaction Processing Performance Council.Parties wishing to copy and distribute TPC materials other than for the purposes outlined above (including incorporating TPC material in a non-TPC document, specification or report), must secure the TPC's written permission. 23 June 2006 Revision 2.4.0 Adding reference data set and audit require-ments to verify populated database, effect of update data and qgen substitution parameters.Scale factors larger than 10,000 are required to use this version.10 July 2006 Revision 2.5.0 dbgen bug fixes in parallel data generation, updates to reference data set/qualification out-put, modified audit rules and updated executive summary example.26 October 2006 Revision 2.6.0 Added Clause 7.2.3.1 about software license pricing, removed Clause 7.1.3.3 about 8 hour log requirement and updated executive sum-mary example in Appendix E14 June 2006 Revision 2.6.1 Editorial correction in Clause 2.1.3.3Clarification of Clause 9.2.4.528 February 2008 Revision 2.6.2 Change substr into substring in Clause 2.25.2, update of membership list, TPC address and copyright statement17 April 2008 Revision 2.7.0 Incorporate BUG fix 595 of qgen11 September 2008 Revision 2.8.0 Add wording to allow substitutions in Clause 7.2. Modify clauses 5.4, 5.4.6, 8.4.2.2 and 9.2.6.1 to refer to pricing specification. Update TPC member companies.DateVers ion DescriptionTPC Benchmark™ H Standard Specification Revision 2.8.0 Page 4Table of Contents0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60.1 Preamble . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60.2 General Implementation Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . 70.3 General Measurement Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 LOGICAL DATABASE DESIGN . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101.1 Business and Application Environment . . . . . . . . . . . . . . . . . . . . . . . 101.2 Database Entities, Relationships, and Characteristics . . . . . . . . . . . . 121.3 Datatype Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4 Table Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.5 Implementation Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171.6 Data Access Transparency Requirements. . . . . . . . . . . . . . . . . . . . . . 202 QUERIES AND REFRESH FUNCTIONS . . . . . . . . . . . . . . . . . . . . . 212.1 General Requirements and Definitions for Queries . . . . . . . . . . . . . . 212.2 Query Compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242.3 Query Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272.4 New Sales Refresh Function (RF1) . . . . . . . . . . . . . . . . . . . . . . . . . . 742.5 Old Sales Refresh Function (RF2) . . . . . . . . . . . . . . . . . . . . . . . . . . . 752.6 Database Evolution Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753 DATABASE SYSTEM PROPERTIES. . . . . . . . . . . . . . . . . . . . . . . . . 773.1 The ACID Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773.2 Atomicity Requirements . . . . . …