TPC BENCHMARK (TM) H (779138), страница 4
Текст из файла (страница 4)
The database is not a one-time snapshot of a business operations database nor is it a database where OLTPapplications are running concurrently. The database must, however, be able to support queries and refresh functionsagainst all tables on a 7 day by 24 hour (7 x 24) basis.While the benchmark models a business environment in which refresh functions are an integral part of datamaintenance, the refresh functions actually required in the benchmark do not attempt to model this aspect of thebusiness environment. Their purpose is rather to demonstrate the update functionality for the DBMS, whilesimultaneously assessing an appropriate performance cost to the maintenance of auxiliary data structures, such assecondary indices.Comment: The benchmark does not include any test or measure to verify continuous database availability orparticular system features which would make the benchmarked configuration appropriate for 7x24 operation.References to continuous availability and 7x24 operation are included in the benchmark specification to provide amore complete picture of the anticipated decision support environment.
A configuration offering less that 7x24TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 11availability can produce compliant benchmark results as long as it meets all the requirements described in thisspecification.Decision MakersDSS QueriesDSSDatabaseTPC-HBusinessAnalysisBusinessOperationsOLTPDatabaseOLTPTransactionsFigure 1: The TPC-H Business Environment illustrates the TPC-H business environment and highlights the basicdifferences between TPC-H and other TPC benchmarks.Figure 1: The TPC-H Business EnvironmentOther TPC benchmarks model the operational end of the business environment where transactions are executed on areal time basis. The TPC-H benchmark, however, models the analysis end of the business environment where trendsare computed and refined data are produced to support the making of sound business decisions.
In OLTPbenchmarks the raw data flow into the OLTP database from various sources where it is maintained for some periodof time. In TPC-H, periodic refresh functions are performed against a DSS database whose content is queried onbehalf of or by various decision makers.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 121.2Database Entities, Relationships, and CharacteristicsThe components of the TPC-H database are defined to consist of eight separate and individual tables (the BaseTables). The relationships between columns of these tables are illustrated in Figure 2: The TPC-H Schema.Figure 2: The TPC-H SchemaPART (P_)SF*200,000PARTSUPP (PS_)SF*800,000LINEITEM (L_)SF*6,000,000ORDERS (O_)SF*1,500,000PARTKEYPARTKEYORDERKEYORDERKEYNAMESUPPKEYPARTKEYCUSTKEYMFGRAVAILQTYSUPPKEYORDERSTATUSBRANDSUPPLYCOSTLINENUMBERTOTALPRICETYPECOMMENTQUANTITYORDERDATESIZECONTAINERRETAILPRICECUSTOMER (C_)SF*150,000CUSTKEYNAMECOMMENTADDRESSSUPPLIER (S_)SF*10,000TAXRETURNFLAGLINESTATUSCLERKSHIPPRIORITYCOMMENTSHIPDATECOMMITDATEACCTBALNAMERECEIPTDATEMKTSEGMENTADDRESSSHIPINSTRUCTNATIONKEYACCTBALDISCOUNTORDERPRIORITYPHONESUPPKEYPHONENATIONKEYEXTENDEDPRICECOMMENTSHIPMODENATION (N_)25NATIONKEYCOMMENTNAMEREGIONKEYCOMMENTCOMMENTREGION (R_)5REGIONKEYNAMECOMMENTLegend:The parentheses following each table name contain the prefix of the column names for that table;The arrows point in the direction of the one-to-many relationships between tables;The number/formula below each table name represents the cardinality (number of rows) of the table.
Someare factored by SF, the Scale Factor, to obtain the chosen database size. The cardinality for the LINEITEMtable is approximate (see Clause 4.2.5).TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 131.3Datatype Definitions1.3.1The following datatype definitions apply to the list of columns of each table:Identifier means that the column must be able to hold any key value generated for that column and be ableto support at least 2,147,483,647 unique values;Comment: A common implementation of this datatype will be an integer.
However, for SF greater than 300 somecolumn values will exceed the range of integer values supported by a 4-byte integer. A test sponsor may use someother datatype such as 8-byte integer, decimal or character string to implement the identifier datatype;Integer means that the column must be able to exactly represent integer values (i.e., values in incrementsof 1) in the range of at least -2,147,483,646 to 2,147,483,647.Decimal means that the column must be able to represent values in the range -9,999,999,999.99 to+9,999,999,999.99 in increments of 0.01; the values can be either represented exactly or interpreted to be inthis range;Big Decimal is of the Decimal datatype as defined above, with the additional property that it must be largeenough to represent the aggregated values stored in temporary tables created within query variants;Fixed text, size N means that the column must be able to hold any string of characters of a fixed length ofN.Comment: If the string it holds is shorter than N characters, then trailing spaces must be stored in the database orthe database must automatically pad with spaces upon retrieval such that a CHAR_LENGTH() function will returnN.Variable text, size N means that the column must be able to hold any string of characters of a variablelength with a maximum length of N.
Columns defined as "variable text, size N" may optionally beimplemented as "fixed text, size N";Date is a value whose external representation can be expressed as YYYY-MM-DD, where all charactersare numeric. A date must be able to express any day within at least 14 consecutive years. There is norequirement specific to the internal representation of a date.Comment: The implementation datatype chosen by the test sponsor for a particular datatype definition must beapplied consistently to all the instances of that datatype definition in the schema, except for identifier columns,whose datatype may be selected to satisfy database scaling requirements.1.3.2The symbol SF is used in this document to represent the scale factor for the database (see Clause 4: ).1.4Table Layouts1.4.1Required TablesThe following list defines the required structure (list of columns) of each table.The annotations ‘Primary Key’ and ‘Foreign Key’, as used in this Clause, are for information only and do not implyadditional requirements to implement primary key and foreign key constraints (see Clause 1.4.2).PART Table LayoutColumn NameDatatype RequirementsCommentP_PARTKEYidentifierSF*200,000 are populatedP_NAMEvariable text, size 55P_MFGRfixed text, size 25TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 14P_BRANDfixed text, size 10P_TYPEvariable text, size 25P_SIZEintegerP_CONTAINERfixed text, size 10P_RETAILPRICEdecimalP_COMMENTvariable text, size 23Primary Key: P_PARTKEYSUPPLIER Table LayoutColumn NameDatatype RequirementsCommentS_SUPPKEYidentifierSF*10,000 are populatedS_NAMEfixed text, size 25S_ADDRESSvariable text, size 40S_NATIONKEYIdentifierS_PHONEfixed text, size 15S_ACCTBALdecimalS_COMMENTvariable text, size 101Foreign Key to N_NATIONKEYPrimary Key: S_SUPPKEYPARTSUPP Table LayoutColumn NameDatatype RequirementsCommentPS_PARTKEYIdentifierForeign Key to P_PARTKEYPS_SUPPKEYIdentifierForeign Key to S_SUPPKEYPS_AVAILQTYintegerPS_SUPPLYCOSTDecimalPS_COMMENTvariable text, size 199Primary Key: PS_PARTKEY, PS_SUPPKEYCUSTOMER Table LayoutColumn NameDatatype RequirementsCommentC_CUSTKEYIdentifierSF*150,000 are populatedTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 15C_NAMEvariable text, size 25C_ADDRESSvariable text, size 40C_NATIONKEYIdentifierC_PHONEfixed text, size 15C_ACCTBALDecimalC_MKTSEGMENTfixed text, size 10C_COMMENTvariable text, size 117Foreign Key to N_NATIONKEYPrimary Key: C_CUSTKEYORDERS Table LayoutColumn NameDatatype RequirementsCommentO_ORDERKEYIdentifierSF*1,500,000 are sparsely populatedO_CUSTKEYIdentifierForeign Key to C_CUSTKEYO_ORDERSTATUSfixed text, size 1O_TOTALPRICEDecimalO_ORDERDATEDateO_ORDERPRIORITYfixed text, size 15O_CLERKfixed text, size 15O_SHIPPRIORITYIntegerO_COMMENTvariable text, size 79Primary Key: O_ORDERKEYComment: Orders are not present for all customers.
In fact, one-third of the customers do not have any order inthe database. The orders are assigned at random to two-thirds of the customers (see Clause 4: ). The purpose ofthis is to exercise the capabilities of the DBMS to handle "dead data" when joining two or more tables.LINEITEM Table LayoutColumn NameDatatype RequirementsCommentL_ORDERKEYidentifierForeign Key to O_ORDERKEYL_PARTKEYidentifierForeign key to P_PARTKEY, first part of thecompound Foreign Key to (PS_PARTKEY,PS_SUPPKEY) with L_SUPPKEYL_SUPPKEYIdentifierForeign key to S_SUPPKEY, second part of thecompound Foreign Key to (PS_PARTKEY,TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 16PS_SUPPKEY) with L_PARTKEYL_LINENUMBERintegerL_QUANTITYdecimalL_EXTENDEDPRICEdecimalL_DISCOUNTdecimalL_TAXdecimalL_RETURNFLAGfixed text, size 1L_LINESTATUSfixed text, size 1L_SHIPDATEdateL_COMMITDATEdateL_RECEIPTDATEdateL_SHIPINSTRUCTfixed text, size 25L_SHIPMODEfixed text, size 10L_COMMENTvariable text size 44Primary Key: L_ORDERKEY, L_LINENUMBERNATION Table LayoutColumn NameDatatype RequirementsCommentN_NATIONKEYidentifier25 nations are populatedN_NAMEfixed text, size 25N_REGIONKEYidentifierN_COMMENTvariable text, size 152Foreign Key to R_REGIONKEYPrimary Key: N_NATIONKEYREGION Table LayoutColumn NameDatatype RequirementsCommentR_REGIONKEYidentifier5 regions are populatedR_NAMEfixed text, size 25R_COMMENTvariable text, size 152Primary Key: R_REGIONKEYTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 171.4.2ConstraintsThe use of constraints is optional and limited to primary key, foreign key, check, and not null constraints.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
