Re: SELECT, UPDATE and ressource usage

I'm not a tuning expert...but I don't think full table scans are taking place here, as both columns in the minus clause are both indexed...Full table scans would be necessary if you didn't know where the value was that you wanted to locate. Here we have a situation where CLIENT probably becomes the driving table...as it only contains 15000 rows.. and all that is needed is to go through them sequentially and for each one that did not contain an entry in transaction header, easily found by looking at the transaction header index... then print out it out...

I can guess at why the second query is giving grief..First of all the clause length(card_number) = 5 may or not be a factor in determining whether use is made of the index...I don't have my tuning book with me. Secondly check you rollback segments.. in performing an update of that size, oracle is going to build a massive rollback segment.. If your rollback segments are small, for example, 10k initial and next extents, and minextents 2, maxextents 99..you may even get into danger of a snapshot that is too old, meaning that the rollback transaction has circled around the segments.. I personally would write the query in PL/SQL as follows :-

DECLARE cursor get_trans is
select * from transaction_header
for update of card_number;

get_rec get_trans%ROWTYPE;
v_counter NUMBER := 1;

BEGIN    open get_trans;

    LOOP

          fetch get_trans into get_rec;
          EXIT when get_trans%NOTFOUND;
  
           if length(get_rec.card_number) = 5 then 
                   update transaction_header 
                   set card_number = '0'||card_number
                   where current of get_trans;

                   v_counter := v_counter +1;
            end;
     
            if v_counter mod 100 := 0 then 
                    commit;
            end if;

      END LOOP;
      
       close get_trans;

END; The purpose behind the v_counter mod 100 := 0, is that every 100 records you commit and thus prevent a build of the rollback segment. This will stop your program from falling over, and hopefully be quicker in processing time as well.

Good Luck

Graham    Received on Tue Oct 27 1998 - 00:05:47 CET