AND-EQUAL in Update statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: AND-EQUAL in Update statement

  1. #1
    Join Date
    Dec 2004
    Posts
    15

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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 ROWIDOF '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 SCANOF 'T1_IDX_1' (NON-UNIQUE) (Cost=2 Card=105)
       
    9    5             BITMAP CONVERSION (FROM ROWIDS)
      
    10    9               SORT (ORDER BY)
      
    11   10                 INDEX (RANGE SCANOF '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 ROWIDOF 'T1' (Cost=10 Card=1 Bytes=31)
       
    4    3         INDEX (RANGE SCANOF '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
  •  


Click Here to Expand Forum to Full Width