-
AND-EQUAL in Update statement
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?
rammi
-
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'
Last edited by DaPi; 01-09-2005 at 09:55 AM.
-
Try NO_INDEX Hint so that CBO will not choose those indexes mentioned in the hint.
Example:
PHP Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|