SQL_ID aj2nz22hbx7fc, child number 0 ------------------------------------- with cons as (select c.owner, c.constraint_name, c.constraint_type, c.table_name, c.r_owner, c.r_constraint_name, cc.column_name, cc.position from dba_constraints c, dba_cons_columns cc where (Not ((c.constraint_name like 'BIN$%==$0') and length(c.constraint_name) = 30)) and ( c.constraint_type = 'R' and ((c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')) or (c.r_owner = sys_context('USERENV', 'CURRENT_SCHEMA'))) and c.owner = cc.owner and c.constraint_name = cc.constraint_name ) or ( c.constraint_type in ('P', 'U') and c.owner = sys_context('USERENV', 'CURRENT_SCHEMA') and c.owner = cc.owner and c.constraint_name = cc.constraint_name)) select 'REF_INTEGRITY' AS type, ref_cons.owner, ref_cons.table_name, ref_cons.column_name, ref_cons.constraint_name, pk Plan hash value: 43758122 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 3533 (100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | 2 | LOAD AS SELECT | | | | | | | |* 3 | FILTER | | | | | | | | 4 | NESTED LOOPS | | 2 | 1266 | | 2974 (3)| 00:00:16 | | 5 | NESTED LOOPS | | 2 | 1222 | | 2972 (3)| 00:00:16 | | 6 | NESTED LOOPS OUTER | | 2 | 1150 | | 2966 (3)| 00:00:16 | | 7 | NESTED LOOPS OUTER | | 2 | 1142 | | 2964 (3)| 00:00:16 | | 8 | NESTED LOOPS | | 2 | 1122 | | 2960 (3)| 00:00:16 | | 9 | NESTED LOOPS | | 2 | 1078 | | 2958 (3)| 00:00:16 | | 10 | NESTED LOOPS OUTER | | 2 | 1050 | | 2954 (3)| 00:00:16 | | 11 | NESTED LOOPS | | 2 | 962 | | 2953 (3)| 00:00:16 | | 12 | NESTED LOOPS | | 2 | 910 | | 2949 (3)| 00:00:16 | | 13 | NESTED LOOPS OUTER | | 2 | 872 | | 2943 (3)| 00:00:16 | | 14 | NESTED LOOPS | | 1 | 423 | | 2941 (3)| 00:00:16 | | 15 | NESTED LOOPS | | 1 | 413 | | 2939 (3)| 00:00:16 | |* 16 | HASH JOIN | | 121 | 46343 | | 2764 (3)| 00:00:15 | | 17 | TABLE ACCESS FULL | USER$ | 382 | 6494 | | 5 (0)| 00:00:01 | |* 18 | HASH JOIN | | 121 | 44286 | | 2759 (3)| 00:00:15 | | 19 | TABLE ACCESS FULL | USER$ | 382 | 53862 | | 5 (0)| 00:00:01 | |* 20 | HASH JOIN RIGHT OUTER | | 303K| 65M| | 2750 (3)| 00:00:15 | | 21 | TABLE ACCESS FULL | USER$ | 382 | 53862 | | 5 (0)| 00:00:01 | |* 22 | HASH JOIN | | 303K| 24M| 12M| 2742 (3)| 00:00:15 | | 23 | TABLE ACCESS FULL | CON$ | 303K| 8901K| | 331 (3)| 00:00:02 | |* 24 | HASH JOIN OUTER | | 303K| 15M| 10M| 1878 (3)| 00:00:10 | |* 25 | TABLE ACCESS FULL | CDEF$ | 303K| 7119K| | 1160 (2)| 00:00:07 | | 26 | TABLE ACCESS FULL | CON$ | 303K| 8901K| | 331 (3)| 00:00:02 | | 27 | TABLE ACCESS BY INDEX ROWID| CON$ | 1 | 30 | | 2 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | I_CON1 | 1 | | | 1 (0)| 00:00:01 | |* 29 | TABLE ACCESS BY INDEX ROWID | CDEF$ | 1 | 10 | | 2 (0)| 00:00:01 | |* 30 | INDEX UNIQUE SCAN | I_CDEF1 | 1 | | | 1 (0)| 00:00:01 | | 31 | VIEW PUSHED PREDICATE | _CURRENT_EDITION_OBJ | 5 | 65 | | 2 (0)| 00:00:01 | |* 32 | FILTER | | | | | | | | 33 | NESTED LOOPS | | 1 | 36 | | 4 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | I_OBJ1 | 1 | 14 | | 3 (0)| 00:00:01 | |* 35 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | | 1 (0)| 00:00:01 | | 36 | NESTED LOOPS | | 1 | 32 | | 4 (0)| 00:00:01 | |* 37 | INDEX RANGE SCAN | I_OBJ4 | 1 | 12 | | 3 (0)| 00:00:01 | |* 38 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 | | 39 | TABLE ACCESS BY INDEX ROWID | CCOL$ | 1 | 19 | | 3 (0)| 00:00:01 | |* 40 | INDEX RANGE SCAN | I_CCOL1 | 1 | | | 2 (0)| 00:00:01 | | 41 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 26 | | 2 (0)| 00:00:01 | |* 42 | INDEX UNIQUE SCAN | I_COL3 | 1 | | | 1 (0)| 00:00:01 | | 43 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 44 | | 1 (0)| 00:00:01 | |* 44 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 0 (0)| | |* 45 | INDEX RANGE SCAN | I_OBJ1 | 1 | 14 | | 2 (0)| 00:00:01 | |* 46 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | | 1 (0)| 00:00:01 | |* 47 | INDEX RANGE SCAN | I_OBJ1 | 1 | 10 | | 2 (0)| 00:00:01 | |* 48 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | | 1 (0)| 00:00:01 | | 49 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 36 | | 3 (0)| 00:00:01 | |* 50 | INDEX RANGE SCAN | I_OBJ1 | 1 | | | 2 (0)| 00:00:01 | |* 51 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | | 1 (0)| 00:00:01 | | 52 | NESTED LOOPS | | 1 | 32 | | 4 (0)| 00:00:01 | |* 53 | INDEX RANGE SCAN | I_OBJ4 | 1 | 12 | | 3 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 | | 55 | NESTED LOOPS | | 1 | 32 | | 4 (0)| 00:00:01 | |* 56 | INDEX RANGE SCAN | I_OBJ4 | 1 | 12 | | 3 (0)| 00:00:01 | |* 57 | INDEX RANGE SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 | | 58 | SORT ORDER BY | | 1 | 4287 | | 559 (1)| 00:00:03 | |* 59 | HASH JOIN | | 1 | 4287 | 11M| 558 (1)| 00:00:03 | |* 60 | VIEW | | 5811 | 11M| | 80 (2)| 00:00:01 | | 61 | TABLE ACCESS FULL | SYS_TEMP_0FD9FCCDE_32009F35 | 5811 | 3592K| | 80 (2)| 00:00:01 | |* 62 | VIEW | | 5811 | 12M| | 80 (2)| 00:00:01 | | 63 | TABLE ACCESS FULL | SYS_TEMP_0FD9FCCDE_32009F35 | 5811 | 3592K| | 80 (2)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))) 16 - access("U"."NAME"=DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME")) 18 - access("OC"."OWNER#"="USER#") filter(((("OC"."NAME" NOT LIKE 'BIN$%==$0' OR LENGTH("OC"."NAME")<>30) AND DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C',8,'H',9,'F',10,'F',11,'F',13,'F','?')='R' AND (DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME")=SYS_CONTEXT('USERENV','CURRENT_SCHEMA') OR CASE WHEN ROWID IS NOT NULL THEN DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME") ELSE NULL END =SYS_CONTEXT('USERENV','CURRENT_SCHEMA'))) OR ((DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C',8,'H',9,'F',1 0,'F',11,'F',13,'F','?')='P' OR DECODE("C"."TYPE#",1,'C',2,'P',3,'U',4,'R',5,'V',6,'O',7,'C',8,'H',9,'F',10,'F',11,'F',13, 'F','?')='U') AND DECODE("U"."TYPE#",2,SUBSTR("U"."EXT_USERNAME",0,30),"U"."NAME")=SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ))) 20 - access("RC"."OWNER#"="USER#") 22 - access("OC"."CON#"="C"."CON#") 24 - access("C"."RCON#"="RC"."CON#") 25 - filter(("C"."TYPE#"<>12 AND "C"."TYPE#"<>8 AND ("C"."TYPE#"<14 OR "C"."TYPE#">17))) 28 - access("C"."OWNER#"="U"."USER#" AND "OC"."NAME"="C"."NAME") 29 - filter(("CD"."TYPE#"<>12 AND ("CD"."TYPE#"<14 OR "CD"."TYPE#">17))) 30 - access("C"."CON#"="CD"."CON#") 32 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL)))) 34 - access("O"."OBJ#"="C"."ROBJ#") 35 - access("O"."OWNER#"="U"."USER#") 37 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88) 38 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) 40 - access("CD"."CON#"="CC"."CON#") 42 - access("CC"."OBJ#"="COL"."OBJ#" AND "CC"."INTCOL#"="COL"."INTCOL#") 44 - access("COL"."OBJ#"="AC"."OBJ#" AND "COL"."INTCOL#"="AC"."INTCOL#") 45 - access("CC"."OBJ#"="O"."OBJ#") 46 - access("O"."OWNER#"="U"."USER#") 47 - access("C"."ENABLED"="OI"."OBJ#") 48 - access("OI"."OWNER#"="UI"."USER#") 50 - access("C"."OBJ#"="O"."OBJ#") 51 - access("O"."OWNER#"="U"."USER#") 53 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88) 54 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) 56 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88) 57 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) 59 - access("REF_CONS"."R_CONSTRAINT_NAME"="PK_CONS"."CONSTRAINT_NAME" AND "REF_CONS"."POSITION"="PK_CONS"."POSITION") filter(("PK_CONS"."OWNER"="REF_CONS"."R_OWNER" OR "PK_CONS"."OWNER"="REF_CONS"."OWNER")) 60 - filter(("PK_CONS"."CONSTRAINT_TYPE"='P' OR "PK_CONS"."CONSTRAINT_TYPE"='U')) 62 - filter("REF_CONS"."CONSTRAINT_TYPE"='R')