hint不能傳播到view內部！
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過內部的hint來控制執行計劃
- OpenStack外部網路不能訪問內部VM
- [Akka]傳送一條訊息的內部流程
- java內部類，區域性內部類，靜態內部類，匿名內部類Java
- java之內部類(InnerClass)----非靜態內部類、靜態內部類、區域性內部類、匿名內部類Java
- 10-Java內部類——成員內部類、區域性內部類、匿名內部類Java
- java內部類之成員內部類之匿名內部類Java
- java內部類之成員內部類之區域性內部類Java
- 深入解析React資料傳遞之元件內部通訊React元件
- Java內部類詳解--匿名內部類Java
- java內部類之成員內部類Java
- Java內部類詳解-- 成員內部類Java
- java內部類，為什麼需要內部類？Java
- Java內部類和匿名內部類的用法Java
- Java內部類詳解--區域性內部類Java
- java內部類之成員內部類例項Java
- NSN 內部推薦 -- 不能從電子郵件地址認證身份和來源.
- Java內部類Java
- Java 內部類Java
- Webdis內部解析Web
- Oracle內部事件Oracle事件
- 匿名內部類
- Java基礎內部類4-內部類進階Java
- Java基礎10---包、內部類、匿名內部類Java
- Java中的匿名內部類及內部類的二三事Java
- 靜態內部類和非靜態內部類區別
- Java中的巢狀類、內部類、靜態內部類Java巢狀
- push_pred, a powerful hint to speed up you inline view query 10X timesinlineView
- 傳遞指標引數（函式內部給指標賦值）示例指標函式賦值
- Laravel 內部呼叫 APILaravelAPI
- 10、Java——內部類Java
- HBase內部機制
- redis 內部機制Redis
- 泛型，內部類泛型
- java內部類案例Java
- 搞懂 JAVA 內部類Java
- mysql不是內部命令MySql
- golang 內部包引用Golang