AND-EQUAL in Update statement

[tamilselvan]

Try NO_INDEX Hint so that CBO will not choose those indexes mentioned in the hint.
Example:

update t1 set subobject_name='SUB'
where object_id between :b1 and :b2 and
      stat2     between :b3 and :b4 and
      status    = :b5 ;

Execution Plan
----------------------------------------------------------
rows will be truncated

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=31)
   1    0   UPDATE OF 'T1'
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=9 Card=1 Bytes=31)
   4    3         BITMAP CONVERSION (TO ROWIDS)
   5    4           BITMAP AND
   6    5             BITMAP CONVERSION (FROM ROWIDS)
   7    6               SORT (ORDER BY)
   8    7                 INDEX (RANGE SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=2 Card=105)
   9    5             BITMAP CONVERSION (FROM ROWIDS)
  10    9               SORT (ORDER BY)
  11   10                 INDEX (RANGE SCAN) OF 'T1_IDX_2' (NON-UNIQUE) (Cost=2 Card=105)


update /*+ NO_INDEX(t1 t1_idx_2)  */ t1 set subobject_name='SUB'
where object_id between :b1 and :b2 and
      stat2     between :b3 and :b4 and
      status    = :b5 ;

Execution Plan
----------------------------------------------------------
rows will be truncated

   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=31)
   1    0   UPDATE OF 'T1'
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=10 Card=1 Bytes=31)
   4    3         INDEX (RANGE SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=2 Card=105) 

Tamil