oracle loop 6 6,oracle sql tuning 6--理解表连接

表与表之间经常是要关联的，应该理解关联[或者说是连接]

什么是连接？连接是一种从多个表中取回数据的一种声明.在连接中一个表被叫做内部表，一个叫做外部表.连接的特点：from后边跟了一堆表...

查询优化器如何为连接选择执行计划呢？先看下以下的概念

Access Paths：对于简单的语句，优化器从连接的每个表中取数据

Join Method：为了连接两个行源中的第对数据，ORACLE执行连接操作，连接的方法包括nested loop, sort merge, cartesian, hash joins

Join Order：连接顺序。对于执行一个语句，如果语句中连接超过两张表。ORACLE先把两个表关联取数据，然后关联后的结果与其它表在关联.这种关联动力一直执行，直到所有表都关联完到结果集为止.

查询优化器为连接选择执行计划的时候，通常考虑以下情况

#1.优化器先决定是否要连接两个或者多个表。结果集合至少包含一条记录。优化器通过唯一索引，主键索引来做判断。

#2.如果连接中有外关联。the table with the outer join operator must come after the other table in the condition in the join order

优化器生成一系列的执行计划[通过连接顺序，连接方法，访问路径生成的]。优化器然后就估算每个执行计划的COST，选择最小的COST执行.优化器估算COST的方式有以下：

nested loops 的COST：读取第一行外部表所要的COST，match内部表所有的COST。优化器通过数据字典中的统计信息来估算这些COST值。

merge join 的COST：基于读取所有行到内存并且排序他们所用的总COST

hash join 的COST：The cost of a hash join is based largely on the cost of building a hash table on one of the input sides to the join and using the rows from the other of the join to probe it[翻译不好！]

对于每个操作的COST，优化器也考虑其它因素。如：

一个小的sort area size会增加merge join排序的成本，因为在小的sort area size情况下，排序通常会占用更多的CPU和IO资源.

DB_FILE_MULTIBLOCK_READ_COUNT参数设置大一点好，可以减少排序成本，另外还可以指定ORDER HINT来指定优化器选择什么ORDER顺序.

下面说下几种连接的方式，要搞清楚

Nested loop joins:嵌套循环非常有用，当小量的数据被连接并且连接条件是以一个有效的方式访问第2个表的时候。对于这种连接方式，通过内部表驱动外部表。Nested loop joins包含以下几个步骤

1.优化器决定那个表是驱动表，并且把驱动表指定为外部表

2.另外的表被指定为内部表

3.对于外部表中的第一条记录，ORACLE都要去访问内部表中的第一行。外部循环对于外部表中的每一行存在，内部循环对于内部表的每一行存在。在执行计划中外部循环先于内部循环出现。如下：

NESTED LOOPS

outer_loop

inner_loop

以下是一个Nested loop joins执行计划的示意图

...

|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|

|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|

|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|

|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |

...

在这个例子中，外部表是employees,外部循环取回employees中的每条记录。对于外部表取回来的每条数据，内部循环在JOBS表中检索与之关联的行.适合于驱动表的记录不要太大的情况，内部表要走索引的情况

何时优化器选择Nested loop joins?

当连接数据量比较小，连接的两个表之间有好的连接条件时会采用这种连接方式。驱动顺序是从外部表到内部表，所以执行计划中的表顺序是很重要的.

外部表是驱动行源，我认为就是驱动表。外部表产生的系列数据来驱动连接条件。表的访问方式可以是索引访问，或者是全表扫描。Nested loop joins产生的数据，可以被应用于另一个Nested loop joins.

内部表会循环，对于每一条从外部表取回的数据。内部表理想的访问方式是用索引。你可以使用USE_NL(table1 table2)提示来指定表的连接方式为Nested loop joins.多层Nested loop joins的执行计划一般是以下样子.

SELECT STATEMENT

NESTED LOOP 3

NESTED LOOP 2          (OUTER LOOP 3.1)

NESTED LOOP 1         (OUTER LOOP 2.1)

OUTER LOOP 1.1     - #1

INNER LOOP 1.2     - #2

INNER LOOP 2.2      - #3

INNER LOOP 3.2       - #4

Hash joins 是什么意思？

Hash joins 一般用于连接数据量较大的表,优化器把两个表中较小的表利用连接键[也就是根据连接字段计算hash 值]在内存中建立hash表,然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。这种方式适合于小表可以完全放到内存中，其成本接近全表扫描两个表的成本之和。

那么何时优化器会使用hash joins呢？

一般说来，如果两个连接的表是等值连接，且满足

#1.大量数据需要被连接

#2.小表的大部分需要被连接

以下是一个hash join的例子

SELECT o.customer_id, l.unit_price * l.quantity

FROM orders o ,order_items l

WHERE l.order_id = o.order_id;

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

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

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

|   0 | SELECT STATEMENT     |              |   665 | 13300 |     8  (25)|

|*  1 |  HASH JOIN           |              |   665 | 13300 |     8  (25)|

|   2 |   TABLE ACCESS FULL  | ORDERS       |   105 |   840 |     4  (25)|

|   3 |   TABLE ACCESS FULL  | ORDER_ITEMS  |   665 |  7980 |     4  (25)|

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

Predicate Information (identified by operation id):

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

1 - access("L"."ORDER_ID"="O"."ORDER_ID")

同样你也可以使用提示USE_HASH 来让关联表使用hash join

Sort merge joins是个什么？

Sort merge joins可用于连接两个独立的行源，Hash joins 一般比Sort merge joins优。另一方面Sort merge joins比hash joins优，当在以下情况出现时:

#1表的结果集是排序好的

#2A sort operation does not have to be done.

如果选择的连接方式不是有效的法(比如：不是索引，是全表扫描)也没必要用sort merge joins

坏处不少的sort merge joins到底什么时候有用呢?

当连接条件是< ,<=,>,>=时有用。 Sort merge joins 对于大数量的结果集比nested loop joins 好。你不应该使用hash joins,除去是等值连接的情况.

在merge join中没有驱动表的概念。merge join包含以下两个步骤

#1.排序连接操作：所有的输入都通过连接关键字排序

#2.merge join操作：排序好的结果集间进行merge join操作

那么优化器何时使用sort merge join呢？

#1.连接条件不是等值连接

#2.优化器认为merge join比hash join成本小的时候.

同样你可以使用USE_MERGE 强迫表采用USE_MERGE 连接方式.

接下来是Cartesian join 笛卡尔连接，这种相信大家都不会用的

外关联，全关联那些就不说了。

下面测试下oracle自带hr用户下的表连接

1.在有正常索引的情况下的nested loops join

2.建立两个类似的测试表，相同的语句在看一下用的是什么连接呢？jobs_bak,employees_bak都没索引

3.在2的情况下我强制让他走Nested loops呢，COST会有什么变化?

慎用！