在oracle中使用的 Hint 是有效域的约束的,如果想调用子查询中的对象时我们就可以用qb_name这个hint来手动命名一个查询块,从而实现跨域调用。
首先我们创建2张表,别且往表里插入一些数据
SQL> create table t1 (anumber,b varchar2(10));
Table created.
Elapsed: 00:00:01.91
SQL> create table t2 (a2number,b2 varchar2(10));
Table created.
Elapsed: 00:00:02.14
SQL> insert into t1values(1,'a');
1 row created.
Elapsed: 00:00:00.88
SQL> insert into t2values(1,'b');
1 row created.
Elapsed: 00:00:00.82
SQL> commit;
Commit complete.
SQL> create index t1_pkon t1(a);
Index created.
SQL> create index t1_pkon t2(a2);
SQL> create index t2_pkon t2(a2);
Index created.
SQL>
然后执行select * from t1 wherea in (select a2 from t2 where a2=1 );并取得该sql的explain plan
--------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 1| 8| 2 (0)| 00:00:01 |
| 1| NESTED LOOPSSEMI | | 1| 8| 2 (0)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID|T1 | 1| 5| 2 (0)| 00:00:01 |
|* 3| INDEX RANGESCAN | T1_PK| 1| | 1 (0)| 00:00:01 |
|* 4 | INDEXRANGESCAN | T2_PK| 1| 3| 0 (0)| 00:00:01 |
从执行计划上看,这里用nested loop应该是走对了。
如果我想走hash join 呢?
其实如果直接有use_hash 这个hint的话由于hint的作用域问题,oracle将忽略这个hint。
SQL> explain plan for
2 select/ * + use_hash(a b) * / a.* from t1 a where a in(select a2 from t2 b where b.a2=1 );
Explained.
SQL> select * fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 1271124872
--------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost(%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 1| 8| 2 (0)| 00:00:01 |
| 1| NESTED LOOPSSEMI | | 1| 8| 2 (0)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID|T1 | 1| 5| 2 (0)| 00:00:01 |
|* 3| INDEX RANGESCAN | T1_PK| 1| | 1 (0)| 00:00:01 |
|* 4| INDEX RANGESCAN | T2_PK| 1| 3| 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
从explain plan 我们可以看到,oracle并没有使用hash join . explain plan 依然和原来的一样。
那正确的写法是怎么样呢
2 select/ *+ qb_name(qb1) leading(a,b) use_hash(@qb2 b) */ * from t1 a where a in(select / * + qb_name(qb2) * / a2 from t2 bwhere a2=1 );
Explained.
SQL> select * fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3084809728
--------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 1| 8| 4 (25)| 00:00:01 |
|* 1 | HASH JOINSEMI | | 1| 8| 4 (25)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID|T1 | 1| 5| 2 (0)| 00:00:01 |
|* 3| INDEX RANGESCAN | T1_PK| 1| | 1 (0)| 00:00:01 |
|* 4 | INDEXRANGESCAN | T2_PK| 1| 3| 1 (0)| 00:00:01 |
像这种需要有前导列的hint,我们需要直接告诉oracle,哪个对象为前导(对于use_hash,我们就需要使用leading这个提示),否则oracle依然会忽视这个hint(参看下面的例子)。
SQL> explain plan for
2 select / * +qb_name(qb1) use_hash(@qb2 b) * / * from t1 a where a in(select / * + qb_name(qb2)* / a2 from t2 b where a2=1 );
Explained.
SQL> select * fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2949623426
--------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)|Time |
--------------------------------------------------------------------------------------
| 0 |SELECTSTATEMENT | | 1| 8| 3 (34)| 00:00:01 |
| 1| NESTEDLOOPS | | | | | |
| 2| NESTEDLOOPS | | 1| 8| 3 (34)| 00:00:01 |
| 3| SORTUNIQUE | | 1| 3| 1 (0)| 00:00:01 |
|* 4| INDEX RANGESCAN | T2_PK| 1| 3| 1 (0)| 00:00:01 |
|* 5| INDEX RANGESCAN | T1_PK| 1 | | 0 (0)| 00:00:01 |
最后强调一点,qb_name是从oracle10g才引入的新特性。
(转自:http://blog.sina.com.cn/s/blog_7c0ae04b0100zppv.html)
转载于:https://blog.51cto.com/jonsen/1617768