Select e.i d_edr_apply_base_info as "idEdrApplyBaseInfo,"
E.p olicy_no as "policyNo,"
E.e ndorse_apply_no as "endorseApplyNo,"
E.e ndorse_no as "endorseNo,"
E.a pply_date as "applyDate,"
To_char (e.a ctual_premium_change, 'fm9999999999990.00) as "actualPremiumChange,"
To_char (b.t otal_insured_amount, 'fm9999999999990.00) as "totalInsuredAmount,"
E.d ocument_status as "documentStatus,"
E.i nput_by as "inputBy,"
E.d epartment_code as "departmentCode,"
To_char (e.v ersion_stamp, 'yyyy - mm - dd hh24: mi: ss. Ff6') as "versionStamp,"
Anderson nsured_name as "insuredName,"
E.i s_before_payfee as "isBeforePayfee,"
Anderson nvoice_no as "invoiceNo",
A.c onveyance_no as "conveyanceNo
"The from edr_apply_base_info e,
Ply_abstract_info a,
Ply_base_info b
Where e.p olicy_no=Amy polumbo olicy_no
And e.p olicy_no=p. olicy_no
And (e.d ocument_status (in '01', '02', '03,' 04, '05,' 06, '07 and' 08 ', '15', '16', '17', '18', 'the', '))
And Amy polumbo roduct_class='09'
And e.a pply_date & gt;=cast (to_date (' 2016-12-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') as the date)

And e.a pply_date & lt;=cast (to_date (' 2017-01-22 23:59:59 ', '- dd yyyy - mm hh24: mi: ss') as the date)

The order by e.a pply_date desc

Edr_apply_base_info has 50 million data, the table has 300 million ply_base_info table data, ply_abstract_info table also has about 50 million data
Policy_no fields and apply_date fields have index

Remove the query condition
And (e.d ocument_status (in '01', '02', '03,' 04, '05,' 06, '07 and' 08 ', '15', '16', '17', '18', 'the', '))
And Amy polumbo roduct_class='09'
0.4 seconds can query out
If you add on the two conditions need more than 5 minutes, a great god please help to see see!

CodePudding user response:

Do not add these two conditions, showing the query out of all the records, and see how long,

CodePudding user response:

See e.d ocument_status and Amy polumbo roduct_class distribution in each table, try to replace in with exist or not exist

And (e.d ocument_status (in '01', '02', '03,' 04, '05,' 06, '07 and' 08 ', '15', '16', '17', '18', 'the', '))
And Amy polumbo roduct_class='09'

In addition, rewritten to try this way:

 
The select... 
The from 
(select... 
The from ply_abstract_info aa 
Where aa. Product_class='09' 
) a 
The join 

(select... 
The from edr_apply_base_info ee 
Where 
And (ee. Document_status in (' 01 ', '02', '03,' 04, '05,' 06, '07 and' 08 ', '15', '16', '17', '18', 'the', ')) 

And ee. Apply_date & gt;=cast (to_date (' 2016-12-23 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') as the date) 

And ee. Apply_date & lt;=cast (to_date (' 2017-01-22 23:59:59 ', '- dd yyyy - mm hh24: mi: ss') as the date) 
) e 
On e.p olicy_no=Amy polumbo olicy_no 

The join ply_base_info b 
On e.p olicy_no=p. olicy_no 

CodePudding user response:

Look at the execution plan, the difference in where

CodePudding user response:

Amy polumbo roduct_class is character data, create indexes can improve the efficiency of part, is ah,

Edr_apply_base_info should be in accordance with the time partition table, because you only get the data of one day,
E.d ocument_status (in '01', '02', '03,' 04, '05,' 06, '07' and '08', '15', '16', '17', '18', 'the', ') this is inefficient, and no optimization statement of the space, can only look to see how much data to determine the day; The best under the condition of the data in the temporary table, then correlation,

CodePudding user response:

LZ can stick execution plans to come out to see