Oracle性能优化之 Oracle里的优化器

优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle数据库里的优化器又分为RBO（基于原则的优化器）和CBO（基于成本的优化器，SQL的成本根据统计信息算出）两种。

一、RBO

Oracle会在代码里事先为各种类型的执行路径定一个等级，一共15个等级，从等级1到等级15，oracle认为等级1的执行路径是效率最高的，等级15是执行效率最差的。对于等级相同的执行计划，oracle根据目标对象的在数据字典中缓存的顺序判断选择哪一种执行计划。RBO是一种适合于OLTP类型SQL语句的优化器。相对于CBO而言，RBO有着先天的缺陷，一旦SQL语句的执行计划出现问题，将很难调整。那么RBO执行计划出现问题，怎么调整目标SQL的执行计划呢？一般有如下方法：等价改写目标SQL，比如在where条件对number和date类型的列添加0（deptno+0>100），varchar2或char类型的列可以添加一个“空字符”，例如“||”。对于多表连接的SQL，可以改变from表的连接顺序（RBO会按照从右往左的顺序决定谁是驱动表，谁是被驱动表。）来达到改变目标SQL执行计划的目的。我们也可以改变相关对象在数据字典中缓存的顺序（创建顺序），来改变执行计划。RBO最大的缺点是以oracle内置代码的规则作为判断标准，而并没有考虑到实际目标表的数据量以及数据分布情况。

二、CBO

CBO选择执行计划时，以目标SQL成本为判断原则，CBO会选择一条执行成本最小的执行计划作为SQL的执行计划，各条执行路径的成本通过目标SQL语句所涉及的表、索引、列等的统计信息算出。这里的成本是oracle通过相关对象的统计信息计算出来的一个值，它实际上代表目标SQL对应执行步骤所消耗的IO、CPU、网络资源（针对于dblink下的分布式数据库系统而言）的消耗量，oracle会把网络资源的消耗量计算在IO成本内，实际上你看到的成本为IO、CPU资源，另外需要注意的是，oracle在未引入系统统计信息之前，CBO所计算的成本值实际全是基于IO计算的。

1、集的势（cardinality）

Cardinality是CBO特有的概念，指集合所包含的记录数，即结果集行数。Cardinality实际上表示对目标SQL某个具体执行步骤的执行结果所包含的记录数的估算，当然，如果针对整个目标SQL，那么此时的cardinality就表示对该SQL最终执行结果所包含的记录数的估算。Cardinality和成本值得估算息息相关，因为oracle得到的制定结果集所需要消耗的IO资源可以近似的看成随着结果集所包含的记录数递增而递增。所以，SQL编写的一个原则就是“尽早的过滤更多的数据”。

2、可选择率（Selectivity）

Selectivity也是CBO特有的概念，它是指“施加指定谓语条件后返回的结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率”，取值范围为0~1，其值越小，代表可选择性越好。Selectivity也可成本值得估算息息相关，可选择率越大，意味着所返回的结果集的cardinality越大，所以估算的成本就越大。实际上CBO就是利用selectivity来计算对应结果集的cardinality的，即：

Computed cardinality=original*selectivity

Cardinility和selectivity的值会直接影响CBO对于相关执行步骤成本的估算，进而影响CBO对于目标SQL的执行计划的选择。

3、可传递性

可传递性也是CBO的特有属性，它是查询转换中所做的第一件事情，其含义是CBO会对目标SQL做等价改写，进而提供更多的执行路径给目标CBO，增加得到最佳执行计划的可能性。RBO不会对目标SQL做等价改写。Oracle里可传递性分为以下3种情况：

1）简单谓语传递

比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1=10”。

2）连接谓语传递

比如原目标SQL中的谓语条件是“t1.c1=t2.c1 and t2.c1=t3.c1”,则CBO可能会给谓语条件额外加上“t1.c1=t3.c1”。

3）外链接谓语传递

比如原目标SQL中的谓语条件是“t1.c1=t2.c1(+) and t1.c1=10”,则CBO可能会给谓语条件额外加上“t2.c1(+)=10”。

