Oracle的优化器有两种优化方式(二)

梦里梦外; 2022-07-12 05:17 242阅读 0赞

15. **/\*+USE\_CONCAT\*/**  
对查询中的WHERE后面的OR条件进行转换为UNION  ALL的组合查询. (懵懂啊,先存着)  
例如:  
select /\*+use\_concat \*/ \* from emp where deptno=10 OR empno=7788;

Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=174)

1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=2 Bytes=174)  
  
16. **/\*+NO\_EXPAND\*/**   
对于WHERE后面的OR 或者IN-LIST的查询语句,

NO\_EXPAND将阻止其基于优化器对其进行扩展.

SELECT /\*+NO\_EXPAND\*/  \*  FROM  EMP  WHERE DEPTNO=10 AND job='MANAGER';  
Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=87)

1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=87)  
  
17. **/\*+NOWRITE\*/**   
禁止对查询块的查询重写操作.  
  
18. **/\*+REWRITE\*/**  
可以将视图作为参数.  
  
19. **/\*+MERGE(TABLE)\*/**   
能够对视图的各个查询进行相应的合并.  
20. **/\*+NO\_MERGE(TABLE)\*/**   
对于有可合并的视图不再合并.  
  
21. **/\*+ORDERED\*/**   
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.

SELECT  /\*+ORDERED\*/ D.DNAME,E.ENAME,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;  
Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4510)

1    0   HASH JOIN (Cost=5 Card=82 Bytes=4510)

2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2706)

3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)  
兑换下EMP和DEPT的顺序,得到

SELECT  /\*+ORDERED\*/ D.DNAME,E.ENAME,E.SAL FROMDEPT D, EMP EWHERE E.DEPTNO=D.DEPTNO;

Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4510)

1    0   HASH JOIN (Cost=5 Card=82 Bytes=4510)

2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)

3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2706)

对比下没有使用强制ORDERED排列的情况的执行计划

SELECT D.DNAME,E.ENAME,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;

Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   NESTED LOOPS

2    1     TABLE ACCESS (FULL) OF 'EMP'

3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4    3       INDEX (UNIQUE SCAN) OF 'PK\_DEPT' (UNIQUE)

22. **/\*+USE\_NL(TABLE)\*/**   
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

**select***/\*+ORDERED USE\_NL(EMP)\*/* empno,DNAME **from** emp, dept  **where** emp.deptno = dept.deptno;  
Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936)

1    0   HASH JOIN (Cost=5 Card=82 Bytes=3936)

2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2132)

3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804 )

对比下使用DEPT作为内部表的执行计划(可以看到耗费的时间明显增加)

**select***/\*+ORDERED USE\_NL(DEPT)\*/* empno,DNAME **from** emp, dept **where** emp.deptno = dept.deptno;

Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=82 Bytes=3936)

1    0   NESTED LOOPS (Cost=84 Card=82 Bytes=3936)

2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2132)

3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=22)

4    3       INDEX (UNIQUE SCAN) OF 'PK\_DEPT' (UNIQUE)

23. **/\*+USE\_MERGE(TABLE)\*/**   
将指定的表与其他行源通过合并排序连接方式连接起来(可是执行计划显示的是HASH JOIN).

select  /\*+USE\_MERGE(emp,dept)\*/ e.ename, d.deptno,dname  from emp e, dept d

where e.deptno = d.deptno  order by e.deptno, d.deptno;  
Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=82 Bytes=3444)

1    0   SORT (ORDER BY) (Cost=7 Card=82 Bytes=3444)

2    1     HASH JOIN (Cost=5 Card=82 Bytes=3444)

3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=1640)

4    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)  
  
24. **/\*+USE\_HASH(TABLE)\*/**   
将指定的表与其他行源通过哈希连接方式连接起来.  
**select***/\*+use\_hash(emp,dept) \*/* empno ,dname   **from** emp, dept   **where** emp.deptno = dept.deptno;  
Execution Plan

\----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936)

1    0   HASH JOIN (Cost=5 Card=82 Bytes=3936)

2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=2132)

3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)

25. **/\*+DRIVING\_SITE(TABLE)\*/**  
强制与ORACLE所选择的位置不同的表进行查询执行. (执行计划看不出任何区别,字面懵懂)  
  
26. **/\*+LEADING(TABLE)\*/**  
将指定的表作为连接次序中的首表.  
  
27. **/\*+CACHE(TABLE)\*/**   
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端  
  
28. **/\*+NOCACHE(TABLE)\*/**  
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端  
  
29. **/\*+APPEND\*/**   
直接插入到表的最后,可以提高速度.   
insert /\*+append\*/ into  empback  select \* from emp ;   
  
30. **/\*+NOAPPEND\*/**   
通过在插入语句生存期内停止并行模式来启动常规插入.  
insert /\*+noappend\*/ into empback select \* from emp ;