NVL (sum (S.S PLITAMOUNT), 0) TOTALMONEY,
NVL (sum (S.S PLITNUM), 0) TOTALAMOUNT
The from PS_CTT_CONTRACT T, PS_CTT_CONTRACTDETAIL S
Where tc ONTRACT_ID=S.C ONTRACT_ID
And tc ONTRACTENDSTATE='0'
And ((T.R UNSTATE in (' 5 ', '10') and
(the exists (select *
The from PS_AA_AVAILBILL A
Where A.C ONTRACT_ID=tc ONTRACT_ID
And a. ILL_STATE in (' 1 ', '2', '3', '4', '10', '11)) and
(not the exists (select biggest ONTRACT_ID
The from PS_EP_TREASURYPAY B
Where biggest ONTRACT_ID=tc ONTRACT_ID) and
Not the exists
(select Arthur c. ONTRACT_ID
The from PS_EP_PAY C
Where Arthur c. ONTRACT_ID=tc ONTRACT_ID) or the exists
(select biggest ONTRACT_ID
The from PS_EP_TREASURYPAY B
Where biggest ONTRACT_ID=tc ONTRACT_ID
And p. AYSTATE in (' 00 ', '01', '3')
Union all
The select Arthur c. ONTRACT_ID
The from PS_EP_PAY C
Where Arthur c. ONTRACT_ID=tc ONTRACT_ID
And C.P AYSTATE in (' 00 ', '01', '3'))))) or
(T.R UNSTATE (in '6', '7', '9') and not the exists
(select biggest ONTRACT_ID
The from PS_EP_TREASURYPAY B
Where biggest ONTRACT_ID=tc ONTRACT_ID
And p. AYSTATE in (' 00 ', '01', '3')) and not the exists
(select Arthur c. ONTRACT_ID
The from PS_EP_PAY C
Where Arthur c. ONTRACT_ID=tc ONTRACT_ID
And C.P AYSTATE in (' 00 ', '01', '3'))))
And T.D ISTRICTCODE='00';
A large amount of data, the SQL has been optimized, and the efficiency of slow, and had the following index
The CREATE INDEX emeb_PS_EP_TREASURYPAY_fast on PS_EP_TREASURYPAY (PAYSTATE, CONTRACT_ID);
Or bad to use, and give a great god see optimization idea,, thank you!
CodePudding user response:
The execution plan
CONTRACT_ID in each table is the only value or there will be a repeat?
CodePudding user response:
There is something wrong with the index was built, the correct should be:
The CREATE INDEX EMEB_PS_EP_TREASURYPAY_FAST ON PS_EP_TREASURYPAY (CONTRACT_ID, PAYSTATE);
CodePudding user response:
The exists with too much, not clear script logic, no optimization,CodePudding user response:
Optimization can not only see the statement, need to pay attention to the related table data, field selectivity, etc.Plus the execution plan
It is best to give this information, statements just appearance