4、CBO的局限性

1）CBO会默认目标SQL语句where条件中出现的各个列之间出现是独立的，没有任何关联。并且CBO会根据这个前提条件来计算selectivity和cardinality，进而估算成本并选择执行计划。但是这种假设并不全是正确的，生产中列与列之间存在关联的现象并不罕见。目前可以用来缓解上述负面影响的方法是使用动态采样和多列统计信息。但动态采样的准确性取决于采样数据的质量以及数量，而多列统计信息并不适合用于多表之间有关联的情形，所以这两种方法只能算是缓解，并不算是完美的解决方案。

2）CBO会假设所有的目标SQL都是独立运行的，并且互不干扰，但实际情况却不完全是这样。

3）CBO对直方图统计信息有多方限制。主要体现在如下2个方面：

（1）在oracle 12c之前，frequency类型的直方图所对应的bucket的数量不能超过254，这样如果列的distinct数量超过254，oracle就会使用height balanced类型的直方图。对于height balanced类型的直方图而言，oracle不会记录所有的nonpopular value的值，所以此种情况下CBO选错执行计划的概率会比frequency类型的情形要高。

（2）在oracle数据库里，如果针对文本类型的字段手机直方图统计信息，则oracle只会将文本的前32个字符（实际只取前15个）取出来并将其转换为浮点数，然后将浮点数作为上述文本字段的直方图统计信息记录在数据字典里。

4）CBO在解析多表关联的目标SQL时，可能会漏选正确的执行计划。在oracle 11gR2中，CBO在解析这种多表关联的目标SQL时，所考虑的各个表的连接顺序的总和受隐含参数_OPTIMIZER_MAX_PERMUTATIONS的限制。这意味着目标SQL不管有多少种连接顺序，CBO最多只考虑其中根据_OPTIMIZER_MAX_PERMUTATIONS计算出来的有限种可能性。

三、优化器基础知识

1、优化器的模式

优化器模式用于决定oracle在解析目标SQL时所选择的优化器类型，以及选择使用CBO时计算成本的侧重点。在oracle数据库中，优化器模式由参数OPTIMIZER_MODE的值决定，通常OPTIMIZER_MODE的值为RULE,CHOOSE,FIRST_ROWS_n(N=1、10、100、1000),FIRST_ROWS或ALL_ROWS。OPTIMIZER_MODE的值得各个含义如下：

1）RULE

RULE表示优化器使用RBO来解析目标SQL，此时目标SQL所涉及的各个对象的统计信息对于RBO来说将毫无意义。

2）CHOOSE

CHOOSE是oracle 9i中OPTIMIZER_MODE的默认值，他表示oracle在解析目标SQL时到底使用CBO还是RBO取决于目标SQL所涉及对象是否有统计信息。具体来说：只要目标SQL对象含有统计信息，即使用CBO，反之，使用RBO来解析目标SQL。

3）FIRST_ROWS_n(N=1、10、100、1000)

FIRST_ROWS_n(N=1、10、100、1000)可以是FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS_100、FIRST_ROWS_1000中的任意一个值，他表示oracle在解析目标SQL时，oracle会使用CBO来解析目标SQL，且此时CBO在计算各条执行路径的成本时的侧重点在于以最快相应速度返回前n条数据。

4）FIRST_ROWS

FIRST_ROWS是一个在oracle 9i中就过时的一个参数，他表示oracle在解析目标SQL时会联合使用CBO和RBO。在大部分情况下，oracle还是会选用CBO作为解析目标SQL，此时oracle的侧重点是以最快的相应速度返回前n行。在一些特俗情况下，oracle会选用RBO来解析目标SQL而不考虑成本。比如当OPTIMIZER_MODE为FIRST_ROWS时有一个内置的规则，就是oracle如果发现能用相关索引来避免排序，则oracle就会选择该索引所对应的路径而不考虑成本值。

5）ALL_ROWS

