1.创建测试用的emp 表
create sequence emp_id;
CREATE TABLE emp
(
emp_id number not null,
emp_no varchar2(20),
emp_name varchar2(50),
dept_no varchar2(20),
job_type varchar2(20)
) tablespace MY_UD;
alter table emp
add constraint pk_emp primary key (emp_id)
using index tablespace I_MY_UD;
create index idx_emp_1 on emp(emp_no) tablespace I_MY_UD;
insert into emp(emp_id,emp_no,emp_name,dept_no,job_type)
select emp_id.nextval as emp_id,
‘emp_no_‘ || lpad(rownum,3,‘0‘) as emp_no
,‘emp_name_‘ || lpad(rownum,3,‘0‘) as emp_name
,‘dept_no_‘ || lpad(trunc(dbms_random.value (1,11)),2,‘0‘) as dept_no
,‘job_type_‘ || lpad(trunc(dbms_random.value (1,6)),2,‘0‘) as job_type
from dual connect by level<=100;
analyze table emp compute statistics;
analyze index pk_emp compute statistics;
analyze index idx_emp_1 compute statistics;
2.dept 表
create sequence dept_id;
CREATE TABLE dept
(
dept_id number not null,
dept_no varchar2(20),
dept_name varchar2(50)
) tablespace MY_UD;
alter table dept
add constraint pk_dept primary key (dept_id)
using index tablespace I_MY_UD;
create index idx_dept_1 on dept(dept_no) tablespace I_MY_UD;
insert into dept(dept_id,dept_no,dept_name)
select dept_id.nextval as dept_id
,‘dept_no_‘ || lpad(rownum,2,‘0‘) as dept_no
,‘dept_name_‘ || lpad(rownum,2,‘0‘) as dept_name
from dual connect by level<=10;
analyze table dept compute statistics;
analyze index pk_dept compute statistics;
analyze index idx_dept_1 compute statistics;
select * from dept
- job 表
create sequence job_id;
CREATE TABLE job
(
job_id number not null,
job_type varchar2(20),
job_name varchar2(50)
) tablespace MY_UD;
alter table job
add constraint pk_job primary key (job_id)
using index tablespace I_MY_UD;
create index idx_job_1 on job(job_type) tablespace I_MY_UD;
insert into job(job_id,job_type,job_name)
select dept_id.nextval as dept_id
,‘job_type_‘ || lpad(rownum,2,‘0‘) as job_type
,‘job_name_‘ || lpad(rownum,2,‘0‘) as job_name
from dual connect by level<=5;
analyze table job compute statistics;
analyze index pk_job compute statistics;
analyze index idx_job_1 compute statistics;
select * from job
- USE_MERGE(排列合并连接)
#在排列合并连接中,Oracle分别将第一个源表、第二个源表按它们各自要连接的列排序,然后将两个已经排序的源表合并。如果找到匹配的数据,就放到结果集中。
#在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
#但是,排列合并连接只能用于等价连接(WHERE e.dept_no=d.dept_no,而不是WHERE D.dept_no>=E.dept_no)。
#排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。
#语法如下 (其中第1张表应该通过合并连接方式来访问，第2张表是可选的，如没有指定第2张表，那么就由优化器决定由谁和第1张表进行连接)：
select /*+ use_merge([@query_block] table [table]) */ column1,...
#例
select /*+ use_merge(e d) */ d.*,e.* from dept d,emp e where e.dept_no=d.dept_no
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7000 | 6 | 00:00:01 |
| 1 | MERGE JOIN | | 100 | 7000 | 6 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 10 | 240 | 2 | 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_DEPT_1 | 10 | | 1 | 00:00:01 |
| * 4 | SORT JOIN | | 100 | 4600 | 4 | 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 | 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("E"."DEPT_NO"="D"."DEPT_NO")
* 4 - filter("E"."DEPT_NO"="D"."DEPT_NO")
5.USE_NL(嵌套循环连接)
#在嵌套循环连接中,Oracle从第一个行源中读取第一行,然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中,然后Oracle将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。
#第一个记录源通常称为外部表,或者驱动表,相应的第二个行源称为内部表。使用嵌套循环连接是一种从连接结果中提取第一批记录的最快速的方法。
#在驱动行源表(就是您正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,
#而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。
#然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。
select /*+ use_nl(d e) */ d.*,e.* from dept d,emp e where e.dept_no=d.dept_no
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7000 | 18 | 00:00:01 |
| 1 | NESTED LOOPS | | 100 | 7000 | 18 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 10 | 240 | 3 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 10 | 460 | 2 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("E"."DEPT_NO"="D"."DEPT_NO")
#Oracle从第一个行源（dept 小表）中读取第一行
select /*+ leading(d) use_nl(e) */ d.*,e.* from dept d,emp e where e.dept_no=d.dept_no
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7000 | 18 | 00:00:01 |
| 1 | NESTED LOOPS | | 100 | 7000 | 18 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 10 | 240 | 3 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | EMP | 10 | 460 | 2 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("E"."DEPT_NO"="D"."DEPT_NO")
6.USE_HASH(哈希连接)
#当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。在哈希连接中,Oracle访问一张表(通常是较大的表),并在内存中建立一张基于连接键的哈希表。然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。
#只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接(HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE)。
#这和嵌套循环连接有点类似——Oracle先建立一张哈希表以利于操作进行。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，
#因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。
#和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接
select /*+ use_hash(e d) */ d.*,e.* from dept d,emp e where e.dept_no=d.dept_no
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7000 | 6 | 00:00:01 |
| * 1 | HASH JOIN | | 100 | 7000 | 6 | 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 10 | 240 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("E"."DEPT_NO"="D"."DEPT_NO")
#emp：大表 (将用于建立哈希表）
select /*+ leading(e) use_hash(d) */ d.*,e.* from dept d,emp e where e.dept_no=d.dept_no
or
select /*+ ordered use_hash(e d) */ d.*,e.* from emp e,dept d where e.dept_no=d.dept_no
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7000 | 6 | 00:00:01 |
| * 1 | HASH JOIN | | 100 | 7000 | 6 | 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 100 | 4600 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 10 | 240 | 3 | 00:00:01 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("E"."DEPT_NO"="D"."DEPT_NO")
https://www.iteye.com/blog/taiwei-peng-2289752