AND-EQUAL in Update statement

[rammi65]

Hi
the following update statement gives ora-379 buffer pool error in oracle version 8.1.6.3 on TRU64.

UPDATE MISPOSTPAID.MIS_DYDSRNEW@MIS SET MIG_OUT=:b1,DEACT=DEACT - :b1 WHERE (POP_DATE) = TRUNC(SYSDATE - 1 ) AND BRANCH = :b3 AND TYPE = 'POST-CORP'

when i trace it,

Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE (REMOTE)
0 UPDATE OF 'MIS_DYDSRNEW' [MIS]
0 AND-EQUAL
0 INDEX (RANGE SCAN) OF 'DSRNEWPOPDT' (NON-UNIQUE) [MIS]
0 INDEX (RANGE SCAN) OF 'DSRNEWBRANCH' (NON-UNIQUE) [MIS]
0 INDEX (RANGE SCAN) OF 'DSRNEWTYPE' (NON-UNIQUE) [MIS]

Oracle Metalink says, AND-EQUAL will throw the above error on 8.1.6.3.
Its in Production and no downtime.
Any workaround? can we able to change the update statement?

[DaPi]

If you can replace it with some PL/SQL, try some variation on:
- Declare my_pk as a table of . . .
- Select PK Into my_pk Bulk Collect From my_table Where . . . ;
loop over:
- Update my_table Set . . . Where PK = my_pk(i);

Otherwise experiment with:
Update my_table Set . . .
Where PK In
(Select PK From my_table
Where . . . . .
AND rownum >= 1);

added later:
If neither of those work, try avoiding the AND-EQUAL by only using one index (not good for efficency, but if it's the only way . . . ) e.g.:
WHERE TRUNC(POP_DATE) = TRUNC(SYSDATE - 1 )
AND BRANCH = :b3
AND UPPER(TYPE) = 'POST-CORP'

[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