use_hash(table1,table2) 指定table1,table2连接以HASH 方式连接,采用这种方式,我们不能控制table1,table2哪个表为驱动表,
优化器会根据cost自动选择驱动表,如果我们非要控制table1为驱动表,可以加上leading(table1)作为提示,或者用ordered 作为提示。
另外如果我们使用use_hash(table1)有可能优化器不会选择hash join,也就是说如果不把表名写完整,优化器可能不会强制使用hint提示,这种行为我不是很理解,不过却给了我一个提示:Hint有时候也会失效,另外也给了我一个提示,使用hint一定要将条件写完整
下面是实验过程
SQL> select /*+ use_hash(emp) */ ename,dept.deptno from emp,dept where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3074306753
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
可能是我的写法有问题,难道非要像这样写use_hash(emp,dept)
SQL> select /*+ use_hash(emp,dept) */ ename,dept.deptno from emp,dept where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 71037407
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 168 | 5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
究竟是我的写法有问题 还是优化器会自己控制呢?这里给了俺提示,以后用提示 还是写完整比较好
从执行计划可以看出dept作为驱动表,如果我想要用emp作为驱动表,可以使用leading,ordered提示
SQL> select /*+ ordered use_hash(emp,dept) */ ename,dept.deptno from emp,dept where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3422444315
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 168 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
还可以这样写
SQL> select /*+ leading(emp) use_hash(emp,dept) */ ename,dept.deptno from dept,emp where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3422444315
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 168 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
use_nl (table1,table2....) 提示指示指定表作为inner table,如果指定的表已经作为了outer table(驱动表),那么优化器会忽略
use_nl 提示,如果非要强制它作为inner table ,可以与ordered 提示一起使用。
下面以SCOTT测试用户作为试验
SQL> select table_name,last_analyzed from user_tables;
TABLE_NAME LAST_ANALYZED
------------------------------ --------------
DEPT 01-12月-09
EMP 01-12月-09
BONUS 01-12月-09
SALGRADE 01-12月-09
表已经分析过了
SQL> select ename,dept.deptno from dept,emp where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3074306753
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
此处优化器选择emp作为驱动表(outer table),因为dept上有索引,而且索引正好建在连接列上,可见优化器选择是正确的
SQL> select /*+ use_nl(emp) */ ename,dept.deptno from dept,emp where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3074306753
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
由于emp作为outer table,优化器忽略use_nl提示
SQL> select /*+ ordered use_nl(emp) */ ename,dept.deptno from dept,emp where dept.deptno=emp.deptno;
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 3566768842
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 168 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 168 | 7 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 36 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
可见,现在dept作为了驱动表
驱动表(driving table/outer table)又称为外层表,驱动表仅仅用于nested loops join 和 hash join
驱动表是用来驱动查询的
在cbo中,优化器会根据cost自动选择驱动表,与表的顺序无关。
通常情况下,驱动表的选择性较高(该列唯一键与列的比值较高),where中的限制条件较多,返回的行数较少的表适合做驱动表,看见pub上面讨论小表适合做驱动表,事实上,大表有时候也会做驱动表,只要在大表上面限制条件较多,从大表上面返回的行数较少的话,大表也适合做驱动表。
始终要记住的就是返回的结果集较小的表适合做驱动表
我们在看执行计划的时候,离nested loops,hash join 近的表就是驱动表,也就是说执行计划中靠上的表是驱动表。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22990797/viewspace-1200795/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22990797/viewspace-1200795/