CREATE TABLE foj1 AS
SELECT object_id, object_name, object_type FROM all_objects;
CREATE TABLE foj2 AS
SELECT object_id, object_name, object_type FROM all_objects;
DELETE FROM foj2 WHERE object_name != 'FOJ1' AND rownum = 1;
set autotrace on exp stat
WITH all_recs AS (
SELECT 1 AS foj1, 0 AS foj2, f.* FROM foj1 f
UNION ALL
SELECT 0 AS foj1, 1 AS foj2, f.* FROM foj2 f
), agg AS (
SELECT sum(foj1) foj1, sum(foj2) foj2, object_id, object_name, object_type
FROM all_recs
GROUP BY object_id, object_name, object_type
)
SELECT *
FROM agg
WHERE foj1 != 1
OR foj2 != 1;
WITH recs1 AS (
SELECT 1 AS foj1, f.* FROM foj1 f
), recs2 AS (
SELECT 1 AS foj2, f.* FROM foj2 f
)
SELECT *
FROM recs1 FULL OUTER JOIN recs2
USING (object_id, object_name, object_type)
WHERE foj1 IS NULL
OR foj2 IS NULL;
|
No significant change from Oracle 11 to Oracle 12:
| Oracle 11.2.0.2.0 | Oracle 12.1.0.1.0 | |
|---|---|---|
Table created.
Table created.
1 row deleted.
FOJ1 FOJ2 OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------- ---------- ------------------------------ -------------------
0 1 101834 FOJ2 TABLE
1 0 20 ICOL$ TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 3001670731
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 118K| 7778K| 237 (3)| 00:00:03 |
| 1 | VIEW | | 118K| 7778K| 237 (3)| 00:00:03 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 118K| 5456K| 237 (3)| 00:00:03 |
| 4 | VIEW | | 118K| 5456K| 233 (1)| 00:00:03 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS FULL| FOJ1 | 62543 | 2504K| 116 (0)| 00:00:02 |
| 7 | TABLE ACCESS FULL| FOJ2 | 56340 | 2255K| 116 (0)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("FOJ1")<>1 OR SUM("FOJ2")<>1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
949 consistent gets
561 physical reads
0 redo size
701 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
OBJECT_ID OBJECT_NAME OBJECT_TYPE FOJ1 FOJ2
---------- ------------------------------ ------------------- ---------- ----------
20 ICOL$ TABLE 1
101834 FOJ2 TABLE 1
Execution Plan
----------------------------------------------------------
Plan hash value: 116219682
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62543 | 2870K| | 550 (1)| 00:00:07 |
|* 1 | VIEW | VW_FOJ_0 | 62543 | 2870K| | 550 (1)| 00:00:07 |
|* 2 | HASH JOIN FULL OUTER| | 62543 | 5374K| 3088K| 550 (1)| 00:00:07 |
| 3 | VIEW | | 56340 | 2420K| | 116 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | FOJ2 | 56340 | 2255K| | 116 (0)| 00:00:02 |
| 5 | VIEW | | 62543 | 2687K| | 116 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL | FOJ1 | 62543 | 2504K| | 116 (0)| 00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FOJ1" IS NULL OR "FOJ2" IS NULL)
2 - access("RECS1"."OBJECT_TYPE"="RECS2"."OBJECT_TYPE" AND
"RECS1"."OBJECT_NAME"="RECS2"."OBJECT_NAME" AND
"RECS1"."OBJECT_ID"="RECS2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
938 consistent gets
0 physical reads
0 redo size
699 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
|
Table created.
Table created.
1 row deleted.
FOJ1 FOJ2 OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ---------- ---------- ------------------------------ -------------------
1 0 100 ORA$BASE EDITION
0 1 101831 FOJ2 TABLE
Execution Plan
----------------------------------------------------------
Plan hash value: 3001670731
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122K| 8041K| 211 (3)| 00:00:03 |
| 1 | VIEW | | 122K| 8041K| 211 (3)| 00:00:03 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 122K| 5640K| 211 (3)| 00:00:03 |
| 4 | VIEW | | 122K| 5640K| 207 (1)| 00:00:03 |
| 5 | UNION-ALL | | | | | |
| 6 | TABLE ACCESS FULL| FOJ1 | 70965 | 2841K| 103 (0)| 00:00:02 |
| 7 | TABLE ACCESS FULL| FOJ2 | 51933 | 2079K| 103 (0)| 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("FOJ1")<>1 OR SUM("FOJ2")<>1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
878 consistent gets
468 physical reads
0 redo size
706 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
OBJECT_ID OBJECT_NAME OBJECT_TYPE FOJ1 FOJ2
---------- ------------------------------ ------------------- ---------- ----------
100 ORA$BASE EDITION 1
101831 FOJ2 TABLE 1
Execution Plan
----------------------------------------------------------
Plan hash value: 116219682
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70965 | 3257K| | 535 (1)| 00:00:07 |
|* 1 | VIEW | VW_FOJ_0 | 70965 | 3257K| | 535 (1)| 00:00:07 |
|* 2 | HASH JOIN FULL OUTER| | 70965 | 6098K| 2848K| 535 (1)| 00:00:07 |
| 3 | VIEW | | 51933 | 2231K| | 103 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL | FOJ2 | 51933 | 2079K| | 103 (0)| 00:00:02 |
| 5 | VIEW | | 70965 | 3049K| | 103 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL | FOJ1 | 70965 | 2841K| | 103 (0)| 00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FOJ1" IS NULL OR "FOJ2" IS NULL)
2 - access("RECS1"."OBJECT_TYPE"="RECS2"."OBJECT_TYPE" AND
"RECS1"."OBJECT_NAME"="RECS2"."OBJECT_NAME" AND
"RECS1"."OBJECT_ID"="RECS2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
867 consistent gets
0 physical reads
0 redo size
704 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
|