[Wamr]

ЗАПРОС №1

SELECT /*+ FIRST_ROWS */ A.*, B.* 
FROM CUSTINVOICE4PAYMJOUR_RU A,CUSTINVOICE4PAYMSALESLINK_RU B 
WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,3)=NLS_LOWER('sth')) 
AND ((SUBSTR(NLS_LOWER(B.DATAAREAID),1,3)=NLS_LOWER('sth')) 
AND ((((SUBSTR(NLS_LOWER(A.SALESID),1,20)=SUBSTR(NLS_LOWER(B.SALESID),1,20)) 
AND (SUBSTR(NLS_LOWER(A.INVOICE4PAYMID),1,20)=SUBSTR(NLS_LOWER(B.INVOICE4PAYMID),1,20))) 
AND (A.INVOICE4PAYMDATE=B.INVOICE4PAYMDATE)) 
AND (SUBSTR(NLS_LOWER(B.ORIGSALESID),1,20)=NLS_LOWER('             0099556')))) 
ORDER BY SUBSTR(NLS_LOWER(A.DATAAREAID),1,3),SUBSTR(NLS_LOWER(A.INVOICE4PAYMID),1,20),A.INVOICE4PAYMDATE

План до (медленный)

6  SELECT STATEMENT
   5  NESTED LOOPS
     2  BMSSA.CUSTINVOICE4PAYMJOUR_RU TABLE ACCESS [BY INDEX ROWID]
       1  BMSSA.I_16003INVOICE4PAYMIDX INDEX [RANGE SCAN]
     4  BMSSA.CUSTINVOICE4PAYMSALESLINK_RU TABLE ACCESS [BY INDEX ROWID]
       3  BMSSA.I_16392INVOICE4PAYMIDX INDEX [RANGE SCAN]

план после (быстрый)

7  SELECT STATEMENT
   6  SORT [ORDER BY]
     5  NESTED LOOPS
       2  BMSSA.CUSTINVOICE4PAYMSALESLINK_RU TABLE ACCESS [BY INDEX ROWID]
         1  BMSSA.I_16392ORIGSALESIDX INDEX [RANGE SCAN]
       4  BMSSA.CUSTINVOICE4PAYMJOUR_RU TABLE ACCESS [BY INDEX ROWID]
         3  BMSSA.I_16003SALESIDX INDEX [RANGE SCAN]

ЗАПРОС №2

SELECT /*+ FIRST_ROWS */ A.*, B.* 
FROM CUSTINVOICE4PAYMJOUR_RU A,CUSTINVOICE4PAYMSALESLINK_RU B 
WHERE (SUBSTR(NLS_LOWER(A.DATAAREAID),1,3)=NLS_LOWER('sth')) 
AND ((SUBSTR(NLS_LOWER(B.DATAAREAID),1,3)=NLS_LOWER('sth')) 
AND ((((SUBSTR(NLS_LOWER(A.SALESID),1,20)=SUBSTR(NLS_LOWER(B.SALESID),1,20)) 
AND (SUBSTR(NLS_LOWER(A.INVOICE4PAYMID),1,20)=SUBSTR(NLS_LOWER(B.INVOICE4PAYMID),1,20))) 
AND (A.INVOICE4PAYMDATE=B.INVOICE4PAYMDATE)))) 
ORDER BY SUBSTR(NLS_LOWER(A.DATAAREAID),1,3),SUBSTR(NLS_LOWER(A.INVOICE4PAYMID),1,20),A.INVOICE4PAYMDATE

план до (быстрый)

6  SELECT STATEMENT
   5  NESTED LOOPS
     2  BMSSA.CUSTINVOICE4PAYMJOUR_RU TABLE ACCESS [BY INDEX ROWID]
       1  BMSSA.I_16003INVOICE4PAYMIDX INDEX [RANGE SCAN]
     4  BMSSA.CUSTINVOICE4PAYMSALESLINK_RU TABLE ACCESS [BY INDEX ROWID]
       3  BMSSA.I_16392INVOICE4PAYMIDX INDEX [RANGE SCAN]

план после (медленный)

7  SELECT STATEMENT
   6  SORT [ORDER BY]
     5  NESTED LOOPS
       2  BMSSA.CUSTINVOICE4PAYMJOUR_RU TABLE ACCESS [BY INDEX ROWID]
         1  BMSSA.I_16003RECID INDEX [RANGE SCAN]
       4  BMSSA.CUSTINVOICE4PAYMSALESLINK_RU TABLE ACCESS [BY INDEX ROWID]
         3  BMSSA.I_16392INVOICE4PAYMIDX INDEX [RANGE SCAN]