Oracle三种链接方式的区别

复制代码 代码如下:select a.f_username
from ( SELECT /*+parallel*/distinct gu.f_username FROM
t_base_succprouser gu where gu.f_expectenddate > (select truncFROM
DUAL) and gu.f_lotid=1 and gu.f_playid=4 and gu.f_paymoney>=1500 )
A left join ( select from t_base_vip_customes and ((vu.f_passeddate
is null ) or (vu.f_passeddate > trunc and or and ( or and
((vu.f_condtionid is null ) or B on A.f_username=B.f_usernam where
b.f_username is null 采用下面的语句 只能查出部分用户 复制代码 代码如下:SELECT
/*+parallel*/distinct gu.f_username FROM t_base_succprouser gu left
join t_base_vip_customes VU on gu.f_username=vu.f_username
gu.f_expectenddate > (select truncFROM DUAL) and
gu.f_lotid=rec_viplotplay.f_lotid and
gu.f_playid=rec_viplotPlay.f_Playid and
gu.f_paymoney>=rec_viplotPlay.F_Conditon_ValuesA and
((vu.f_passeddate is null ) or (vu.f_passeddate > trunc and or
(vu.f_lotid=rec_viplotplay.f_lotid)) and ( or
(vu.f_playid=rec_viplotPlay.f_Playid)) and ((vu.f_condtionid is null
) or (vu.f_condtionid=rec_viplotPlay.F_CondtionID)) and
vu.f_username is null 执行计划: 复制代码 代码如下:SELECT STATEMENT, GOAL =
ALL_ROWS HASH UNIQUE NESTED LOOPS OUTER PARTITION RANGE ALL TABLE
ACCESS FULL Object name=T_BASE_SUCCPROUSER VIEW FILTER TABLE ACCESS
FULL Object name=T_BASE_VIP_CUSTOMES FAST DUAL
后来改成了下面就能全部查出来了 复制代码
代码如下:SELECT /*+parallel*/distinct gu.f_username FROM
t_base_succprouser gu left join t_base_vip_customes VU on
gu.f_username=vu.f_username and ((vu.f_passeddate is null ) or
(vu.f_passeddate > trunc and or
(vu.f_lotid=rec_viplotplay.f_lotid)) and ( or
(vu.f_playid=rec_viplotPlay.f_Playid)) and ((vu.f_condtionid is null
) or (vu.f_condtionid=rec_viplotPlay.F_CondtionID)) where
gu.f_expectenddate > (select truncFROM DUAL) and
gu.f_lotid=rec_viplotplay.f_lotid and
gu.f_playid=rec_viplotPlay.f_Playid and
gu.f_paymoney>=rec_viplotPlay.F_Conditon_ValuesA and
vu.f_username is null 执行计划: SELECT STATEMENT, GOAL = ALL_ROWS
HASH UNIQUE FILTER NESTED LOOPS OUTER TABLE ACCESS BY GLOBAL INDEX ROWID
Object name=T_BASE_SUCCPROUSER INDEX RANGE SCAN Object
name=IX_BASE_PROUSER_LOWEX FAST DUAL VIEW TABLE ACCESS FULL Object
name=T_BASE_VIP_CUSTOMES oracle 不懂先把数据给过滤掉然后在来连接吗?
太笨了!而且这样把符合条件的数据也过滤掉了

一、概述

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

       两张表的连接有三种执行方式:1)嵌套循环连接;2)散列连接;3)归并连接。两张表连接时选择这三种中的哪一种呢?这取决于索引、以及连接的代价。在该系列的第三篇(本文)文章中讲解嵌套循环连接,第四篇文章中讲解散列连接,第五篇文章中讲解归并连接。在第六篇以后会分析IN子查询以及EXISTS子查询。

last’));

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

      达梦数据库、oracle数据库、sql server数据库在数据库执行计划方面并无本质区别,因此上篇文章使用达梦数据库作为实例数据库进行分析,这篇文章我们选择oracle 10g作为实例数据库。

  | 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

另外需要申明一点的是:因为oracle的源代码是不公开的,我这里描写的是根据执行计划、成本代价以及10053文件进行反推的结果,尽管这样,从大的方向上讲,不会出现问题,仅做抛砖引玉。

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

Oracle数据库常用的显示执行计划的方式有两种:

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

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

1)set autotrace on 命令;

  | 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

2)explain plan for 命令;

