优化器的查询转换

1.视图合并

?

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 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

SQL> create view emp_vm as   2  select empno,ename,job,sal,comm,deptno   3  from emp   4  where deptno=30;   View created.   SQL> select empno   2  from emp_vm   3  where empno > 150;        EMPNO ----------       7499       7521       7654       7698       7844       7900   6 rows selected.   SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  997u1v1b1k5sc, child number 0 ------------------------------------- select empno from emp_vm where empno > 150   Plan hash value: 169057108   -------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          | |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     5 |    35 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | PK_EMP |    14 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$F5BB74E1 / EMP@SEL$2    2 - SEL$F5BB74E1 / EMP@SEL$2   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$F5BB74E1")       MERGE(@"SEL$2")       OUTLINE(@"SEL$1")       OUTLINE(@"SEL$2")       INDEX_RS_ASC(@"SEL$F5BB74E1" "EMP"@"SEL$2" ("EMP"."EMPNO"))       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("DEPTNO"=30)    2 - access("EMPNO">150)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "EMPNO"[NUMBER,22]    2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]     49 rows selected.   SQL>

2.谓词推进

优化器将查询块的相关的谓词推进到视图查询块。

a.定义一个视图

?

1 2 3 4

create view all_emp_vw as     ( select empno, ename, job, comm, deptno from emp )     union     ( select empno, ename, job, comm, deptno from contract_workers );

 b.执行查询

?

1 2 3

select ename from all_emp_vw where empno = 50;

c.谓词推进(优化器真正执行的sql)

?

1 2 3 4

select ename from ( select empno, ename, job, comm, deptno from emp where empno=50 union select empno, ename, job, comm, deptno from contract_workers where empno=50 );

3.非嵌套子查询：将子查询转换成等价的join连接

4.使用物化视图进行查询重写