HP ProLiant 3000 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Ser - Page 35

DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix B Query 11 - Important Stock Identification TPC-D Business Question: Finds, from scanning the available stock of Suppliers in a given Nation, all the Parts that represent a significant percentage of the total value of all available Parts. The query displays the part number and the value of those Parts in descending order of value. Functionality: Query 11 is a subquery with both the inner and outer query composed of a 3-table join of the same 3 moderate and small tables. A HAVING clause associates the two sub-queries. 1/25 of these tables' rows are selected, with several million rows being returned in the final answer set. Query 12 - Shipping Modes and Order Priority TPC-D Business Question: Counts, by shipping mode, for Lineitems actually received by Customers in a given year, the number of Lineitems belonging to Orders for which the Receiptdate exceeds the Commitdate for 2 different specified shipping modes. Only Lineitems that were actually shipped before the Commitdate are considered. Late Lineitems are partitioned into two groups, those with priority Urgent or High, and those with a priority other than Urgent or High. Functionality: Query 12 is a two-table join of the two largest tables, with 1/7 of the Lineitems being selected based on date, and all Orders participating in the join. CASE functionality is used to allow a single processing of both tables in order to count the qualifying rows for two different sets of priority criteria. Two rows are returned from the aggregation. Query 13 - Sales Clerk Performance TPC-D Business Question: Computes the total loss of revenue on Orders placed by a given Order clerk due to Parts being returned by Customers. The query groups and orders the results by the year in which the Parts were ordered. Functionality: Query 13 is a short-running query, as each clerk has only 1500 Orders no matter what the volume of data in the database. The join between the two largest tables involves a comparatively small amount of data, as only the Lineitems that have been returned for those 1500 Orders are processed. As there are seven years in the database, seven or fewer rows will be returned, one per year. Query 14 - Promotion Effect TPC-D Business Question: Determines what percentage of the revenue in a given year and month was derived from promotional Parts. The query considers only Parts actually shipped in that month and gives the percentage. Functionality: Query 14 calculates the numerator and the denominator of a fraction at the same time by using the CASE syntax to scan and process the data in one single sweep. A two-table join is required, with only 1/84th (one month) of the largest table qualifying for the join based on a date. Query 15 - Top Supplier TPC-D Business Question: Finds the Supplier who contributed the most to the overall revenue for Parts shipped during a given quarter of a given year. In case of a tie, the query lists all Suppliers whose contribution was equal to the maximum, presented in Supplier number order. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398