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

DSS Sizing and Tuning of Oracle8 for Windows NT on Compaq Servers Appendix C Query 6 - Original SELECT SUM(L_EXTENDEDPRICE * L_DISCOUNT) AS REVENUE FROM LINEITEM WHERE L_SHIPDATE >= TO_DATE('1997-01-01','YYYY-MM-DD') AND L_SHIPDATE < ADD_MONTHS(TO_DATE('1997-01-01','YYYY-MM-DD'),12) AND L_DISCOUNT BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 AND L_QUANTITY < 24; Query 7 - Original SELECT SUPP_NATION, CUST_NATION, YEAR, SUM(VOLUME) AS REVENUE FROM (SELECT N1.N_NAME AS SUPP_NATION, N2.N_NAME AS CUST_NATION, TO_CHAR(L_SHIPDATE,'YYYY') AS YEAR, L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2 WHERE S_SUPPKEY = L_SUPPKEY AND O_ORDERKEY = L_ORDERKEY AND C_CUSTKEY = O_CUSTKEY AND S_NATIONKEY = N1.N_NATIONKEY AND C_NATIONKEY = N2.N_NATIONKEY AND ((N1.N_NAME = 'UNITED STATES' AND N2.N_NAME = 'INDIA') OR (N1.N_NAME = 'INDIA' AND N2.N_NAME = 'UNITED STATES')) AND L_SHIPDATE BETWEEN TO_DATE('1995-01-01','YYYY-MM-DD') AND TO_DATE('1996-12-31','YYYY-MM-DD') ) SHIPPING GROUP BY SUPP_NATION, CUST_NATION, YEAR ORDER BY SUPP_NATION, CUST_NATION, YEAR; Query 8 - Variant B SELECT YEAR, SUM(DECODE(NATION, 'UNITED STATES', VOLUME, 0)) / SUM(VOLUME) AS MKT_SHARE FROM (SELECT TO_CHAR(O_ORDERDATE,'YYYY') AS YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION FROM PARTS, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION WHERE P_PARTKEY = L_PARTKEY AND S_SUPPKEY = L_SUPPKEY © 1998 Compaq Computer Corporation, All Rights Reserved Doc No ECG156/0398