oracle 3张300万数据表连表查询，不想建分区表，已经建了索引，查询时间太长，如何优化。

[lyric520123]

select decc,objtype,objname,objid,usrid,timesheetid,timetypename,hour1,houradd1,flag1,aflag1,hour2,houradd2,flag2,aflag2,hour3,houradd3,flag3,aflag3,hour4,houradd4,flag4,
aflag4,hour5,houradd5,flag5,aflag5,hour6,houradd6,flag6,aflag6,hour7,houradd7,flag7,aflag7,hour0,houradd0
from (select '1' decc,
0 objtype,
'开发一部' objname,
pb.type objid,
0 usrid,
0 timesheetid,
'' timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
0 flag1,
0 aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
0 flag2,
0 aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
0 flag3,
0 aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
0 flag4,
0 aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
0 flag5,
0 aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
0 flag6,
0 aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
0 flag7,
0 aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in (select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by pb.type
union all
select '11' decc,
0 objtype,
ou.username objname,
pb.type objid,
wt.userid usrid,
0 timesheetid,
'' timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
0 flag1,
0 aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
0 flag2,
0 aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
0 flag3,
0 aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
0 flag4,
0 aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
0 flag5,
0 aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
0 flag6,
0 aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
0 flag7,
0 aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in(select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by pb.type, ou.username, wt.userid
union all
select '111' decc,
0 objtype,
tb.taskname objname,
pb.type objid,
wt.userid usrid,
wt.timesheetid,
cd.paraname timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
sum(case when timesheetdetailid = 1 then wtd.submitstate else 0 end) flag1,
sum(case when timesheetdetailid = 1 then wtd.approvalstate else 0 end) aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
sum(case when timesheetdetailid = 2 then wtd.submitstate else 0 end) flag2,
sum(case when timesheetdetailid = 2 then wtd.approvalstate else 0 end) aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
sum(case when timesheetdetailid = 3 then wtd.submitstate else 0 end) flag3,
sum(case when timesheetdetailid = 3 then wtd.approvalstate else 0 end) aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
sum(case when timesheetdetailid = 4 then wtd.submitstate else 0 end) flag4,
sum(case when timesheetdetailid = 4 then wtd.approvalstate else 0 end) aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
sum(case when timesheetdetailid = 5 then wtd.submitstate else 0 end) flag5,
sum(case when timesheetdetailid = 5 then wtd.approvalstate else 0 end) aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
sum(case when timesheetdetailid = 6 then wtd.submitstate else 0 end) flag6,
sum(case when timesheetdetailid = 6 then wtd.approvalstate else 0 end) aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
sum(case when timesheetdetailid = 7 then wtd.submitstate else 0 end) flag7,
sum(case when timesheetdetailid = 7 then wtd.approvalstate else 0 end) aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join tk_baseinfo tb
on wt.taskid = tb.taskid
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join cfg_dictionarypara cd
on cd.paratypename = 'TK_TASKTYPE'
and cd.delflag = 0
and wt.tasktype = cd.paravalue
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in (select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by tb.taskname,
pb.type,
wt.userid,
wt.timesheetid,
cd.paraname,
wt.tasktype) obj
order by objid desc, usrid, timesheetid;
合计工时，wk_timesheet ,wk_timesheetdetail,tk_baseinfo这三张张表都是300万以上的数据 ，查询耗时30s左右，大家有没有方法优化 一下 ，不建表分区情况下。

[liu志坚]

这么长的sql.... 做个10046事件，分析耗时主要在哪，然后针对性分析吧。

[zcs_zzh]

1、三个SQL用UNION ALL合并起来，每个SQL分别优化，检查性能； 2、外关联要去掉，没有用处； 3、wk_timesheet表上要有索引，timestartdate列应该是索引的第一列；

[lyric520123]

1、三个SQL用UNION ALL合并起来，每个SQL分别优化，检查性能； 2、外关联要去掉，没有用处； 3、wk_timesheet表上要有索引，timestartdate列应该是索引的第一列； 第1，3都弄了，第2条外关联要去掉是外关联改成内联接？

[zcs_zzh]

从SQL写法上看，大查询（三个UNION ALL合并的SQL，不是子查询）有外关联和没有外关联的查询结果是相同的，因此不需要外关联，你也可以验证下。

[桃花岛黄岛主]

这和SQL的写法关系不大，建不建分区关系也不大，最有效的办法 就是用建一个聚簇，用三个表聚簇吧