autotrace ansi full outer join : autotrace « SQL Plus « Oracle PL / SQL






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

1.autotrace command
2.Autotrace on and off
3.Autotrace lower text function
4.Autotrace running total
5.Autotrace a query on a huge table
6.Autotrace a large table
7.Autotrace a query with group clause
8.Execution Plan
9.autotrace a nested query
10.set autotrace on explain for every single statement
11.set autotrace traceonly explain for bitmap index
12.set autotrace traceonly explain, and condition
13.set autotrace traceonly statistics
14.set autotrace traceonly statistics for 'select * from tableName'
15.set autotrace traceonly
16.AUTOTRACE exists (subquery)
17.AUTOTRACE table joining
18.AUTOTRACE table joining and aggregate function
19.autotrace count(*)
20.autotrace ctxsys.context index
21.autotrace merge command
22.autotrace table with/without an index