Re: Problem with table size

Ken Eaton wrote:
>
> A couple of questions... Is the long text field filled in when the
> record is added, or is it often updated to its final value afterwards?
> (Something must be placed in it because of the NOT NULL constraint).

The MESSAGE_TEXT fields contains the text of weather forecasts and are filled in when the row is added or updated. There is no such thing as a final value as the forecasts are perishable and can also vary greatly in length from day to day. The value in VERSION allows a brief history to be retained, generally 5 versions which for most forecasts will last a couple of days. Other text fields contain weather observations and are updated at 10 minute intervals.

> What is your PCTFREE set to?

Our DBA has provided the following information from dba_tables:

 OWNER			PROD
 TABLE_NAME		TEXT_TBL
 TABLESPACE_NAME	PRODUCTS
 CLUSTER_NAME
 PCT_FREE		10
 PCT_USED		40
 INI_TRANS		1
 MAX_TRANS		255
 INITIAL_EXTENT		26357760
 NEXT_EXTENT		20807680
 MIN_EXTENTS		1
 MAX_EXTENTS		249
 PCT_INCREASE		5
 FREELISTS		1
 FREELIST_GROUPS	1
 BACKED_UP		N
 NUM_ROWS		29030
 BLOCKS			113384
 EMPTY_BLOCKS		1075
 AVG_SPACE		3936
 CHAIN_CNT		135
 AVG_ROW_LEN		183
 DEGREE			1
 INSTANCES		1
 CACHE			N
 TABLE_LOCK		ENABLED

According to the documentation I have available AVG_SPACE is the average number of bytes free in each data block. At 3936 bytes free in a 4K block this value is obviously too high and it appears the only a single row is being stored in a block. I can't see how this can be caused by a PCT_FREE of 10 (default).

> How much updating occurs to the record
> after it is added?

As I mentioned above these rows are being continually updated at intervals varying from 10 minutes to months or even years if the text relates to a warning that is rarely issued.

> Why not use varchar2 on the other character fields?
> (This is not likely your problem, but could save you some space.)

Certainly varchar2 could be used.

> Is an
> export/import possible to do within your availability window?

Yes.

> What is
> your blocksize?

4k.

> I suspect that a lot of chaining is occurring, but it
> is hard to tell with the information provided.

From the above figures 135 for a chain count does not seem high in the context of 113385 blocks in use.

> What is the life of a row
> before the numbers wrap and it gets deleted?

As mentioned above this can vary greatly but most rows would be deleted and inserted a couple of times a day.

> I would also create the
> table with the long field as the last column...
>

O.K.

Any other comments about the above information would be appreciated. Thanks.

Ian Butt.
Bureau of Meteorology, Australia
I.Butt_at_BoM.GOV.AU Received on Fri May 23 1997 - 00:00:00 CDT