Oracle9i 的查询优化

Size: px

Start display at page:

Download "Oracle9i 的查询优化"

顷晏
4 years ago
Views:

1 Oracle9i Oracle

2 Oracle9i Oracle?...4 SQL CUBE OR OLAP Oracle9i 2

3 Oracle9i 3

4 Oracle9i Oracle Oracle Oracle SQL SQL SQL, SQL SQL Oracle? Oracle 1992 Oracle7 10 Oracle Oracle Oracle9i 4

5 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

6 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

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

8 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

9 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 SELECT * FROM EMP, DEPT WHERE (EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS' AND SAL > ) 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 > 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

10 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

11 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

12 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

13 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

14 Oracle Oracle EXPLAIN PLAN Oracle v$sql_plan Oracle Oracle Oracle Oracle B B B Oracle9i 14

15 Oracle / and-equal B AND/OR MINUS (NOT) COUNT Oracle Oracle Oracle Oracle Oracle9i 15

16 DBA DBA Oracle Oracle Oracle Oracle B B I/O 10 Oracle9i AND OR Oracle DML Oracle Oracle WHERE AND OR NOT Oracle9i 16

17 Oracle AND 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

18 / Oracle Oracle Oracle Oracle Oracle Oracle Oracle I/O Oracle9i 18

19 WHERE / Oracle Oracle / / Oracle Oracle 8 Oracle9i 19

20 Oracle GROUP-BY Oracle DISTINCT, GROUP BY, ORDER BY ORDER BY Oracle ORDER BY OLAP Oracle OLAP SQL CUBE ROLLUP SQL Oracle Oracle9i 20

21 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

22 Oracle Oracle I/O N Oracle DBA SQL Oracle B WHERE Oracle Oracle Oracle Oracle9i 22

23 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

24 Oracle DBA Oracle Oracle Oracle SQL WHERE WHERE Oracle DBA Oracle Oracle WHERE SELECT * FROM EMP WHERE JOB_TITLE = 'VICE PRESIDENT' AND SAL < % 40% 40, *.40 =.02 2% Oracle9i 24

25 DBA Oracle WHERE Oracle N N Oracle Oracle Oracle SQL Oracle9i 25

26 CPU SQL SQL SQL Oracle SQL 100 1% % Oracle9i 26

27 Oracle DBA Oracle SQL Oracle DBA Oracle Oracle Oracle DBA Oracle DBA CPU DBA SQL CPU CPU SQL Oracle9i 27

28 SQL 20 SQL SQL Oracle Oracle Oracle Oracle9i 28

29 Oracle9i George Lumpkin, Hakan Jakobsson Oracle Corporation 500 Oracle Parkway Redwood Shores, CA U.S.A Oracle Oracle Corporation Oracle Corporation 2002 Oracle Oracle9i 29

DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( ) SQL ( ) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) S

9 DB2 优化器 DB2 SQL select c1 c2 from ( DB2 )??? DB2?!?, no no DB2 I/O ( transrate overhead ) SQL DML (INSERT UPDATE DELETE) DB2 (access plan) DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( 728 747 ) SQL