|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

举例说明使用set
autotrace命令:

  | 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

SQL> create table t1(c1 int,c2 int);

|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

Table created.

  |

|   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

SQL> create index it1c1 on t1(c1);

|00:00:00.01 |

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

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

Index created.

  COUNT(*)

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

SQL> insert into t1 values(1,1);

  COUNT(*)

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

1 row created.

  COUNT(*)

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

SQL> insert into t1 values(2,2);

  COUNT(*)

     0

 

未完待续:
2 hash join
使用限制最多,不支持>、<、<>、like
3 merge sort join
支持>、<不支持<>、like

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace on explain;

SQL> select c1 from t1 where c1=1;

        C1


         1

 

Execution Plan


  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)

   1    0   INDEX (RANGE SCAN) OF ‘IT1C1’ (INDEX) (Cost=1 Card=1 Bytes

          =13)

SQL> set autotrace off;

SQL>

 

       我们可以看到,执行了“set autotrace on
explain;”语句之后,接下来的查询、插入、更新、删除语句就会显示执行计划,直到执行“set autotrace
off;”语句。如果是设置了“set autotrace on;”,除了会显示执行计划之外,还会显示一些有用的统计信息。本系列文章不涉及查询代价的评估分析。

       我们从上一段代码中,我们发现在显示“select
c1 from t1 where c1=1;”执行计划之前显示了该执行语句的查询结果。这说明:显示执行计划之前就真正地将该查询语句执行了一遍。这样会带来一个不好后果,假设我们现在有一条语句,执行的时间需要半个小时,即使我们仅仅需要知道该语句的执行计划,此种情况下,我们必须等待半个小时。因此,如果查询的性能很慢,我们可以选择选择使用explain plan for命令。

 

举例说明explain plan
for命令:

SQL> explain plan for
select c1 from t1 where c1=1;

Explained.

SQL> select * from
table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT


Plan hash value: 2624316456


| Id | Operation        | Name | Rows |
Bytes | Cost (%CPU)| Time     |


|   0 | SELECT STATEMENT |       |     1
|    13 |     1   (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IT1C1 |     1
|    13 |     1   (0)| 00:00:01 |


Predicate Information (identified by
operation id):


PLAN_TABLE_OUTPUT


   1 – access(“C1″=1)

Note


   – dynamic sampling used for this
statement

17 rows selected.

SQL>

       使用“explain plan for 查询语句;”生成执行计划,然后使用“select * from
table(DBMS_XPLAN.display);”语句显示执行计划。

 

      下面的内容,将通过一些例子来理解嵌套理解执行计划:


1.不带索引的嵌套连接的执行计划该如何理解?

      构造处测试场景:

create table t1(c1 int,c2 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

 

create table t2(d1 int,d2 int);

create index it2d1 on t2(d1);

insert into t2 values(1,1);

insert into t2 values(2,2);

insert into t2 values(3,3);

insert into t2 values(4,4);

     查询语句为:

select /*+ USE_NL(t2)
*/ c1,c2 from t1 inner join t2 on c1=d2;

     该语句中“/*+ USE_NL(t2)
*/”是我们常说的hint提示,这里的USE_NL告诉优化程序使用嵌套连接对表进行连接,t2为内部表。此查询语句的执行计划为:

Execution Plan


   0      SELECT STATEMENT
Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)

   1    0   NESTED LOOPS (Cost=4 Card=2
Bytes=78)

   2    1     TABLE ACCESS (FULL) OF
‘T1’ (TABLE) (Cost=2 Card=2 Bytes

          =52)

 

   3    1     TABLE ACCESS (FULL) OF
‘T2’ (TABLE) (Cost=1 Card=1 Bytes

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website