ITPub博客

首页 > IT基础架构 > 网络安全 > hint不能传播到view内部!

hint不能传播到view内部!

原创 网络安全 作者:warehouse 时间:2008-04-28 13:11:31 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Optimizer Hints!
请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5115400