Compaq ProLiant 5500 DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq - Page 33
Appendix B: TPC-D Query Definitions - price
View all Compaq ProLiant 5500 manuals
Add to My Manuals
Save this manual to your list of manuals |
Page 33 highlights
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix B: TPC-D Query Definitions Appendix B Query 1 - Pricing Summary Report TPC-D Business Question: Provides a summary pricing report for all Lineitems shipped as of a given date, a date always within 180 days of the greatest shipdate contained in the database. Functionality: Query 1 performs multiple aggregations and summaries by reading and processing over 95% of the rows of the database's largest table. Only this single table is scanned with a very low number of rows being returned. Query 2 - Minimum Cost Supplier TPC-D Business Question: Find, in a given Region, for each Part of a certain type and size, the Supplier who can supply it at minimum cost. If several suppliers in that region offer the desired part type and size at the same (minimum) cost, the query lists the Parts from Suppliers with the 100 highest account balances. Functionality: Query 2 is a correlated subquery based on a 5-table join in both outer query and inner query. Close to 5% of the Supplier rows result from the selection criteria and query processing, but only the 100 Suppliers with the highest account balances are returned. Query 3 - Shipping Priority TPC-D Business Question: Retrieves the shipping priority and potential revenue of Orders having the largest revenue among those that had not been shipped as of a given date. Orders are listed in decreasing order of revenue. If more than 10 unshipped Orders exist, only the 10 Orders with the largest revenue are listed. Functionality: Query 3 performs a 3-table join on three of the larger tables in the database. Anywhere from 1/5 to _ of the rows of each of the 3 tables participate in the joins, with a final aggregation that produces a very high number of rows, in the millions for most volume points. Only the 10 Orders with the highest revenue are returned. Query 4 - Order Priority Checking TPC-D Business Question: Counts the number of Orders ordered in a given quarter of a given year in which at least one Lineitem was received by the Customer later than its committed date. The query lists the count of such Orders for each order priority sorted in ascending priority order. Functionality: Query 4 is a correlated subquery in which about 1/28 of the Lineitem rows are selected for evaluation based on order date. An aggregation that produces a count by priority produces 5 rows in the answer set. Query 5 - Local Supplier Volume TPC-D Business Question: Lists for each Nation in a Region the revenue volume that resulted from Lineitem transactions in which the Customer ordering parts and the Supplier filling them were both within that Nation. The query considers only Parts ordered in a given year, displaying the Nations and revenue volume in descending order by revenue. Functionality: This is a 5-table join of large and small tables, where the data aggregated is reduced down to 1/5 of the Customers and Suppliers (representing one Region out of five) and 1/7 of the Lineitems (one © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398