ALL_ROWS是oracle 10g及以后oracle的版本中OPTIMIZER_MODE的默认值，它表示oracle会使用CBO来解析目标SQL，此时CBO计算目标SQL的各个执行路径的成本的侧重点是最佳吞吐量。当OPTIMIZER_MODE为FIRST_ROWS时，CBO计算成本侧重于最快响应时间；当OPTIMIZER_MODE为ALL_ROWS时，CBO计算成本侧重于最佳吞吐量。

2、结果集（row source）

结果集是指包含指定执行结果的集合。对于优化器而言，结果集对应SQL执行计划的执行步骤。执行计划的各个步骤的输出结果集和输入结果集可以通过执行计划分析出。

3、访问数据的方法

优化器访问数据的方法有3种，一种是直接访问表；一种是访问索引，直接从索引中取值；另一种是先访问索引，再回表。

1）访问表的方法

访问表的方法2种：全表扫描；rowid扫描。

（1）全表扫描

全表扫面会从表所占用的第一个extent的第一个块开始扫描，一直到该表的最高水位线为止，这个范围的所有的数据块，oracle都会读到。

（2）rowid访问

Rowid访问是指oracle在访问目标表的数据时，直接通过数据所在的rowid去定位并访问这些数据。Oracle中rowid访问有两层含义：一是根据用户SQL输入的rowid值直接去读取数据记录；另一种是先去访问相关索引，然后根据访问索引所返回的rowid回表去读取具体的记录数。

对于oracle数据库中的堆表，可以通过oracle内置的rowid伪列得到对应的rowid值，然后我们可以通过dbms_rowid包中的相关方法（dbms_rowid.rowid_relative_fno：文件号；dbms_rowid.rowid_block_number：数据块号；dbms_rowid.rowid_row_number：数据块中行号）通过上述取得的rowid值取得数据行的实际物理位置。例：

SQL> select empno,ename,rowid, dbms_rowid.rowid_relative_fno(rowid)

||'_'||dbms_rowid.rowid_block_number(rowid)||'_'||dbms_rowid.rowid_row_number(rowid) localtion

     from emp;

    EMPNO    ENAME       ROWID                        LOCALTION

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

########## SMITH     AAAMfMAAEAAAAAgAAA             4_32_0

..............

########## MILLER    AAAMfMAAEAAAAAgAAN             4_32_13

14 rows selected.

2）访问索引的方法

使用B-TREE索引的优势有以下几点：

a、所有的叶子快都在同一层，即所有的叶子块距离根节点的距离是相同的。

b、Oracle保证所有的B-tree索引都是自平衡的，即不可能出现不同的索引叶子块不处于同一层的情况。

c、通过B-tree索引访问数据不会因为表数据量的增加效率明显降低，这也是走索引与全表扫描最大的区别。

（1）索引唯一性扫描：

索引唯一性扫描（index unique scan）是针对唯一性索引（unique index）的扫描。它仅仅适用于where条件是等值查询的目标SQL。

（2）索引范围扫描：

索引范围扫描（index range scan）适用于所有的B-tree索引，当扫描的对象是唯一性索引时，此时目标SQL的where条件一定是范围条件，当扫描的对象是非唯一性索引时，此时目标SQL的where条件没有限制。

（3）索引全扫描：

索引全扫描（index full scan）适用于所有类型的B-tree索引（唯一性索引和非唯一性索引），所谓索引全扫描是指要扫描索引的所有叶子块的所有行。这里需要注意的是索引全扫描要扫描索引的所有叶子块，但不是意味着要扫描索引的所有分支块。即默认情况下，索引全扫描从左到右访问索引的所有叶子块。因为索引是有序的，所以索引全扫描的结果也是有序的。索引全扫描既能达到排序的效果又能避免对目标索引的索引键值列的真正排序操作。默认情况下，索引全扫描的有序性决定了索引全扫描是不能够并行访问的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是索引的键值列的情况。正常情况下，当索引的全部键值均为null时不入索引，这意味着oracle中能做索引全扫描的前提条件是目标索引至少有一列是not null的。

