深入解析Oracle的三种join方法

雪凤凰_cu 发表于 2011-12-22 08:54

<div class="t_msgfont" id="postmessage_272916" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; ">NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内 表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回 的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是 驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO 默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。 HASH JOIN : 散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据 源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配 的行。 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成 本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的 分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量 提高I/O 的性能。 也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散 列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用<a href="http://space.itpub.net/22664653/viewspace-678383" target="_self" style="word-break: break-all; text-decoration: underline; color: rgb(51, 51, 51); line-height: normal !important; " target="_blank">SQL</a>工作区 自动<a href="http://space.itpub.net/22664653/viewspace-678383" target="_self" style="word-break: break-all; text-decoration: underline; color: rgb(51, 51, 51); line-height: normal !important; " target="_blank">管理</a>，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。 排序合并连接 通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执 行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用 USE_MERGE(table_name1 table_name2)来强制使用排序合并连接</div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "> </div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "></div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "></div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "> </div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; ">Nested loop join: 步骤：确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）。需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。 cost = outer access cost + (inner access cost * outer cardinality) | 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为outer table, JOBS为inner table. Hash join 步骤：将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。 cost = (outer access cost * # of hash partitions) + inner access cost -------------------------------------------------------------------------- | 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)| -------------------------------------------------------------------------- ORDERS为HASH TABLE，ORDER_ITEMS扫描 Sort merge join 步骤：将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式： 1.RBO模式 2.不等价关联(>,<,>=,<=,<>) 3.HASH_JOIN_ENABLED=false 4.数据源已排序 cost = (outer access cost * # of hash partitions) + inner access cost</div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "> </div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "></div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "></div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; "> </div><div class="t_msgfont" style="word-break: break-all; line-height: 1.8em !important; margin-top: 10px; margin-right: 0px; margin-bottom: 10px; margin-left: 0px; ">转载biti的一段话:           举例，表连接返回一条记录      存在两个表，一个   10条记录   ，一个1000万条记录           若2表都存在连接字段索引，若以小表为驱动表，则      代价：        10*   (通过索引在大表查询一条记录的代价)             若以大表为驱动表：           1000万   *   (通过索引在小表中查询一条记录的代价)           通过索引获取一条记录，10rows的表，代价通常在   3   blocks        索引2块，表一块           而如果是1000万的表，索引可能达到4块表一块      这样一来参考上面的计算，你说哪个更好？很显然！           小表查询参考           SQL>   create   table   test   as   select   *   from   all_objects   where   rownum   <   11;           Table   created.           SQL>   create   index   test_index   on   test(object_id);           Index   created.           SQL>   select   object_id   from   test;           OBJECT_ID      ----------      18159      7781      4841      19891      22549      17099      17712      4287      10107      19135           10   rows   selected.                Execution   Plan      ----------------------------------------------------------      0   SELECT   STATEMENT   ptimizer=CHOOSE      1   0   TABLE   ACCESS   (FULL)   OF   'TEST'                          Statistics      ----------------------------------------------------------      0   recursive   calls      12   db   block   gets      6   consistent   gets      0   physical   reads      0   redo   size      736   bytes   sent   via   SQL*Net   to   client      425   bytes   received   via   SQL*Net   from   client      2   SQL*Net   roundtrips   to/from   client      0   sorts   (memory)      0   sorts   (disk)      10   rows   processed           SQL>   select   *   from   test   where   object_id   =   4287;           OWNER   OBJECT_NAME      ------------------------------   ------------------------------      SUBOBJECT_NAME   OBJECT_ID   DATA_OBJECT_ID   OBJECT_TYPE      ------------------------------   ----------   --------------   ------------------      CREATED   LAST_DDL_   TIMESTAMP   STATUS   T   G   S      ---------   ---------   -------------------   -------   -   -   -      SYS   /1033c8a_SqlTypeWithMethods      4287   JAVA   CLASS      14-NOV-00   03-JUL-03   2003-07-03:11:18:19   INVALID   N   N   N                     Execution   Plan      ----------------------------------------------------------      0   SELECT   STATEMENT   ptimizer=CHOOSE      1   0   TABLE   ACCESS   (BY   INDEX   ROWID)   OF   'TEST'      2   1   INDEX   (RANGE   SCAN)   OF   'TEST_INDEX'   (NON-UNIQUE)                          Statistics      ----------------------------------------------------------      0   recursive   calls      0   db   block   gets      3   consistent   gets      0   physical   reads      0   redo   size      1157   bytes   sent   via   SQL*Net   to   client      425   bytes   received   via   SQL*Net   from   client      2   SQL*Net   roundtrips   to/from   client      0   sorts   (memory)      0   sorts   (disk)      1   rows   processed   </div>

yeeek 发表于 2011-12-22 09:15

这个字体很特别啊

页: [1]

Chinaunix's Archiver

深入解析Oracle的三种join方法