The left link with the subquery (for help).

The project had a busy day, feel dizzy, help you to help see next paragraph two SQL

Oracle 10G version

The first section
select ebpoE.zbthnr as zbthnr1,ebpoE.Zqgdh,
        o.zbthnr2,
       ebpoE.ebeln,
       ebpoE.ebelp from t_sap_eban ebpoE
               
       left join (     
       select eban.banfn,eban.bnfpo,decode(nvl(ekpo.ebeln_pre, ''),
                           '',
                           (select eb.zbthnr
                              from t_sap_eban eb
                             where eb.ebeln || '-' || eb.ebelp =
                                   ekpo.konnr || '-' || ekpo.ktpnr
                                    and 
                                    eb.loekz is null  and rownum =1),
                           (select eb.zbthnr
                              from t_sap_eban eb
                             where eb.ebeln || '-' || eb.ebelp =
                                   ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
                                   and 
                                    eb.loekz is null and rownum =1)) as zbthnr2
               from t_sap_eban eban, t_sap_ekpo ekpo
              WHERE nvl(eban.ebeln,'') || '-' ||nvl(eban.ebelp,'') =
                    ekpo.ebeln || '-' || ekpo.ebelp
                    ) o
       on o.banfn||'-'||o.bnfpo = ebpoE.banfn||'-'||ebpoE.bnfpo 
       
 where ebpoE.Ebeln = '4700038450' 


The second section
select ebpoE.zbthnr as zbthnr1,ebpoE.Zqgdh,
       (select decode(nvl(ekpo.ebeln_pre, ''),
                           '',
                           (select nvl(eb.zbthnr, '')
                              from t_sap_eban eb
                             where eb.ebeln || '-' || eb.ebelp =
                                   ekpo.konnr || '-' || ekpo.ktpnr
                                    and
                                    eb.loekz is null  and rownum =1),
                           (select nvl(eb.zbthnr, '')
                              from t_sap_eban eb
                             where eb.ebeln || '-' || eb.ebelp =
                                   ekpo.ebeln_pre || '-' || ekpo.ebelp_pre
                                   and
                                    eb.loekz is null and rownum =1)) as zbthnr2
               from t_sap_eban eban, t_sap_ekpo ekpo
              WHERE eban.ebeln || '-' || eban.ebelp =
                    ekpo.ebeln || '-' || ekpo.ebelp
                    and eban.banfn|| '-' ||eban.bnfpo = ebpoE.banfn|| '-' ||ebpoE.bnfpo)
                    as zbthnr2,
       ebpoE.ebeln,
       ebpoE.ebelp
  from t_sap_eban ebpoE where nvl(ebpoE.Ebeln,'') = '4700038450' 


The two section SQL is in fact a mean field zbthnr2 via the ekpo table related field if there is value, if no value is to perform a logical judgment gets a value as the value

The first section of the SQL is the first value out then left link conditions to obtain this value

The second section of the SQL is zbthnr2 the values of the sub query mode

According to the experience of words should be is the first stage of the SQL execution time is shorter. .

But now this is the case, the first section of SQL without "where ebpoE.Ebeln ='4700038450'", search all the values for 10 seconds
But added "where ebpoE.Ebeln ='4700038450'" words for at least 10 minutes and didn't come out data.

The second section SQL if added "where ebpoE.Ebeln ='4700038450'" query the corresponding value is 20 seconds
If not, at least 10 minutes and didn't come out data.

EBAN in the primary key for eban.banfn, eban.bnfpo and set up eban.ebeln and eban.ebelp index

EKPO in the primary key for eban.ebeln and eban.ebelp


Please do me a favor the prawns

Started by Hugh at November 16, 2016 - 2:11 AM

After adding different conditions, to see what the explain plan

Posted by Liz at November 24, 2016 - 2:43 AM

Add conditions explain plan implementation of Cost becomes small, but very strange is not data query

Posted by Hugh at December 09, 2016 - 3:34 AM

Has been in the query, using the Pl/SQL Developer tool, add a condition has been in query state, but is not to display data

Posted by Hugh at December 15, 2016 - 4:24 AM

Data reading quantity is too large?
Might also explain the plan itself is not right
Reconstruction related index.?

Posted by Liz at December 24, 2016 - 4:48 AM

Look, should be used
eban.banfn|| '-' ||eban.bnfpo = ebpoE.banfn|| '-' ||ebpoE.bnfpo

The matching value to obtain the connection when the primary key, not access the index.. So all changed the eban.banfn = eban.bnfpo and ebpoE.banfn = ebpoE.bnfpo faster


But now the problem is to use count (*) to statistical data seems to be very slow. .

Wonder is it right? The amount of data is too large

Posted by Hugh at December 31, 2016 - 5:14 AM