oracle sql语句优化
一开始写的sql
select (select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '01'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name1,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '02'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name2,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '03'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name3,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '04'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name4,
uu.user_id,
ud.user_name,
su.system_id,
sd.chinese_name system_name,
ug.ug_name,
uu.d_flag auth_status,
uu.created_date auth_created_date,
br.branch_code,
bi.branch_name ope_branch_name,
br.HR_STATUS,
pol.descr sl_line_name
from branch_info bi,
(select a.branch_code, um_id, HR_STATUS, emplid
from oa_dept_branch a,
(select c.deptid,
b.um_id,
decode(c.is_valid, 'Y', '在职', 'N', '离职') HR_STATUS,
b.emplid
from ps_oa_ad_user b,
ps_oa_job c
where b.emplid = c.emplid
and c.empl_rcd = 0
and c.effdt = (select max(effdt)
from ps_oa_job j1
where j1.emplid = c.emplid
and j1.empl_rcd = c.empl_rcd
and j1.is_valid_flag = c.is_valid_flag
and j1.effdt <= sysdate)
and c.effseq = (select max(effseq)
from ps_oa_job j2
where j2.emplid = c.emplid
and j2.empl_rcd = c.empl_rcd
and j2.is_valid_flag = c.is_valid_flag
and j2.effdt = c.effdt)
and c.business_unit = b.business_unit
and c.is_valid_flag = 'Y'
and b.is_valid_flag = 'Y') d
where a.deptid = d.deptid) br,
um_ug_user uu,
um_user_def ud,
um_system_ug su,
um_system_def sd,
um_ug_def ug,
pub_user_branch_oper_privs ubop,
PS_OA_EMPL_LINE poel,
ps_oa_line pol
where bi.branch_code = br.branch_code
and uu.user_id = ud.user_id
and uu.user_id = br.um_id
and uu.d_flag = 'N'
and ud.d_flag = 'N'
and su.ug_id = uu.ug_id
and su.system_id = sd.system_id
and su.d_flag = 'N'
and sd.d_flag = 'N'
and ug.ug_id = uu.ug_id
and ug.d_flag = 'N'
and ubop.um_user_id = uu.user_id
and ubop.valid_flag = 'Y'
and ubop.is_valid = 'Y'
and bi.branch_code = ubop.branch_code
and br.emplid = poel.emplid
and poel.sl_line = pol.sl_line
and poel.is_valid = 'Y'
and pol.is_valid = 'Y';
跑出来的时间可能需要8s左右，后面逐个表内联去分析原因，发现不会有重复数据，只是因为
有些列没有展示出来而已，所以最后的数据不可避免地要进行去重操作，
以下是分析过程
{field: "branchName1", title: "总公司", width: 15, align: "left"}, 1
{field: "branchName2", title: "分公司", width: 15, align: "left"}, 2
{field: "branchName3", title: "三级机构", width: 15, align: "left"}, 3
{field: "branchName4", title: "四级机构", width: 15, align: "left"}, 4
{field: "hrStatus", title: "人员状态", width: 10, align: "left"}, 5
{field: "userId", title: "用户ID", width: 10, align: "left"}, 6 0.3s 加上这个id的时候开始偶尔要2s,内连没法优化
没有重复数据,只是ug_name不一样而已
{field: "ugName", title: "权限名称", width: 15, align: "left"}, 7 uu表 这个直接在uu表拿,不需要再关联um_ug_def表
{field: "authStatus", title: "权限状态", width: 10, align: "left"}, 8
{field: "authCreatedDateShow", title: "开通时间", width: 10, align: "left"}, 9
{field: "userName", title: "用户姓名", width: 10, align: "left"}, 10 0.3s -> 0.7s
{field: "systemName", title: "系统名称", width: 15, align: "left"}, 11 0.7s -> 0.9s
{field: "opeBranchName", title: "操作机构", width: 15, align: "left"}, 12 平均1.几s 可以接受
{field: "slLineName", title: "人员条线", width: 10, align: "left"} 13 直接8s往上 !!!!!!!
最后查到原因在slLineName所在的PS_OA_EMPL_LINE表上，数据大概60w左右，
内联之后会变得很慢，以下是优化后的sql
主要是把slLineName的获取单独再拿出来，并且用上了over partition by 函数
用于去重，因为单个员工可以有很多个条线的变更过程，只要取离当前时间最近的一条即可
获取slLineName的方式值得借鉴，下次写的时候可借鉴，只在select的时候
去关联需要的表就好，不要全部放一块去内联，这样表一多就会变得比较慢，
并且也不好去修改，可读性不太好
前提是只取单行数据时这样去写，否则不行
优化后的 sql
select (select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '01'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name1,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '02'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name2,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '03'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name3,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '04'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name4,
uu.user_id,
ud.user_name,
su.system_id,
sd.chinese_name system_name,
uu.ug_name,
decode(uu.d_flag, 'Y', '无效', 'N', '有效') auth_status,
uu.created_date auth_created_date,
br.branch_code,
bi.branch_name ope_branch_name,
br.HR_STATUS,
(
select pol.descr from
(select t.* from ( select poel.*,row_number() over (partition by emplid order by effdt desc) rn from PS_OA_EMPL_LINE poel) t
where t.rn = 1) poel,ps_oa_line pol
where br.emplid = poel.emplid
and poel.sl_line = pol.sl_line
and poel.is_valid = 'Y'
and pol.is_valid = 'Y'
and rownum = 1
) sl_line_name
from branch_info bi,
(select a.branch_code, um_id, HR_STATUS, emplid
from oa_dept_branch a,
(select c.deptid,
b.um_id,
decode(c.is_valid, 'Y', '在职', 'N', '离职') HR_STATUS,
b.emplid
from ps_oa_ad_user b,
ps_oa_job c
where b.emplid = c.emplid
and c.empl_rcd = 0
and c.effdt = (select max(effdt)
from ps_oa_job j1
where j1.emplid = c.emplid
and j1.empl_rcd = c.empl_rcd
and j1.is_valid_flag = c.is_valid_flag
and j1.effdt <= sysdate)
and c.effseq = (select max(effseq)
from ps_oa_job j2
where j2.emplid = c.emplid
and j2.empl_rcd = c.empl_rcd
and j2.is_valid_flag = c.is_valid_flag
and j2.effdt = c.effdt)
and c.business_unit = b.business_unit
and c.is_valid_flag = 'Y'
and b.is_valid_flag = 'Y') d
where a.deptid = d.deptid) br,
um_ug_user uu,
um_user_def ud,
um_system_ug su,
um_system_def sd,
pub_user_branch_oper_privs ubop
where bi.branch_code = br.branch_code
and uu.user_id = ud.user_id
and uu.user_id = br.um_id
and uu.d_flag = 'N'
and ud.d_flag = 'N'
and su.ug_id = uu.ug_id
and su.system_id = sd.system_id
and su.d_flag = 'N'
and sd.d_flag = 'N'
and ubop.um_user_id = uu.user_id
and ubop.valid_flag = 'Y'
and ubop.is_valid = 'Y'
and bi.branch_code = ubop.branch_code
and bi.is_valid = 'Y';
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '01'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name1,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '02'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name2,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '03'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name3,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '04'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name4,
uu.user_id,
ud.user_name,
su.system_id,
sd.chinese_name system_name,
uu.ug_name,
decode(uu.d_flag, 'Y', '无效', 'N', '有效') auth_status,
uu.created_date auth_created_date,
br.branch_code,
bi.branch_name ope_branch_name,
br.HR_STATUS,
(
select pol.descr from
(select t.* from ( select poel.*,row_number() over (partition by emplid order by effdt desc) rn from PS_OA_EMPL_LINE poel) t
where t.rn = 1) poel,ps_oa_line pol
where br.emplid = poel.emplid
and poel.sl_line = pol.sl_line
and poel.is_valid = 'Y'
and pol.is_valid = 'Y'
and rownum = 1
) sl_line_name
from branch_info bi,
(select a.branch_code, um_id, HR_STATUS, emplid
from oa_dept_branch a,
(select c.deptid,
b.um_id,
decode(c.is_valid, 'Y', '在职', 'N', '离职') HR_STATUS,
b.emplid
from ps_oa_ad_user b,
ps_oa_job c
where b.emplid = c.emplid
and c.empl_rcd = 0
and c.effdt = (select max(effdt)
from ps_oa_job j1
where j1.emplid = c.emplid
and j1.empl_rcd = c.empl_rcd
and j1.is_valid_flag = c.is_valid_flag
and j1.effdt <= sysdate)
and c.effseq = (select max(effseq)
from ps_oa_job j2
where j2.emplid = c.emplid
and j2.empl_rcd = c.empl_rcd
and j2.is_valid_flag = c.is_valid_flag
and j2.effdt = c.effdt)
and c.business_unit = b.business_unit
and c.is_valid_flag = 'Y'
and b.is_valid_flag = 'Y') d
where a.deptid = d.deptid) br,
um_ug_user uu,
um_user_def ud,
um_system_ug su,
um_system_def sd,
pub_user_branch_oper_privs ubop
where bi.branch_code = br.branch_code
and uu.user_id = ud.user_id
and uu.user_id = br.um_id
and uu.d_flag = 'N'
and ud.d_flag = 'N'
and su.ug_id = uu.ug_id
and su.system_id = sd.system_id
and su.d_flag = 'N'
and sd.d_flag = 'N'
and ubop.um_user_id = uu.user_id
and ubop.valid_flag = 'Y'
and ubop.is_valid = 'Y'
and bi.branch_code = ubop.branch_code
and bi.is_valid = 'Y';
优化后跑完需要0.7s左右，
数据量不大，distinct和group by方式去重都需要3s，
不知道是不是我方式有问题，窗口函数去重竟然需要8s，直接弃用，
rowid去重的话由于最终的表是一个联合视图，根本用不了
所以去重后的final sql
只要加上distinct关键字即可
这个是mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sinolife.um.authList.mapper.AuthListMapper">
<resultMap id="authListMap" type="com.sinolife.um.authList.domain.AuthList">
<result column="branch_name1" property="branchName1" jdbcType="VARCHAR"/>
<result column="branch_name2" property="branchName2" jdbcType="VARCHAR"/>
<result column="branch_name3" property="branchName3" jdbcType="VARCHAR"/>
<result column="branch_name4" property="branchName4" jdbcType="VARCHAR"/>
<result column="user_id" property="userId" jdbcType="VARCHAR"/>
<result column="user_name" property="userName" jdbcType="VARCHAR"/>
<result column="system_id" property="systemId" jdbcType="VARCHAR"/>
<result column="system_name" property="systemName" jdbcType="VARCHAR"/>
<result column="ug_name" property="ugName" jdbcType="VARCHAR"/>
<result column="auth_status" property="authStatus" jdbcType="VARCHAR"/>
<result column="auth_created_date" property="authCreatedDate" jdbcType="TIMESTAMP"/>
<result column="ope_branch_name" property="opeBranchName" jdbcType="VARCHAR"/>
<result column="HR_STATUS" property="hrStatus" jdbcType="VARCHAR"/>
<result column="sl_line_name" property="slLineName" jdbcType="VARCHAR"/>
</resultMap>
<!-- 查询存量权限清单 -->
<sql id="selectAuthListSql">
select distinct (select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '01'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name1,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '02'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name2,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '03'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name3,
(select t.branch_name
from branch_info t
where t.is_valid = 'Y'
and t.branch_level = '04'
start with t.branch_code = bi.branch_code
connect by t.branch_code = prior t.parent_branch) branch_name4,
uu.user_id,
ud.user_name,
su.system_id,
sd.chinese_name system_name,
uu.ug_name,
decode(uu.d_flag, 'Y', '无效', 'N', '有效') auth_status,
uu.created_date auth_created_date,
br.branch_code,
bi.branch_name ope_branch_name,
br.HR_STATUS,
(
select pol.descr
from (select t.*
from (select poel.*, row_number() over (partition by emplid order by effdt desc) rn
from PS_OA_EMPL_LINE poel) t
where t.rn = 1) poel,
ps_oa_line pol
where br.emplid = poel.emplid
and poel.sl_line = pol.sl_line
and poel.is_valid = 'Y'
and pol.is_valid = 'Y'
and rownum = 1
) sl_line_name
from
(select bi.branch_code，bi.is_valid,bi.branch_name
from branch_info bi
start with bi.branch_code = #{branchCode}
connect by prior bi.branch_code = bi.parent_branch) bi,
(select a.branch_code, um_id, HR_STATUS, emplid
from oa_dept_branch a,
(select c.deptid,
b.um_id,
decode(c.is_valid, 'Y', '在职', 'N', '离职') HR_STATUS,
b.emplid
from ps_oa_ad_user b,
ps_oa_job c
where b.emplid = c.emplid
and c.empl_rcd = 0
and c.effdt = (select max(effdt)
from ps_oa_job j1
where j1.emplid = c.emplid
and j1.empl_rcd = c.empl_rcd
and j1.is_valid_flag = c.is_valid_flag
and j1.effdt <![CDATA[ <= ]]> sysdate)
and c.effseq = (select max(effseq)
from ps_oa_job j2
where j2.emplid = c.emplid
and j2.empl_rcd = c.empl_rcd
and j2.is_valid_flag = c.is_valid_flag
and j2.effdt = c.effdt)
and c.business_unit = b.business_unit
and c.is_valid_flag = 'Y'
and b.is_valid_flag = 'Y') d
where a.deptid = d.deptid) br,
um_ug_user uu,
um_user_def ud,
um_system_ug su,
um_system_def sd,
pub_user_branch_oper_privs ubop
where bi.branch_code = br.branch_code
and uu.user_id = ud.user_id
and uu.user_id = br.um_id
and uu.d_flag = 'N'
and ud.d_flag = 'N'
and su.ug_id = uu.ug_id
and su.system_id = sd.system_id
and su.d_flag = 'N'
and sd.d_flag = 'N'
and ubop.um_user_id = uu.user_id
and ubop.valid_flag = 'Y'
and ubop.is_valid = 'Y'
and bi.branch_code = ubop.branch_code
and bi.is_valid = 'Y'
<if test="systemIds !=null and systemIds.size != 0">
and su.system_id in
<foreach collection="systemIds" item="item" open="(" separator="," close=")">
#{item,jdbcType=VARCHAR}
</foreach>
</if>
<if test="startDate != null and startDate != ''">
and uu.created_date <![CDATA[>= ]]> to_date(#{startDate,jdbcType=VARCHAR}, 'yyyy-MM-dd')
</if>
<if test="endDate != null and endDate != ''">
and uu.created_date <![CDATA[< ]]> to_date(#{endDate,jdbcType=VARCHAR}, 'yyyy-MM-dd') + 1
</if>
order by user_id
</sql>
<select id="getAuthListCount" resultType="int" parameterType="com.sinolife.um.authList.domain.Params">
select count(1) from (<include refid="selectAuthListSql"/>)
</select>
<select id="getAuthList" resultMap="authListMap"
parameterType="com.sinolife.um.authList.domain.Params">
SELECT * FROM ( select rownum r, union_tb.* from (
<include refid="selectAuthListSql"/>) union_tb )
<if test="exportFlag == null or exportFlag == ''">
<![CDATA[ WHERE r > #{startIndex,jdbcType=NUMERIC}
AND r <= #{startIndex,jdbcType=NUMERIC} + #{pageSize,jdbcType=NUMERIC}
]]>
</if>
</select>
<select id="getBranchTree" resultType="com.sinolife.um.authList.domain.BranchInfo">
select t.branch_code id, t.branch_name text, nvl(t.parent_branch,'null') parent, t.branch_level
from branch_info t
where t.is_valid = 'Y'
start with t.branch_code = '86'
connect by prior t.branch_code = t.parent_branch
</select>
</mapper>
浙公网安备 33010602011771号