leading use_nl() 与 ordered use_nl()

此文章为转载，源文出处

http://www.360doc.com/content/10/0715/14/865221_39175609.shtml

首先我们来分别介绍一下这四个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：      如果指定了一个可用的索引，则优化器使用此索引扫描      如果指定了多个可用索引，优化器则会使用成本最低的那个索引扫描，或者选择扫描多个索引，然后合并结果。此种情况下，oracle推荐使用index_combine hint。      如果没有指定索引，优化器则会考虑所有可用索引，然后使用成本最低的那个索引扫描，同时也可以选择扫描多个索引，然后合并结果。 接下来让我们看一下具体的示例： 先要准备一些 实验 数据： create table t2 as select * from dba_objects; create index indx_t2 on t2(object_id); create table t3 as select * from dba_objects where rownum<12000; create index indx_t3 on t3(object_id); 下面开始进行演示，此处会分为几种情况分别讨论： 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的结果是一样的。