《收获,不止Oracle 》读后笔记 (4) 表连接学以致用

*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/*~~~~~~~ 表连接 ~~~~~~~*/

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

嵌套循环 (NL)

1、驱动表返回多少条记录, 被驱动表访问多少次 (Starts)

2、"有" 驱动顺序

3、无需排序 (Used-Mem)

4、支持所有SQL连接条件, 无连接条件限制

哈希连接 (Hash Join)

1、驱动表和被驱动表只会访问0或1次, 调整 PGA 中 HASH_AREA_SIZE

2、"有" 驱动顺序

3、无需排序 (Used-Mem), 但消耗内存用于建立 HASH 表

4、不支持>、<、<>、like 连接谓词, 否则查询计划使用NL

排序合并(Merge Join)

1、驱动表和被驱动表只会访问0或1次, 调整 PGA 中 SORT_AREA_SIZE

2、"无" 驱动顺序

3、需要排序 (Used-Mem), 仅取 "部分" 使用字段 (Select)

4、不支持<>、like (支持 >、<) 连接谓词, 否则查询计划使用NL

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

嵌套循环与索引

1、两表关联返回的记录不多, 最佳情况是驱动表结果集仅返回1条或几条记录, 而被驱动表仅匹配到1条或几条记录, 与(被)驱动表记录数无关

2、遇到不等值查询导致哈希和排序合并连接使用受限, 可以采用NL

3、理想状况: 驱动表 "限制条件" 所在列有索引 / 被驱动表 "连接条件" 所在列有索引

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

哈希连接与索引

"连接条件列索引" 起不到快速检索的作用,  但 "限制条件" 列索引可以快速检索记录

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

排序合并与索引

连接列索引消除排序的的作用(Oracle 排序合并连接本身存在缺陷, 连接条件2个列都建过索引, 但只能消除一张表的排序)

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/*~~~~~~~ 实验脚本 ~~~~~~~*/

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

#Remark :  #

insert into  t1 

    select rownum, rownum, dbms_random.string('a', 50) from dual 

    connect by level <= 100

    order by dbms_random.random;

commit;

set linesize 1000

alter session set statistics_level = all;

select /*+ leading(t2) use_merge(t1) */ *

from t1, t2

where 1=1

    and t1.id = t2.t1_id

;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

执行计划:

Starts : 表访问次数

E-Rows : 估算行数

A-Rows : 实际行数