这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果,而一个需要10多分钟。查看他们的执行计划,发现果然不通。sql1:
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.flag as FLAG,
(select zonename from zonecode where zonecode = t.zonecode) as zonename,
(select orgname
from aidszh_sgra_organise
where orgcode = t.orgcode) as orgname,
t.PID_ORG AS PID_ORG,
t.ORGCODE AS ORGCODE,
t.CARD_CODE AS CARD_CODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
t.PID AS PID,
t.ANTIN AS ANTIN,
t.PATIENT_NAME AS PATIENT_NAME,
t.ID AS ID,
(select name from aidszh_sgra_dd_sex where id = t.SEX) AS SEX,
to_char(t.BIRTHDAY, 'yyyy-mm-dd') AS BIRTHDAY,
(select name
from aidszh_sgra_dd_chargesrc
where id_dic = t.CHARGESRC) AS CHARGESRC,
to_char(t.DT_ANTIVIRUS, 'yyyy-mm-dd') AS DT_ANTIVIRUS,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
to_char(t.DT_REPORT, 'yyyy-mm-dd') AS DT_REPORT,
to_char(t.TM_CREATE, 'yyyy-mm-dd') AS TM_CREATE,
f.cure_org as CURE_ORG,
(decode(f.LAST_CLINIC_TREATMENT,
'3',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_adult_newstatus f,
AIDSZH_SGRA_ADULT_INFO t,
(select /*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_adult_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.dt_last_aduit is not null) g
where t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1 and 1 = 1 and
1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))
ORDER BY T.ANTIN那么它的执行计划为:这条sql只需要几秒就能查询出数据。下面来看这条sql,和上面类似,就换了两张表。
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN它的执行计划为:这个执行时间需要十几分钟。说明:表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系,数据量也很多。我不想大篇幅改变sql的结构,很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有,有时候没有,完全取决于用户的操作。请高手分析这两个sql的异同,并提出第二个sql的优化建议,达到和第一个sql同样的效果。
多谢。
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.flag as FLAG,
(select zonename from zonecode where zonecode = t.zonecode) as zonename,
(select orgname
from aidszh_sgra_organise
where orgcode = t.orgcode) as orgname,
t.PID_ORG AS PID_ORG,
t.ORGCODE AS ORGCODE,
t.CARD_CODE AS CARD_CODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
t.PID AS PID,
t.ANTIN AS ANTIN,
t.PATIENT_NAME AS PATIENT_NAME,
t.ID AS ID,
(select name from aidszh_sgra_dd_sex where id = t.SEX) AS SEX,
to_char(t.BIRTHDAY, 'yyyy-mm-dd') AS BIRTHDAY,
(select name
from aidszh_sgra_dd_chargesrc
where id_dic = t.CHARGESRC) AS CHARGESRC,
to_char(t.DT_ANTIVIRUS, 'yyyy-mm-dd') AS DT_ANTIVIRUS,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
to_char(t.DT_REPORT, 'yyyy-mm-dd') AS DT_REPORT,
to_char(t.TM_CREATE, 'yyyy-mm-dd') AS TM_CREATE,
f.cure_org as CURE_ORG,
(decode(f.LAST_CLINIC_TREATMENT,
'3',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_adult_newstatus f,
AIDSZH_SGRA_ADULT_INFO t,
(select /*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_adult_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.dt_last_aduit is not null) g
where t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1 and 1 = 1 and
1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))
ORDER BY T.ANTIN那么它的执行计划为:这条sql只需要几秒就能查询出数据。下面来看这条sql,和上面类似,就换了两张表。
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN它的执行计划为:这个执行时间需要十几分钟。说明:表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系,数据量也很多。我不想大篇幅改变sql的结构,很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有,有时候没有,完全取决于用户的操作。请高手分析这两个sql的异同,并提出第二个sql的优化建议,达到和第一个sql同样的效果。
多谢。
解决方案 »
- 存储过程问题
- package下的procedure下变量赋值也要提交事务?
- 求教ORA-01772 错误的问题,oci编程
- 随机函数的新问题,报缺少右括号
- drop user 123 cascade 提示ora-01935:missing user or role name
- 关于sqlloader的问题,为什么如果某个字段的值有空的整条记录就导不进去
- 请教个问题!关于汉字查询!
- 这样的存储过程在执行到一半时如果忽然有特发事件如断电,前面已经执行的操作会自动取消吗?
- Oracle的菜菜问题,关于基本概念
- csdn最近怎么啦???顺便散分!
- oracle的图片插入问题
- oracle10g查询表问题
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where
exists (select 1 from aidszh_mst_patients b where b.id_no=t.id)
t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and t.pid in (select s.cure_no from aidszh_mst_hivchech s)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where
exists (select 1 from aidszh_mst_patients b where b.id_no=t.id)
and
t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and t.pid in (select s.cure_no from aidszh_mst_hivchech s)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN
1. 可以使用exist试试
2. 另外,如果aidszh_mst_patients和aidszh_mst_hivchech中内容不多的话,可以全写出来,改用or .. or .. or
3. 最后就是试试 inner join的用法了,麻烦一些,也许有提高
select t.*
from AIDSZH_SGRA_ADULT_INFO t,
aidszh_mst_patients b,
aidszh_mst_hivchech s
where t.id = b.id_no
and t.pid = s.cure_no;
from AIDSZH_SGRA_ADULT_INFO t,
aidszh_mst_patients b,
aidszh_mst_hivchech s
where t.id = b.id_no
and t.pid = s.cure_no;
中,AIDSZH_SGRA_ADULT_INFO 的目前数据量很少,也就几百,正式库中会有几十万。aidszh_mst_patients 和aidszh_mst_hivchech 都有十几万的数据。
执行计划如下:
select t.*
from aidszh_mst_patients b,
aidszh_mst_hivchech s,
AIDSZH_SGRA_ADULT_INFO t,
where b.id_no = t.id
and s.cure_no = t.pid;