alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching : 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  myTable1
  2  as
  3  select mod(rownum,1000) id, rpad('x',300,'x') data
  4    from all_objects
  5   where rownum <= 5000;
SQL>
SQL> create table  myTable2
  2  as
  3  select rownum id, rpad('x',300,'x') data
  4    from all_objects
  5   where rownum <= 1000;
SQL>
SQL> create index myTable1_idx on myTable1(id);
SQL>
SQL> create index myTable2_idx on myTable2(id);
SQL>
SQL> begin
  2     dbms_stats.gather_table_stats( user, 'myTable1', method_opt => 'for all indexed columns',cascade=>true );
  3     dbms_stats.gather_table_stats( user, 'myTable2', method_opt => 'for all indexed columns',cascade=>true );
  4  end;
  5  /
SQL> set autotrace traceonly  explain
SQL>
SQL> select *
  2    from myTable1, myTable2
  3   where myTable1.id = myTable2.id
  4     and myTable2.id between 5 and 55;

Execution Plan
----------------------------------------------------------
Plan hash value: 3784280921

------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    56 | 20664 |   260 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MYTABLE1     |     1 |   178 |     5 |
|   2 |   NESTED LOOPS                |              |    56 | 20664 |   260 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYTABLE2     |    51 |  9741 |     5 |
|*  4 |     INDEX RANGE SCAN          | MYTABLE2_IDX |    51 |       |     2 |
|*  5 |    INDEX RANGE SCAN           | MYTABLE1_IDX |     1 |       |       |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
   5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
       filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> alter session set optimizer_index_cost_adj = 50;
SQL>
SQL> alter session set optimizer_index_caching = 0;
SQL>
SQL> select *
  2    from myTable1, myTable2
  3   where myTable1.id = myTable2.id
  4     and myTable2.id between 5 and 55;

Execution Plan
----------------------------------------------------------
Plan hash value: 3784280921

------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    56 | 20664 |   156 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | MYTABLE1     |     1 |   178 |     3 |
|   2 |   NESTED LOOPS                |              |    56 | 20664 |   156 |
|   3 |    TABLE ACCESS BY INDEX ROWID| MYTABLE2     |    51 |  9741 |     3 |
|*  4 |     INDEX RANGE SCAN          | MYTABLE2_IDX |    51 |       |     1 |
|*  5 |    INDEX RANGE SCAN           | MYTABLE1_IDX |     1 |       |     1 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MYTABLE2"."ID">=5 AND "MYTABLE2"."ID"<=55)
   5 - access("MYTABLE1"."ID"="MYTABLE2"."ID")
       filter("MYTABLE1"."ID">=5 AND "MYTABLE1"."ID"<=55)

Note
-----
   - cpu costing is off (consider enabling it)

SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable1;
SQL> drop table myTable2;
SQL>