Problem with table size

From: Ian Butt <I.Butt_at_BoM.GOV.AU>
Date: 1997/05/22
Message-ID: <3383A6E2.41C6_at_BoM.GOV.AU>#1/1

Dear Oracle Users,

PROBLEM

---

[Quoted]         I have a problem with a table that continues to increase in size. Rows are deleted and new rows are inserted keeping the total number of rows about the same but the space occupied by the table continues to increase. I think the problem relates to my use of a long datatype in the table. If you think you can help please read the details below.

DETAILS

---

        I am using Oracle7 Server Release 7.3.2.3.0 - Production Release on an IBM RS/6000 running AIX 4.1.

         
	A number of tables have this problem and all include a long datatype.

An example of the structure of one of these table follows:

	SQL> desc text_tbl;
	Name                            Null?    Type
	------------------------------- -------- ----
	MESSAGE_ID                      NOT NULL CHAR(20)
	MESSAGE_TYPE                    NOT NULL NUMBER(4)
	ORIGINATOR                               CHAR(40)
	STORED_DT                       NOT NULL DATE
	MESSAGE_TEXT                    NOT NULL LONG
	VERSION                         NOT NULL NUMBER(3)

	The table is modified by a C application using embedded SQL.

	The number of rows per MESSAGE_ID is controlled by the VERSION column,

the maximum value allowed for VERSION is held in a separate table. When VERSION reaches the maximum number it returns to 1, deletes the current row for MESSAGE_ID with VERSION equal to 1 and the inserts the new row and the MESSAGE_TEXT associated with it. VERSION numbers continue to be incremented from that value.

        The application updating this table does not seem to have any problems nor do the applications that read the message text fields.

        An analysis of the table shows that is has:

	Rows:                     29014
	Minimum MESSAGE_TEXT:         1 byte
	Maximum MESSAGE_TEXT:     14660 bytes
	Total MESSAGE_TEXT:     2885853 bytes

        For this application the long is expected to handle only up to

30k bytes. The table is updated frequently (about 27500 times in 24 hours) and because the maximum number of versions has been reached for most MESSAGE_ID's involves the delete/insert mentioned above.

        Despite the fact that the actual text contained in the table is 2885853 bytes the DBA reports the following statistics for the table:

Owner                Type       Name

Bytes Blocks Extents Max Extents Initial Extent Next Extent Pct

-------------------- ---------- ------------------------------

----------- -------- -------- ------------ -------------- ------------

---

PROD                 TABLE      TEXT_TBL
468828160   114460      216          249       26357760     20807680

5

Number of rows in table "TEXT_TBL" is: 29014

        The table actually occupies 468828160 bytes and continues to grow! Eventually it reaches the maximum extent allocated and something has to be done. At the moment the DBA is just increasing the extents available.

QUESTIONS

---

        Is this normal for an Oracle table containing a long datatype?

        If this in not normal what is causing it?

        How can I fix it or avoid it?

Thank you for any responses.

Ian Butt
Bureau of Meteorology, Australia
I.Butt_at_BoM.GOV.AU Received on Thu May 22 1997 - 00:00:00 CEST