TPC BENCHMARK (TM) H (779138), страница 17
Текст из файла (страница 17)
24].S_PHONE generated according to Clause 4.2.2.9.S_ACCTBAL random value [-999.99 .. 9,999.99].S_COMMENT text string [25,100].SF * 5 rows are randomly selected to hold at a random position a string matching "Customer%Complaints". Another SF * 5 rows are randomly selected to hold at a random position astring matching "Customer%Recommends", where % is a wildcard that denotes zero or morecharacters.SF * 200,000 rows in the PART table with:TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 85P_PARTKEY unique within [SF * 200,000].P_NAME generated by concatenating five unique randomly selected strings from the following list,separated by a single space:{"almond", "antique", "aquamarine", "azure", "beige", "bisque", "black", "blanched", "blue","blush", "brown", "burlywood", "burnished", "chartreuse", "chiffon", "chocolate", "coral","cornflower", "cornsilk", "cream", "cyan", "dark", "deep", "dim", "dodger", "drab", "firebrick","floral", "forest", "frosted", "gainsboro", "ghost", "goldenrod", "green", "grey", "honeydew","hot", "indian", "ivory", "khaki", "lace", "lavender", "lawn", "lemon", "light", "lime", "linen","magenta", "maroon", "medium", "metallic", "midnight", "mint", "misty", "moccasin", "navajo","navy", "olive", "orange", "orchid", "pale", "papaya", "peach", "peru", "pink", "plum", "powder","puff", "purple", "red", "rose", "rosy", "royal", "saddle", "salmon", "sandy", "seashell", "sienna","sky", "slate", "smoke", "snow", "spring", "steel", "tan", "thistle", "tomato", "turquoise", "violet","wheat", "white", "yellow"}.P_MFGR text appended with digit ["Manufacturer#",M], where M = random value [1,5].P_BRAND text appended with digits ["Brand#",MN], where N = random value [1,5] and M is definedwhile generating P_MFGR.P_TYPE random string [Types].P_SIZE random value [1 ..
50].P_CONTAINER random string [Containers].P_RETAILPRICE = (90000 + ((P_PARTKEY/10) modulo 20001 ) + 100 * (P_PARTKEY modulo1000))/100P_COMMENT text string [5,22].For each row in the PART table, four rows in PartSupp table with:PS_PARTKEY = P_PARTKEY.PS_SUPPKEY = (ps_partkey + (i * (( S/4 ) + (int)(ps_partkey-1 )/S)))) modulo S + 1 where i is the ithsupplier within [0 .. 3] and S = SF * 10,000.PS_AVAILQTY random value [1 ..
9,999].PS_SUPPLYCOST random value [1.00 .. 1,000.00].PS_COMMENT text string [49,198].SF * 150,000 rows in CUSTOMER table with:C_CUSTKEY unique within [SF * 150,000].C_NAME text appended with minimum 9 digits with leading zeros ["Customer#", C_CUSTKEY].C_ADDRESS random v-string [10,40].C_NATIONKEY random value [0 .. 24].C_PHONE generated according to Clause 4.2.2.9.C_ACCTBAL random value [-999.99 .. 9,999.99].C_MKTSEGMENT random string [Segments].C_COMMENT text string [29,116].For each row in the CUSTOMER table, ten rows in the ORDERS table with:O_ORDERKEY unique within [SF * 1,500,000 * 4].Comment: The ORDERS and LINEITEM tables are sparsely populated by generating a key value that causes thefirst 8 keys of each 32 to be populated, yielding a 25% use of the key range.
Test sponsors must not take advantageof this aspect of the benchmark. For example, horizontally partitioning the test database onto different devices inorder to place unused areas onto separate peripherals is prohibited.O_CUSTKEY = random value [1 .. (SF * 150,000)].The generation of this random value must be such that O_CUSTKEY modulo 3 is not zero.Comment: Orders are not present for all customers. Every third customer (in C_CUSTKEY order) is not assignedany order.O_ORDERSTATUS set to the following value:"F" if all lineitems of this order have L_LINESTATUS set to "F"."O" if all lineitems of this order have L_LINESTATUS set to "O"."P" otherwise.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 86O_TOTALPRICE computed as:sum (L_EXTENDEDPRICE * (1+L_TAX) * (1-L_DISCOUNT)) for all LINEITEM of this order.O_ORDERDATE uniformly distributed between STARTDATE and (ENDDATE - 151 days).O_ORDERPRIORITY random string [Priorities].O_CLERK text appended with minimum 9 digits with leading zeros ["Clerk#", C] where C = random value[000000001 ..
(SF * 1000)].O_SHIPPRIORITY set to 0.O_COMMENT text string [19,78].For each row in the ORDERS table, a random number of rows within [1 .. 7] in the LINEITEM table with:L_ORDERKEY = O_ORDERKEY.L_PARTKEY random value [1 .. (SF * 200,000)].L_SUPPKEY = (L_PARTKEY + (i * (( S/4 ) + (int)(L_partkey-1 )/S)))) modulo S + 1where i is the corresponding supplier within [0 .. 3] and S = SF * 10,000.L_LINENUMBER unique within [7].L_QUANTITY random value [1 .. 50].L_EXTENDEDPRICE = L_QUANTITY * P_RETAILPRICEWhere P_RETAILPRICE is from the part with P_PARTKEY = L_PARTKEY.L_DISCOUNT random value [0.00 ..
0.10].L_TAX random value [0.00 .. 0.08].L_RETURNFLAG set to a value selected as follows:If L_RECEIPTDATE <= CURRENTDATEthen either "R" or "A" is selected at randomelse "N" is selected.L_LINESTATUS set the following value:"O" if L_SHIPDATE > CURRENTDATE"F" otherwise.L_SHIPDATE = O_ORDERDATE + random value [1 ..
121].L_COMMITDATE = O_ORDERDATE + random value [30 .. 90].L_RECEIPTDATE = L_SHIPDATE + random value [1 .. 30].L_SHIPINSTRUCT random string [Instructions].L_SHIPMODE random string [Modes].L_COMMENT text string [10,43].25 rows in the NATION table with:N_NATIONKEY unique value between 0 and 24.N_NAME string from the following series of (N_NATIONKEY, N_NAME, N_REGIONKEY).(0, ALGERIA, 0);(1, ARGENTINA, 1);(2, BRAZIL, 1);(3, CANADA, 1);(4, EGYPT, 4);(5, ETHIOPIA, 0);(6, FRANCE, 3);(7, GERMANY, 3);(8, INDIA, 2);(9, INDONESIA, 2);(10, IRAN, 4);(11, IRAQ, 4);(12, JAPAN, 2);(13, JORDAN, 4);(14, KENYA, 0);(15, MOROCCO, 0);(16, MOZAMBIQUE, 0);(17, PERU, 1);(18, CHINA, 2);(19, ROMANIA, 3);(20, SAUDI ARABIA, 4);(21, VIETNAM, 2);(22, RUSSIA, 3);(23, UNITED KINGDOM, 3);(24, UNITED STATES, 1)N_REGIONKEY is taken from the series above.N_COMMENT text string [31,114].5 rows in the REGION table with:R_REGIONKEY unique value between 0 and 4.R_NAME string from the following series of (R_REGIONKEY, R_NAME).(0, AFRICA);(1, AMERICA);(2, ASIA);(3, EUROPE);(4, MIDDLE EAST)R_COMMENT text string [31,115].TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 874.2.4Refresh Function Data Generation4.2.4.1 The test database is initially populated with 75% sparse ‘Primary Keys’ for the ORDERS and LINEITEM tables(see Clause 4.2.3) where only the first eight key values of each group of 32 keys are used.
Subsequently, the refreshfunction RF1 uses the 'holes' in the key ranges for inserting new rows.4.2.4.2 DBGEN generates refresh data sets for the refresh functions such that:For the first through the 1,000th execution of RF1 data sets are generated for inserting 0.1% new rows witha ‘Primary Keys’ within the second 8 key values of each group of 32 keys;For the first through the 1,000th execution of RF2 data sets are generated for deleting 0.1% existing rowswith a ‘Primary Keys’ within the original first 8 key values of each group of 32 keys.Comment: As a result, after 1,000 executions of RF1/RF2 pairs the test database is still populated with 75% sparse‘Primary Keys’ , but the second 8 key values of each group of 32 keys are now used.4.2.4.3 The refresh function data set generation scheme can be repeated until 4000 RF1/RF2 pairs have been executed, atwhich point the population of the test database is once again in its initial state.4.2.5Database Size4.2.5.1 Table 3: Estimated Database Size shows the test database size for a scale factor, SF, of 1.Table 3: Estimated Database Size1Table NameCardinality(in rows)Length (in bytes)of Typical2 RowTypical2 TableSize (in MB)SUPPLIER10,0001592PART200,00015530PARTSUPP800,000144110CUSTOMER150,00017926ORDERS1,500,000104149LINEITEM36,001,215112641NATION125128<1REGION15124<1Total8,661,245956Fixed cardinality: does not scale with SF.2Typical lengths and sizes given here are examples, not requirements, of what could result from animplementation (sizes do not include storage/access overheads).3The cardinality of the LINEITEM table is not a strict multiple of SF since the number of lineitems in anorder is chosen at random with an average of four (see Clause 4.2.5.2).TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 88Comment : 1 MB is defined to be 220 bytes.
Data types are sized as follows: 4-byte integers, 8-byte decimals, 4-bytedates.4.2.5.2 Table 4: LINEITEM Cardinality shows the cardinality of the LINEITEM table at all authorized scale factors.Table 4: LINEITEM CardinalityScale Factor (SF)Cardinality of LINEITEM Table160012151059986052301799983721006000379023001799989091100059999897093000180000483061000059999994267300001799999782681000005999999692004.3Database Load Time4.3.1The process of building the test database is known as database load.
Database load consists of timed and untimedcomponents. However, all components must be fully disclosed (see Clause 8.3.4.6).4.3.2The total elapsed time to prepare the test database for the execution of the performance test is called the databaseload time, and must be reported. This includes all of the elapsed time to create the tables defined in Clause 1.4, loaddata, create indices, define and validate constraints, gather statistics for the test database, configure the system undertest as it will be during the performance test, and ensure that the test database meets the ACID requirementsincluding syncing loaded data on devices used to implement data redundancy mechanisms and the taking of abackup of the database, when necessary.4.3.3The population of the test database, as defined in Clause 4.2, consists of two logical phases:1.Generation Phase: the process of using DBGen to generate records in a format for use by the DBMS loadfacility.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
