alter session set optimizer_mode=all_rows : OPTIMIZER_MODE « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. OPTIMIZER_MODE

SQL> create table myTable
  2  as
  3  select rownum id, a.*
  4    from all_objects a
  5   where 1=0
  6  /
SQL>
SQL>
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>
SQL> drop table myTable;
SQL>
SQL>