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

Query 16 - Original, Query 16 - Original Con't., Query 17 - Original

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 44 highlights

Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Query 16 - Original SELECT P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT FROM PARTSUPP,PARTS Query 16 - Original (Con't.) WHERE P_PARTKEY = PS_PARTKEY AND P_BRAND 'Brand#25' AND P_TYPE NOT LIKE 'STANDARD BRUSHED%' AND P_SIZE IN (35, 15 ,43 ,27 ,30, 8, 42, 17) AND PS_SUPPKEY NOT IN (SELECT S_SUPPKEY FROM SUPPLIER WHERE S_COMMENT LIKE '%Better Business Bureau%Complaints%' ) GROUP BY P_BRAND, P_TYPE, P_SIZE ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE; Query 17 - Original SELECT SUM(L_EXTENDEDPRICE)/7.0 AS AVG_YEARLY FROM LINEITEM, PARTS WHERE P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#25' AND P_CONTAINER = 'SM CAN' AND L_QUANTITY < (SELECT 0.2* AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY); © 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 C
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers
1998 Compaq Computer Corporation, All Rights Reserved
Doc No ECG156/0398
Query 16 – Original
SELECT
P_BRAND,
P_TYPE,
P_SIZE,
COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT
FROM PARTSUPP,PARTS
Query 16 – Original (Con’t.)
WHERE
P_PARTKEY = PS_PARTKEY
AND P_BRAND <> 'Brand#25'
AND P_TYPE NOT LIKE 'STANDARD BRUSHED%'
AND P_SIZE IN (35, 15 ,43 ,27 ,30, 8, 42, 17)
AND PS_SUPPKEY NOT IN
(SELECT
S_SUPPKEY
FROM SUPPLIER
WHERE
S_COMMENT LIKE '%Better Business Bureau%Complaints%'
)
GROUP BY P_BRAND, P_TYPE, P_SIZE
ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE;
Query 17 – Original
SELECT
SUM(L_EXTENDEDPRICE)/7.0 AS AVG_YEARLY
FROM LINEITEM, PARTS
WHERE
P_PARTKEY = L_PARTKEY
AND P_BRAND = 'Brand#25'
AND P_CONTAINER = 'SM CAN'
AND L_QUANTITY <
(SELECT
0.2* AVG(L_QUANTITY)
FROM LINEITEM
WHERE
L_PARTKEY = P_PARTKEY);