doc上介绍hint不能被传播到view内部,除非在创建view时把hint写在view的内部或者使用global hint!
[@more@]By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.
Note: If the view is a single-table, then the hint is not propagated. |
Unless the hints are inside the base view, they might not be honored from a query against the view.
--===================================
大致的测试过程:
测试过程1:
SQL> select a.*,b.*
2 from tt a , tt1 b
3 where a.id=b.id ;
ID NA ID NAME AGE
---------- -- ---------- ---------- ----------
3 c 3 b 0
4 d 4 b 0
6 f 6 b 0
2 b 2 d 3
5 e 5 e 4
1 a 1 c 2
已选择6行。
执行计划
----------------------------------------------------------
Plan hash value: 3353321945
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 7 | 84 | 6 (17)| 00:00
:01 |
|* 1 | HASH JOIN | | 7 | 84 | 6 (17)| 00:00
:01 |
| 2 | INDEX FULL SCAN | IDX_TT_ID_NAME | 7 | 42 | 1 (0)| 00:00
:01 |
| 3 | TABLE ACCESS FULL| TT1 | 11 | 66 | 4 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select /*+ use_nl(a b) */ a.*,b.* from tt a , tt1 b where a.id=b.id;
ID NA ID NAME AGE
---------- -- ---------- ---------- ----------
1 a 1 c 2
2 b 2 d 3
3 c 3 b 0
4 d 4 b 0
5 e 5 e 4
6 f 6 b 0
已选择6行。
执行计划
----------------------------------------------------------
Plan hash value: 4228579713
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 7 | 84 | 8
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | | |
| |
| 2 | NESTED LOOPS | | 7 | 84 | 8
(0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_TT_ID_NAME | 7 | 42 | 1
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_TT1 | 1 | | 0
(0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TT1 | 1 | 6 | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ID"="B"."ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
1 physical reads
0 redo size
756 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> create or replace view v1(id , name , id1 , name1 , age) as
2 select a.*,b.*
3 from tt a , tt1 b
4 where a.id=b.id ;
视图已创建。
SQL> select * from v1;
ID NA ID1 NAME1 AGE
---------- -- ---------- ---------- ----------
3 c 3 b 0
4 d 4 b 0
6 f 6 b 0
2 b 2 d 3
5 e 5 e 4
1 a 1 c 2
已选择6行。
执行计划
----------------------------------------------------------
Plan hash value: 3353321945
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 7 | 84 | 6 (17)| 00:00
:01 |
|* 1 | HASH JOIN | | 7 | 84 | 6 (17)| 00:00
:01 |
| 2 | INDEX FULL SCAN | IDX_TT_ID_NAME | 7 | 42 | 1 (0)| 00:00
:01 |
| 3 | TABLE ACCESS FULL| TT1 | 11 | 66 | 4 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
754 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select /*+ use_nl(tt tt1) */ * from v1;
ID NA ID1 NAME1 AGE
---------- -- ---------- ---------- ----------
3 c 3 b 0
4 d 4 b 0
6 f 6 b 0
2 b 2 d 3
5 e 5 e 4
1 a 1 c 2
已选择6行。
执行计划
----------------------------------------------------------
Plan hash value: 3353321945
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 7 | 84 | 6 (17)| 00:00
:01 |
|* 1 | HASH JOIN | | 7 | 84 | 6 (17)| 00:00
:01 |
| 2 | INDEX FULL SCAN | IDX_TT_ID_NAME | 7 | 42 | 1 (0)| 00:00
:01 |
| 3 | TABLE ACCESS FULL| TT1 | 11 | 66 | 4 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
754 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
--=================================
If the view is a single-table, then the hint is not propagated.
即使时单表view也不能传播进去
测试过程2:
SQL> select count(*) from tt;
COUNT(*)
----------
7
执行计划
----------------------------------------------------------
Plan hash value: 668303689
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_TT_ID_NAME | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> create or replace view v_tt as select * from tt;
视图已创建。
SQL> select count(*) from v_tt;
COUNT(*)
----------
7
执行计划
----------------------------------------------------------
Plan hash value: 668303689
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_TT_ID_NAME | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ full(tt) */ count(*) from tt;
COUNT(*)
----------
7
执行计划
----------------------------------------------------------
Plan hash value: 3133740314
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TT | 7 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
6 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ full(tt) */ count(*) from v_tt;
COUNT(*)
----------
7
执行计划
----------------------------------------------------------
Plan hash value: 668303689
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_TT_ID_NAME | 7 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
--==================================
测试过程3:
--如果要想让hint传播到view的内部,oracle建议使用global hint而不是local hint,oracle建议使用global hint而不是把hint写到view的内部(When you want to specify hints for tables that appear inside views, use global hints instead of embedding the hint in the view)。global hint的使用如下:
SQL> select /*+ full(v_tt.tt) */ count(*) from v_tt;
COUNT(*)
----------
7
执行计划
----------------------------------------------------------
Plan hash value: 3133740314
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TT | 7 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1003108/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1003108/