select
F5.ID,
F5.ORGID,
F5.BINDID,
F5.PROCESSDEFID,
F5.ISEND,
F5.NAME,
F5.AWENFNUM,
F5.TEL,
R5.WARNPROCESSID,
R5.WARNID,
R5.FROMWARNLEVEL,
R5.WARNSTATUS,
R5.WARNLEVEL,
R5.WARNVAL,
R5.STYPE,
R5.PROCESSTM,
R5.PROCESSPERSON
from BO_EU_LAWENFOFF5 F5
LEFT JOIN ST_WARNPROCESS_R5 R5
ON F5.ID=R5.WARNPROCESSID
WHERE F5.ID <=‘300’
AND R5.WARNPROCESSID <=‘200’;
1 #NSET2: [247, 554722, 525]
2 #PRJT2: [247, 554722, 525]; exp_num(17), is_atom(FALSE)
3 #HASH2 INNER JOIN: [247, 554722, 525]; KEY_NUM(1); KEY(R5.WARNPROCESSID=F5.ID) KEY_NULL_EQU(0)
4 #BLKUP2: [59, 50000, 159]; INDEX33555716(R5)
5 #SSEK2: [59, 50000, 159]; scan_type(ASC), INDEX33555716(ST_WARNPROCESS_R5 as R5), scan_range[(null2,min),(‘200’,max))
6 #BLKUP2: [166, 111000, 366]; IDX_ID_BO_EU_LAWENFOFF5(F5)
7 #SSEK2: [166, 111000, 366]; scan_type(ASC), IDX_ID_BO_EU_LAWENFOFF5(BO_EU_LAWENFOFF5 as F5), scan_range(null2,‘200’]

以上查询中，两个表都是100万的数据，这两个表在连接之后做了条件过滤，两个表都走了索引，连接情况变为了HASH INNER JOIN连接，根据多次测试，表连接之后如果有where条件进行过滤，即便不是索引项，有时候也会走索引
select
F5.ID,
F5.ORGID,
F5.BINDID,
F5.PROCESSDEFID,
F5.ISEND,
F5.NAME,
F5.AWENFNUM,
F5.TEL,
R5.WARNPROCESSID,
R5.WARNID,
R5.FROMWARNLEVEL,
R5.WARNSTATUS,
R5.WARNLEVEL,
R5.WARNVAL,
R5.STYPE,
R5.PROCESSTM,
R5.PROCESSPERSON,
S3.ID,
S3.NAMEADD,
O3.ID,
O3.NAME,
O3.SEX,
O3.ADDRESS,
O3.TELE
from BO_EU_LAWENFOFF5 F5
LEFT JOIN ST_WARNPROCESS_R5 R5
ON F5.ID=R5.WARNPROCESSID
LEFT JOIN ADDRESS3 S3 ON F5.ID = S3.ID
LEFT JOIN USERINFO O3 ON R5.WARNPROCESSID=O3.ID ;
1 #NSET2: [22220, 99049524, 366]
2 #PRJT2: [22220, 99049524, 366]; exp_num(24), is_atom(FALSE)
3 #HASH RIGHT JOIN2: [22220, 99049524, 366]; key_num(1), ret_null(0), KEY(O3.ID=exp_cast(R5.WARNPROCESSID))
4 #CSCN2: [1, 1000, 222]; INDEX33555662(USERINFO as O3)
5 #HASH RIGHT JOIN2: [11420, 99049524, 366]; key_num(1), ret_null(0), KEY(S3.ID=F5.ID)
6 #CSCN2: [1, 2003, 96]; INDEX33555599(ADDRESS3 as S3)
7 #HASH LEFT JOIN2: [619, 99049524, 366]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(F5.ID=R5.WARNPROCESSID)
8 #CSCN2: [179, 1000000, 366]; INDEX33555714(BO_EU_LAWENFOFF5 as F5)
9 #CSCN2: [137, 1000000, 159]; INDEX33555715(ST_WARNPROCESS_R5 as R5)

如果是以上语句，涉及多表联合查询，表数据比较多，而且发现是使用的hash right join\hash left join，建议使用hint，ENABLE_HASH_JOIN(0),取消使用hash链接，改为索引链接或者嵌套连接，速度会提升
select
/+ENABLE_HASH_JOIN(0)/
F5.ID,
F5.ORGID,
F5.BINDID,
F5.PROCESSDEFID,
F5.ISEND,
F5.NAME,
F5.AWENFNUM,
F5.TEL,
R5.WARNPROCESSID,
R5.WARNID,
R5.FROMWARNLEVEL,
R5.WARNSTATUS,
R5.WARNLEVEL,
R5.WARNVAL,
R5.STYPE,
R5.PROCESSTM,
R5.PROCESSPERSON,
S3.ID,
S3.NAMEADD,
O3.ID,
O3.NAME,
O3.SEX,
O3.ADDRESS,
O3.TELE
from BO_EU_LAWENFOFF5 F5
LEFT JOIN ST_WARNPROCESS_R5 R5
ON F5.ID=R5.WARNPROCESSID
LEFT JOIN ADDRESS3 S3 ON F5.ID = S3.ID
LEFT JOIN USERINFO O3 ON R5.WARNPROCESSID=O3.ID ;

以上语句通过执行HINT参数改变hash链接为索引链接，执行速度提升