Chinaunix首页 | 论坛 | 博客
  • 博客访问: 682082
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3029
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(100)

文章存档

2020年(65)

2014年(3)

2013年(32)

分类: Oracle

2020-07-13 16:59:22

  我们知道,对于USE_NL,USE_HASH必须指定leading或ordered,从而保证驱动表的顺序,如果不指定leading或ordered,可能你指定的是USE_HASH,内部给你走成USE_NL。

  leading可以写多个指定顺序leading(a,b,c,d)---a驱动b,然后结果集驱动c,结果集驱动d  ((a,b),c),d
  对于hash join可以使用swap_join_inputs和no_swap_join_inputs来指定驱动顺序,比如

leading(a,b,c,d) use_hash(a,b,c,d) swap_join_inputs(c) no_swap_join_inputs(d)
        hash join
          hash_join
               c
               hash_join
                a
                b
           d

 
 
 
 
  对于use_nl则无法通过swap_join_inputs控制顺序复杂的顺序,简单的可以用leading写多个
leading(b,a,c) use_nl(a,b,c)              
use_nl(a,b,c)相当于 use_nl(a) use_nl(b) use_nl(c) use_hash写多个一样

nested loops 
             nested loops
                 b
                 a
             c 

 
 
 
  但是如果想要实现类似hash join的写法,让c做驱动表,驱动b,a的nested loops,则不行
只能这样做:


select /*+ ordered use_nl(t3 x) */ *
from t3,
(select /*+ no_merge use_nl(t1, t2) */
  t1.object_id, t1.owner, t1.object_name
  from t1,t2
  where t1.object_id=t2.object_id) X
where x.object_name=t3.object_name;
The plan is
-------------------------------
| Operation            | Name |
-------------------------------
| SELECT STATEMENT     |      |
|  NESTED LOOPS        |      |
|   TABLE ACCESS FULL  | T3   |
|   VIEW               |      |
|    NESTED LOOPS      |      |
|     TABLE ACCESS FULL| T1   |
|     TABLE ACCESS FULL| T2   |
-------------------------------




  这种全以一个表为基表的外连接,基表肯定第一次连接。当然可以修改下sql,然后其他的基表可以先做:


DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
DROP TABLE d;
CREATE TABLE a AS SELECT * FROM All_Objects;
CREATE TABLE b AS SELECT * FROM All_Objects WHERE ROWNUM<100;
CREATE TABLE c AS SELECT * FROM All_Objects WHERE ROWNUM<200;
CREATE TABLE d AS SELECT * FROM all_objects WHERE ROWNUM<10;
BEGIN
  dbms_stats.gather_table_stats(ownname => USER,tabname => 'a',cascade => TRUE);
    dbms_stats.gather_table_stats(ownname => USER,tabname => 'b',cascade => TRUE);
      dbms_stats.gather_table_stats(ownname => USER,tabname => 'c',cascade => TRUE);
        dbms_stats.gather_table_stats(ownname => USER,tabname => 'd',cascade => TRUE);
END;
SELECT * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
--leading指明a,c的结果集做驱动,驱动d,然后结果集驱动b,但是对于hash_join,可能走right join,所以加上no_swap_join_inputs

SELECT/*+leading(a,c,d,b) use_hash(a,b,c,d) no_swap_join_inputs(b)  no_swap_join_inputs(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

执行计划如下所示:
----------------------------------------------------------
Plan hash value: 2471173529
-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 74150 |    22M|       |  2265   (1)| 00:00:28 |
|*  1 |  HASH JOIN OUTER     |      | 74150 |    22M|    18M|  2265   (1)| 00:00:28 |
|*  2 |   HASH JOIN OUTER    |      | 74150 |    17M|    13M|  1343   (1)| 00:00:17 |
|*  3 |    HASH JOIN OUTER   |      | 74150 |    12M|  7896K|   685   (1)| 00:00:09 |
|   4 |     TABLE ACCESS FULL| A    | 74150 |  7023K|       |   296   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| C    |   199 | 15323 |       |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | D    |     9 |   675 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL  | B    |    99 |  7425 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------





  下面的虽然swap_join_inputs(b)写在前面,但是leading里的d在b前面,所以d先做swap,然后b,最终b是第一驱动表:


SELECT/*+leading(a,c,d,b) use_hash(a,b,c,d) swap_join_inputs(b)  swap_join_inputs(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 74150 |    22M|       |   693   (1)| 00:00:09 |
|*  1 |  HASH JOIN RIGHT OUTER |      | 74150 |    22M|       |   693   (1)| 00:00:09 |
|   2 |   TABLE ACCESS FULL    | B    |    99 |  7425 |       |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER|      | 74150 |    17M|       |   689   (1)| 00:00:09 |
|   4 |    TABLE ACCESS FULL   | D    |     9 |   675 |       |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN OUTER     |      | 74150 |    12M|  7896K|   685   (1)| 00:00:09 |
|   6 |     TABLE ACCESS FULL  | A    | 74150 |  7023K|       |   296   (1)| 00:00:04 |
|   7 |     TABLE ACCESS FULL  | C    |   199 | 15323 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------



  这样a,b可以先做join

SELECT/*+ordered  use_hash(c) swap_join_inputs(c)*/  COUNT(*)  FROM
b,c,a,d
 WHERE a.object_id=b.object_id(+)
 AND b.object_id=c.object_id(+)
 AND a.object_id=d.object_id(+);




