autotrace ansi full outer join
SQL>
SQL>
SQL> create table myTable as
2 select 'myTable' as C1
3 ,OBJECT_NAME
4 ,SUBOBJECT_NAME
5 ,OBJECT_ID
6 ,DATA_OBJECT_ID
7 ,OBJECT_TYPE
8 ,CREATED
9 ,LAST_DDL_TIME
10 ,TIMESTAMP
11 ,STATUS
12 ,TEMPORARY
13 ,GENERATED
14 ,SECONDARY
15 from dba_objects;
Table created.
SQL>
SQL> create table myTable2 as
2 select 'myTable2' as C1
3 ,OBJECT_NAME || 'myTable2' as object_name
4 ,SUBOBJECT_NAME
5 ,OBJECT_ID
6 ,DATA_OBJECT_ID
7 ,OBJECT_TYPE
8 ,CREATED
9 ,LAST_DDL_TIME
10 ,TIMESTAMP
11 ,STATUS
12 ,TEMPORARY
13 ,GENERATED
14 ,SECONDARY
15 from dba_objects
16 where rownum <= 10000;
Table created.
SQL>
SQL> create index myTable_object_id on myTable (object_id);
Index created.
SQL>
SQL> create index myTable2_object_id on myTable2 (object_id);
Index created.
SQL>
SQL> analyze table myTable compute statistics;
Table analyzed.
SQL>
SQL> analyze table myTable2 compute statistics;
Table analyzed.
SQL>
SQL> set autotrace TRACEONLY
SQL> set timing on
SQL> select *
2 from myTable a, myTable2 b
3 where a.object_id = b.object_id(+)
4 union
5 select *
6 from myTable a, myTable2 b
7 where a.object_id(+) = b.object_id;
13158 rows selected.
Elapsed: 00:00:00.62
Execution Plan
----------------------------------------------------------
Plan hash value: 4186416997
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23158 | 4296K| | 47039 |
| 1 | SORT UNIQUE | | 23158 | 4296K| 10M| 47039 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 13158 | 2441K| | 26343 |
| 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| | 27 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | | 2 |
|* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | | 1 |
| 7 | NESTED LOOPS OUTER | | 10000 | 1855K| | 20022 |
| 8 | TABLE ACCESS FULL | MYTABLE2 | 10000 | 966K| | 22 |
| 9 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 1 | 91 | | 2 |
|* 10 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | | | 1 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
10 - access("A"."OBJECT_ID"(+)="B"."OBJECT_ID")
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
43520 consistent gets
0 physical reads
0 redo size
1301014 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL>
SQL>
SQL> select *
2 from myTable a full outer join myTable2 b
3 using (object_id);
13158 rows selected.
Elapsed: 00:00:00.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3236823177
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13658 | 4401K| 26365 |
| 1 | VIEW | | 13658 | 4401K| 26365 |
| 2 | UNION-ALL | | | | |
| 3 | NESTED LOOPS OUTER | | 13158 | 2441K| 26343 |
| 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| 27 |
| 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | 2 |
|* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | 1 |
|* 7 | FILTER | | | | |
| 8 | TABLE ACCESS FULL | MYTABLE2 | 500 | 49500 | 22 |
|* 9 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | 13 | 1 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "MYTABLE" "A" WHERE
"A"."OBJECT_ID"=:B1))
9 - access("A"."OBJECT_ID"=:B1)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45912 consistent gets
0 physical reads
0 redo size
956084 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL>
SQL> set timing off
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTable2;
Table dropped.
Related examples in the same category