（4）索引快速扫描

索引快速扫描（index fast full scan）适用于所有类型的B-tree索引（唯一性索引和非唯一性索引），索引快速扫描要扫描索引的所有叶子块的所有行。索引快速扫描与索引全扫描有以下区别：

#索引快速扫描仅仅适用于CBO。

#索引快速扫描可以使用多块读，同时也可以并行执行。

#索引快速扫描的结果不一定是有序的。

（5）索引跳跃式扫描

索引跳跃式扫描（index skip scan）适用于所有复合类型的B-tree索引（包括唯一性索引与非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以使用上述索引，这就像是扫描索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样。

索引跳跃式扫描对目标索引的所有前导列做了distinct遍历，其含义相当于对目标SQL做改写。Oracle中的索引跳跃式扫描仅仅适用于那些目标前导列的distinct值较少，后续非前导列的可选择性较好的目标SQL，因为索引跳跃式扫描的执行效率会随着目标索引的前导列的distinct值得增加而递减。

3）表连接

当优化器解析含有表连接的目标SQL时，它会根据目标SQL的SQL文本的写法来决定表连接的类型外，还必须决定如下的3个部分才能最终决定执行计划。

a、表连接顺序

不管目标SQL中有多少个表做链接，oracle在执行该SQL时只能两两做链接直到目标SQL中的所有表连接完毕。这里有两层含义：一是指俩个表做连接时，优化器要决定这两个表谁是驱动表，谁是被驱动表。二是优化器要决定哪两张表要先做连接，哪个表要随后最连接，哪个表最后做连接。

b、表连接的方法

Oracle数据库中表连接的方法有：排序合并连接、嵌套循环连接、哈希连接和笛卡尔连接。所以优化器要选择两张表要以哪种方式做连接。

c、访问单表的方法

优化器在决定最终执行计划时，除了考虑表连接顺序以及表连接方法外还要决定以索引方式访问具体表数据还是以全表扫描方式访问表数据，即优化器还要决定访问单表的方法。

（1）表连接类型

Oracle数据库中表连接分为内连接和外链接两种类型。

（a）内连接（inner join）

内连接是指表连接的连接结果只包含那些完全满足连接条件的记录。对于SQL而言，只要其where条件中没有定义那些外连接关键字（如left outer join、right outer join、full outer join）外的所有连接类型定义为内连接。

（b）外连接（outer join）

外连接是对内连接的一种扩展，它是指表连接的结果除了包括那些满足条件的连接结果外还包含驱动表中不满足连接条件的结果。标准SQL的外连接分为左连接（left outer join）、右连接（righr outer join）、全连接（full outer join）三种。全连接可以近似看成是先做左连接union再做右连接，这里所说的近似是因为oracle实际的处理并不是这样做的，因为union要对结果集进行排序，而全连接并不需要排序。

对于外连接而言，除了表连接的条件之外的额外条件在目标SQL的SQL文本中所处的位置可能会影响该SQL的执行结果，而内连接没有限制。

（2）表连接方法

优化器在解析含有表连接的目标SQL时，当根据目标SQL的SQL文本决定了表的连接类型后，接下来还要决定表的连接方法。Oracle中两表的连接方法有“排序合并”，“嵌套循环”，“哈希连接”，“笛卡尔连接”四种。

（a）排序合并连接（sort merge join）

排序合并连接是一种两表做表连接时用排序操作（sort）和合并操作（merge）来得到表连接结果的方法。排序合并的执行顺序如下（假设连接表为t1和t2）：

（a.1）首先以目标SQL中的谓语条件去访问表t1，然后对结果按连接列进行排序，排好序的结果记为结果集1.

（a.2）然后以目标SQL中的谓语条件去访问表t2，然后对结果按连接列进行排序，排好序的结果记为结果集2.

（a.3）然后对结果集1和结果集2进行合并操作，从中取匹配记录作为排序合并的最终结果。