dingjun123@ORADB> select count(*) from a;
  COUNT(*)
----------
     74277
已选择 1 行。
已用时间:  00: 00: 00.01
dingjun123@ORADB> select count(*) from b;
  COUNT(*)
----------
        99
已选择 1 行。
已用时间:  00: 00: 00.00
dingjun123@ORADB> select count(*) from c;
  COUNT(*)
----------
       199
已选择 1 行。
已用时间:  00: 00: 00.01
dingjun123@ORADB> select count(*) from d;
  COUNT(*)
----------
         9
已选择 1 行。
已用时间:  00: 00: 00.01
dingjun123@ORADB> set autotrace traceonly exp
dingjun123@ORADB> SELECT * FROM
  2  a,b,c,d
  3  WHERE a.object_id=b.object_id(+)
  4      AND a.object_id=c.object_id(+)
  5      AND a.object_id=d.object_id(+);
已用时间:  00: 00: 00.00
执行计划
Plan hash value: 2669012812
--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      | 74277 |    22M|   309   (2)| 00:00:04 |
|*  1 |  HASH JOIN RIGHT OUTER  |      | 74277 |    22M|   309   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL     | C    |   199 | 15323 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER |      | 74277 |    17M|   305   (2)| 00:00:04 |
|   4 |    TABLE ACCESS FULL    | B    |    99 |  7425 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER|      | 74277 |    12M|   301   (1)| 00:00:04 |
|   6 |     TABLE ACCESS FULL   | D    |     9 |   675 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL   | A    | 74277 |  7036K|   297   (1)| 00:00:04 |
--------------------------------------------------------------------------------

--使用leading只能控制a,c的顺序,c,d无法控制
--ordered和leading相比还是有灵活性的一面
SELECT/*+ leading(a) use_hash(c) swap_join_inputs(c)  use_hash(c)  use_hash(d) */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
--使用ordered,先c,a做hash right,然后b使用no_swap_join_inputs,则c,a的结果作为hash表
---d--->((c--->a)---->b)
SELECT/*+ ordered use_hash(c) swap_join_inputs(c)  use_hash(b)  no_swap_join_inputs(b) use_hash(d) */ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
---((c--->a)---->b)---->d
SELECT/*+ ordered use_hash(c) swap_join_inputs(c)  use_hash(b)  no_swap_join_inputs(b) use_hash(d)  no_swap_join_inputs(d)*/ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
   
   
d--->(b---->(c---->a))
SELECT/*+ ordered use_hash(c) swap_join_inputs(c)  use_hash(b)  use_hash(d) */ * FROM
a,c,b,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
--swap_join_inputs和ordered连用,貌似可以用多次(和leading连用好像也可以)
SELECT/*+ ordered use_hash(b) swap_join_inputs(b)  use_hash(c)
 swap_join_inputs(c) use_hash(d)  swap_join_inputs(d)*/ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);
  
--ordered貌似是建议join的顺序按from顺序,但是不一定,但是第1个表肯定是驱动表
SELECT/*+ ordered */ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);

--leading单表指定驱动表,但是后面有swap,oracle会选择,d肯定也是一个驱动表,可能d,a结合,也可能其他的
--leading指定多表,oracle是备选驱动表,也就是驱动表肯定是在这个里面,但是如果指定swap则不一定
SELECT/*+ leading(a)  use_hash(d)  swap_join_inputs(d)*/ * FROM
a,b,c,d
WHERE a.object_id=b.object_id(+)
    AND a.object_id=c.object_id(+)
    AND a.object_id=d.object_id(+);



   
阅读(406) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~