威尼斯www.9778.com-威尼斯正版官方网站

Oracle三种链接方式的区别

日期:2019-12-25编辑作者:数据库

代码如下:select a.f_username from ( SELECT /*+parallel(gu,4)*/distinct gu.f_username FROM t_base_succprouser gu where gu.f_expectenddate (select trunc(sysdate,'Y')FROM 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(sysdate,'Y') )) and ((vu.f_lotid is null ) or (vu.f_lotid=1)) and ((vu.f_playid is null ) or (vu.f_playid=4)) and ((vu.f_condtionid is null ) or (vu.f_condtionid=3)) )B on A.f_username=B.f_usernam where b.f_username is null 采用下面的语句 只能查出部分用户 代码如下:SELECT /*+parallel(gu,4)*/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 trunc(sysdate,'Y')FROM 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(sysdate,'Y') )) and ((vu.f_lotid is null ) or (vu.f_lotid=rec_viplotplay.f_lotid)) and ((vu.f_playid is null ) 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(gu,4)*/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(sysdate,'Y') )) and ((vu.f_lotid is null ) or (vu.f_lotid=rec_viplotplay.f_lotid)) and ((vu.f_playid is null ) 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 trunc(sysdate,'Y')FROM 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

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

本文由威尼斯www.9778.com发布于数据库,转载请注明出处:Oracle三种链接方式的区别

关键词:

Oracle 数据库操作类

代码如下:using System; using System.Data; using System.Configuration;using System.Data.OracleClient; using System.Text; usingSystem.Windows.Forms...

详细>>

新Orcas语言特性

New "Orcas" Language Feature: Query Syntax Saturday, April 21, 2007 2:12   LINQ To SQL 语法及实例大全   上个月我开始了一个贴子系列,...

详细>>

Oracle数据库及应用程序优化开荒者网络Oracle

[NextPage] 2.3优化规划重演日志文件 2.3.1重演日志文件的大小由于数据库在选用重演日志文件时是循环使用它们的,而且...

详细>>

oracle 10g常见问题精选 (2)

安装Oracle9I出现"加载数据库时出错:areasQueries错误",提示停止该组件或所有组件安装,之后安装不能进行。 在penti...

详细>>