0 Replies Latest reply on Nov 20, 2018 6:25 PM by 3758553

    Mixed SQL performance with compression on Exadata

    3758553

      Hi,

       

      I'm in the middle of learning and testing table compression in one of our test DW databases.  The results have been mixed in term of performance

      for a select statement that I'm testing against OLTP and EHCC tables.  I flush the buffer cache before each select.  As you can see from the below results,  the only

      consistent results seem to be query high while others are not.  For considerations, I'm looking to incorporate advanced compression

      at the tablespace level for active DML schemas and query high for the largest schema that has minimal DML.

       

      Can anyone please help or point out what would cause such mixed results?  Thanks in advance.

       

      Exadata type = X5-2

      Rack type = Quarter

      RDBMS version: 12.1.0.2

       

                               nocompressed      advanced      query low      query high      arch low      arch high

      table size(gb)           61                      36                    20               11                     9                7

       

      Run 1:

      elapse time(mm:ss) 17:40                17:41             15:27             12:15           15:53            12:00

      Run 2:               

      elapse time(mm:ss) 14:42                 13:42             23:13              13:00           21:32           14:56

      Run 3:               

      elapse time(mm:ss) 13:40                  20:46             18:02             13:23           15:20            12:47

       

      I run the same SQL against each table with different table name for that compression type. The below sql is for table EX_D_JDE_F0911_A (advanced).

       

      SELECT

      SOURCE_TBL.KCO,

      SOURCE_TBL.DCT,

      SOURCE_TBL.DOC,

      SOURCE_TBL.DGJ,

      SOURCE_TBL.JELN,

      SOURCE_TBL.EXTL,

      SOURCE_TBL.POST,

      SOURCE_TBL.ICU,

      SOURCE_TBL.ICUT,

      SOURCE_TBL.DICJ,

      SOURCE_TBL.DSYJ,

      SOURCE_TBL.TICU,

      SOURCE_TBL.CO,

      SOURCE_TBL.ANI,

      SOURCE_TBL.AM,

      SOURCE_TBL.AID,

      SOURCE_TBL.MCU,

      SOURCE_TBL.OBJ,

      SOURCE_TBL.SUB,

      SOURCE_TBL.SBL,

      SOURCE_TBL.SBLT,

      SOURCE_TBL.LT,

      SOURCE_TBL.PN,

      SOURCE_TBL.CTRY,

      SOURCE_TBL.FY,

      SOURCE_TBL.FQ,

      SOURCE_TBL.CRCD,

      SOURCE_TBL.CRR,

      SOURCE_TBL.HCRR,

      SOURCE_TBL.HDGJ,

      SOURCE_TBL.AA,

      SOURCE_TBL.U,

      SOURCE_TBL.UM,

      SOURCE_TBL.GLC,

      SOURCE_TBL.RE,

      SOURCE_TBL.EXA,

      SOURCE_TBL.EXR,

      SOURCE_TBL.R1,

      SOURCE_TBL.R2,

      SOURCE_TBL.R3,

      SOURCE_TBL.SFX,

      SOURCE_TBL.ODOC,

      SOURCE_TBL.ODCT,

      SOURCE_TBL.OSFX,

      SOURCE_TBL.PKCO,

      SOURCE_TBL.OKCO,

      SOURCE_TBL.PDCT,

      SOURCE_TBL.AN8,

      SOURCE_TBL.CN,

      SOURCE_TBL.DKJ,

      SOURCE_TBL.DKC,

      SOURCE_TBL.ASID,

      SOURCE_TBL.BRE,

      SOURCE_TBL.RCND,

      SOURCE_TBL.SUMM,

      SOURCE_TBL.PRGE,

      SOURCE_TBL.TNN,

      SOURCE_TBL.ALT1,

      SOURCE_TBL.ALT2,

      SOURCE_TBL.ALT3,

      SOURCE_TBL.ALT4,

      SOURCE_TBL.ALT5,

      SOURCE_TBL.ALT6,

      SOURCE_TBL.ALT7,

      SOURCE_TBL.ALT8,

      SOURCE_TBL.ALT9,

      SOURCE_TBL.ALT0,

      SOURCE_TBL.ALTT,

      SOURCE_TBL.ALTU,

      SOURCE_TBL.ALTV,

      SOURCE_TBL.ALTW,

      SOURCE_TBL.ALTX,

      SOURCE_TBL.ALTZ,

      SOURCE_TBL.DLNA,

      SOURCE_TBL.CFF1,

      SOURCE_TBL.CFF2,

      SOURCE_TBL.ASM,

      SOURCE_TBL.BC,

      SOURCE_TBL.VINV,

      SOURCE_TBL.IVD,

      SOURCE_TBL.WR01,

      SOURCE_TBL.PO,

      SOURCE_TBL.PSFX,

      SOURCE_TBL.DCTO,

      SOURCE_TBL.LNID,

      SOURCE_TBL.WY,

      SOURCE_TBL.WN,

      SOURCE_TBL.FNLP,

      SOURCE_TBL.OPSQ,

      SOURCE_TBL.JBCD,

      SOURCE_TBL.JBST,

      SOURCE_TBL.HMCU,

      SOURCE_TBL.DOI,

      SOURCE_TBL.ALID,

      SOURCE_TBL.ALTY,

      SOURCE_TBL.DSVJ,

      SOURCE_TBL.TORG,

      SOURCE_TBL.REG#,

      SOURCE_TBL.PYID,

      SOURCE_TBL.USERS,

      SOURCE_TBL.PID,

      SOURCE_TBL.JOBN,

      SOURCE_TBL.UPMJ,

      SOURCE_TBL.UPMT,

      SOURCE_TBL.ABR1,

      SOURCE_TBL.ABR2,

      SOURCE_TBL.ABR3,

      SOURCE_TBL.ABR4,

      SOURCE_TBL.ABT1,

      SOURCE_TBL.ABT2,

      SOURCE_TBL.ABT3,

      SOURCE_TBL.ABT4,

      SOURCE_TBL.ITM,

      SOURCE_TBL.PM01,

      SOURCE_TBL.PM02,

      SOURCE_TBL.PM03,

      SOURCE_TBL.PM04,

      SOURCE_TBL.PM05,

      SOURCE_TBL.PM06,

      SOURCE_TBL.PM07,

      SOURCE_TBL.PM08,

      SOURCE_TBL.PM09,

      SOURCE_TBL.PM10,

      SOURCE_TBL.BCRC,

      SOURCE_TBL.CRRM,

      SOURCE_TBL.PRGF,

      SOURCE_TBL.TXA1,

      SOURCE_TBL.EXR1,

      SOURCE_TBL.TXITM,

      SOURCE_TBL.ACTB,

      SOURCE_TBL.GPF1,

      SOURCE_TBL.ACR,

      SOURCE_TBL.DLNID,

      SOURCE_TBL.CKNU,

      SOURCE_TBL.BUPC,

      SOURCE_TBL.AHBU,

      SOURCE_TBL.EPGC,

      SOURCE_TBL.JPGC,

      SOURCE_TBL.RC5,

      SOURCE_TBL.SFXE,

      SOURCE_TBL.OFM,

      SOURCE_TBL.HASH_FULL_RECORD,

      CASE WHEN (MASTERTABLE.DCT IS NULL AND MASTERTABLE.DOC IS NULL AND MASTERTABLE.KCO IS NULL AND MASTERTABLE.DGJ IS NULL AND MASTERTABLE.JELN IS NULL AND MASTERTABLE.LT IS NULL AND MASTERTABLE.EXTL IS NULL) THEN 'INSERT' ELSE 'UPDATE' END AS CDC_OPERATION FROM

      (SELECT

      EX_D_JDE_F0911_A.KCO,

      EX_D_JDE_F0911_A.DCT,

      EX_D_JDE_F0911_A.DOC,

      EX_D_JDE_F0911_A.DGJ,

      EX_D_JDE_F0911_A.JELN,

      EX_D_JDE_F0911_A.EXTL,

      EX_D_JDE_F0911_A.POST,

      EX_D_JDE_F0911_A.ICU,

      EX_D_JDE_F0911_A.ICUT,

      EX_D_JDE_F0911_A.DICJ,

      EX_D_JDE_F0911_A.DSYJ,

      EX_D_JDE_F0911_A.TICU,

      EX_D_JDE_F0911_A.CO,

      EX_D_JDE_F0911_A.ANI,

      EX_D_JDE_F0911_A.AM,

      EX_D_JDE_F0911_A.AID,

      EX_D_JDE_F0911_A.MCU,

      EX_D_JDE_F0911_A.OBJ,

      EX_D_JDE_F0911_A.SUB,

      EX_D_JDE_F0911_A.SBL,

      EX_D_JDE_F0911_A.SBLT,

      EX_D_JDE_F0911_A.LT,

      EX_D_JDE_F0911_A.PN,

      EX_D_JDE_F0911_A.CTRY,

      EX_D_JDE_F0911_A.FY,

      EX_D_JDE_F0911_A.FQ,

      EX_D_JDE_F0911_A.CRCD,

      EX_D_JDE_F0911_A.CRR,

      EX_D_JDE_F0911_A.HCRR,

      EX_D_JDE_F0911_A.HDGJ,

      EX_D_JDE_F0911_A.AA,

      EX_D_JDE_F0911_A.U,

      EX_D_JDE_F0911_A.UM,

      EX_D_JDE_F0911_A.GLC,

      EX_D_JDE_F0911_A.RE,

      EX_D_JDE_F0911_A.EXA,

      EX_D_JDE_F0911_A.EXR,

      EX_D_JDE_F0911_A.R1,

      EX_D_JDE_F0911_A.R2,

      EX_D_JDE_F0911_A.R3,

      EX_D_JDE_F0911_A.SFX,

      EX_D_JDE_F0911_A.ODOC,

      EX_D_JDE_F0911_A.ODCT,

      EX_D_JDE_F0911_A.OSFX,

      EX_D_JDE_F0911_A.PKCO,

      EX_D_JDE_F0911_A.OKCO,

      EX_D_JDE_F0911_A.PDCT,

      EX_D_JDE_F0911_A.AN8,

      EX_D_JDE_F0911_A.CN,

      EX_D_JDE_F0911_A.DKJ,

      EX_D_JDE_F0911_A.DKC,

      EX_D_JDE_F0911_A.ASID,

      EX_D_JDE_F0911_A.BRE,

      EX_D_JDE_F0911_A.RCND,

      EX_D_JDE_F0911_A.SUMM,

      EX_D_JDE_F0911_A.PRGE,

      EX_D_JDE_F0911_A.TNN,

      EX_D_JDE_F0911_A.ALT1,

      EX_D_JDE_F0911_A.ALT2,

      EX_D_JDE_F0911_A.ALT3,

      EX_D_JDE_F0911_A.ALT4,

      EX_D_JDE_F0911_A.ALT5,

      EX_D_JDE_F0911_A.ALT6,

      EX_D_JDE_F0911_A.ALT7,

      EX_D_JDE_F0911_A.ALT8,

      EX_D_JDE_F0911_A.ALT9,

      EX_D_JDE_F0911_A.ALT0,

      EX_D_JDE_F0911_A.ALTT,

      EX_D_JDE_F0911_A.ALTU,

      EX_D_JDE_F0911_A.ALTV,

      EX_D_JDE_F0911_A.ALTW,

      EX_D_JDE_F0911_A.ALTX,

      EX_D_JDE_F0911_A.ALTZ,

      EX_D_JDE_F0911_A.DLNA,

      EX_D_JDE_F0911_A.CFF1,

      EX_D_JDE_F0911_A.CFF2,

      EX_D_JDE_F0911_A.ASM,

      EX_D_JDE_F0911_A.BC,

      EX_D_JDE_F0911_A.VINV,

      EX_D_JDE_F0911_A.IVD,

      EX_D_JDE_F0911_A.WR01,

      EX_D_JDE_F0911_A.PO,

      EX_D_JDE_F0911_A.PSFX,

      EX_D_JDE_F0911_A.DCTO,

      EX_D_JDE_F0911_A.LNID,

      EX_D_JDE_F0911_A.WY,

      EX_D_JDE_F0911_A.WN,

      EX_D_JDE_F0911_A.FNLP,

      EX_D_JDE_F0911_A.OPSQ,

      EX_D_JDE_F0911_A.JBCD,

      EX_D_JDE_F0911_A.JBST,

      EX_D_JDE_F0911_A.HMCU,

      EX_D_JDE_F0911_A.DOI,

      EX_D_JDE_F0911_A.ALID,

      EX_D_JDE_F0911_A.ALTY,

      EX_D_JDE_F0911_A.DSVJ,

      EX_D_JDE_F0911_A.TORG,

      EX_D_JDE_F0911_A.REG#,

      EX_D_JDE_F0911_A.PYID,

      EX_D_JDE_F0911_A.USERS,

      EX_D_JDE_F0911_A.PID,

      EX_D_JDE_F0911_A.JOBN,

      EX_D_JDE_F0911_A.UPMJ,

      EX_D_JDE_F0911_A.UPMT,

      EX_D_JDE_F0911_A.ABR1,

      EX_D_JDE_F0911_A.ABR2,

      EX_D_JDE_F0911_A.ABR3,

      EX_D_JDE_F0911_A.ABR4,

      EX_D_JDE_F0911_A.ABT1,

      EX_D_JDE_F0911_A.ABT2,

      EX_D_JDE_F0911_A.ABT3,

      EX_D_JDE_F0911_A.ABT4,

      EX_D_JDE_F0911_A.ITM,

      EX_D_JDE_F0911_A.PM01,

      EX_D_JDE_F0911_A.PM02,

      EX_D_JDE_F0911_A.PM03,

      EX_D_JDE_F0911_A.PM04,

      EX_D_JDE_F0911_A.PM05,

      EX_D_JDE_F0911_A.PM06,

      EX_D_JDE_F0911_A.PM07,

      EX_D_JDE_F0911_A.PM08,

      EX_D_JDE_F0911_A.PM09,

      EX_D_JDE_F0911_A.PM10,

      EX_D_JDE_F0911_A.BCRC,

      EX_D_JDE_F0911_A.CRRM,

      EX_D_JDE_F0911_A.PRGF,

      EX_D_JDE_F0911_A.TXA1,

      EX_D_JDE_F0911_A.EXR1,

      EX_D_JDE_F0911_A.TXITM,

      EX_D_JDE_F0911_A.ACTB,

      EX_D_JDE_F0911_A.GPF1,

      EX_D_JDE_F0911_A.ACR,

      EX_D_JDE_F0911_A.DLNID,

      EX_D_JDE_F0911_A.CKNU,

      EX_D_JDE_F0911_A.BUPC,

      EX_D_JDE_F0911_A.AHBU,

      EX_D_JDE_F0911_A.EPGC,

      EX_D_JDE_F0911_A.JPGC,

      EX_D_JDE_F0911_A.RC5,

      EX_D_JDE_F0911_A.SFXE,

      EX_D_JDE_F0911_A.OFM,

      STANDARD_HASH (

      EX_D_JDE_F0911_A.KCO||'|'||

      EX_D_JDE_F0911_A.DCT||'|'||

      EX_D_JDE_F0911_A.DOC||'|'||

      EX_D_JDE_F0911_A.DGJ||'|'||

      EX_D_JDE_F0911_A.JELN||'|'||

      EX_D_JDE_F0911_A.EXTL||'|'||

      EX_D_JDE_F0911_A.POST||'|'||

      EX_D_JDE_F0911_A.ICU||'|'||

      EX_D_JDE_F0911_A.ICUT||'|'||

      EX_D_JDE_F0911_A.DICJ||'|'||

      EX_D_JDE_F0911_A.DSYJ||'|'||

      EX_D_JDE_F0911_A.TICU||'|'||

      EX_D_JDE_F0911_A.CO||'|'||

      EX_D_JDE_F0911_A.ANI||'|'||

      EX_D_JDE_F0911_A.AM||'|'||

      EX_D_JDE_F0911_A.AID||'|'||

      EX_D_JDE_F0911_A.MCU||'|'||

      EX_D_JDE_F0911_A.OBJ||'|'||

      EX_D_JDE_F0911_A.SUB||'|'||

      EX_D_JDE_F0911_A.SBL||'|'||

      EX_D_JDE_F0911_A.SBLT||'|'||

      EX_D_JDE_F0911_A.LT||'|'||

      EX_D_JDE_F0911_A.PN||'|'||

      EX_D_JDE_F0911_A.CTRY||'|'||

      EX_D_JDE_F0911_A.FY||'|'||

      EX_D_JDE_F0911_A.FQ||'|'||

      EX_D_JDE_F0911_A.CRCD||'|'||

      EX_D_JDE_F0911_A.CRR||'|'||

      EX_D_JDE_F0911_A.HCRR||'|'||

      EX_D_JDE_F0911_A.HDGJ||'|'||

      EX_D_JDE_F0911_A.AA||'|'||

      EX_D_JDE_F0911_A.U||'|'||

      EX_D_JDE_F0911_A.UM||'|'||

      EX_D_JDE_F0911_A.GLC||'|'||

      EX_D_JDE_F0911_A.RE||'|'||

      EX_D_JDE_F0911_A.EXA||'|'||

      EX_D_JDE_F0911_A.EXR||'|'||

      EX_D_JDE_F0911_A.R1||'|'||

      EX_D_JDE_F0911_A.R2||'|'||

      EX_D_JDE_F0911_A.R3||'|'||

      EX_D_JDE_F0911_A.SFX||'|'||

      EX_D_JDE_F0911_A.ODOC||'|'||

      EX_D_JDE_F0911_A.ODCT||'|'||

      EX_D_JDE_F0911_A.OSFX||'|'||

      EX_D_JDE_F0911_A.PKCO||'|'||

      EX_D_JDE_F0911_A.OKCO||'|'||

      EX_D_JDE_F0911_A.PDCT||'|'||

      EX_D_JDE_F0911_A.AN8||'|'||

      EX_D_JDE_F0911_A.CN||'|'||

      EX_D_JDE_F0911_A.DKJ||'|'||

      EX_D_JDE_F0911_A.DKC||'|'||

      EX_D_JDE_F0911_A.ASID||'|'||

      EX_D_JDE_F0911_A.BRE||'|'||

      EX_D_JDE_F0911_A.RCND||'|'||

      EX_D_JDE_F0911_A.SUMM||'|'||

      EX_D_JDE_F0911_A.PRGE||'|'||

      EX_D_JDE_F0911_A.TNN||'|'||

      EX_D_JDE_F0911_A.ALT1||'|'||

      EX_D_JDE_F0911_A.ALT2||'|'||

      EX_D_JDE_F0911_A.ALT3||'|'||

      EX_D_JDE_F0911_A.ALT4||'|'||

      EX_D_JDE_F0911_A.ALT5||'|'||

      EX_D_JDE_F0911_A.ALT6||'|'||

      EX_D_JDE_F0911_A.ALT7||'|'||

      EX_D_JDE_F0911_A.ALT8||'|'||

      EX_D_JDE_F0911_A.ALT9||'|'||

      EX_D_JDE_F0911_A.ALT0||'|'||

      EX_D_JDE_F0911_A.ALTT||'|'||

      EX_D_JDE_F0911_A.ALTU||'|'||

      EX_D_JDE_F0911_A.ALTV||'|'||

      EX_D_JDE_F0911_A.ALTW||'|'||

      EX_D_JDE_F0911_A.ALTX||'|'||

      EX_D_JDE_F0911_A.ALTZ||'|'||

      EX_D_JDE_F0911_A.DLNA||'|'||

      EX_D_JDE_F0911_A.CFF1||'|'||

      EX_D_JDE_F0911_A.CFF2||'|'||

      EX_D_JDE_F0911_A.ASM||'|'||

      EX_D_JDE_F0911_A.BC||'|'||

      EX_D_JDE_F0911_A.VINV||'|'||

      EX_D_JDE_F0911_A.IVD||'|'||

      EX_D_JDE_F0911_A.WR01||'|'||

      EX_D_JDE_F0911_A.PO||'|'||

      EX_D_JDE_F0911_A.PSFX||'|'||

      EX_D_JDE_F0911_A.DCTO||'|'||

      EX_D_JDE_F0911_A.LNID||'|'||

      EX_D_JDE_F0911_A.WY||'|'||

      EX_D_JDE_F0911_A.WN||'|'||

      EX_D_JDE_F0911_A.FNLP||'|'||

      EX_D_JDE_F0911_A.OPSQ||'|'||

      EX_D_JDE_F0911_A.JBCD||'|'||

      EX_D_JDE_F0911_A.JBST||'|'||

      EX_D_JDE_F0911_A.HMCU||'|'||

      EX_D_JDE_F0911_A.DOI||'|'||

      EX_D_JDE_F0911_A.ALID||'|'||

      EX_D_JDE_F0911_A.ALTY||'|'||

      EX_D_JDE_F0911_A.DSVJ||'|'||

      EX_D_JDE_F0911_A.TORG||'|'||

      EX_D_JDE_F0911_A.REG#||'|'||

      EX_D_JDE_F0911_A.PYID||'|'||

      EX_D_JDE_F0911_A.USERS||'|'||

      EX_D_JDE_F0911_A.PID||'|'||

      EX_D_JDE_F0911_A.JOBN||'|'||

      EX_D_JDE_F0911_A.ABR1||'|'||

      EX_D_JDE_F0911_A.ABR2||'|'||

      EX_D_JDE_F0911_A.ABR3||'|'||

      EX_D_JDE_F0911_A.ABR4||'|'||

      EX_D_JDE_F0911_A.ABT1||'|'||

      EX_D_JDE_F0911_A.ABT2||'|'||

      EX_D_JDE_F0911_A.ABT3||'|'||

      EX_D_JDE_F0911_A.ABT4||'|'||

      EX_D_JDE_F0911_A.ITM||'|'||

      EX_D_JDE_F0911_A.PM01||'|'||

      EX_D_JDE_F0911_A.PM02||'|'||

      EX_D_JDE_F0911_A.PM03||'|'||

      EX_D_JDE_F0911_A.PM04||'|'||

      EX_D_JDE_F0911_A.PM05||'|'||

      EX_D_JDE_F0911_A.PM06||'|'||

      EX_D_JDE_F0911_A.PM07||'|'||

      EX_D_JDE_F0911_A.PM08||'|'||

      EX_D_JDE_F0911_A.PM09||'|'||

      EX_D_JDE_F0911_A.PM10||'|'||

      EX_D_JDE_F0911_A.BCRC||'|'||

      EX_D_JDE_F0911_A.CRRM||'|'||

      EX_D_JDE_F0911_A.PRGF||'|'||

      EX_D_JDE_F0911_A.TXA1||'|'||

      EX_D_JDE_F0911_A.EXR1||'|'||

      EX_D_JDE_F0911_A.TXITM||'|'||

      EX_D_JDE_F0911_A.ACTB||'|'||

      EX_D_JDE_F0911_A.GPF1||'|'||

      EX_D_JDE_F0911_A.ACR||'|'||

      EX_D_JDE_F0911_A.DLNID||'|'||

      EX_D_JDE_F0911_A.CKNU||'|'||

      EX_D_JDE_F0911_A.BUPC||'|'||

      EX_D_JDE_F0911_A.AHBU||'|'||

      EX_D_JDE_F0911_A.EPGC||'|'||

      EX_D_JDE_F0911_A.JPGC||'|'||

      EX_D_JDE_F0911_A.RC5||'|'||

      EX_D_JDE_F0911_A.SFXE||'|'||

      EX_D_JDE_F0911_A.OFM

      , 'MD5') as HASH_FULL_RECORD

      FROM EX_D_JDE_F0911_A ) SOURCE_TBL

      LEFT JOIN

      (SELECT EX_JDE_F0911_A.DCT,EX_JDE_F0911_A.DOC, EX_JDE_F0911_A.KCO,EX_JDE_F0911_A.DGJ, EX_JDE_F0911_A.JELN,EX_JDE_F0911_A.LT, EX_JDE_F0911_A.EXTL, EX_JDE_F0911_A.HASH_FULL_RECORD FROM EX_JDE_F0911_A

      INNER JOIN

      (SELECT EX_JDE_F0911_A.DCT, EX_JDE_F0911_A.DOC, EX_JDE_F0911_A.KCO, EX_JDE_F0911_A.DGJ, EX_JDE_F0911_A.JELN, EX_JDE_F0911_A.LT, EX_JDE_F0911_A.EXTL, MAX(EX_JDE_F0911_A.LOAD_DATETIME) AS MAX_LOAD_DATETIME FROM EX_JDE_F0911_A GROUP BY EX_JDE_F0911_A.DCT,EX_JDE_F0911_A.DOC, EX_JDE_F0911_A.KCO,EX_JDE_F0911_A.DGJ, EX_JDE_F0911_A.JELN,EX_JDE_F0911_A.LT, EX_JDE_F0911_A.EXTL) MAX_MT

      ON (MAX_MT.DCT = EX_JDE_F0911_A.DCT AND MAX_MT.DOC = EX_JDE_F0911_A.DOC AND MAX_MT.KCO =

      EX_JDE_F0911_A.KCO AND NVL(MAX_MT.DGJ, to_date('19000101','YYYYMMDD')) = NVL(EX_JDE_F0911_A.DGJ, to_date('19000101','YYYYMMDD')) AND MAX_MT.JELN = EX_JDE_F0911_A.JELN AND MAX_MT.LT = EX_JDE_F0911_A.LT AND NVL(MAX_MT.EXTL, 'XX') = NVL(EX_JDE_F0911_A.EXTL, 'XX')) AND EX_JDE_F0911_A.LOAD_DATETIME = MAX_MT.MAX_LOAD_DATETIME) MASTERTABLE

      ON SOURCE_TBL.DCT = MASTERTABLE.DCT AND SOURCE_TBL.DOC = MASTERTABLE.DOC AND SOURCE_TBL.KCO = MASTERTABLE.KCO AND NVL(SOURCE_TBL.DGJ, to_date('19000101','YYYYMMDD')) = NVL(MASTERTABLE.DGJ, to_date('19000101','YYYYMMDD')) AND SOURCE_TBL.JELN = MASTERTABLE.JELN AND SOURCE_TBL.LT = MASTERTABLE.LT AND NVL(SOURCE_TBL.EXTL, 'XX') = NVL(MASTERTABLE.EXTL, 'XX')

      WHERE (MASTERTABLE.DCT IS NULL AND MASTERTABLE.DOC IS NULL AND MASTERTABLE.KCO IS NULL AND MASTERTABLE.DGJ IS NULL AND MASTERTABLE.JELN IS NULL AND MASTERTABLE.LT IS NULL AND MASTERTABLE.EXTL IS NULL) OR SOURCE_TBL.HASH_FULL_RECORD <> MASTERTABLE.HASH_FULL_RECORD;