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

Group By C_custkey, C_name, C_acctbal, C_phone, N_name, C_address

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

DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C AND O_ORDERDATE < ADD_MONTHS(TO_DATE('1995-01-01' ,'YYYY-MM-DD'),3) AND L_RETURNFLAG = 'R' AND C_NATIONKEY = N_NATIONKEY GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT ORDER BY REVENUE DESC; Query 11 - Variant A CREATE TABLE PART_VALUE0 (PARTNO INTEGER, VALUE NUMBER(20, 2) ) tablespace TS_S ; INSERT INTO PART_VALUE0 SELECT PS_PARTKEY, SUM(PS_SUPPLYCOST*PS_AVAILQTY) FROM PARTSUPP, SUPPLIER, NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'UNITED STATES' GROUP BY PS_PARTKEY; CREATE TABLE SUM_PART_VALUE0 (TOTAL_VALUE NUMBER(20, 2)) tablespace TS_S ; INSERT INTO SUM_PART_VALUE0 SELECT SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0000010000 FROM PARTSUPP, SUPPLIER, NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'UNITED STATES'; SELECT PARTNO AS P_PARTKEY, VALUE FROM PART_VALUE0 WHERE VALUE > (SELECT SUM(TOTAL_VALUE) FROM SUM_PART_VALUE0 ) ORDER BY VALUE DESC; DROP TABLE PART_VALUE0; DROP TABLE SUM_PART_VALUE0; Query 12 - Variant B SELECT L_SHIPMODE, SUM(DECODE(O_ORDERPRIORITY,'1-URGENT',1,'2-HIGH',1,0)) AS HIGH_LINE_COUNT, SUM(DECODE(O_ORDERPRIORITY,'1-URGENT',0,'2-HIGH',0,1)) AS © 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
DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers
Appendix C
1998 Compaq Computer Corporation, All Rights Reserved
Doc No ECG156/0398
AND O_ORDERDATE < ADD_MONTHS(TO_DATE('1995-01-01' ,'YYYY-MM-DD'),3)
AND L_RETURNFLAG = 'R'
AND C_NATIONKEY = N_NATIONKEY
GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS,
C_COMMENT
ORDER BY REVENUE DESC;
Query 11 – Variant A
CREATE TABLE PART_VALUE0 (PARTNO INTEGER, VALUE NUMBER(20, 2) )
tablespace TS_S
;
INSERT INTO PART_VALUE0
SELECT
PS_PARTKEY,
SUM(PS_SUPPLYCOST*PS_AVAILQTY)
FROM PARTSUPP, SUPPLIER, NATION
WHERE
PS_SUPPKEY = S_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'UNITED STATES'
GROUP BY PS_PARTKEY;
CREATE TABLE SUM_PART_VALUE0 (TOTAL_VALUE NUMBER(20, 2))
tablespace TS_S
;
INSERT INTO SUM_PART_VALUE0
SELECT
SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0000010000
FROM PARTSUPP, SUPPLIER, NATION
WHERE
PS_SUPPKEY = S_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'UNITED STATES';
SELECT
PARTNO AS P_PARTKEY,
VALUE
FROM PART_VALUE0
WHERE
VALUE >
(SELECT
SUM(TOTAL_VALUE)
FROM SUM_PART_VALUE0
)
ORDER BY VALUE DESC;
DROP TABLE PART_VALUE0;
DROP TABLE SUM_PART_VALUE0;
Query 12 – Variant B
SELECT
L_SHIPMODE,
SUM(DECODE(O_ORDERPRIORITY,'1-URGENT',1,'2-HIGH',1,0)) AS
HIGH_LINE_COUNT,
SUM(DECODE(O_ORDERPRIORITY,'1-URGENT',0,'2-HIGH',0,1)) AS