oracle hints
来源:互联网 发布:gpd pocket 知乎 编辑:程序博客网 时间:2024/04/30 03:54
oracle hints
今天是2013-10-08,对于oracle hint有很多,具体可以参考联机手册:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABIJGJF
http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF501
刚刚开始,我进行hash join连接发现如下:
SQL> select /*+use_hash(emp)*/ empno from emp,dept where dept.deptno=emp.deptno;Execution Plan----------------------------------------------------------Plan hash value: 716400937-----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 1 (0)| 00:00:01 || 1 | NESTED LOOPS | | 14 | 140 | 1 (0)| 00:00:01 || 2 | INDEX FULL SCAN | IND_EMP | 14 | 98 | 1 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN| REVERSE_INDEX | 1 | 3 | 0 (0)| 00:00:01 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")SQL>
我明明指定的是emp做为驱动表然后进行hash join,但是不行,需要指定另个表,但是use_hash不能规定优化器来选择驱动表。
eg:
SQL> select /*+use_hash(emp,dept)*/ empno from emp,dept where dept.deptno=emp.deptno;Execution Plan----------------------------------------------------------Plan hash value: 2255485930----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")SQL> select /*+use_hash(dept,emp)*/ empno from dept,emp where dept.deptno=emp.deptno;Execution Plan----------------------------------------------------------Plan hash value: 2255485930----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")SQL>
我们可以选择使用ordered或是leading来指定optimizer选择哪个表为驱动表。
note:
The LEADING
hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than theORDERED
hint.
The LEADING
hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflictingLEADING
hints, then all of them are ignored. If you specify the ORDERED
hint, it overrides all LEADING
hints.
The ORDERED
hint instructs Oracle to join tables in the order in which they appear in theFROM
clause. Oracle recommends that you use the LEADING
hint, which is more versatile than theORDERED
hint.
When you omit the ORDERED
hint from a SQL statement requiring a join, the optimizer chooses the order in which to join the tables. You might want to use theORDERED
hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.
eg:
SQL> select /*+leading(emp) use_hash(dept,emp)*/ empno from dept ,emp where dept.deptno=emp.deptno;Execution Plan----------------------------------------------------------Plan hash value: 929644576----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")SQL> select /*+leading(dept) use_hash(dept,emp)*/ empno from dept ,emp where dept.deptno=emp.deptno;Execution Plan----------------------------------------------------------Plan hash value: 2255485930----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")SQL>
SQL> select /*+ordered use_hash(emp,dept)*/ empno from emp,dept where emp.deptno=dept.deptno;Execution Plan----------------------------------------------------------Plan hash value: 929644576----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")SQL> select /*+ordered use_hash(emp,dept)*/ empno from dept,emp where emp.deptno=dept.deptno;Execution Plan----------------------------------------------------------Plan hash value: 2255485930----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0)| 00:00:01 ||* 1 | HASH JOIN | | 14 | 140 | 2 (0)| 00:00:01 || 2 | INDEX FULL SCAN| REVERSE_INDEX | 4 | 12 | 1 (0)| 00:00:01 || 3 | INDEX FULL SCAN| IND_EMP | 14 | 98 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")SQL>
- Oracle Hints
- Oracle Hints
- Oracle Hints
- Oracle Hints
- oracle hints
- Oracle Hints
- oracle hints
- Oracle Hints
- Oracle Hints
- ORACLE Hints
- Oracle SQL Hints
- Join Order Hints(Oracle)
- Oracle Hints用法汇总
- oracle hints用法总结
- oracle hints --english description
- Oracle hints语句
- Oracle Hints详解
- Oracle 所有的hints
- 以安装WordPress为例介绍如何使用WampServer
- Rational Rose 2003 下载、破解及安装方法(图文)
- asp.net gridview中添加checkbox列,用于全部选中记录或取消全选
- 基于XMPP协议的手机多方多端即时通讯方案
- TextFX插件命令功能
- oracle hints
- spring @RequestParam注释
- asp.net弹出多个模态窗口
- 代码覆盖率测试
- SOCKET的类型
- 关于android 自己实现 back键 home键, Intent flag 的一些用法
- js获取鼠标点击时坐标
- Node.js学习(10)----文件系统fs
- hadoop dfsadmin -setSpaceQuota 设置目录配额的问题