HINT如何跨越视图生效！
来源：互联网 发布：淘宝店首页设计 编辑：程序博客网 时间：2024/06/09 17:52
oracle的hint可以强制指定sql的执行计划：比如选择索引、表的连接顺序以及表的连接方式。不过如果查询的对象是视图，使用hint就变得麻烦一些。看一个具体的例子：
SQL> create table t1 (id number,name varchar2(30),type varchar2(30));表已创建。SQL> create table t2 (id number,name varchar2(30),type varchar2(30));表已创建。SQL> create index ind_t1_name on t1(name);索引已创建。SQL> create index ind_t2_name on t2(name);索引已创建。SQL> insert into t1 select rownum,object_name,object_type from dba_objects where object_type = 'TABLE';已创建2793行。SQL> insert into t2 select rownum,object_name,object_type from dba_objects where object_type = 'TABLE';已创建2793行。SQL> commit;提交完成。SQL> create view v_t as select * from t1 union all select * from t2;视图已创建。这里创建了一个包含union all的视图，下面对这个视图进行查询：
SQL> set autotrace off;SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> set autotrace traceonly;SQL> select * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 932312278---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 282 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1_NAME | 6 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%') 6 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed这里显然oracle 11g非常的聪明，选择了索引扫描，如果我想看一下全表扫描的效率呢？但是对于包含视图的查询，普通的hint指定方法是无效的：SQL> select /*+ full(t1) */ * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 932312278---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 282 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1_NAME | 6 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%') 6 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed正确的指定方法并不复杂，只要指定view的名称作为表明的前缀就可以了：SQL> select /*+ full(v_t.t1) */ * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 2534531233---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL | T1 | 6 | 282 | 7 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("NAME" LIKE 'B%') 5 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redo size 859 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed对于T2表也可以采用同样的方法：SQL> select /*+ full(v_t.t1) full(v_t.t2) */ * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 680321928----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL| T1 | 6 | 282 | 7 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| T2 | 6 | 282 | 7 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("NAME" LIKE 'B%') 4 - filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 47 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed如果视图中使用了别名，那么hint中指定也要使用别名：SQL> set autotrace off;SQL> create or replace view v_t as select * from t1 a union all select * from t2 b;视图已创建。SQL> set autotrace traceonly;SQL> select /*+ full(v_t.t1) full(v_t.t2) */ * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 932312278---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 282 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1_NAME | 6 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%') 6 - access("NAME" LIKE 'B%') filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 24 recursive calls 0 db block gets 65 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processedSQL> select /*+ full(v_t.a) full(v_t.b) */ * from v_t where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 680321928----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | V_T | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL| T1 | 6 | 282 | 7 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| T2 | 6 | 282 | 7 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("NAME" LIKE 'B%') 4 - filter("NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 20 recursive calls 0 db block gets 103 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed使用hint既然可以对数据库中已经创建的视图有效，那么是否对查询中oracle临时生成的视图也有效呢？如果oracle可以将执行计划推到视图的内部，那么没有道理不支持sql语句执行过程中生成的临时视图。SQL> select * from(select * from t1 union all select * from t2) a where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 1026494751---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 282 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1_NAME | 6 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("T1"."NAME" LIKE 'B%') filter("T1"."NAME" LIKE 'B%') 6 - access("T2"."NAME" LIKE 'B%') filter("T2"."NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 17 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed在执行计划的第一步就生成了一个视图。SQL> select /*+ full(t1) full(t2) */ * from(select * from t1 union all select * from t2) a where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 1026494751---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | || 3 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 282 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IND_T1_NAME | 6 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| T2 | 6 | 282 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_T2_NAME | 6 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("T1"."NAME" LIKE 'B%') filter("T1"."NAME" LIKE 'B%') 6 - access("T2"."NAME" LIKE 'B%') filter("T2"."NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 13 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed直接指定hint显然是没有效果的，尝试使用前面介绍的方法：将查询的别名作为表名的前缀。SQL> select /*+ full(a.t1) full(a.t2) */ * from(select * from t1 union all select * from t2) a where name like 'B%';已选择12行。执行计划----------------------------------------------------------Plan hash value: 1505077622----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 47 | 0 (0)| 00:00:01 || 1 | VIEW | | 1 | 47 | 0 (0)| 00:00:01 || 2 | UNION-ALL | | | | | ||* 3 | TABLE ACCESS FULL| T1 | 6 | 282 | 7 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| T2 | 6 | 282 | 7 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T1"."NAME" LIKE 'B%') 4 - filter("T2"."NAME" LIKE 'B%')Note----- - dynamic sampling used for this statement (level=2)统计信息---------------------------------------------------------- 13 recursive calls 0 db block gets 101 consistent gets 0 physical reads 0 redo size 873 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed- HINT如何跨越视图生效！
- HINT 不生效？
- Oracle如何在有视图的SQL中使用hint(提示)来设置视图内的
- Insert 中 Append Hint 生效时间 实验
- hint不生效怎么找原因
- Oracle之hint研究查询生效范围
- thickbox 如何跨越Iframe
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
- 销售，如何跨越电话障碍
- 如何跨越PHP学习瓶颈
- 如何跨越PHP学习瓶颈
- 技术如何跨越到管理
- 如何查看域名解析生效
- 如何查看域名解析生效
- Hint
- hint
- 使用ARM标准C库进行嵌入式应用程序开发
- 【转】windows xp下PHP环境搭建及BugFree安装步骤
- 关于向Word文档中的RichText控件插入rtf内容的问题
- Linux 下时间和定时器
- 堆和栈的区别(经典的一篇)
- HINT如何跨越视图生效！
- js调用.net后台事件，和后台调用前台等方法总结
- List of colors (from wikipedia)
- 什么是创业实验田
- 用指定的编码（如UTF-8） 写文件
- 提笔忘字，亟须重视的电脑病
- ASP.NET webservice，查询天气预报，手机号码等
- Flex 图片阴影
- centos 5.4 升级php到5.3的方法
