HP ProLiant 3000 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 34
Query 9 - Product Type Profit Measure
View all HP ProLiant 3000 manuals
Add to My Manuals
Save this manual to your list of manuals |
Page 34 highlights
Appendix B DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers year out of seven). The largest detail table has no direct selection applied to it. Five rows are returned, constituting the revenue for each nation in the selected region. Query 6 - Forecasting Revenue Change Business Question: Considers all the Lineitems shipped in a given year with discounts between DISCOUNT - 0.01 and DISCOUNT + 0.01. The query lists the amount by which the total revenue would have increased if these discounts had been eliminated for Lineitems with L_QUANTITY less than an inputted quantity. Functionality: This query accesses the large detail table only (Lineitem) selecting about 12% of the rows, and returning a single column answer. Query 7 - Volume Shipping TPC-D Business Question: Finds, for two given Nations, the gross discounted revenues derived from Lineitems in which parts were shipped from a Supplier in either Nation to a Customer in the other Nation during 1995 and 1996. Two nations are given as input parameters. Functionality: Query 7 is a 6-table join that requires a small 25-row table, NATION, to be aliased and processed as though it were two distinct look-up tables. A date constraint selects 2/7 of the Lineitem rows, while a join to the lookup tables result in 2/5 of the Customers and Suppliers being selected. Four rows are returned. Query 8 - National Market Share TPC-D Business Question: The market share for a given Nation within a given Region is defined as the fraction of the revenue from the products of a specified type in that Region that was supplied by Suppliers from the given Nation. The query determines this for the years 1995 and 1996. Functionality: Query 8 is an 8-table join including the NATION table twice. CASE (if/else logic) is used to determine and return a percent value based on two different years. Selection constraints qualify 1/50 of the Part rows, and 1/25 of the Nations. Query 9 - Product Type Profit Measure TPC-D Business Question: Finds, for each nation and each year, the profit for all Parts ordered in that year which contain a specified substring in their part-names and which were filled by a Supplier in that nation. Functionality: Query 9 is a 6-table join with selection criteria applied to only one small table, Part. A text string search is done against the PART table, resulting in 5% of the Part rows being selected. Revenue is aggregated for each combination of Year and Nation, returning 175 rows. Query 10 - Returned Item Reporting TPC-D Business Question: Finds the top 20 Customers, in terms of their effect on lost revenue for a given quarter, who have returned Parts. The query considers only Parts that were ordered in the specified quarter. Customers are listed in descending order of lost revenue. Functionality: Query 10 is a 4-table join of three large tables and one look-up table. Customer detail is returned by this query, alongside of only one column of summarized data. 1/28 of the Order rows qualify based on date, while 1 in 4 Lineitems match the criteria of having a return flag of 'R'. Millions of rows are returned from this query, but the final sort determines which 20 rows are to be displayed in the answer set. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398