Oracle二种链接形式的区分

–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

  | 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

  COUNT(*)

     0

 

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

|00:00:00.01 |

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

–分析T2表被访问次数不同的因由
—解释T2表为何被访问100次
select count(*) from t1;

last’));

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

  COUNT(*)

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

|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

|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

  | 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

  | 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

1 nested loops join
–我们用设置statistics_level=all的措施来考察如下表连接语句的实践计划:

  COUNT(*)

     2
—解释T2表为什么被访问1次
select count(*) from t1 where t1.n = 19;

  |

|   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.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

  COUNT(*)

    100
—解释T2表为何被访问2次
select count(*) from t1 where t1.n in (17,19);

last’));

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

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

相关文章