使用Oracle进行机构权限设计时的一次优化

业务需求：机构用户只能查询到归属于本机构和下级机构的数据。(此处000为根节点机构代码)
原始SQL:

select * from a_table 
	where belong_org_code  in (
		select org_code   
			from org_table   START WITH org_code = '000' connect by prior org_code = upper_org_code
		)

由于机构信息过多in中的机构信息有一万多条，同时a表中拥有50多万的数据，导致这条sql执行时间13s！
在大神的指点下赶紧抢救！
解决方法：
在a表中添加org_path字段存储机构路径（如：/000/001/012）记录每条信息的机构路径
修改后的SQL：

--输出a表中012机构所有同级机构或下级机构数据
select * from a_table where instr(org_path,'/000/001/012') >0

通过下面的SQL即可获得org_path（即类似于：/000/001/012）字符串:

--输出结果/000/001/012
select sys_connect_by_path(org_code,'/') path 
	from org_table  
		where org_code = '012' start with org_code = '000' connect by prior org_code = upper_org_code

然后给org_path建立索引，执行0.21s！
最后：大佬牛逼！

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

1 select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置
3 select instr('helloworld','wo') from dual; --返回结果：6    即“wo”同时出现，第一个字母“w”出现的位置

INSTR方法的格式为
INSTR(src, subStr,startIndex, count)
src: 源字符串
subStr : 要查找的子串
startIndex : 从第几个字符开始。负数表示从右往左查找。
count: 要找到第几个匹配的序号
返回值： 子串在字符串中的位置，第1个为1；不存在为0. （特别注意：如果src为空字符串，返回值为null）。

1: select instr('helloworld','l',2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
2: select instr('helloworld','l',3,2) from dual;  --返回结果：4    也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
3: select instr('helloworld','l',4,2) from dual;  --返回结果：9    也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
4: select instr('helloworld','l',-1,1) from dual;  --返回结果：9    也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
5: select instr('helloworld','l',-2,2) from dual;  --返回结果：4    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
6: select instr('helloworld','l',2,3) from dual;  --返回结果：9    也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
7: select instr('helloworld','l',-2,3) from dual; --返回结果：3    也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置

关于instr可参考：https://www.cnblogs.com/itzhoucong/p/11647496.html