HINT无法传输到VIEW内部
在Performance Tuning Guide and Reference中提到在复杂的VIEW上（其实有两个表join下就可以了）加HINT无法传输到VIEW内部。
By default, hints do not propagate inside a complex view. |
注意：简单的VIEW，比如针对一张表建的视图，还是可以通过在VIEW上加HINT实现的：（HINT要加上VIEW NAME上，而不是基表上）
如下例所示：
sys@ORCL>select * from t_view; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3131770069 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99 | 1287 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_T | 99 | 1287 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"<100) ---加在表名上，不起作用： sys@ORCL>select /*+ FULL(test) */ * from t_view; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3131770069 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99 | 1287 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_T | 99 | 1287 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"<100) ---加在视图名上，能起作用： sys@ORCL>select /*+ FULL(t_view) */ * from t_view; 99 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99 | 1287 | 6 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST | 99 | 1287 | 6 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"<100) |
对于复杂视图，我们可以通过在创建VIEW时就加上HINT或global hints来解决这个问题。
ORACLE 推荐使用global hints：
Oracle recommends using global hints instead of embedding the hint in the view. |
下面来看看这两个方法的使用：
sys@ORCL>select a.* from test_a a,test_b b 2 where a.a=b.a; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2103443040 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 11 (10)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 253K| 11 (10)| 00:00:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TEST_B | 10000 | 126K| 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."A"="B"."A") sys@ORCL>create view view_hash as select a.* from test_a a,test_b b 2 where a.a=b.a; View created. sys@ORCL>select * from view_hash; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2103443040 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 11 (10)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 253K| 11 (10)| 00:00:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TEST_B | 10000 | 126K| 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."A"="B"."A") sys@ORCL>select /*+ use_nl(a,b) */ a.* from test_a a,test_b b 2 where a.a=b.a; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3933039601 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 10017 (1)| 00:02:01 | | 1 | NESTED LOOPS | | 10000 | 253K| 10017 (1)| 00:02:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_B | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."A"="B"."A") sys@ORCL>select /*+ use_nl(a,b) */ * from view_hash; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2103443040 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 11 (10)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 253K| 11 (10)| 00:00:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TEST_B | 10000 | 126K| 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."A"="B"."A") |
上面的例子中，我们可以看出，HINT无法传输到VIEW内部使用：
以下是采用global hints来使VIEW上的HINT产生作用：
sys@ORCL>select /*+ use_nl(vh.a vh.b ) */ * from view_hash vh; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3933039601 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 10017 (1)| 00:02:01 | | 1 | NESTED LOOPS | | 10000 | 253K| 10017 (1)| 00:02:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_B | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."A"="B"."A") |
以下为在创建视图时，就加上HINT来达到目的：
sys@ORCL>create view view_inside as 2 select /*+ use_nl(a,b) */ a.* from test_a a,test_b b 3 where a.a=b.a; View created. sys@ORCL>select * from view_inside; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3933039601 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10000 | 253K| 10017 (1)| 00:02:01 | | 1 | NESTED LOOPS | | 10000 | 253K| 10017 (1)| 00:02:01 | | 2 | TABLE ACCESS FULL| TEST_A | 10000 | 126K| 5 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_B | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."A"="B"."A") |
— The End —
关键字: SQL优化 | 性能优化
站内搜索