hint oracle qbname_关于qb_name的应用

在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)