Home>Question Details


ripudaman -- Thanks for the question regarding "Sql query", version 10.2.0.1

Submitted on 13-May-2008 10:08 Central time zone
Tom's latest followup | Bookmark | Bottom
Last updated 19-May-2008 15:36

You Asked

Sir,

I have joined this team having severe problems with SQL and found one having excessive CpU from AWR reports.
There are many changes that we have suggested to this insert, could you please give your valuable inputs also please.

On a 10G instance is there really a benefit of giving hints.

regards
ripu



INSERT INTO SYNCDIFFDEPENDENCY(SDD2BEFOREDEPENDENCY, SDD2AFTERDEPENDENCY) SELECT /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ MASTERSYNCDIFF.SYNCDIFFID MASTERSYNCDIFFID, DEPENDANTSYNCDIFF.SYNCDIFFID DEPENDANTSYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFF MASTERSYNCDIFF, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 1 AND MASTERSYNCDIFF.SYNCDIFFID = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, -1) UNION ALL SELECT /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ MASTERSYNCDIFF.SYNCDIFFID, DEPENDANTSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFF MASTERSYNCDIFF, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 1 AND MASTERSYNCDIFF.SYNCDIFFID = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK IS NULL AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK IS NULL UNION ALL SELECT /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) INDEX(MasterSyncDiff SYDI_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ MASTERSYNCDIFF.SYNCDIFFID, DEPENDANTSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF MASTERSYNCDIFF, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, -1) UNION ALL SELECT /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) INDEX(MasterSyncDiff SYDI_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ MASTERSYNCDIFF.SYNCDIFFID, DEPENDANTSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF MASTERSYNCDIFF, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 1 AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK IS NULL AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK IS NULL UNION ALL SELECT /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ DEPENDANTSYNCDIFF.SYNCDIFFID, MASTERSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFF MASTERSYNCDIFF, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 2 AND MASTERSYNCDIFF.SYNCDIFFID = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, -1) UNION ALL SELECT /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ DEPENDANTSYNCDIFF.SYNCDIFFID, MASTERSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFF MASTERSYNCDIFF, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 2 AND MASTERSYNCDIFF.SYNCDIFFID = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK IS NULL AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK IS NULL UNION ALL SELECT /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) INDEX(MasterSyncDiff SYDI_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ DEPENDANTSYNCDIFF.SYNCDIFFID, MASTERSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF MASTERSYNCDIFF, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, -1) UNION ALL SELECT /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) INDEX(MasterSyncDiff SYDI_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */ DEPENDANTSYNCDIFF.SYNCDIFFID, MASTERSYNCDIFF.SYNCDIFFID FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFFOBJECT DEPENDANTOBJECT, SYNCDIFF MASTERSYNCDIFF, SYNCDIFF DEPENDANTSYNCDIFF, SYNCDIFFTYPE MASTERSYNCDIFFTYPE, SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE WHERE DEPENDANTSYNCDIFF.SYNCDIFFID = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION = :B2 AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF IS NULL AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1) AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1) AND MASTERSYNCDIFF.SYNCDIFFID != DEPENDANTSYNCDIFF.SYNCDIFFID AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID AND MASTERSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY = 2 AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF = IDLIST.ID AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK IS NULL AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK IS NULL MINUS SELECT SDD2BEFOREDEPENDENCY, SDD2AFTERDEPENDENCY FROM (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))) IDLIST, SYNCDIFFDEPENDENCY WHERE SDD2BEFOREDEPENDENCY = IDLIST.ID OR SDD2AFTERDEPENDENCY = IDLIST.ID

and we said...

hah, I'll just post this for the comments....

I'd lose all of the hints (we are doing what you ask us to do, if that takes a lot of cpu, maybe you should stop asking us to do that...)

I'd frankly be very scared of things like:

NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)

A join where NULLS are equal to one - not much good can come from that.


Realistically - did you expect anyone to be able to say anything about this multipage query?
Reviews    
3 stars great...   May 15, 2008 - 11pm Central time zone
Bookmark | Bottom | Top
Reviewer: Amardeep Sidhu from New Delhi, India
Tom

Great that you managed to decode it...otherwise when i was scrolling it looked like that Matrix text in black n white ;)


4 stars   May 15, 2008 - 11pm Central time zone
Bookmark | Bottom | Top
Reviewer: Aman.... from India
I got lost when the page's scrolling was going on and on and on.....
Aman....


3 stars   May 16, 2008 - 12am Central time zone
Bookmark | Bottom | Top
Reviewer: David Aldridge from NOVA
SQL Developer 1.5's formatting functionality helps ... a bit ...

 INSERT INTO SYNCDIFFDEPENDENCY
  (SDD2BEFOREDEPENDENCY, SDD2AFTERDEPENDENCY
  )
 SELECT
  /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_UPK_I) 
USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */
  MASTERSYNCDIFF.SYNCDIFFID MASTERSYNCDIFFID,
  DEPENDANTSYNCDIFF.SYNCDIFFID DEPENDANTSYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 1
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 1
AND MASTERSYNCDIFF.SYNCDIFFID                         = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK                = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1)    = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, 
-1)

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_OBJ_I) 
USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */
  MASTERSYNCDIFF.SYNCDIFFID,
  DEPENDANTSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 1
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 1
AND MASTERSYNCDIFF.SYNCDIFFID                         = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT             = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK               IS NULL
AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK               IS NULL

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) 
INDEX(MasterSyncDiff SYDI_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, 
DependantSyncDiffType) */
  MASTERSYNCDIFF.SYNCDIFFID,
  DEPENDANTSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 1
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 1
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF           = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK                = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1)    = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, 
-1)

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) 
INDEX(MasterSyncDiff SYDI_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, 
DependantSyncDiffType) */
  MASTERSYNCDIFF.SYNCDIFFID,
  DEPENDANTSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 1
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 1
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF           = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT             = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK               IS NULL
AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK               IS NULL

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_UPK_I) 
USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */
  DEPENDANTSYNCDIFF.SYNCDIFFID,
  MASTERSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 2
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 2
AND MASTERSYNCDIFF.SYNCDIFFID                         = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK                = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1)    = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, 
-1)

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(MasterSyncDiff) USE_NL(DependantObject) INDEX(DependantObject SYOB_OBJ_I) 
USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, DependantSyncDiffType) */
  DEPENDANTSYNCDIFF.SYNCDIFFID,
  MASTERSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 2
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 2
AND MASTERSYNCDIFF.SYNCDIFFID                         = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT             = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK               IS NULL
AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK               IS NULL

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) 
INDEX(MasterSyncDiff SYDI_UPK_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, 
DependantSyncDiffType) */
  DEPENDANTSYNCDIFF.SYNCDIFFID,
  MASTERSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 2
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 2
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF           = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK                = MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT, -1)    = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT, 
-1)

UNION ALL
 
 SELECT
  /*+ ORDERED USE_NL(DependantObject) INDEX(DependantObject SYOB_SYDI_FK_I) USE_NL(MasterSyncDiff) 
INDEX(MasterSyncDiff SYDI_OBJ_I) USE_NL(DependantSyncDiff) USE_HASH(MasterSyncDiffType, 
DependantSyncDiffType) */
  DEPENDANTSYNCDIFF.SYNCDIFFID,
  MASTERSYNCDIFF.SYNCDIFFID
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST                       ,
  SYNCDIFFOBJECT DEPENDANTOBJECT ,
  SYNCDIFF MASTERSYNCDIFF        ,
  SYNCDIFF DEPENDANTSYNCDIFF     ,
  SYNCDIFFTYPE MASTERSYNCDIFFTYPE,
  SYNCDIFFTYPE DEPENDANTSYNCDIFFTYPE
  WHERE DEPENDANTSYNCDIFF.SYNCDIFFID                  = DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2RELATION           = :B2
AND MASTERSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN          = DEPENDANTSYNCDIFF.SYNCDIFF2MANAGEMENTDOMAIN
AND MASTERSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF            IS NULL
AND DEPENDANTSYNCDIFF.SYNCDIFF2NEWERSYNCDIFF         IS NULL
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2UDMCLASS,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYUDMCLASS, -1)
AND NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2SELECTOR,  -1) = 
NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYSELECTOR, -1)
AND MASTERSYNCDIFF.SYNCDIFFID                        != DEPENDANTSYNCDIFF.SYNCDIFFID
AND MASTERSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE              = MASTERSYNCDIFFTYPE.SYNCDIFFTYPEID
AND DEPENDANTSYNCDIFF.SYNCDIFF2SYNCDIFFTYPE           = DEPENDANTSYNCDIFFTYPE.SYNCDIFFTYPEID
AND MASTERSYNCDIFFTYPE.DIRECTIONALITY                 = 2
AND DEPENDANTSYNCDIFFTYPE.DIRECTIONALITY              = 2
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2SYNCDIFF           = IDLIST.ID
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2OBJECT             = MASTERSYNCDIFF.SYNCDIFF2PRIMARYOBJECT
AND DEPENDANTOBJECT.SYNCDIFFOBJECT2UPK               IS NULL
AND MASTERSYNCDIFF.SYNCDIFF2PRIMARYUPK               IS NULL MINUS
 SELECT SDD2BEFOREDEPENDENCY,
  SDD2AFTERDEPENDENCY
   FROM
  (SELECT ID FROM TABLE(CAST(:B1 AS T_SYNC_IDLIST))
  ) IDLIST,
  SYNCDIFFDEPENDENCY
  WHERE SDD2BEFOREDEPENDENCY = IDLIST.ID
OR SDD2AFTERDEPENDENCY       = IDLIST.ID


4 stars Sql query   May 16, 2008 - 4am Central time zone
Bookmark | Bottom | Top
Reviewer: ripudaman nanda from uk
Thanks,  yes it is impossible to give a review. 

2 things that came out were NVL thing and Hint..

Thanks a ton


3 stars please explain more   May 16, 2008 - 6pm Central time zone
Bookmark | Bottom | Top
Reviewer: babloo from minneapolis mn usa
Hi Tom , 
Can you please explain more about this 

"NVL(DEPENDANTOBJECT.SYNCDIFFOBJECT2DIMOBJECT, -1) = NVL(MASTERSYNCDIFF.SYNCDIFF2PRIMARYDIMOBJECT, 
-1) 

A join where NULLS are equal to one - not much good can come from that. 
"

how to handle the cases where columns being joined can be null and null values are to be ignored .


Followup   May 19, 2008 - 3pm Central time zone:

but you are saying nulling values are NOT to be ignored - you are saying "null is equal to null", that just isn't true.

it isn't that you are ignoring nulls, you are saying "nulls are a valid value and null = null" - that is not the right way to have decided to store your information. Apparently, these columns should have had a default value that represents something meaningful to your application - not the value of "we don't know what the heck the value here is"


All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.

About Oracle | Legal Notices and Terms of Use | Privacy Statement