了解如何利用ordered,use_nl(),leading(),index()进行调优 ...
来源:百度文库 编辑:神马文学网 时间:2024/05/17 01:05:49
首先我们来分别介绍一下这四个hint的概念:1. ORDERED hint:
ordered hint 指示oracle按照from关键字后的表顺序来进行连接。如果没有及时收集统计信息,查询优化器没有得到足够信息,此时你可以自行选择适当的inner及outer表进行连接。ORACLE推荐使用LEADING hint,它比ordered hint有更多的用途。
2. LEADING hint: leading hint 指示查询优化器使用指定的表作为连接的首表,即驱动表。
3. USE_NL hint use_nl hint指示查询优化器使用nested loops方式连接指定表和其他行源,并且将强制指定表作为inner表;但如果此表同时作为outer表则忽略此hint。当使用use_nl时ORACLE推荐与ordered或leading hint搭配使用。
4. INDEX hint index hint指示查询优化器对指定表使用索引扫描。 hint行为依赖于是否明确指定index:
下面开始进行演示,此处会分为几种情况分别讨论:1. 不使用任何hint,此时t2与t3表通过hash join进行连接。 select count(*) from t3,t2 where t2.object_id=t3.object_id;
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 27 (8)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 26 | | ||* 2 | HASH JOIN | | 8749 | 222K| 27 (8)| 00:00:01 || 3 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| 5 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INDX_T2 | 43608 | 553K| 21 (5)| 00:00:01 |----------------------------------------------------------------------------------2. 使用ordered与use_nl hint,并且指定t2作为inner表,也就是被驱动表;因为ordered也是按照t3驱动t2的顺序,因此此时use_nl可以发挥作用 select /*+ ordered use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id; 此处可用leading取代ordered,执行计划则是一样的,SQL语句如下: select /*+ leading(t3) use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id;----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 8763 (1)| 00:00:56 || 1 | SORT AGGREGATE | | 1 | 26 | | || 2 | NESTED LOOPS | | 8749 | 222K| 8763 (1)| 00:00:56 || 3 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| 5 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | INDX_T2 | 1 | 13 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------注意此处使用nested loops进行连接,并且t2作为inner表。 3. 使用leading与use_nl hint,leading指定t2作为连接的首表,即驱动表或者是外表,而use_nl又指定t2作为inner表,也就是被驱动表;根据上面所述,如果use_nl指定的表同时作为outer表出现,则忽略此hint select /*+ leading(t2) use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id; 此处同样也可以用ordered替代,执行计划仍然是相同的,SQL语句如下: select /*+ ordered use_nl(t2)*/count(*) from t2,t3 where t2.object_id=t3.object_id;------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | | 47 (7)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 26 | | | ||* 2 | HASH JOIN | | 8749 | 222K| 1072K| 47 (7)| 00:00:01 || 3 | INDEX FAST FULL SCAN| INDX_T2 | 43608 | 553K| | 21 (5)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| | 5 (0)| 00:00:01 |------------------------------------------------------------------------------------------此时得出的执行计划与不加任何hint的结果是一样的。
ordered hint 指示oracle按照from关键字后的表顺序来进行连接。如果没有及时收集统计信息,查询优化器没有得到足够信息,此时你可以自行选择适当的inner及outer表进行连接。ORACLE推荐使用LEADING hint,它比ordered hint有更多的用途。
2. LEADING hint: leading hint 指示查询优化器使用指定的表作为连接的首表,即驱动表。
3. USE_NL hint use_nl hint指示查询优化器使用nested loops方式连接指定表和其他行源,并且将强制指定表作为inner表;但如果此表同时作为outer表则忽略此hint。当使用use_nl时ORACLE推荐与ordered或leading hint搭配使用。
4. INDEX hint index hint指示查询优化器对指定表使用索引扫描。 hint行为依赖于是否明确指定index:
- 如果指定了一个可用的索引,则优化器使用此索引扫描
- 如果指定了多个可用索引,优化器则会使用成本最低的那个索引扫描,或者选择扫描多个索引,然后合并结果。此种情况下,oracle推荐使用index_combine hint。
- 如果没有指定索引,优化器则会考虑所有可用索引,然后使用成本最低的那个索引扫描,同时也可以选择扫描多个索引,然后合并结果。
下面开始进行演示,此处会分为几种情况分别讨论:1. 不使用任何hint,此时t2与t3表通过hash join进行连接。 select count(*) from t3,t2 where t2.object_id=t3.object_id;
----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 27 (8)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 26 | | ||* 2 | HASH JOIN | | 8749 | 222K| 27 (8)| 00:00:01 || 3 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| 5 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INDX_T2 | 43608 | 553K| 21 (5)| 00:00:01 |----------------------------------------------------------------------------------2. 使用ordered与use_nl hint,并且指定t2作为inner表,也就是被驱动表;因为ordered也是按照t3驱动t2的顺序,因此此时use_nl可以发挥作用 select /*+ ordered use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id; 此处可用leading取代ordered,执行计划则是一样的,SQL语句如下: select /*+ leading(t3) use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id;----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 8763 (1)| 00:00:56 || 1 | SORT AGGREGATE | | 1 | 26 | | || 2 | NESTED LOOPS | | 8749 | 222K| 8763 (1)| 00:00:56 || 3 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| 5 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | INDX_T2 | 1 | 13 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------注意此处使用nested loops进行连接,并且t2作为inner表。 3. 使用leading与use_nl hint,leading指定t2作为连接的首表,即驱动表或者是外表,而use_nl又指定t2作为inner表,也就是被驱动表;根据上面所述,如果use_nl指定的表同时作为outer表出现,则忽略此hint select /*+ leading(t2) use_nl(t2)*/count(*) from t3,t2 where t2.object_id=t3.object_id; 此处同样也可以用ordered替代,执行计划仍然是相同的,SQL语句如下: select /*+ ordered use_nl(t2)*/count(*) from t2,t3 where t2.object_id=t3.object_id;------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | | 47 (7)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 26 | | | ||* 2 | HASH JOIN | | 8749 | 222K| 1072K| 47 (7)| 00:00:01 || 3 | INDEX FAST FULL SCAN| INDX_T2 | 43608 | 553K| | 21 (5)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INDX_T3 | 8748 | 111K| | 5 (0)| 00:00:01 |------------------------------------------------------------------------------------------此时得出的执行计划与不加任何hint的结果是一样的。