select e.id_edr_apply_base_info as "idEdrApplyBaseInfo",
e.policy_no as "policyNo" ,
e.endorse_apply_no as "endorseApplyNo",
e.endorse_no as "endorseNo",
e.apply_date as "applyDate",
to_char(e.actual_premium_change,'fm9999999999990.00') as "actualPremiumChange",
to_char(b.total_insured_amount,'fm9999999999990.00') as "totalInsuredAmount",
e.document_status as "documentStatus",
e.input_by as "inputBy",
e.department_code as "departmentCode",
to_char(e.version_stamp,'yyyy-mm-dd hh24:mi:ss.ff6') as "versionStamp",
a.insured_name as "insuredName",
e.is_before_payfee as "isBeforePayfee",
a.invoice_no as "invoiceNo",
a.conveyance_no as "conveyanceNo"
from edr_apply_base_info e,
ply_abstract_info a,
ply_base_info b
where e.policy_no = a.policy_no
and e.policy_no = b.policy_no
and (e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
and a.product_class = '09'
and e.apply_date >= cast(to_date('2016-12-23 00:00:00','yyyy-mm-dd hh24:mi:ss') as date)
and e.apply_date <= cast(to_date('2017-01-22 23:59:59','yyyy-mm-dd hh24:mi:ss') as date)
order by e.apply_date descedr_apply_base_info 这个表有5千万数据,ply_base_info表有3亿数据,ply_abstract_info 表大概也有5千万数据
policy_no 字段和apply_date 字段都有索引去掉查询条件
and (e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
and a.product_class = '09'
0.4秒就可以查询出来
如果添加上这两个条件需要5分钟以上,请各位大神帮忙看看!
e.policy_no as "policyNo" ,
e.endorse_apply_no as "endorseApplyNo",
e.endorse_no as "endorseNo",
e.apply_date as "applyDate",
to_char(e.actual_premium_change,'fm9999999999990.00') as "actualPremiumChange",
to_char(b.total_insured_amount,'fm9999999999990.00') as "totalInsuredAmount",
e.document_status as "documentStatus",
e.input_by as "inputBy",
e.department_code as "departmentCode",
to_char(e.version_stamp,'yyyy-mm-dd hh24:mi:ss.ff6') as "versionStamp",
a.insured_name as "insuredName",
e.is_before_payfee as "isBeforePayfee",
a.invoice_no as "invoiceNo",
a.conveyance_no as "conveyanceNo"
from edr_apply_base_info e,
ply_abstract_info a,
ply_base_info b
where e.policy_no = a.policy_no
and e.policy_no = b.policy_no
and (e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
and a.product_class = '09'
and e.apply_date >= cast(to_date('2016-12-23 00:00:00','yyyy-mm-dd hh24:mi:ss') as date)
and e.apply_date <= cast(to_date('2017-01-22 23:59:59','yyyy-mm-dd hh24:mi:ss') as date)
order by e.apply_date descedr_apply_base_info 这个表有5千万数据,ply_base_info表有3亿数据,ply_abstract_info 表大概也有5千万数据
policy_no 字段和apply_date 字段都有索引去掉查询条件
and (e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
and a.product_class = '09'
0.4秒就可以查询出来
如果添加上这两个条件需要5分钟以上,请各位大神帮忙看看!
and a.product_class = '09'另外, 改写成这种方式试试:
select ...
from
(select ...
from ply_abstract_info aa
where aa.product_class = '09'
) a
join
(select ...
from edr_apply_base_info ee
where
and (ee.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
and ee.apply_date >= cast(to_date('2016-12-23 00:00:00','yyyy-mm-dd hh24:mi:ss') as date)
and ee.apply_date <= cast(to_date('2017-01-22 23:59:59','yyyy-mm-dd hh24:mi:ss') as date)
) e
on e.policy_no = a.policy_no join ply_base_info b
on e.policy_no = b.policy_no
e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19','') 这个是效率低下,又没有优化空间的语句,只能看查询当天的数据多少来判断;最好把该条件下的数据放入临时表,再进行关联。