Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with table size
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.comReceived on Thu May 29 1997 - 00:00:00 CDT