Oracle9i Oracle 2002 2
Oracle9i...4...4...4 Oracle?...4 SQL...5...6...6...6...7...8...9...9 CUBE...10...11...11...11 OR...12...12...14...14...15...15...16...16...18...18...18...19...19...19...20...20 OLAP...20...21...21 Oracle9i 2
...22...22...22...23...23...23...23...24...24...24...25...25...26...26...27...28 Oracle9i 3
Oracle9i Oracle Oracle Oracle SQL SQL SQL, SQL SQL Oracle? Oracle 1992 Oracle7 10 Oracle Oracle Oracle9i 4
Oracle 4 SQL Oracle SQL SQL SQL SQL, Oracle EXPLAIN PLAN Oracle v$sql_plan SQL SQL Oracle SQL I/OCPU SQL Oracle CPU Oracle Oracle Oracle Oracle SAP Peoplesoft SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL Oracle9i 5
SQL Oracle SQL SQL Oracle Oracle Oracle Oracle CREATE VIEW TEST_VIEW AS SELECT ENAME, DNAME, SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; SELECT ENAME, DNAME FROM TEST_VIEW WHERE SAL > 10000; EMP DEPT SAL SELECT ENAME, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000; EMP DEPT SAL>10000 GROUP BY DISTINCT Oracle Oracle9i 6
GROUP BY CREATE VIEW AVG_SAL_VIEW AS SELECT DEPTNO, AVG(SAL) AVG_SAL_DEPT FROM EMP GROUP BY DEPTNO Oakland SELECT DEPT.NAME, AVG_SAL_DEPT FROM DEPT, AVG_SAL_VIEW WHERE DEPT.DEPTNO = AVG_SAL_VIEW.DEPTNO AND DEPT.LOC = 'OAKLAND' SELECT DEPT.NAME, AVG(SAL) FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND DEPT.LOC = 'OAKLAND' GROUP BY DEPT.ROWID, DEPT.NAME EMP EMP Oracle 10000 SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000) Oracle OPERATION OBJECT_NAME OPTIONS SELECT STATEMENT FILTER TABLE ACCESS DEPT FULL TABLE ACCESS EMP FULL DEPT EMP Oracle9i 7
Oracle - - OPERATION OBJECT_NAME OPTIONS SELECT STATEMENT HASH JOIN SEMI TABLE ACCESS DEPT FULL TABLE ACCESS EMP FULL SQL SQL SQL SELECT DNAME FROM EMP E, DEPT D WHERE D.DEPTNO <SEMIJOIN> E.DEPTNO AND E.SAL > 10000; DEPT EMP OPERATION OBJECT_NAME OPTIONS SELECT STATEMENT HASH JOIN SORT UNIQUE TABLE ACCESS EMP FULL TABLE ACCESS DEPT FULL SQL SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000; Oracle SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002' Oracle9i 8
ORDER LINEITEM SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002' AND L_SHIPDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002' Oracle Dallas 100000 SELECT * FROM EMP, DEPT WHERE (EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS' AND SAL > 100000) OR (EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS' AND JOB_TITLE ='VICE PRESIDENT'} SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = DALLAS AND (SAL > 100000 OR JOB_TITLE = 'VICE PRESIDENT'); LOC DEPT Oracle CREATE VIEW EMP_AGG AS SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO; Oracle9i 9
SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10; Oracle DEPTNO=10 SQL SELECT DEPTNO, AVG(SAL)5 FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO; DEPTNO=10 GROUP-BY Oracle WHERE WHERE GROUP-BY CUBE SQL CUBE SQL group-by SQL CUBE SELECT MONTH, REGION, DEPARTMENT FROM (SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES GROUP BY CUBE (MONTH, REGION, DEPT)) WHERE MONTH = JAN-2001 ; SQL SELECT MONTH, REGION, DEPARTMENT FROM (SELECT MONTH, REGION, DEPARTMENT, SUM(SALES_AMOUNT) AS REVENUE FROM SALES WHERE MONTH = JAN-2001 GROUP BY MONTH, CUBE(REGION, DEPT)) WHERE MONTH = JAN-2001 ; SQL 2001 SQL CUBE Oracle9i 10
Oracle Oracle SQL SQL CREATE MATERIALIZED VIEW SALES_SUMMARY AS SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT FROM SALES, TIME WHERE SALES.TIME_ID = TIME.TIME_ID GROUP BY SALES.CUST_ID, TIME.MONTH; SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME WHERE SALES.CUST_ID = CUST.CUST_ID AND SALES.TIME_ID = TIME.TIME_ID GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH; SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH, SALES_SUMMARY.AMT FROM CUSTOMER, SALES_SUMMARY WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID; Oracle Oracle Oracle Oracle Oracle9i 11
Oracle Oracle9i OR WHERE OR OR UNION ALLOR Oakland SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND (P1.PORT_NAME = 'OAKLAND' OR P2.PORT_NAME = 'OAKLAND') SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P1.PORT_NAME = 'OAKLAND' UNION ALL SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P2.PORT_NAME = 'OAKLAND' AND P1.PORT_NAME <> 'OAKLAND' UNION ALL Oracle P1 Oracle P2 Oracle SQL Oracle9i 12
2001 DAY QUARTER SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, DAY, QUARTER, PRODUCT, STORE WHERE SALES.DAY_ID = DAY.DAY_ID AND DAY.QUARTER_ID =QUARTER.QUARTER_ID AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID AND SALES.STORE_ID = STORE.STORE_ID AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES' AND QUARTER.QUARTER_NAME = '2001Q3' GROUP BY STORE.STATE SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, STORE WHERE SALES.STORE_ID = STORE.STORE_ID AND SALES.DAY_ID IN (SELECT DAY.DAY_ID FROM DAY, QUARTER WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID AND QUARTER.QUARTER_NAME = '2001Q3') AND SALES.PRODUCT_ID IN (SELECT PRODUCT.PRODUCT_ID FROM PRODUCT WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES') GROUP BY STORE.STATE SQL DAY-ID PRODUCT-ID store.statestore PRODUCTDAY QUARTER Oracle Oracle9i 13
Oracle Oracle EXPLAIN PLAN Oracle v$sql_plan Oracle Oracle Oracle Oracle B B B Oracle9i 14
Oracle / and-equal B AND/OR MINUS (NOT) COUNT Oracle 5 120 5 10 3 1 Oracle Oracle Oracle Oracle9i 15
DBA DBA Oracle Oracle Oracle Oracle B B I/O 10 Oracle9i AND OR Oracle DML Oracle Oracle WHERE AND OR NOT Oracle9i 16
Oracle AND http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/books/ez30x B00/2.4.1 Oracle SELECT COUNT(*) FROM CUSTOMER WHERE STATE = 'CA' AND MARITAL_STATUS = 'MARRIED' Oracle CA MARRIED 1 Oracle Oracle Oracle Oracle Oracle Oracle B B Oracle9i 17
/ 1. 2. Oracle Oracle Oracle Oracle Oracle Oracle Oracle I/O Oracle9i 18
WHERE / Oracle Oracle / / Oracle Oracle 8 Oracle9i 19
Oracle GROUP-BY Oracle DISTINCT, GROUP BY, ORDER BY ORDER BY Oracle ORDER BY OLAP Oracle OLAP SQL CUBE ROLLUP SQL Oracle Oracle9i 20
CPU SQL Oracle SQL Oracle SQL OracleMicrosoft SQL ServerIBM InformixSybase IBM Almaden 1999 DB2 DB2 Oracle Oracle Oracle E-Business Suite 11i ERP 270,000 SQL 3% Oracle9i 21
Oracle Oracle I/O N Oracle DBA SQL Oracle B WHERE Oracle Oracle Oracle Oracle9i 22
CPU I/O CPU CPU I/O CPU I/O Oracle CPU I/O I/O I/O Oracle Oracle Oracle Oracle Oracle Oracle DBA DBA Oracle Oracle Oracle9i 23
Oracle DBA Oracle Oracle Oracle SQL WHERE WHERE Oracle DBA Oracle Oracle WHERE SELECT * FROM EMP WHERE JOB_TITLE = 'VICE PRESIDENT' AND SAL < 40000 5% 40% 40,000.05 *.40 =.02 2% Oracle9i 24
DBA Oracle WHERE Oracle N N Oracle Oracle Oracle SQL Oracle9i 25
CPU SQL SQL SQL Oracle SQL 100 1% 1000.1% Oracle9i 26
Oracle DBA Oracle SQL Oracle DBA Oracle Oracle Oracle DBA Oracle DBA CPU DBA SQL CPU CPU SQL Oracle9i 27
SQL 20 SQL SQL Oracle Oracle Oracle Oracle9i 28
Oracle9i 2002 2 George Lumpkin, Hakan Jakobsson Oracle Corporation 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. +1.650.506.7000 +1.650.506.7200 www.oracle.com Oracle Oracle Corporation Oracle Corporation 2002 Oracle Oracle9i 29