Oracle9i 2002 2
Oracle9i... 4... 4... 4 Oracle... 4 SQL... 6... 6... 6... 7... 7... 9... 9... 9 CUBE... 10... 11... 11... 11 OR... 12... 12... 14... 14... 15... 15... 16... 16... 18... 18... 18... 19... 19... 19 GROUP-BY... 20... 20 OLAP... 20... 21... 21... 22... 22... 22... 23... 23... 23... 24... 24 Oracle9i 2 Oracle Corporation Query Optimization in Oracle9i
... 24... 24... 25... 26... 26... 26... 28... 29 Oracle9i 3 Oracle Corporation Query Optimization in Oracle9i
Oracle9i Oracle Oracle9i Oracle SQL SQL SQL SQL SQL Oracle Oracle 1992 Oracle7 10 Oracle9i 4 Oracle Corporation Query Optimization in Oracle9i
Oracle Oracle 4 SQL : Oracle SQL SQL SQL : SQL Oracle EXPLAIN PLAN Oracle v$sql_plan SQL SQL : Oracle SQL I/O CPU : SQL Oracle CPU Oracle Oracle Oracle Oracle Applications SAP Peoplesoft Oracle9i 5 Oracle Corporation Query Optimization in Oracle9i
SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL Oracle SQL 2 : SQL : 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 Oracle9i 6 Oracle Corporation Query Optimization in Oracle9i
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 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 Oracle9i 7 Oracle Corporation Query Optimization in Oracle9i
OPERATION OBJECT NAME OPTIONS SELECT STATEMENT FILTER TABLE ACCESS DEPT FULL TABLE ACCESS EMP FULL DEPT EMP 1 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; 1 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; Oracle9i 8 Oracle Corporation Query Optimization in Oracle9i
1 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' 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' 1 Oracle Vice President 100000 Dallas 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'); DEPT LOC 2 1 Oracle Oracle9i 9 Oracle Corporation Query Optimization in Oracle9i
/ CREATE VIEW EMP_AGG AS SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO; SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10; Oracle DEPTNO=10 SQL SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO; GROUP-BY DEPTNO=10 Oracle WHERE WHERE GROUP-BY / CUBE SQL CUBE SQL group-by 1 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 ; 2001 1 SQL CUBE SQL Oracle9i 10 Oracle Corporation Query Optimization in Oracle9i
1 1 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; sales_summary sales 1 Oracle Oracle 1 Oracle Oracle Oracle9i 11 Oracle Corporation Query Optimization in Oracle9i
Oracle Oracle9i OR WHERE OR OR UNION ALL OR Oakland 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 P1 2 P2 1 ( ) Oracle SQL Oracle9i 12 Oracle Corporation Query Optimization in Oracle9i
2001 3 DAY QUARTER 2 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 2 day_id product_id 2 day_id product_id 2 select-list store.state store PRODUCT DAY QUARTER 2 Oracle Oracle9i 13 Oracle Corporation Query Optimization in Oracle9i
Oracle 1 Oracle EXPLAIN PLAN Oracle v$sql_plan Oracle Oracle Oracle Oracle B B B - - Oracle9i 14 Oracle Corporation Query Optimization in Oracle9i
Oracle / and-equal B / AND/OR MINUS (NOT) COUNT / Oracle 5 120 5! = 120 5 10 3,000,000 100,000,000 Oracle 1 Oracle 1 10 Oracle9i 15 Oracle Corporation Query Optimization in Oracle9i
Oracle DBA DBA Oracle Oracle 1 Oracle Oracle B B I/O 1/10 10 Key Data Warehousing Features in Oracle9i: A Comparative Analysis AND/OR Oracle DML Oracle Oracle WHERE AND/OR/NOT Oracle9i 16 Oracle Corporation Query Optimization in Oracle9i
Oracle AND ROWID http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/books/ez30xb00/2.4.1 Oracle SELECT COUNT(*) FROM CUSTOMER WHERE STATE = 'CA' AND MARITAL_STATUS = 'MARRIED' / Oracle CA AND 1 2 Oracle Oracle Oracle Oracle Oracle Oracle 1 B B Oracle9i 17 Oracle Corporation Query Optimization in Oracle9i
ROWID/ product_id 2 1. : ID 2. : Oracle Oracle Oracle Oracle Oracle / Oracle Oracle I/O Oracle9i 18 Oracle Corporation Query Optimization in Oracle9i
ROWID WHERE ROWID / Oracle Oracle / / / Oracle 2 3 Oracle 3 Oracle9i 19 Oracle Corporation Query Optimization in Oracle9i
GROUP-BY Oracle GROUP-BY 1 Oracle DISTINCT GROUP BY ORDER BY 1 ORDER BY Oracle ORDER BY OLAP Oracle OLAP SQL SQL CUBE ROLLUP 1 Oracle 1 Oracle9i 20 Oracle Corporation Query Optimization in Oracle9i
CPU 1 SQL Oracle SQL Oracle SQL Oracle Microsoft SQL Server IBM Informix Sybase 1999 International DB2 Users Group Meeting IBM Almaden Research Center Why does DB2's optimizer generate wrong plans? Oracle Oracle Oracle E-Business Suite 11i Oracle9i 21 Oracle Corporation Query Optimization in Oracle9i
ERP 270,000 SQL 0.3% Oracle Oracle I/O N Oracle DBA SQL Oracle 2 B WHERE Oracle Oracle Oracle Oracle9i 22 Oracle Corporation Query Optimization in Oracle9i
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 Oracle9i 23 Oracle Corporation Query Optimization in Oracle9i
Oracle Oracle 1 Oracle DBA Oracle Oracle Oracle 2 SQL WHERE WHERE Oracle DBA 1 Oracle Oracle 2 : 1 2 WHERE SELECT * FROM EMP WHERE JOB_TITLE = 'VICE PRESIDENT' AND SAL < 40000 Oracle9i 24 Oracle Corporation Query Optimization in Oracle9i
2 5% Vice President 40% 40,000 0.05 0.40 = 0.02 Vice President Job_title Job_title 2% : DBA Oracle WHERE Oracle 2 N N Oracle Oracle9i 25 Oracle Corporation Query Optimization in Oracle9i
/ Oracle Oracle SQL CPU SQL SQL SQL Oracle Oracle9i 26 Oracle Corporation Query Optimization in Oracle9i
SQL 100 1% 1000 0.1% Oracle DBA Oracle SQL DBA Oracle Oracle Oracle Oracle DBA Oracle9i 27 Oracle Corporation Query Optimization in Oracle9i
Oracle DBA CPU 2 : : DBA 1 SQL CPU CPU SQL 2 SQL 20 SQL SQL Oracle9i 28 Oracle Corporation Query Optimization in Oracle9i
Oracle Oracle Oracle Oracle9i 29 Oracle Corporation Query Optimization in Oracle9i
Oracle9i 2000 2 : George Lumpkin, Hakan Jakobsson Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. : : +1.650.506.7000 : +1.650.506.7200 www.oracle.com Oracle Corporation Query Optimization in Oracle9i Oracle Copyright 2002 Oracle Corporation All rights reserved.