Mega Code Archive

 

Categories / Oracle PLSQL / SQL Plus

 

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.