hint不能傳播到view內部!

warehouse發表於2008-04-28

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章