使用"hint"设置视图内的SQL查询方式

--创建一个表T1,有两个字段C1、C2create table T1(  C1 VARCHAR2(30) not null,  C2 NUMBER);--在C2上建有BTree索引(IDX$T1$C2)create index IDX$T1$C2 on T1 (C2);--创建一个表T2,有两个字段C1、C2create table T2(  C1 VARCHAR2(30) not null,  C2 NUMBER);--建有一个视图V1create or replace view v1 asselect * from t1union allselect * 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 |--------------------------------------------------------------------

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

select /*+ index(a.T1,IDX$T1$C2)*/*from (select * from T1union all        select * from T2) a where c2 > '171'

select /*+ index(a.b,IDX$T1$C2)*/ *from (select * from T2 bunion allselect * from T1 b) a where c2 > '171'