Select count (tc) ONTRACT_ID TOTALNUM,
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:

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:

1, use less subquery
2, according to the execution plan optimization, such as the index

CodePudding user response:

This is a typical bad SQL, nesting child query too much
Other related tables are built on index for the contract number, by the 1/f, that kind of form, the original index method doesn't work,

CodePudding user response:

The

refer to the original poster jnloverll response:

select count (tc) ONTRACT_ID TOTALNUM,
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!

The subquery is too much, and it is the sort of conditions less query, basic is no optimization of space, if really need optimization, see if there are any repeat of SQL, use with structure, perhaps can reduce a bit of logic to read

CodePudding user response:

Oracle11g basically no exists after the statement of optimization has been useless, so much the exists don't get headaches from all that,

CodePudding user response:

Did you see the execution plan a table access for many times, have a with the as

CodePudding user response:

You this code looks logic chaos, are unlikely to be helpful and indexed, Suggestions for the not exists in the table with as first, avoid scan times