精华内容
下载资源
问答
  • oracle三种表连接方式
    千次阅读
    2015-12-10 09:10:42

    表的连接方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN


    NESTED LOOP

    对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是 nested loops。
    一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nstedloop。如果驱动表返回记录太多,就不适合nested loops了。如果连接字段没有索引,则适合走hash join,因为不需要索引。
    可用ordered提示来改变CBO默认的驱动表,可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

    HASH JOIN
    hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表(或数据源),利用连接键(也就是根据连接字段计算hash 值)在内存中建立hash表,然后扫描大表,每读到一条记录就来探测hash表一次,找出与hash表匹配的行。
    当小表可以全部放入内存中,其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
    至于两个表都进行分区,其好处是可以使用parallel query,就是多个进程同时对不同的分区进行join,然后再合并。但是复杂。
    使用hash join时,HASH_AREA_SIZE初始化参数必须足够的大,如果是9i,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可。
    以下条件下hash join可能有优势:
    两个巨大的表之间的连接。
    在一个巨大的表和一个小表之间的连接。
    可用ordered提示来改变CBO默认的驱动表,可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。

    SORT MERGE JOIN
    sort merge join的操作通常分三步:对连接的每个表做table access full;对table access full的结果进行排序;进行merge join对排序结果进行合并。sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下,9i开始已经很少出现了,因为其排序成本高,大多为hash join替代了。
    通常情况下hash join的效果都比sort merge join要好,然而如果行源已经被排过序,在执行sort merge join时不需要再排序了,这时sort merge join的性能会优于hash join。
    在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,sort merge join会比nested loops性能更佳。
    可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

    为何nested loop要求小表驱动,hash join又为何要求小表hashed(转)
    nested loop的过程:选择row resource 最小的那个表作为驱动表(外部表),内部表要求有选择性高的index。 从外部表(驱动表)里一次取一行对内部表的每行进行比较,即外部表有几个distinct行,就有几次nested loop。

    所以nested loop的代价是:

    cost = outer access cost + (inner access cost * outer cardinality)

    outer access cost应该是读取驱动表到内存,而内部表要求有有效的index也是为了降低inner access cost,而驱动表要求小,也就很好理解了。

    Hash join 在缺少index的情况下比nested loop更有效,一般情况下比nested loop更快,因为处理内存中的hash表比b-tree index更迅速。有无index,对hash join并没有什么影响。

    一般也是选择较小的表(内部表)读入内存,进行hash 算法,根据连接键构建成hash table。该表如果太大的话,会根据当前系统的参数设置,将该表分piece读入内存构建成hash表。然后外部表中的每行也被读入内存进行hash算法,得到一个hash值,对该piece进行比较。

    所以hash join的cost 如下:

    cost = (outer access cost * number of hash partitions) + inner access cost

    上述两个cost里面都是先计算出读入内存的小表的access cost,只不过小表对于nested loop来说是outer table,而对于hash join而言是inner table

    可见,如果一个hash内存设置的大的话,如果只有一个piece,那么cost=内部表与外部表的access cost相加之和,相当的。当然也要算上hash的代价,所以hash应该比nest loop快,但是选择nested loop还是hash join,一切交给optimizer就可以了。

    连接说明:
    1. Oracle一次只能连接两个表。不管查询中有多少个表, Oracle 在连接中一次仅能操作两张表。
    2.当执行多个表的连接时,优化器从一个表开始,将它与另一个表连接;然后将中间结果与下一个表连接,以此类推,直到处理完所有表为止。

    解答:为了提高查询性能,Oracle 连接中需要选择合适的驱动表,这是为什么?
    1.以一个比较两本字典来做例子:
    一本字典有索引目录(dict a), 一本没有(dict b)
    现在要找出所有a开头的单词的异同
    那么比较的时候,你会怎么比较?
    合理的做法应该是以没有索引的字典dict b开始, 找到a开头的那一页, 然后对于每个单词, 通过使用索引在dict a中找到相应的条目
    如果反过来, 效率会相同吗?

    2. 驱动表是在使用多表嵌套连接时,会先全表扫描该驱动表,然后驱动表返回的结果集中一行一行去匹配被驱动表(可以利用索引),所以我们会选择小表做为驱动 表,而被驱动使用索引进行连接。对于基于规则的优化系统,驱动表在FROM后是有次序设置的,而在基于成本的优化,ORACLE会选择最合适的驱动表。

    row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个 较小的row source,使连接的效率较高,这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时,应用where子句中对该表的限制条件。
    应该把数据小的表当做驱动表,也就是把要做驱动的表放在靠近from的地方。
    更多相关内容
  • MySQL中表连接方式

    千次阅读 多人点赞 2020-06-28 14:00:27
    为了能够很好理解MySQL中表连接方式,以如下两个表作为例子演示: Table1:t1 id name 1 张三 2 李四 4 王五 Table2:t2 id age 1 18 2 20 3 19 1. 笛卡尔积 两表关联...

    为了能够很好理解MySQL中表连接方式,以如下两个表作为例子演示:

    • Table1:t1
    idname
    1张三
    2李四
    4王五
    • Table2:t2
    idage
    118
    220
    319

     

    1. 笛卡尔积

    两表关联,把左表的列和右表的列通过笛卡尔积的形式表达出来。

    • 语法

        select * from t1 join t2

    • 结果

    idnameidage
    1张三118
    2李四118
    4王五118
    1张三220
    2李四220
    4王五220
    1张三319
    2李四319
    4王五319

    2.外连接

    2.1 左外连接:left join

    两表关联,左表全部保留,右表关联不上用null表示。

    • 示意图

    在这里插入图片描述

    • 语法
      select * from t1 left join t2 on t1.id = t2.id

    • 结果

    idnameidage
    1张三118
    2李四220
    4王五nullnull

    2.2 右外连接

    两表关联,右表全部保留,左表关联不上用null表示。

    • 示意图

    在这里插入图片描述

    • 语法

           select * from t1 right join t2 on t1.id = t2.id

    • 结果
    idnameidage
    1张三118
    2李四220
    nullnull319


    2.3 全连接:full join


    两表关联,两表的内容均保留,没有关联的字段用null表示。

    • 示意图

    在这里插入图片描述

    • 语法

    oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。
    (1)oracle
    select * from t1 full join t2 on t1.id = t2.id
    (2)mysql
    select * from t1 left join t2 on t1.id = t2.id
    union
    select * from t1 right join t2 on t1.id = t2.id;

    结果

    idnameidage
    1张三118
    2李四220
    4王五nullnull
    nullnull319


    3. 内连接:inner join

    两表关联,保留两表中交集的记录。

    • 示意图

    在这里插入图片描述

    • 语法

    select * from t1 inner join t2 on t1.id = t2.id;

    • 结果
    idnameidage
    1张三118
    2李四220


    4. 更多的连接方式

    4.1 左表独有


    两表关联,查询左表独有的数据,类似于集合中的t1 - t2。

    • 示意图

    在这里插入图片描述

    • 语法

           select * from t1 left join t2 on t1.id = t2.id where t2.id is null;

    • 结果
    idnameidage
    4王五nullnull


    4.2 右表独有

    两表关联,查询右表独有的数据,类似于集合中的t2 - t1。

    • 示意图

    在这里插入图片描述

    • 语法

          select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

    • 结果
    idnameidage
    nullnull319


    4.3 并集去交集


    两表关联,取并集然后去交集。

    • 示意图

    在这里插入图片描述

    • 语法

    select * from t1 left join t2 on t1.id = t2.id where t2.id is null
    union
    select * from t1 right join t2 on t1.id = t2.id where t1.id is null;

    • 输出
    idnameidage
    4王五nullnull
    nullnull319

     

    展开全文
  • 三种表连接方式

    千次阅读 2021-06-13 18:17:14
    右连结,A连接B,以B为主,B的所有数据会全部显示,A的数据只显示对应与B对应的部分,存在但没有对应的数据不会显示,与B对应不存在的数据显示为null。(与上面左连结同理) join 内

    做作业时发现一会儿用这个连结,一会用那个连结。总结一下:

    left join

    左连接,表1连结表2,以左表为主,查询出的结果显示左表中的所有数据,右边显示和左边有交集的数据。也就是说,以左表为主,左表的数据会全部显示,右表只显示左表对应的数据,其他右表存在但无左表对应的数据不显示。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zQiB9rsj-1623579411901)(C:\Users\lcy\AppData\Roaming\Typora\typora-user-images\1623578751431.png)]

    right join

    右连结,A表右连接B表,以B表为主,B表的所有数据会全部显示,A表的数据只显示对应与B表对应的部分,存在但没有对应的数据不会显示,与B表对应不存在的数据显示为null。(与上面左连结同理)
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xalwllsx-1623579411904)(C:\Users\lcy\AppData\Roaming\Typora\typora-user-images\1623579025945.png)]

    join

    内连接,显示两表的交集部分,也就是说两表对应的部分都有数据,A内连接B,A中有但B中没有的数据不会显示,B中有但A中没有的数据也不会显示,只有A表和B表都有的数据才会显示。不会有null出现。

    显示。不会有null出现。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MU201wZB-1623579411906)(C:\Users\lcy\AppData\Roaming\Typora\typora-user-images\1623579202872.png)]

    展开全文
  • Oracle-表连接类型和表连接方式

    万次阅读 2017-11-29 15:55:14
    表连接类型:Oracle - 表连接的几种类型: 内连接(自然连接) 外连接 左外连接 (左边的表不加限制) 右外连接(右边的表不加限制) 全外连接(左右两表都不加限制) 自连接(同一张表内的连接) SQL的标准语法:select ...

    表连接类型:

    Oracle - 表连接的几种类型:

    • 内连接(自然连接)
    • 外连接

      • 左外连接 (左边的表不加限制)
      • 右外连接(右边的表不加限制)
      • 全外连接(左右两表都不加限制)
    • 自连接(同一张表内的连接)

    SQL的标准语法:

    select table1.column,table2.column
    from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;
    
    inner join 表示内连接;
    left join表示左外连接;
    right join表示右外连接;
    full join表示完全外连接;
    on子句 用于指定连接条件。
    
    注意:
    1.如果使用from子句指定内、外连接,则必须要使用on子句指定连接条件;
    2.如果使用(+)操作符指定外连接,则必须使用where子句指定连接条件。

    示意图如下:

    连接类型示意图


    表连接方式:指执行计划里面的表连接方式

    Oracle - 表连接的几种方式:

    • SORT MERGE JOIN(排序-合并连接)
    • NESTED LOOPS(嵌套循环)
    • HASH JOIN(哈希连接)
    • CARTESIAN PRODUCT(笛卡尔积)

    连接说明:

    1. Oracle一次只能连接两个表。不管查询中有多少个表,Oracle 在连接中一次仅能操作两张表。
    2. 当执行多个表的连接时,优化器从一个表开始,将它与另一个表连接;然后将中间结果与下一个表连接,以此类推,直到处理完所有表为止。

    举例:

    注:这里将首先存取的表称作 row source 1,将之后参与连接的表称作 row source 2;

    (1) SORT MERGE JOIN(排序-合并连接):

    假设有查询:select a.name, b.name from table_A a join table_B b on (a.id = b.id)

    内部连接过程:

    a) 生成 row source 1 需要的数据,按照连接操作关联列(如示例中的a.id)对这些数据进行排序

    b) 生成 row source 2 需要的数据,按照与 a) 中对应的连接操作关联列(b.id)对数据进行排序

    c) 两边已排序的行放在一起执行合并操作(对两边的数据集进行扫描并判断是否连接)

    延伸:

    如果示例中的连接操作关联列 a.id,b.id 之前就已经被排过序了的话,连接速度便可大大提高,因为排序是很费时间和资源的操作,尤其对于有大量数据的表。

    故可以考虑在 a.id,b.id 上建立索引让其能预先排好序。不过遗憾的是,由于返回的结果集中包括所有字段,所以通常的执行计划中,即使连接列存在索引,也不会进入到执行计划中,除非进行一些特定列处理(如仅仅只查询有索引的列等)。

    排序-合并连接的表无驱动顺序,谁在前面都可以;

    排序-合并连接适用的连接条件有: < <= = > >= ,不适用的连接条件有: <> like

    (2) NESTED LOOPS(嵌套循环):

    内部连接过程:

    a) 取出 row source 1 的 row 1(第一行数据),遍历 row source 2 的所有行并检查是否有匹配的,取出匹配的行放入结果集中

    b) 取出 row source 1 的 row 2(第二行数据),遍历 row source 2 的所有行并检查是否有匹配的,取出匹配的行放入结果集中

    c) ……

    若 row source 1 (即驱动表)中返回了 N 行数据,则 row source 2 也相应的会被全表遍历 N 次。

    因为 row source 1 的每一行都会去匹配 row source 2 的所有行,所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2(如建立适当的索引)时,效率较高。

    延伸:

    嵌套循环的表有驱动顺序,注意选择合适的驱动表。

    嵌套循环连接有一个其他连接方式没有的好处是:可以先返回已经连接的行,而不必等所有的连接操作处理完才返回数据,这样可以实现快速响应。

    应尽可能使用限制条件(Where过滤条件)使驱动表(row source 1)返回的行数尽可能少,同时在匹配表(row source 2)的连接操作关联列上建立唯一索引(UNIQUE INDEX)或是选择性较好的非唯一索引,此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多,即使匹配表连接操作关联列上存在索引,连接效率也不会很高。

    (3)HASH JOIN(哈希连接) :

    哈希连接只适用于等值连接(即连接条件为 = )

    HASH JOIN对两个表做连接时并不一定是都进行全表扫描,其并不限制表访问方式;

    内部连接过程简述:

    a) 取出 row source 1(驱动表,在HASH JOIN中又称为Build Table) 的数据集,然后将其构建成内存中的一个 Hash Table(Hash函数的Hash KEY就是连接操作关联列),创建Hash位图(bitmap)。

    b) 取出 row source 2(匹配表)的数据集,对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置,在该位置上检查能否找到匹配的数据。


    补充:

    还想进一步加深学习的同学可以点击下列链接,写的算比较详细的了。

    1. Oracle表三种连接方式(sql优化)

    2. 看懂Oracle执行计划

    展开全文
  • MySQL 多查询连接方式详解

    千次阅读 2019-01-08 17:16:23
    表连接方式: 内连接:inner join,join 外连接:left join,left outer join,right join,right outer join,union 交叉连接:cross join 通过具体实例进行分析: department部门表: employee员工表: ...
  • PostgreSQL的三种表连接方式简述

    千次阅读 2015-09-23 11:43:41
    我们知道PostgreSQL有三种表连接方式,分别是嵌套循环连接(Nestloop join)、哈希连接(Hash join)、排序合并连接(merge join)。 这三种连接方式是如何工作的呢?如果你熟悉Oracle的话,应该会发现着三种连接方式与...
  • 数据库连接方式

    千次阅读 2021-04-14 21:54:36
    数据库连接方式 先总体概述一下:一般用的是join 和 union 1. 笛卡尔积:join 2. 内连接:inner join on 3. 外连接:全外连接(full join on),左外连接(left join on),右外连接(right join on) 4. 联合...
  • 数据库中连接方式详解

    万次阅读 2019-06-13 10:52:48
    通过连接运算符可以实现多个查询。连接是关系数据库模型的主要特点,也是它区别于其它类型数据库管理系统的一个标志。 在 关系数据库管理系统中,建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在...
  • SQL SERVER三种表连接方式

    千次阅读 2014-03-19 09:36:26
    简介  在SQL Server中,我们所常见的表与表之间的Inner Join,Outer Join都会...理解这三种物理连接是理解在表连接时解决性能问题的基础,下面我来对这三种连接的原理,适用场景进行描述。   嵌套循环连接(Nested L
  • SQL表连接的几种方式

    千次阅读 2020-07-23 16:37:20
    表连接的几种方式表表连接分类举例说明 表 这里有两张表,分别和学生表和选课表 students sno s_name gender age height speciality 1001 张三 男 18 1.78 计算机 1002 李四 男 25 1.80 计算机 1003 ...
  • 【Oracle】三种表连接方式

    千次阅读 2017-03-06 19:11:19
    表连接方式有三种分别是:排序合并连接(Sort Merge Join)、嵌套循环连接(Nested Loops Join)、哈希连接(Hash Join)。 1、 排序合并连接(Sort Merge Join) 排序合并连接的执行过程如下所示: * 将每个行源...
  • 数据库连接方式

    千次阅读 2019-09-03 14:38:47
    数据库连接方式 连接查询方式有: 内连接、外连接(左连接、右连接、全连接)、交叉连接 左连接和右连接的区别: 左连接以左为基准进行查询,左数据会全部显示出来,右如果和左匹配的数据则显示相应字段...
  • Oracle 表连接方式详解(外链接、内连接、自连接)

    万次阅读 多人点赞 2016-08-03 16:18:08
    Oracle 之间的连接分为三种: 1. 内连接(自然连接) 2. 外连接 (1)左外连接 (左边的不加限制)  (2)右外连接(右边的不加限制)  (3)全外连接(左右两都不加限制) 3. 自连接(同一张内的连接...
  • Oracle 表连接方式(内连接/外连接/自连接) 详解

    万次阅读 多人点赞 2010-08-07 20:59:00
    <br />Oracle 之间的连接分为三种: 1. 内连接(自然连接) 2. 外连接 (1)左外连接 (左边的不加限制)  (2)右外连接(右边的不加限制)  (3)全外连接(左右两都不加...
  • 一、创建两张实验用:wireless_site.merchant和wireless_site.bb SQL&gt; select count(*) from wireless_site.merchant;  COUNT(*) ----------  14005 SQL&gt; SQL&gt; select count(*) from ...
  • 数据库多表连接方式介绍-HASH-JOIN

    万次阅读 2018-03-13 13:13:23
    1.概述 hash join是一种数据库在进行多表连接时的处理算法,对于多表连接还有两种比较常用的方式:sort merge-join 和 nested loop。 为了比较清楚的介绍hash join的使用场景以及为何要引入这样一种连接算法,这里...
  • 详解数据库之间的连接方式

    万次阅读 2018-09-09 22:16:30
    1.内联结也就是在两张的相同属性上做的等值连接即自然连接,下面用简明的图例进行说明 上面是我们用到两张,可以看到在两张中的相同属性为TNO 即为教师的职工号,那么此时如果我们想要对李诚老师的课程进行...
  • 常用的数据库多表连接方法

    千次阅读 2018-08-23 15:09:45
    1、内连接 (1).from a,b   语法:select a.x,b.y from a,b where a.id=b.id;  备注:返回所有满足where条件的行 (2).inner join  语法:select a.x from a inner join b on a.id=b.id  备注:inner ...
  • oracle: 表连接方式---hash join原理

    千次阅读 2016-06-22 22:29:27
    hash join适合 等值连接,且连结列数值均匀分布的情况。
  • 表连接的三种方式简单介绍

    千次阅读 2016-12-18 00:34:37
    表连接的三种方式简单介绍 1. NESTED LOOP 对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般...
  • Mysql多查询常用连接方式

    千次阅读 2016-12-14 11:01:25
    不同数据库的连接查询分类和形式稍有不同,其中Mysql连接查询的常用形式如下。本文并没有对连接查询进行严谨详细的分类,过于详细反而容易迷糊。为了方便以后使用,小的死磕之后进行了简单的归纳总结。
  • sql中的数据三种连接方式

    千次阅读 2017-05-10 14:59:34
    sql中的连接方式可分为三种:内连接,外连接,交叉连接 数据库数据: 图书 学生1.内连接: 1.1.等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接中的所有列,包括...
  • sql表连接的几种方式

    千次阅读 2020-10-15 08:31:35
    sql表连接的几种方式 这里有两张表TableA和TableB,分别是姓名表和年龄表,用于我们例子的测试数据 TableA id name 1 t1 2 t2 4 t4 ...
  • Hive总结(六)的三种连接方式

    千次阅读 2017-03-31 18:09:31
    0.数据源hive> SELECT * FROM test; id_name 9 Nermaer 31 JiaJia 10 Messi 16 Santi 6 Tian 21 Pirlo hive> SELECT * FROM test_name; 26 Santi 26 Tian 99 xiaozha991内连接 *SELECE a.,b.* FROM
  • 在早期版本,Oracle提供的是nested-loop join,两表连接就相当于二重循环,假定两表分别有m行和n行 如果内循环是全表扫描,时间复杂度就是O(m*n) 如果内循环是索引扫描,时间复杂度就是O(m*㏒n) 而hash join的...
  • mysql多表连接后结果集的排序顺序

    千次阅读 2019-07-18 11:58:55
    连接: 以驱动的排序结果作为总结果集的排序顺序 内连接: 以已经排序的作为总结果集的排序顺序,若有多张表,采用最后一张排序的排序顺序 ...
  • 1.内连接 inner join 左右中都出现 2.左连接 left join 左为准 3.右连接 right join 右为准  4. 完全连接 linq的左连接连接连接  1.左连接 var leftJoin=from LT in table1 join RT in ...
  • Oracle多查询,四种连接方式

    万次阅读 多人点赞 2018-03-22 09:26:24
     自连接(self join)是SQL语句中经常要用的连接方式,使用自连接可以将自身的一个镜像当作另一个来对待,从而能够得到一些特殊的数据。  示例:  在oracle的scott的schema中有一个是emp。在emp中的每一个...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,252,514
精华内容 501,005
关键字:

表连接方式

友情链接: 69418235.rar