SELECT OBJECTID FROM (SELECT /*+ USE_NL(a asec) USE_HASH(dsec) */ a.OBJECTID /* ArcSDE DIFF_INSERT_RESTORE */ FROM UTARC.A558 a,SDE.state_lineages SL, UTARC.A558 asec, (SELECT /*+ USE_NL(D558) INDEX(D558 D558_PK) */ SDE_DELETES_ROW_ID, DELETED_AT FROM UTARC.D558 WHERE DELETED_AT IN (SELECT /*+ */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = 447670 AND l.lineage_id <= 447706 AND l.lineage_id > 447689)) dsec WHERE a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = 447670 AND SL.lineage_id <= 447706 AND (a.OBJECTID,a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ INDEX(D558 D558_PK) */ SDE_DELETES_ROW_ID,SDE_STATE_ID FROM UTARC.D558 WHERE DELETED_AT IN (SELECT /*+ INDEX(l LINEAGES_PK) */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :state_id1)) AND a.OBJECTID NOT IN (SELECT /*+ HASH_AJ INDEX_DESC(SL LINEAGES_PK) */ DISTINCT OBJECTID FROM SDE.state_lineages SL, UTARC.A558 WHERE SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = 447702 AND SL.lineage_id <= 447705 AND SL.lineage_id > 447689 ) AND a.OBJECTID = asec.OBJECTID AND asec.SDE_STATE_ID IN (SELECT /*+ */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = 447670 AND l.lineage_id <= 447706 AND l.lineage_id > 447689) AND asec.OBJECTID = dsec.SDE_DELETES_ROW_ID(+) AND asec.SDE_STATE_ID >= dsec.DELETED_AT(+) AND dsec.DELETED_AT IS NULL ) V__558 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.15 0.15 0 0 0 0 Fetch 1 135.43 136.15 0 34575917 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 135.59 136.31 0 34575917 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 151 Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER (cr=34575917 pr=0 pw=0 time=136153811 us) 1 HASH JOIN OUTER (cr=34575917 pr=0 pw=0 time=136153806 us) 1 NESTED LOOPS (cr=34575906 pr=0 pw=0 time=136153378 us) 288000 NESTED LOOPS (cr=34287904 pr=0 pw=0 time=135360223 us) 216 MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=985 us) 54 INDEX RANGE SCAN LINEAGES_PK (cr=2 pr=0 pw=0 time=130 us)(object id 743555) 216 BUFFER SORT (cr=2 pr=0 pw=0 time=376 us) 4 SORT UNIQUE (cr=2 pr=0 pw=0 time=53 us) 4 INDEX RANGE SCAN LINEAGES_PK (cr=2 pr=0 pw=0 time=16 us)(object id 743555) 288000 INDEX RANGE SCAN A558_PK (cr=34287900 pr=0 pw=0 time=134781579 us)(object id 748725) 76608 NESTED LOOPS (cr=32292762 pr=0 pw=0 time=121494938 us) 15782962 INDEX RANGE SCAN LINEAGES_PK (cr=726836 pr=0 pw=0 time=18328127 us)(object id 743555) 76608 INDEX UNIQUE SCAN D558_PK (cr=31565926 pr=0 pw=0 time=93429048 us)(object id 752767) 25 NESTED LOOPS (cr=1994440 pr=0 pw=0 time=10860539 us) 1139668 INDEX RANGE SCAN DESCENDING LINEAGES_PK (cr=569846 pr=0 pw=0 time=3356732 us)(object id 743555) 25 INDEX UNIQUE SCAN A558_PK (cr=1424594 pr=0 pw=0 time=5045871 us)(object id 748725) 1 INDEX UNIQUE SCAN A558_PK (cr=288002 pr=0 pw=0 time=1222123 us)(object id 748725) 1 VIEW (cr=11 pr=0 pw=0 time=95 us) 1 NESTED LOOPS SEMI (cr=11 pr=0 pw=0 time=93 us) 14 INDEX RANGE SCAN D558_PK (cr=2 pr=0 pw=0 time=36 us)(object id 752767) 1 INDEX UNIQUE SCAN LINEAGES_PK (cr=9 pr=0 pw=0 time=40 us)(object id 743555) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 latch: cache buffers chains 1 0.00 0.00 SQL*Net message from client 1 0.01 0.01 ******************************************************************************** SELECT OBJECTID FROM (SELECT /*+ USE_NL(a asec) USE_HASH(dsec) */ a.OBJECTID /* ArcSDE DIFF_INSERT_RESTORE */ FROM UTARC.A564 a,SDE.state_lineages SL, UTARC.A564 asec, (SELECT /*+ USE_NL(D564) INDEX(D564 D564_PK) */ SDE_DELETES_ROW_ID, DELETED_AT FROM UTARC.D564 WHERE DELETED_AT IN (SELECT /*+ */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = 447670 AND l.lineage_id <= 447706 AND l.lineage_id > 447689)) dsec WHERE a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = 447670 AND SL.lineage_id <= 447706 AND (a.OBJECTID,a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ INDEX(D564 D564_PK) */ SDE_DELETES_ROW_ID,SDE_STATE_ID FROM UTARC.D564 WHERE DELETED_AT IN (SELECT /*+ INDEX(l LINEAGES_PK) */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name AND l.lineage_id <= :state_id1)) AND a.OBJECTID NOT IN (SELECT /*+ HASH_AJ INDEX_DESC(SL LINEAGES_PK) */ DISTINCT OBJECTID FROM SDE.state_lineages SL, UTARC.A564 WHERE SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = 447702 AND SL.lineage_id <= 447705 AND SL.lineage_id > 447689 ) AND a.OBJECTID = asec.OBJECTID AND asec.SDE_STATE_ID IN (SELECT /*+ */ l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = 447670 AND l.lineage_id <= 447706 AND l.lineage_id > 447689) AND asec.OBJECTID = dsec.SDE_DELETES_ROW_ID(+) AND asec.SDE_STATE_ID >= dsec.DELETED_AT(+) AND dsec.DELETED_AT IS NULL ) V__564 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.14 0.15 0 0 0 0 Fetch 1 108.40 108.91 0 19145975 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 108.56 109.06 0 19145975 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 151 Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER (cr=19145975 pr=0 pw=0 time=108912399 us) 1 HASH JOIN OUTER (cr=19145975 pr=0 pw=0 time=108912396 us) 1 NESTED LOOPS (cr=19145964 pr=0 pw=0 time=108911938 us) 287996 NESTED LOOPS (cr=18857966 pr=0 pw=0 time=107422738 us) 216 MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=988 us) 54 INDEX RANGE SCAN LINEAGES_PK (cr=2 pr=0 pw=0 time=78 us)(object id 743555) 216 BUFFER SORT (cr=2 pr=0 pw=0 time=388 us) 4 SORT UNIQUE (cr=2 pr=0 pw=0 time=50 us) 4 INDEX RANGE SCAN LINEAGES_PK (cr=2 pr=0 pw=0 time=12 us)(object id 743555) 287996 INDEX RANGE SCAN A564_PK (cr=18857962 pr=0 pw=0 time=106845261 us)(object id 748763) 75462 NESTED LOOPS (cr=16862850 pr=0 pw=0 time=94274906 us) 15776171 INDEX RANGE SCAN LINEAGES_PK (cr=724452 pr=0 pw=0 time=18332425 us)(object id 743555) 75462 INDEX UNIQUE SCAN D564_PK (cr=16138398 pr=0 pw=0 time=67712067 us)(object id 752785) 28 NESTED LOOPS (cr=1994410 pr=0 pw=0 time=10869120 us) 1139652 INDEX RANGE SCAN DESCENDING LINEAGES_PK (cr=569838 pr=0 pw=0 time=3380274 us)(object id 743555) 28 INDEX UNIQUE SCAN A564_PK (cr=1424572 pr=0 pw=0 time=5007226 us)(object id 748763) 1 INDEX UNIQUE SCAN A564_PK (cr=287998 pr=0 pw=0 time=1170440 us)(object id 748763) 1 VIEW (cr=11 pr=0 pw=0 time=102 us) 1 NESTED LOOPS SEMI (cr=11 pr=0 pw=0 time=97 us) 14 INDEX RANGE SCAN D564_PK (cr=2 pr=0 pw=0 time=23 us)(object id 752785) 1 INDEX UNIQUE SCAN LINEAGES_PK (cr=9 pr=0 pw=0 time=39 us)(object id 743555) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.01 0.01 ********************************************************************************