子查询，表连接，表关联方式简单介绍

最新推荐文章于 2023-05-19 11:40:43 发布

朝闻道-夕死可矣

最新推荐文章于 2023-05-19 11:40:43 发布

阅读量1k

点赞数

分类专栏： ORACLE

本文链接：https://blog.csdn.net/jc_benben/article/details/9698749

版权

ORACLE 专栏收录该内容

79 篇文章 0 订阅

订阅专栏

SELECT查询顺序

比如select col1,count(1) from tab1 where col1=1 group by col1 having count(1)>1 group by col1;

执行顺序：

FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY

集合

集合是当数据分散在不同的表中，进行合并的操作过程，也叫合并查询，但是要注意列的个数和属性要匹配。分为以下几种

UNION合并去重
UNION ALL 合并
INTERSECT 交集
MINUS 差集

子查询

子查询分类：

标量子查询

一般情况下，一个标量子查询就是在select查询中返回一个字段的一行数据，select查询中返回多行的子查询会返回错误

举例

select (select sysdate from dual) from dual;

关联子查询

一般是在主查询中的where条件后，关联子查询：

select * from ta 
where exists (select tb.owner,tb.object_name 
    from tb where ta.owner=tb.owner and ta.object_name=tb.object_name);

可以看到这个查询也是多列子查询，多行子查询

非关联子查询

select * from ta 
where (ta.owner,ta.object_name) in
 (select tb.owner,tb.object_name from tb where tb.owner='USER1'and tb.object_name='TABLE_NAME1');

多行子查询

多行子查询涉及到IN，EXISTS，ANY，ALL，前两种好理解，下面看下ANY和ALL

select empno,ename,sal from scott.emp where sal>(select avg(sal) from scott.emp group by job);

以上查询是根据不同job类型计算员工平均工资，>all 的条件为大于最大的平均工资

同样<all 表示小于最小平均工资，而=all 无意义

any的意思：

>ANY 大于最小值
<ANY小于最大值
=ANY相当于IN

其实想想，条年+all相当于后边的任意一个都满足，条件+ANY表示任一条件都可行

表连接

表连接方式分为三种：内连接（自然连接），外连接-左外连接（左边的表不加限制），右外连接（右边的表不加限制），全外连接（左右表都不加限制）；笛卡尔积

语法：

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表示完全外连接；

内连接

内连接（inner join/join)

Inner join逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，所以内连接用的很少。

举例：有两个表每个表都有两列，id和name，table1，table2，

Select a.id,a.name,b.name from table1 a 【inner】 join table2 b on a.id=b.id; 将会连接两个表id一样的记录，等价于

Select a.id,a.name,b.name from table1 a,table2 b where a.id=b.id;

自然连接natural join，在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

注意，没有连接条件，就相当于使用where条件连接了所有的条件：

Select id,name from table1 a natural join table2 b;

Select dave.id,bl.name From table1 join table2 on dave.id = bl.id and table1.name=table2.name;

自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。下面介绍一下自连接的方法:

将原表复制一份作为另一个表,两表做笛卡儿相等连接。

例显示雇员的编号,名称,以及该雇员的经理名称

SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGER

WHERE WORKER.MGR = MANAGER.EMPNO;

非等值连接

非等值连接就是连个表的连接条件不相等，比如一个表是薪水等级表，另一个是员工薪水表，现在要查询员工薪水表中的薪水在一个薪水等级表中的薪水等级就可以用到between...and...

select emp_no,emp_sal,sal_min,sal_max from emp,sal_level where emp.emp_sal >=sql_level and emp.emp_sal< sql_level.sql_max;

using子句

还有一个using子句，多表连接中当多列列名相同时，使用其中的一列同名列连接，而不需要写连接条件，语法：

select <字段列表> from tbl1 【inner】 join tab2 using<参照字段列表>

例如：select emp_no,ename,sal from scott.emp join scott.dept using(deptno);

相当于select empno,ename,sal from scott.emp join scott.dept where emp.deptno=dept.deptno;

注意：using子句不能跟natural join混合使用

外连接

outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种：左外连接，右外连接，全外连接。对应SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成：LEFT/RIGHT/FULL JOIN。

在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录。那么在相关联的结果集行中列显示为空值（NULL）。

外连接也可以使用+来实现

左外连接：（left outer join/left join)

left join是以左表的记录为基础的,table1可以看成左表,table2可以看成右表,它的结果集是table1表中的数据，在加上table1表和table2表匹配的数据。换句话说,左表的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL

select * from table1 a left join table2 b on a.id = b.id;相当于Select * from table1 a,table2 b where a.id=b.id(+);

同样右外连接：

select * from table1 a right join table2 b on a.id = b.id;相当于Select * from table1 a,table2 b where a.id(+)=b.id;

笛卡尔积

笛卡尔积是把表中的所有记录做乘机操作，生成大量结果，通常是由于连接条件确实造成的，所以需要注意

连接条件缺失
统计数据不准确，例如统计信息中是0行，但是实际有1000W

表之间的关联方式

目前oracle支持的有三种

排序合并连接（Sort Merge join,简称SMJ）
嵌套连接(Nested Loops Join 简称NL)
哈希连接（Hash Join简称HJ）也称哈希连接

还有一个是笛卡尔积（Merge Join Cartesian 简称MJC），避免使用

排序合并连接（SMJ）

