alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching

    

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>

Related examples in the same category

1.	alter the session with the ALTER SESSION statement and set the session's time zone forward
2.	alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
3.	alter session set OPTIMIZER_MODE = RULE
4.	Adjust your session time zone to -08:00, display the contents of your time table.
5.	alter session set cursor_sharing = force
6.	alter session set use_stored_outlines
7.	Event-based call and exception tracing
8.	event-based call and exception tracing.
9.	SQL trace
10.	uses dynamic SQL to issue an ALTER SESSION statement.
11.	ALTER SESSION SET QUERY_REWRITE_ENABLED
12.	alter session set sql_trace=true
13.	sql_trace a stored procedure
14.	Set the following session parameters to enable query rewrite:
15.	If your session time zone is not US/Central (-06:00), alter your session to Central time:
16.	alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
17.	Alter time_zone
18.	sessiontimezone, current_timestamp
19.	demonstrates the use of DBMS_SQL to execute an ALTER SESSION statement.
20.	interaction between ALTER SESSION and autonomous transactions.
21.	ALTER SESSION SET SQL_TRACE = TRUE
22.	ALTER SESSION SET optimizer_dynamic_sampling
23.	alter session set NLS_DATE_FORMAT='DD-MM-YYYY'
24.	alter session set current_schema=Smart
25.	alter session set db_file_multiblock_read_count
26.	alter session set hash_area_size
27.	alter session set nls_date_format='dd-mm-yyyy', nls_language=Dutch, nls_currency='Eur'
28.	alter session set optimizer_max_permutations=80000
29.	alter session set optimizer_mode=all_rows
30.	alter session set optimizer_mode=first_rows
31.	alter session set query_rewrite_integrity=enforced
32.	alter session set sort_area_size = 102400000
33.	alter session set workarea_size_policy=manual
34.	alter system flush shared_pool
35.	alter system quiesce restricted
36.	alter system resume
37.	alter system suspend
38.	set optimizer_features_enable = '8.1.5' scope = spfile
39.	show filestat
40.	show parameter optimizer_features
41.	show verify
42.	If your session time zone is not US/Central Standard Time (-06:00), alter your session to Central Standard time:

alter session set optimizer_index_cost_adj, alter session set optimizer_index_caching : Session variable « SQL Plus « Oracle PL / SQL

Related examples in the same category