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

Query 16 - Parts/Supplier Relationship, Query 17 - Small Quantity Order Review

Get HP ProLiant 3000 PDF manuals and user guides View all HP ProLiant 3000 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 36 highlights

Appendix B DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Functionality: Query 15 requires either a view with aggregates or a temporary table. In the view, 1/28th of the Lineitems (3 months) are selected based on date and aggregated on Supplier. Close to all the Suppliers in the database will participate as output from this first step aggregation. The query itself returns details from the Supplier row which represents the maximum revenue. One row is returned from this query. Query 16 - Parts/Supplier Relationship TPC-D Business Question: Counts the number of Suppliers who can supply Parts that satisfy a particular customer's requirements. The Customer is interested in Parts of eight different sizes as long as they are not a given type, not of a given brand, and not from a Supplier who has had complaints registered at the Better Business Bureau. Results must be presented in descending count and ascending brand, type and size. Functionality: Query 16 uses a subquery with text compares using wild cards to select eligible Suppliers. It uses a two table join for Part and Part Supplier. Inequality and IN/NOT IN functions, with comparison to parameters passed into the query, are used to pare down the answer set. Query 17 - Small Quantity Order Review TPC-D Business Question: Considers parts of a given brand and with a given container type and determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7year database. What would be the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of less than 20% of this average were no longer taken? Functionality: Query 17 uses correlated subquery functionality to perform what-if analysis. A join of Lineitem and Part take place in both inner and outer queries. 1/1000th of the rows in the Part table qualify based on the selection criteria. The item quantity that represents 20% of the average item quantity is calculated in the subquery with the AVERAGE aggregation. © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
Appendix B
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers
1998 Compaq Computer Corporation, All Rights Reserved
Doc No ECG156/0398
Functionality
: Query 15 requires either a view with aggregates or a temporary table. In the view, 1/28th
of the Lineitems (3 months) are selected based on date and aggregated on Supplier. Close to all the
Suppliers in the database will participate as output from this first step aggregation. The query itself
returns details from the Supplier row which represents the maximum revenue. One row is returned from
this query.
Query 16 - Parts/Supplier Relationship
TPC-D Business Question:
Counts the number of Suppliers who can supply Parts that satisfy a particular
customer's requirements. The Customer is interested in Parts of eight different sizes as long as they are
not a given type, not of a given brand, and not from a Supplier who has had complaints registered at the
Better Business Bureau. Results must be presented in descending count and ascending brand, type and
size.
Functionality
: Query 16 uses a subquery with text compares using wild cards to select eligible Suppliers.
It uses a two table join for Part and Part Supplier.
Inequality and IN/NOT IN functions, with comparison
to parameters passed into the query, are used to pare down the answer set.
Query 17 - Small Quantity Order Review
TPC-D Business Question:
Considers parts of a given brand and with a given container type and
determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-
year database.
What would be the average yearly gross (undiscounted) loss in revenue if orders for these
parts with a quantity of less than 20% of this average were no longer taken?
Functionality:
Query 17 uses correlated subquery functionality to perform what-if analysis. A join of
Lineitem and Part take place in both inner and outer queries. 1/1000th of the rows in the Part table qualify
based on the selection criteria. The item quantity that represents 20% of the average item quantity is
calculated in the subquery with the AVERAGE aggregation.