Mega Code Archive

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>