warehouse 客栈 : hint不能传播到view内部！

===========================================================

hint不能传播到view内部！

===========================================================

作者: warehouse(http://warehouse.itpub.net)
发表于: 2008.04.28 13:11
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/460753
---------------------------------------------------------------

doc上介绍hint不能被传播到view内部，除非在创建view时把hint写在view的内部或者使用global hint!

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

--------------------------------------------------------------------------------

-----

--------------------------------------------------------------------------------

-----

| 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

--------------------------------------------------------------------------------

---------------

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

--------------------------------------------------------------------------------

-----

--------------------------------------------------------------------------------

-----

| 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

--------------------------------------------------------------------------------

-----

--------------------------------------------------------------------------------

-----

| 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")

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

统计信息
----------------------------------------------------------
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

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

统计信息
----------------------------------------------------------
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>

warehouse 发表于:2008.04.28 13:11 ::分类: ( 性能优化 ) ::阅读:(56次) :: 评论 (0)

发表评论

«	八月 2008					»
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

«	八月 2008					»
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

«	八月 2008					»
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

«	八月 2008					»
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31