|
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>
|