SQL>
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL>
SQL> alter session set hash_area_size = 1024;
Session altered.
SQL>
SQL> create table t as select * from all_objects where rownum < 50;
Table created.
SQL>
SQL> analyze table t compute statistics
2 for table for columns object_id;
Table analyzed.
SQL>
SQL> set autotrace traceonly
SQL> alter session set sort_area_size = 102400;
Session altered.
SQL>
SQL> select *
2 from t myTable1, t myTable2
3 where myTable1.object_id = myTable2.object_id
4 /
49 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 7448 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 49 | 7448 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 49 | 3724 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 49 | 3724 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MYTABLE1"."OBJECT_ID"="MYTABLE2"."OBJECT_ID")
Statistics
----------------------------------------------------------
244 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
4375 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49 rows processed
SQL>
SQL> drop table t;
Table dropped.