SQL>
SQL> create table myTable1
2 as
3 select mod(rownum,1000) id, rpad('x',300,'x') data
4 from all_objects
5 where rownum <= 5*1000;
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 50 and 55;
SQL>
SQL> alter session set optimizer_index_caching = 50;
SQL>
SQL> select *
2 from myTable1, myTable2
3 where myTable1.id = myTable2.id
4 and myTable2.id between 50 and 55;
SQL>
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table myTable1;
SQL> drop table myTable2;