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

And L_receiptdate < Add_monthsto_date'1993-01-01

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

Appendix C DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers LOW_LINE_COUNT FROM ORDERS, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND L_SHIPMODE IN ('SHIP','RAIL') AND L_COMMITDATE < L_RECEIPTDATE AND L_SHIPDATE < L_COMMITDATE Query 12 - Variant B (Con't.) AND L_RECEIPTDATE >= TO_DATE('1993-01-01','YYYY-MM-DD') AND L_RECEIPTDATE < ADD_MONTHS(TO_DATE('1993-01-01', 'YYYY-MM-DD'),12) GROUP BY L_SHIPMODE ORDER BY L_SHIPMODE; Query 13 - Original SELECT YEAR, SUM(REVENUE) AS REVENUE FROM (SELECT TO_CHAR(O_ORDERDATE,'YYYY') AS YEAR, L_EXTENDEDPRICE * (1-L_DISCOUNT) AS REVENUE FROM LINEITEM, ORDERS WHERE O_ORDERKEY = L_ORDERKEY AND O_CLERK = 'Clerk#000000977' AND L_RETURNFLAG = 'R' ) PERFORMANCE GROUP BY YEAR ORDER BY YEAR; Query 14 - Variant C CREATE TABLE ALL_SALES0 (TYPE VARCHAR2(25), AMOUNT NUMBER(20, 2)) tablespace TS_S ; CREATE TABLE SUM_PROMO_SALES0 (PROMO_AMOUNT NUMBER(20, 2)) tablespace TS_S ; CREATE TABLE SUM_ALL_SALES0 (ALL_AMOUNT NUMBER(20, 2)) tablespace TS_S ; INSERT INTO ALL_SALES0 SELECT P_TYPE, SUM(L_EXTENDEDPRICE *(1-L_DISCOUNT)) FROM LINEITEM, PARTS WHERE L_PARTKEY = P_PARTKEY AND L_SHIPDATE >= TO_DATE('1997-12-01','YYYY-MM-DD') AND L_SHIPDATE < ADD_MONTHS(TO_DATE('1997-12-01','YYYY-MM-DD'), 1) GROUP BY P_TYPE; © 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
LOW_LINE_COUNT
FROM ORDERS, LINEITEM
WHERE
O_ORDERKEY = L_ORDERKEY
AND L_SHIPMODE IN ('SHIP','RAIL')
AND L_COMMITDATE < L_RECEIPTDATE
AND L_SHIPDATE < L_COMMITDATE
Query 12 – Variant B (Con’t.)
AND L_RECEIPTDATE >= TO_DATE('1993-01-01','YYYY-MM-DD')
AND L_RECEIPTDATE < ADD_MONTHS(TO_DATE('1993-01-01',
'YYYY-MM-DD'),12)
GROUP BY L_SHIPMODE
ORDER BY L_SHIPMODE;
Query 13 – Original
SELECT
YEAR,
SUM(REVENUE) AS REVENUE
FROM
(SELECT
TO_CHAR(O_ORDERDATE,'YYYY') AS YEAR,
L_EXTENDEDPRICE * (1-L_DISCOUNT) AS REVENUE
FROM LINEITEM, ORDERS
WHERE
O_ORDERKEY = L_ORDERKEY
AND O_CLERK = 'Clerk#000000977'
AND L_RETURNFLAG = 'R'
) PERFORMANCE
GROUP BY YEAR
ORDER BY YEAR;
Query 14 – Variant C
CREATE TABLE ALL_SALES0 (TYPE VARCHAR2(25), AMOUNT NUMBER(20, 2))
tablespace TS_S
;
CREATE TABLE SUM_PROMO_SALES0 (PROMO_AMOUNT NUMBER(20, 2))
tablespace TS_S
;
CREATE TABLE SUM_ALL_SALES0 (ALL_AMOUNT NUMBER(20, 2))
tablespace TS_S
;
INSERT INTO ALL_SALES0
SELECT
P_TYPE,
SUM(L_EXTENDEDPRICE *(1-L_DISCOUNT))
FROM
LINEITEM, PARTS
WHERE
L_PARTKEY = P_PARTKEY
AND L_SHIPDATE >= TO_DATE('1997-12-01','YYYY-MM-DD')
AND L_SHIPDATE < ADD_MONTHS(TO_DATE('1997-12-01','YYYY-MM-DD'), 1)
GROUP BY P_TYPE;