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 sort_area_size = 102400000;
SQL>
SQL> alter session set hash_area_size = 204800000;
SQL>
SQL> select a.object_type, b.object_name
2 from myTable a, myTable b
3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
----------------------------------------------------------
Plan hash value: 2666611345
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 |
|* 1 | HASH JOIN | | 67 | 3082 | 3 |
| 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 |
| 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> alter session set sort_area_size = 65536;
SQL>
SQL> alter session set hash_area_size = 131072;
SQL>
SQL> select a.object_type, b.object_name
2 from myTable a, myTable b
3 where a.last_ddl_time = b.last_ddl_time;
Execution Plan
----------------------------------------------------------
Plan hash value: 2666611345
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 3082 | 3 |
|* 1 | HASH JOIN | | 67 | 3082 | 3 |
| 2 | TABLE ACCESS FULL| MYTABLE | 82 | 1640 | 1 |
| 3 | TABLE ACCESS FULL| MYTABLE | 82 | 2132 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME")
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table myTable;