排序合并连接的适用场景总结入下：

b.1）通常情况下，排序合并连接的执行效率不如哈希连接，但是前者使用范围广，因为hash连接只适用于等值连接，而排序合并还适用于其他连接（比如<、>、<=、>=）。

b.2）通常情况下，排序合并不适合OLTP类型连接，本质是排序对于OLTP类型来说成本是昂贵的。但是，如果能避免排序，也是可以适合于OLTP系统的。

（b）嵌套循环连接（nested loops join）

嵌套循环连接是两个表在做连接时采用两层嵌套循环（外层循环和内层循环）的方式来得到表连接结果的方法。嵌套循环的执行顺序如下（假设连接表为t1和t2）：

（a.1）首先，优化器会根据一定的规则来决定t1和t2谁是驱动表、谁是被驱动表，驱动表做外层循环，被驱动表做内层循环，这里假设t1是驱动表，t2是被驱动表。

（a.2）接着以目标SQL中的谓语条件去访问驱动表t1，所得到的结果集记为结果集1.

（a.3）然后遍历驱动结果集1及遍历被驱动表2，即取出结果集1中的第一条记录，然后按照连接条件去遍历t2，查看是否有匹配记录；接着取出结果集1的第二条记录，按照同样的方法去遍历表t2，查看匹配记录，直到结果集1中所有的记录全部遍历完成为止。

嵌套循环连接的适用场景总结入下：

b.1）如果驱动表所对应的驱动结果集的记录较少，同时被驱动表的连接列上又存在唯一性索引（或者被驱动表的连接列上存在选择性较好的非唯一性索引），那么此时使用嵌套循环的执行效率会非常高。如果驱动表所对应的驱动结果集数量较多，那么即使被驱动表的连接列上存在唯一性索引，那么执行效率也不会很高。

b.2）只要驱动结果集的记录较少，那么就具备了做嵌套循环的前提条件，而驱动表所对应的驱动结果集是在对驱动表应用了目标SQL之后所得到的结果集，所以大表也会可以作为驱动表的。

b.3）嵌套循环可以实现快速响应。

嵌套循环在访问被驱动表时，如果被驱动表有索引，将会采用单块读的方式访问索引，同时，如果返回结果集列不在索引中取得，嵌套循环连接也要采用单块读的方式回表。Oracle 11g中，oracle引入了向量I/O（vector i/o），oracle就可以将原来一批单块读所消耗的I/O组合起来，然后用一个向量I/O去批量处理它们，使用这种方法达到单块读的数量不下降的情况下减少这些单块读所需要耗费的物理I/O数量，也就提高了嵌套循环的效率。体现在执行计划上，你会发现在oracle 11g中本来一次嵌套循环可以处理完毕的SQL，但在执行计划中嵌套循环连接的数量由原来的1个变为现在的2个。

（c）hash连接（hash join）

Hash连接是指两表在做连接时主要依靠哈希算法来取得结果集的方法。Oracle 7.3中引入了hash连接，在oracle 10g版本中，优化器是否启用哈希连接取决于隐藏_HASH_JOIN_ENABLED，而在oracle 10g前，优化器是否引用hash连接取决于参数HASH_JOIN_ENABLED。Hash连接的执行顺序如下（假设连接表为t1和t2）：

a.1）首先，oracle会根据参数hash_area_size、db_block_size、_hash_multiblock_io_count的值来决定hash partition的数量（hash partition是一种逻辑上的概念，它实际上是一组hash bucket的集合，所有的hash partition的集合就成为hash table。）。

a.2）表t1和t2施加了目标SQL的谓语条件后，得到的结果中数量较少的那个结果集会被oracle选为哈希连接的驱动结果集，这里假设t1结果集为驱动结果集，记为s，t2结果集较多，记为b。

a.3）接着oracle会遍历s，读取s中的每一条记录，并对每一条记录按照t1中的连接列做哈希运算，这个哈希运算会使用2个hash内置函数，这两个hash内置函数同时对连接列计算哈希值，我们把2个函数分别记为hash_func_1和hash_func_2，他们所计算的值分别为hash_value_1和hash_value_2。

