ACCESSOracle三种植链接方式的区别

1 nested loops join
–我们因而装statistics_level=all的道来观察如下表连接语句的推行计划:

–T2表被看100破(驱动表访问1糟,被驱动表访问100糟)
–这个set linesize
1000对dbms_xplan.display_cursor还是有震慑之,如果没安装,默认情况下之出口,将会见少了不少列,如BUFFERS等
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
–略去记录结果
select * from table(dbms_xplan.display_cursor(null,null,’allstats

last’));

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time

  | Buffers |

|   0 | SELECT STATEMENT   |      |      1 |        |    100
|00:00:00.94 |     100K|
|   1 |  NESTED LOOPS      |      |      1 |    100 |    100
|00:00:00.94 |     100K|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100
|00:00:00.01 |      14 |
|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100

|00:00:00.94 |     100K|

3 – filter(“T1″.”ID”=”T2″.”T1_ID”)

—换个话,这次T2表被聘2次(驱动表访问1不成,被令表访问2次于)
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(17, 19);
select * from table(dbms_xplan.display_cursor(null,null,’allstats

last’));

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time

  | Buffers |

|   0 | SELECT STATEMENT   |      |      1 |        |      2
|00:00:00.02 |    2019 |
|   1 |  NESTED LOOPS      |      |      1 |      2 |      2
|00:00:00.02 |    2019 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      2 |      2
|00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      2 |      1 |      2

|00:00:00.02 |    2011 |

 2 – filter((“T1”.”N”=17 OR “T1”.”N”=19))
 3 – filter(“T1″.”ID”=”T2″.”T1_ID”)

–继续换个话,这次T2表被聘1次(驱动表访问1软,被驱动表访问1浅)
Set linesize 1000
alter session set statistics_level=all ;
  SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,’allstats
last’));

SQL> select * from

table(dbms_xplan.display_cursor(null,null,’allstats last’));

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time

  | Buffers |

|   0 | SELECT STATEMENT   |      |      1 |        |      1
|00:00:00.01 |    1014 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1
|00:00:00.01 |    1014 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1
|00:00:00.01 |       8 |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1

|00:00:00.01 |    1006 |

2 – filter(“T1”.”N”=19)
3 – filter(“T1″.”ID”=”T2″.”T1_ID”)

—接下来,T2表居然给访问0糟(驱动表访问1赖,被令表访问0潮)
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 999999999;
select * from table(dbms_xplan.display_cursor(null,null,’allstats

last’));

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time

  | Buffers |

|   0 | SELECT STATEMENT   |      |      1 |        |      0
|00:00:00.01 |       7 |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      0
|00:00:00.01 |       7 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0
|00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| T2   |      0 |      1 |      0

|00:00:00.01 |       0 |

2 – filter(“T1”.”N”=999999999)
3 – filter(“T1″.”ID”=”T2″.”T1_ID”)

—到最终,不只是T2表被访问0不良,连T1表也访问0不行
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND 1=2;
select * from table(dbms_xplan.display_cursor(null,null,’allstats
last’));


| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time

  |

|   0 | SELECT STATEMENT    |      |      1 |        |      0
|00:00:00.01 |
|*  1 |  FILTER             |      |      1 |        |      0
|00:00:00.01 |
|   2 |   NESTED LOOPS      |      |      0 |    100 |      0
|00:00:00.01 |
|   3 |    TABLE ACCESS FULL| T1   |      0 |    100 |      0
|00:00:00.01 |
|*  4 |    TABLE ACCESS FULL| T2   |      0 |      1 |      0

|00:00:00.01 |

1 – filter(NULL IS NOT NULL)
4 – filter(“T1″.”ID”=”T2″.”T1_ID”)

–分析T2表被聘次数不同的来头
—解释T2表为甚被拜100涂鸦
select count(*) from t1;

  COUNT(*)

    100
—解释T2表为啥吃聘2不成
select count(*) from t1 where t1.n in (17,19);

  COUNT(*)

     2
—解释T2表为底给访1坏
select count(*) from t1 where t1.n = 19;

  COUNT(*)

     1
—解释T2表为甚让访问0次
select count(*) from t1 where t1.n = 999999999;

  COUNT(*)

     0

 

未完待续:
2 hash join
运范围最多,不支持>、<、<>、like
3 merge sort join
支持>、<不支持<>、like

相关文章