This hint is valid only for the following parameters:
OPTIMIZER_DYNAMIC_SAMPLING,
OPTIMIZER_INDEX_CACHING,
OPTIMIZER_INDEX_COST_ADJ,
OPTIMIZER_SECURE_VIEW_MERGING,
STAR_TRANSFORMATION_ENABLED
加hint后
备之后查
OPTIMIZER_DYNAMIC_SAMPLING,
OPTIMIZER_INDEX_CACHING,
OPTIMIZER_INDEX_COST_ADJ,
OPTIMIZER_SECURE_VIEW_MERGING,
STAR_TRANSFORMATION_ENABLED
举例如下
SQL> set autotrace trace
SQL> select empno from emp e, dept d where e.ename=d.dname ;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3434568436
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 80 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX_DEPT1 | 4 | 40 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."ENAME"="D"."DNAME")
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
286 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
加hint后
SQL> select /*+ opt_param('hash_join_enabled','false') */ empno from emp e, dept d where e.ename=d.dname;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2082400136
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 80 | 6 (34)| 00:00:01 |
| 1 | MERGE JOIN | | 4 | 80 | 6 (34)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 40 | 2 (50)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_DEPT1 | 4 | 40 | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."ENAME"="D"."DNAME")
filter("E"."ENAME"="D"."DNAME")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
286 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
备之后查