sql语句如下:select /*+index(t,PK_aidszh_sgra_adult_info_his)*/
f.id_record as id,
f.zonecode as postcode,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 2) || '000000') as prov,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 4) || '0000') as city,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 6) || '00') as district,
f.orgcode as site_code,
(select orgname from aidszh_sgra_organise where orgcode = f.ORGCODE) as site_name,
t.antin as card_code,
t.pid as ptid,
t.patient_name as name,
t.id as ic,
f.addrcode as addr_county,
(select ZONENAME from zonecode where zonecode = f.addrcode) as addr_name,
f.addr1 as addr_town,
f.addr as addr_door,
f.flwno as visit_n,
(case
when f.is_flw = 1 and f.clinic_treatment <> 3 then
'1'
when f.end_cause = 1 then
'2'
when f.end_cause = 2 then
'3'
when f.end_cause = 3 then
'4 '
when (f.clinic_treatment = 3 and f.is_flw = 1) then
'5'
else
'1'
end) as flw_status,
f.CD4 as f5cd4,
f.CD8 as f5cd8,
to_char(f.DT_CD8BLOOD, 'yyyy-mm-dd') as f5cdt,
f.VIRULN as f5vl, f.OTHER_CLINIC as fother
from aidszh_sgra_adult_info_his t,
aidszh_sgra_adult_newstatus_h h,
aidszh_sgra_adult_flw_his f
where t.card_id = f.card_id
and t.FLAG = 2
and t.card_id = h.card_id
and f.ZONECODE like '210726%'
and f.TM_CREATE < to_date('2010-06-01', 'yyyy-mm-dd')
其中,aidszh_sgra_adult_info_his 数据量8w,
aidszh_sgra_adult_newstatus_h 数据量8w,aidszh_sgra_adult_flw_his 数据量80w目前执行时间为3.5s,执行计划如下:我发现以上执行计划表AIDSZH_SGRA_ADULT_NEWSTATUS_H走全表扫描,于是建了一个索引,在看执行计划,的确走了索引,但效率并没有提高,还是3.5s。请sql高手帮忙优化一下,控制在2s以内!
f.id_record as id,
f.zonecode as postcode,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 2) || '000000') as prov,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 4) || '0000') as city,
(select cnname
from zonecode
where zonecode = substr(f.zonecode, 1, 6) || '00') as district,
f.orgcode as site_code,
(select orgname from aidszh_sgra_organise where orgcode = f.ORGCODE) as site_name,
t.antin as card_code,
t.pid as ptid,
t.patient_name as name,
t.id as ic,
f.addrcode as addr_county,
(select ZONENAME from zonecode where zonecode = f.addrcode) as addr_name,
f.addr1 as addr_town,
f.addr as addr_door,
f.flwno as visit_n,
(case
when f.is_flw = 1 and f.clinic_treatment <> 3 then
'1'
when f.end_cause = 1 then
'2'
when f.end_cause = 2 then
'3'
when f.end_cause = 3 then
'4 '
when (f.clinic_treatment = 3 and f.is_flw = 1) then
'5'
else
'1'
end) as flw_status,
f.CD4 as f5cd4,
f.CD8 as f5cd8,
to_char(f.DT_CD8BLOOD, 'yyyy-mm-dd') as f5cdt,
f.VIRULN as f5vl, f.OTHER_CLINIC as fother
from aidszh_sgra_adult_info_his t,
aidszh_sgra_adult_newstatus_h h,
aidszh_sgra_adult_flw_his f
where t.card_id = f.card_id
and t.FLAG = 2
and t.card_id = h.card_id
and f.ZONECODE like '210726%'
and f.TM_CREATE < to_date('2010-06-01', 'yyyy-mm-dd')
其中,aidszh_sgra_adult_info_his 数据量8w,
aidszh_sgra_adult_newstatus_h 数据量8w,aidszh_sgra_adult_flw_his 数据量80w目前执行时间为3.5s,执行计划如下:我发现以上执行计划表AIDSZH_SGRA_ADULT_NEWSTATUS_H走全表扫描,于是建了一个索引,在看执行计划,的确走了索引,但效率并没有提高,还是3.5s。请sql高手帮忙优化一下,控制在2s以内!
1、select字句先不用特定数据,直接用*
2、aidszh_sgra_adult_flw_his表的ZONECODE、TM_CREATE和FLAG等是否有索引
3、aidszh_sgra_adult_info_his和aidszh_sgra_adult_newstatus_h 2个8W数据的表的card_id要有索引
4、如果上面3个步骤之后不慢,那就是select中的子查询慢;
5、如果还是慢,那就是aidszh_sgra_adult_flw_his建的索引,再从步骤一开始慢慢查看。
2、where语句出了flag没有索引,其他的都有索引。flag一共就两个值没有加索引的必要。
看是否能起到优化的作用
调整后的顺序如下: where t.FLAG = 2
and t.card_id = h.card_id
and t.card_id = f.card_id
and f.ZONECODE like '210726%'
and f.TM_CREATE < to_date('2010-06-01', 'yyyy-mm-dd')
select * from aidszh_sgra_adult_flw_his
where f.ZONECODE like '210726%'
and f.TM_CREATE < to_date('2010-06-01', 'yyyy-mm-dd');要是这都慢,就从这个表优化。
from zonecode
where zonecode = substr(f.zonecode, 1, 2) || '000000') as prov,
这个看起来怎么这么别扭。