Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with table size

Re: Problem with table size

From: Will Kooiman <wkooiman_at_csac.com>
Date: 1997/05/29
Message-ID: <338E54A0.7945@csac.com>#1/1

Your table continues to increase in size because you are doing delete/insert logic. If you change your code to update/insert logic your problem should go away.

Ex:

delete from text_tbl where message_id = :h_message_id;

insert into text_tbl
  (message_id, ..., message_text, version) values
  (:h_message_id, ..., :h_message_text, :h_version);

Should be

update text_tbl
set message_type = :h_message_type,

    originator = :h_originator,
.
.

    message_text = :h_message_text,
    version      = :h_version
where message_id = :h_message_id;

if ( sqlca.sqlerrd[2] == 0 )
{ insert into text_tbl

      (message_id, ..., message_text, version)    values

      (:h_message_id, ..., :h_message_text, :h_version); }

The problem has to do with Oracle not updating the high water mark when doing inserts or deletes. I've seen this problem a million times, and it has always been corrected by using update/insert logic.

The LONG column is probably responsible for exaggerating the effects of using delete/insert logic.

You should also compress the table after making the code change.

Will.

-- 
======================================================================
Will Kooiman                        Computer Systems Authority
Systems Consultant                  6380 LBJ Freeway, Suite 181
(972) 960-0180 x236                 Dallas, TX 75240
mailto:wkooiman@csac.com            http://www.csac.com
Received on Thu May 29 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US