a.4）然后oracle会按照hash_value_1的值把相应的s中对应的记录存储在不同的hash partition的不同hash bucket里，同时跟该记录记录在一起的还有该hash_func_2计算出来的hash_value_2。注意，存储在hash bucket里的并不是完整的行记录，只需要存储于目标SQL相关的查询列和连接列足够，我们把s对应的hash partition记为si。

a.5）在构建si的同时，oracle会构建一个bit map索引，这个索引用来记录每个hash bucket是否有记录。

a.6）如果s的结果集很大，就有可能出现pga被填满的情况，此时oracle会将工作区中记录最多的hash partition写到磁盘上，接着oracle会继续构建hash table，在继续构建的过程中，如果工作区内存满了，oracle会重复上述工作。如果构建的hash partition已经被写会磁盘，则此时oracle回去更新磁盘上的hash partition，即把hash_value_2直接加到这个已经位于磁盘上的hash partition对应的hash bucket中。极端请况可能出现某个hash partition的部分记录还在内存中，该hash partition的剩余部分已经被写道磁盘上。

a.7）上述构建s所对应的hash table的过程会一直持续下去，知道遍历完s中的所有记录为止。

a.8）oralce会对所有si按照他们所包含的记录数排序，然后依次将排序好的数据尽可能存放到内存中，如果内存实在放不下，那些数据量交大的hash partition还是会被写到磁盘上。

a.9）oracle至此已经处理好s，接着开始处理b。

a.10）oracle会遍历b，读取b中的每一条记录，并按照t2的连接列做哈希运算，这个过程和步骤3一模一样。

a.11）上述过程会一直持续下去，直到遍历完所有数据为止。

a.12）至此oracle处理完所有内存中数据，接着处理硬盘上的数据。

a.13）因为构建si和bj时用了同样的hash函数，所以oracle处理磁盘上的数据时可以放心的匹配。即只有对应的hash partition number值相同的si和bj才可能会产生满足条件的记录。

a.14）对于每一个sn和bn，他们中结果集较少的会被当做驱动结果集。继续匹配。

a.15）步骤14中存在匹配记录，则该匹配记录也会作为满足目标SQL的连接条件的记录返回。

a.16）上述过程会一直持续下去，直到遍历完所有sn和bn为止。

hash连接的适用场景总结入下：

b.1）哈希连接不一定需要排序，大部分情况下。

b.2）哈希连接的驱动表所对应的选择列可选择性应尽可能好，因为这个可选择性会影响到hash bucket的记录数，而hash bucket的记录数又会直接影响从该hash backet中查找匹配记录数的效率。不好的体现在于哈希连接执行了很长时间都没有结束，数据库所在的数据库服务器cpu占用率很高，但是目标SQL所消耗的逻辑读很低，因为此时大部分时间都浪费在了遍历上述hash bucket的记录上，而遍历hash bucket的记录发生在pga，所以不消耗逻辑读。

b.3）哈希连接只适用于CBO，同时哈希连接也只适用于等值连接。

b.4）哈希连接适合于小表和大表做表连接且连接结果集记录数较多的情形，特别是在小表的连接列可选择性特别好的情况下，这时hash连接的执行时间近似于对大表的执行时间。

b.5）当两表做hash连接时，如果在施加了目标SQL指定的谓语条件后所得到的数量较小的那个结果集所对应的hash table能够完全被容纳在内存中（pga），此时hash连接执行效率会非常高。

（d）笛卡尔连接（cross join）

笛卡尔连接又称笛卡尔积（cartesian product），它是一种两表在做连接时没有任何连接条件的表连接方法。笛卡尔积是一种特殊的合并连接，这里的合并连接和排序合并类似，只不过笛卡尔连接不需要排序，并且执行合并时并没有连接条件。的执行顺序如下（假设连接表为t1和t2）：

