本文主要介绍了Oracle数据库如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式。
在实际的工作中，很多人经常会用到视图访问，且视图一般都是已经写好了，但在许多情况下，并不太适宜在视图内直接加hint。
环境如下：
--创建一个表T1,有两个字段C1、C2
create table T1
(
C1 VARCHAR2(30) not null,
C2 NUMBER
);
--在C2上建有BTree索引(IDX$T1$C2)
create index IDX$T1$C2 on T1 (C2);
--创建一个表T2,有两个字段C1、C2
create table T2
(
C1 VARCHAR2(30) not null,
C2 NUMBER
);
--建有一个视图V1
create or replace view v1 as
select * from t1
union all
select * from t2;
--现有一条语句如下：
select * from V1 where V1.c2>'171'
--执行计划如下所示：
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59428 | 1741K| 21 |
| 1 | VIEW | V1 | 59428 | 1741K| 21 |
| 2 | UNION-ALL | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 59424 | 754K| 19 |
|* 4 | TABLE ACCESS FULL | T2 | 4 | 120 | 2 |
-------------------------------------------------------------------- |
根据优化建议，要求T1表使用C2列上的索引IDX$T1$C2会性能更好。
当然，对表和字段进行比较细的统计分析后，可能可以达到效果，但是本次介绍是的使用hint。
经过测试以下的两种SQL都不能达到预期效果:
select /*+ index(V1,IDX$T1$C2)*/
*
from V1
where V1.c2>'171';
select /*+ index(T1,IDX$T1$C2)*/
*
from V1
where V1.c2>'171';
正确的写法如下
select /*+ index(V1.T1,IDX$T1$C2)*/
*
from V1
where V1.c2>'171'
最后的执行计划如下：
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59428 | 1741K| 738 |
| 1 | VIEW | V1 | 59428 | 1741K| 738 |
| 2 | UNION-ALL | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 59424 | 754K| 736 |
|* 4 | INDEX RANGE SCAN | IDX$T1$C2 | 59424 | | 24 |
|* 5 | TABLE ACCESS FULL | T2 | 4 | 120 | 2 |
----------------------------------------------------------------------------- |
其实子查询中的hint与视图的一样，如下例：
select /*+ index(a.T1,IDX$T1$C2)*/
*
from (select * from T1
union all
select * from T2) a
where c2 > '171' |
经过测试和整理，hint在引用对象时是可以嵌套引用的，如例子中的V1.T1表示V1对象中的T1对象。
注释：
如果对象有别名，则只能使用别名，如果对象里有相同的别名则只能对第一个对象使用hint。
select /*+ index(a.b,IDX$T1$C2)*/
*
from (select * from T2 b
union all
select * from T1 b) a
where c2 > '171' |
大家可以发现上面的SQL里的hint就没有效果,这里的a.b其实是指T2表，另外hint在引用对象时是不能加模式名的,加了反而会使hint无效。
