SQL> create table myTable
2 as
3 select rownum id, a.*
4 from all_objects a
5 where 1=0
6 /
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> alter session set optimizer_mode=all_rows;
SQL>
SQL> select t1.object_name, t2.object_name
2 from myTable t1, myTable t2
3 where t1.object_id = t2.object_id
4 and t1.owner = 'WMSYS'
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2666611345
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 3 |
|* 1 | HASH JOIN | | 1 | 77 | 3 |
|* 2 | TABLE ACCESS FULL| MYTABLE | 1 | 47 | 1 |
| 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2460 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OWNER"='WMSYS')
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table myTable;
SQL>