a.1）首先以目标SQL中制定的谓语条件访问表t1，此时所得到的结果集记为结果集1，结果集1的记录数为m。

a.2）其次以目标SQL中制定的谓语条件访问表t2，此时所得到的结果集记为结果集2，结果集1的记录数为n。

a.3）最后结果集1和结果集2执行合并操作。笛卡尔积连接结果记录数为m*n。

笛卡尔连接总结如下：

b.1）笛卡尔连接出现一般都是目标SQL中漏写了连接条件，所以笛卡尔连接一般都是不好的，除非刻意这样做（比如有时利用笛卡尔积来减少对目标SQL中大表的全表扫描次数）。

b.2）有时候笛卡尔连接的出现时因为目标SQL中使用了ordered hint，同时该SQL中文本委会相邻的两个表又没有直接的连接条件。

b.3）有时候笛卡尔连接出现时因为统计信息不准。

（4）反连接（anti join）

oracle中没有专门的关键词定义反连接，形如“t1.x anti= t2.y”来表示反连接，其中t1为驱动表，t2是被驱动表，反连接条件为t1.x = t2.y。这里“t1.x anti= t2.y”的含义是只要表t2中由满足条件的“t1.x = t2.y”的记录存在，则t1表中满足条件的“t1.x = t2.y”的记录就会被丢弃，最后返回的记录就是t1表中那些不满足条件“t1.x = t2.y”的记录。目标SQL中那些外部where条件为not exists、not in或<>all的子查询在做子查询展开时常常会被转换为对应的反连接。（即返回在t2中满足条件但是不在t1中满足条件的记录。）

例：t1和t2在在各自的连接列col2上没有null值情况下：

select * from t1 where col2 not in (select col2 from t2);

或

select * from t1 where col2<>all(select col2 from t2);

或

select * from t1 where not exists (select 1 from t2 where col2=t1.col2);

对于连接表有null值得情况说明如下：

（a）t1和t2表在各自的连接列col2上一但有null值，以上例子的执行结果将不完全等价。

（b）not in和<>all对null值敏感，这意味着not in和<>all后面的子查询或常量一但出现null值，则整个sql的执行结果就是null。

（c）not exsts对null值不敏感，这意味着not exsts后面值出现null对结果不会有什么影响。

oracle的处理办法：为了解决not in和<>all对null值敏感的问题，oracle推出了改良的反连接，这种反连接能够处理null值，oracle称其为null-aware anti jion，对应执行计划“..... anti na”,其中na就是null aware的缩写。在oracle 11gR2中，oralce是否启用null-aware anti join受隐藏_optimizer_null_aware_antijoin控制，其默认值为true。

（5）半连接（semi join）

oracle中没有专门的关键词定义半连接，形如“t1.x semi= t2.y”来表示半连接，其中t1为驱动表，t2是被驱动表，反连接条件为t1.x = t2.y。这里“t1.x anti= t2.y”的含义是只要表t2中找到一条满足的“t1.x = t2.y”的记录存在，则马上停止搜索t2，并直接返回t1表中满足条件的“t1.x = t2.y”的记录，最后返回的记录就是t1表中那些满足条件“t1.x = t2.y”的记录。也就是说，t2中满足条件t1.x = t2.y的记录有多条，t1表也只会返回一条记录。所以半连接和普通的内连接不同，半连接会去重。目标SQL中那些外部where条件为exists、in或<>any的子查询在做子查询展开时常常会被转换为对应的半连接。（即返回在t2中满足条件并且在t1中满足条件的记录。）

例：

select * from t1 where col2 in (select col2 from t2);

或

select * from t1 where col2<>any(select col2 from t2);

或

select * from t1 where exists (select 1 from t2 where col2=t1.col2);

（6）星型连接（star join）

星型连接通常应用于数据仓库类型应用，他是一个单个事实表外键和多个维度表主键之间的连接，维度表通过连接条件和事实表连接，维度表之间一般没有直接连接关系。一般事实表的外键上还会存在bit map索引。