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 desc
edr_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分鐘以上，請各位大神幫忙看看！
uj5u.com熱心網友回復：
不加這兩個條件，展示出查詢出所有記錄，看看多長時間。uj5u.com熱心網友回復：
看看e.document_status 和a.product_class 在各自表中的分布情況， 試試將 in 換成exist 或 not existand (e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19',''))
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
uj5u.com熱心網友回復：
看看執行計劃，二者的差異在哪里uj5u.com熱心網友回復：
a.product_class是字符型資料，創建索引能提高部分效率，聊勝于無啊。edr_apply_base_info 應該是按照時間磁區的表，因為你只取一天的資料，
e.document_status in ('01','02','03','04','05','06','07','08','15','16','17','18','19','') 這個是效率低下，又沒有優化空間的陳述句，只能看查詢當天的資料多少來判斷；最好把該條件下的資料放入臨時表，再進行關聯。
uj5u.com熱心網友回復：
LZ能不能貼執行計劃出來看看
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/106205.html
標籤：開發
上一篇：oracle語言翻譯
下一篇：oracle 相關子查詢問題