Wow, thanks All for the great suggestions
These users are out of state, so my pea brain was thinking of
Unload Data for Table
Delete Rows
Alter Table ...
...
Load the data back into the Table
But I will look at these other suggestions first.
I have done lots of Alter Tables in the past but this if the first time it has
nailed me. I guess I finally have a user
with enough data to reach a limit.
Thanks again
Marc
From: Bill Eyring
Sent: Wednesday, June 16, 2010 8:38 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: RB2 files quadruples in size?
Marc,
I don't know if you run this code often, but I am assuming this is a one time
schema change.
If so, this bloating will happen when you make extensive schema changes. Reload
the database and all will be good. Also pack index.
Next time you do schema changes drop the indexes first, make the changes and
re-create indexes.
Another suggestion which will avoid the bloating effect:
Unload the table structure and data (TravCard.all)
Edit TravCard.all and do your schema changes
Delete the table TravCard
Run TravCard.all.
Bill Eyring
p.s. Here is a small piece of code to get exact row size in a table.
set v vtable text=null
set v vsize int=null
CHOOSE vTable FOR #TBLVIEWS +
CHKBOX 1 +
TITLE 'Select table then click on [OK] button to continue' +
CAPTION 'Get Table Row Size' LINES 19 FORMATTED +
option TITLE_FONT_SIZE 10 +
|TITLE_FONT_NAME tahoma +
|BUTTONS_SHOW_GLYPH ON +
|THEMENAME razzmatazz
if vtable is null the
goto theend
endi
sel SYS_ROW_SIZE into vsize vi1 fro sys_tables whe SYS_TABLE_NAME=.vtable
set v vmsg text=('Table Row Size for'&.vtable&'is'&(ctxt(.vsize)))
pau 2 using .vmsg caption 'Find Table Row Size' icon confirm +
OPTION MESSAGE_FONT_NAME Tahoma +
|MESSAGE_FONT_COLOR 0 +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_BOLD ON +
|THEMENAME RAZZMATAZZ
label theend
clear variables vtable,vsize
ret
--------------------------------------------------------------------------------
From: [email protected] [mailto:[email protected]] On Behalf Of MDRD
Sent: Wednesday, June 16, 2010 8:49 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RB2 files quadruples in size?
Hi
The RB2 file went from about 200 megs to over 800 megs all of the sudden?
It appears this block of code caused the problem,
My only guess is to put a Pack Table every so often to keep from hitting this
wall
Thanks
Marc
-- there is only 300 rows in this table
CREATE INDEX v1 ON `spbutton` (`NewButName` ASC )
CREATE INDEX v2 ON `spbutton` (`V2` ASC )
CREATE INDEX v3 ON `spbutton` (`V3` ASC )
CREATE INDEX v4 ON `spbutton` (`V4` ASC )
-- only 10-20 rows in this table the column was Text 50
ALTER TABLE PopTCS ALTER PopTCSTxt TO PopTCSTxt TEXT 150
-- only 13,337 rows in this table the Text fields were 50 each
-- total row size after this is around 3021
ALTER TABLE travcard ALTER Sympt1 TO Sympt1 TEXT 150
ALTER TABLE travcard ALTER Sympt2 TO Sympt2 TEXT 150
ALTER TABLE travcard ALTER Sympt3 TO Sympt3 TEXT 150
ALTER TABLE travcard ALTER Sympt4 TO Sympt4 TEXT 150
ALTER TABLE travcard ALTER Sympt5 TO Sympt5 TEXT 150
ALTER TABLE travcard ALTER Therapy1 TO Therapy1 TEXT 150
ALTER TABLE travcard ALTER Therapy2 TO Therapy2 TEXT 150
ALTER TABLE travcard ALTER Therapy3 TO Therapy3 TEXT 150
ALTER TABLE travcard ALTER Therapy4 TO Therapy4 TEXT 150
ALTER TABLE travcard ALTER Therapy5 TO Therapy5 TEXT 150
ALTER TABLE travcard ALTER Adj1 TO Adj1 TEXT 150
ALTER TABLE travcard ALTER Adj2 TO Adj2 TEXT 150
ALTER TABLE travcard ALTER Adj3 TO Adj3 TEXT 150
ALTER TABLE travcard ALTER Adj4 TO Adj4 TEXT 150
ALTER TABLE travcard ALTER Adj5 TO Adj5 TEXT 150
