
Get a base-line cost and plan for acquiring the rows


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=35 Card=34 Bytes=34272)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=35 Card=34 Bytes=34272)
   2    1     INDEX (RANGE SCAN) OF 'T1_MOD' (NON-UNIQUE) (Cost=1 Card=34)




See what happens if we add an 'order by primary key'.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1450 Card=34 Bytes=34272)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1450 Card=34 Bytes=34272)
   2    1     INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=21 Card=34)




Block the index with a no_merge hint


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=41 Card=34 Bytes=17952)
   1    0   SORT (ORDER BY) (Cost=41 Card=34 Bytes=17952)
   2    1     VIEW (Cost=35 Card=34 Bytes=17952)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=35 Card=34 Bytes=34272)
   4    3         INDEX (RANGE SCAN) OF 'T1_MOD' (NON-UNIQUE) (Cost=1 Card=34)




Block the index with a no_index hint


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=34 Bytes=34272)
   1    0   SORT (ORDER BY) (Cost=43 Card=34 Bytes=34272)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=35 Card=34 Bytes=34272)
   3    2       INDEX (RANGE SCAN) OF 'T1_MOD' (NON-UNIQUE) (Cost=1 Card=34)




Session altered.


Cost ratio set to 34 - PK path should be accepted


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1450 Card=34 Bytes=34272)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1450 Card=34 Bytes=34272)
   2    1     INDEX (FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=21 Card=34)




Session altered.


Cost ratio set to 33 - PK NOSORT should be too expensive


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=34 Bytes=34272)
   1    0   SORT (ORDER BY) (Cost=43 Card=34 Bytes=34272)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=35 Card=34 Bytes=34272)
   3    2       INDEX (RANGE SCAN) OF 'T1_MOD' (NON-UNIQUE) (Cost=1 Card=34)



