select q.id, --ID
q.wfid, --工作流ID
q.slbh, --受理编号
to_char(q.hdsj, 'yyyy-MM-dd HH24:mi:ss') as hdsj, --回单时间
q.ywhm, --业务号码
q.ywlx, --业务类型
to_char(q.ydsj, 'yyyy-MM-dd HH24:mi:ss') as ydsj, --约定时间
q.hdrymc --回单人员名称
from PPMS_T_WFORM_HIS_QUERY q --归档工单查询表
where q.job_wgid in
(select t.orgid
from aclorgtreeinfo t
start with t.orgid = '00000000000000000000000000000001'
connect by prior t.orgid = t.parentorgid) and
q.ydsj > to_date('2008-03-01', 'YYYY-MM-DD')
union
select p.id, --ID
p.wfid, --工作流ID
p.slbh, --受理编号
to_char(p.Jobfinish_Time, 'yyyy-MM-dd HH24:mi:ss') as hdsj, --回单时间
p.ywhm, --业务号码
p.ywlx, --业务类型
to_char(p.book_time, 'yyyy-MM-dd HH24:mi:ss') as ydsj, --预约时间
p.hdrymc --回单人员名称
from PPMS_T_WFORM_CURRENT p
where p.status not in ('11') and
p.job_wgid in
(select t.orgid
from aclorgtreeinfo t
start with t.orgid = '00000000000000000000000000000001'
connect by prior t.orgid = t.parentorgid) and
p.book_time > to_date('2008-03-01', 'YYYY-MM-DD')我要查询orgid='00000000000000000000000000000001'阶层 下面阶层所有的数据
我写的这个查询速度好慢。
能不能帮我优化一下
q.wfid, --工作流ID
q.slbh, --受理编号
to_char(q.hdsj, 'yyyy-MM-dd HH24:mi:ss') as hdsj, --回单时间
q.ywhm, --业务号码
q.ywlx, --业务类型
to_char(q.ydsj, 'yyyy-MM-dd HH24:mi:ss') as ydsj, --约定时间
q.hdrymc --回单人员名称
from PPMS_T_WFORM_HIS_QUERY q --归档工单查询表
where exist
(
select '1' from
(q.job_wgid in
(select t.orgid
from aclorgtreeinfo t
start with t.orgid = '00000000000000000000000000000001'
connect by prior t.orgid = t.parentorgid) and
q.ydsj > to_date('2008-03-01', 'YYYY-MM-DD') a where a.orgid=q.job_wgid )
union
select p.id, --ID
p.wfid, --工作流ID
p.slbh, --受理编号
to_char(p.Jobfinish_Time, 'yyyy-MM-dd HH24:mi:ss') as hdsj, --回单时间
p.ywhm, --业务号码
p.ywlx, --业务类型
to_char(p.book_time, 'yyyy-MM-dd HH24:mi:ss') as ydsj, --预约时间
p.hdrymc --回单人员名称
from PPMS_T_WFORM_CURRENT p
where p.status not in ('11') and
exist
(
select '1' from
(q.job_wgid in
(select t.orgid
from aclorgtreeinfo t
start with t.orgid = '00000000000000000000000000000001'
connect by prior t.orgid = t.parentorgid) and
q.ydsj > to_date('2008-03-01', 'YYYY-MM-DD') a where a.orgid=q.job_wgid ) and
p.book_time > to_date('2008-03-01', 'YYYY-MM-DD')
把IN的改成exist看看
这个可以直接p.status <>'11'的啊另外看下这个SQL的执行计划,看看主要费时在哪里,是不是做的全表扫描
一张是历史表,一张是在线表。在线表里面会有归档的数据。现在就是要查归档的数据。
(select t.orgid
from aclorgtreeinfo t
start with t.orgid = '00000000000000000000000000000001'
connect by prior t.orgid = t.parentorgid)这个语句导致的
先set autotrace on
然后再执行你的语句,然后把执行计划的结果贴出来