如果连接属性上都有索引，则何以利用索引已排序的字段做合并连接，否则，需要先对量表在连接属性上排序，之后对排序结果再连接。一般情况下，哈希连接比排序合并好，但是如果行源已被排序，那么排序合并少了排序的操作，这种情况下排序合并会更优。使用hit：USE_MERGE(T1 T2)强制使用排序合并连接

嵌套循环连接（NL）

嵌套循环连接用到了驱动表（外部表）的概念，是一个嵌套循环，一般学过c语言的都知道for循环，原理类似。原理：

读取驱动表（外部表）的每一行，然后在被驱动表（内部表）中检查是否有匹配的行，所有匹配的行都被放到结果集中，然后处理驱动表下一行，一直持续到驱动表所有行处理完。这种连接方式是最快获取记录的方法之一，适合快速响应的语句中，因为它可以先返回已经匹配的行，而不必等待处理完；一般情况下适合大表小表的连接，小表左右驱动表

哈希连接（HJ）

当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表;优化器再扫描表连接中的大表，将大表中的数据（具体应该是大表的记录做哈希）与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。

具体过程，当哈希表构建完成后，进行下面的处理：

1) 第二个大表进行扫描

2) 如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区

3) 大表的第一个分区cache到内存

4) 对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面

5) 与第一个分区一样，其它的分区也类似处理。

6) 所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。

具体执行过程是很复杂的，请参看：https://blog.csdn.net/jc_benben/article/details/9713017

当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。

当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表

SQL> select a.user_id,b.user_id from user_info a ,dev_info b where a.user_id=b.user_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1657342960

--------------------------------------------------------------------------------
--------

| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
--------

|   0 | SELECT STATEMENT   |           | 73561 |  1293K|       |   946   (1)| 00
:00:01 |

|*  1 |  HASH JOIN         |           | 73561 |  1293K|  1216K|   946   (1)| 00
:00:01 |

|   2 |   TABLE ACCESS FULL| USER_INFO | 72814 |   355K|       |   387   (1)| 00
:00:01 |

|   3 |   TABLE ACCESS FULL| DEV_INFO  | 95141 |  1207K|       |   387   (1)| 00
:00:01 |

--------------------------------------------------------------------------------
--------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."USER_ID"="B"."USER_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划。

select /*+ use_hash(a b)*/ a.user_id,b.user_id  
from user_info a, dev_info b  
where a.user_id = b.user_id;

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速

笛卡尔积

没有连接条件下会产生，隐含参数"_OPTIMIZER_MJC_ENABLED"控制，默认开启

SQL> select * from ta ,tb;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1887170023

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 10000 |  9394K|    58   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      | 10000 |  9394K|    58   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | TA   |   100 | 48100 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |   100 | 48100 |    56   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | TB   |   100 | 48100 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         38  recursive calls
         10  db block gets
        193  consistent gets
          2  physical reads
          0  redo size
     594363  bytes sent via SQL*Net to client
       7934  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

比较：

表关联方式	排序合并连接（SMJ）	嵌套循环（NL）	哈希连接（HJ）
hint	/+ USE_MERGE(T1 T2)/	/+ USE_NL(T1 T2)/ T1作为驱动表	/+ USE_JASH(T1 T2)/
驱动表概念	无	有，应该小表作为驱动表，具体应该是过滤条件限制后返回少的记录表作为驱动表	有，小标作为驱动表，内存消耗小得多高效
表访问次数	两个表都最多访问1次	驱动表最多访问一次，被驱动表访问N次	两个表都最多访问1次
排序	需要排序消耗内存(SORT_AREA_SIZE)	不排序	不排序，会消耗内存（HASH_AREA_SIZE）用于建立HASH表
优化	1，只取业务需要的字段，避免* 2，在两个表的限制条件上创建索引 3，在两个表的连接条件上创建索引 4，确保PGA足够，避免排序	1，选择结果集小的作为驱动表 2，对驱动表的限制条件建立索引，被驱动表连接条件建立索引	1，只取业务需要的字段，避免* 2，小结果集的表做驱动表 3，连个表的限制条件上创建索引 4，确保PGA的size容纳HASH运算
限制	支持>，<，>=，<=，但不支持<>,like	支持所有连接方式的写法	只适用于CBO，并且用于等值连接，不支持<>,<,> ,>=,<=,OR,like
消耗资源	磁盘I/O,内存，临时空间	CUP，磁盘I/O	CPU
隐含参数	"_OPTIMIZER_SORTMERGE_JOIN_ENABLED"默认TRUE表示开启	无	"_HASH_JOIN_ENABLED"默认开启TRUE
场合	1，倾向于OLAP系统，吞吐量大的场景 2，有索引情况下合适使用	1，一般在OLTP，返回结果集少的地方 2，如果驱动表返回的结果集较小，且在被驱动表上有索引（唯一索引，或者选择性高的普通索引）则和可能使用NL 3，快速相应的场合 4，其他两种连接条件限制的场合	1，一般来讲HJ连接优于前两种，在大表连接，尤其是一个小表和一个大表小表的hash table 完全能容纳在内存中-PGA的工作区，此时效率很高 2，如果连接的表在同一数量级，且数量比较大，也会选择HJ 3，一般用于OLAP场景，返回记录数多 4，表中无索引，倾向于HJ

朝闻道-夕死可矣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
子查询，表连接，表关联方式简单介绍

SELECT查询顺序比如select col1,count(1) from tab1 where col1=1 group by col1 having count(1)>1 group by col1;执行顺序：FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY集合集合是当数据分散在...
复制链接

扫一扫