FreeSpace Question Drop/Delete 1

[RodStagg]

We have recently changed the way we process our Fast Loads so that the tables are deleted rather than dropped - thus saving time and locks on DBC Tables for example.

All tables have defaulted to a FreeSpacePercentage (FSP) of 15 and now to optimise retrieval times and space I am thinking of setting this to 0 for tables that are simply refreshed each time.

My question is how this FSP will affect tables that are deleted (rather than recreated). Obviously I know that to implement this change I will have to perform an initial drop and recreate but will I still get the saving following subsequent deletes in the production jobs?

Also does anybody have experience of reducing FSP to zero and whether the extra rows returned in a buffer actually improves response times?

Thanks

 

[dnoeth]

FSP = 0 is the default for Teradata unless you modified the File System Field 1 FreeSpacePercent using dbscontrol.

You can submit a ALTER TABLE xxx, FREESPACE=0 instead of DROP/CREATE.

FREESPACE is honoured by FastLoad, Insert/Select or MLOAD into an empty table, RESTORE, add FALLBACK, Create Index.

Especially in V2R5 with Cylinder Read there might be a bit faster response time, when the query is IO bound. And if not, you'll free perm space for those tables, which are only reloaded.

Dieter

 

[tdatgod]

Hi,
Freespace percent > 0 is only required When you update a table.

If you have summary tables that you drop/delete and recreate from scratch via Insert/Select and you never issue any single row

update
Insert
delete ... where ...

Then having an FSP of 0 will be a boost since all the cylinders will be full as possible and when that table is scanned a minimum number of cylinders will need to be read.

Also remember FSP only affects tables which are bigger than 4 MEG * number of amps. Anything smaller than that doesn't span mulitple cylinders, unless it is SKEWED, in which case you have bigger problems than just FSP.



Now if you have a table that you update, Insert and delete into you may want to consider an FSP based upon how much data you add to the table at a time or between packdisks.


If you want to monitor your FSP of your tables you can run the

SHOWSPACE

command from

FERRET.


Now the SHOWSPACE command actually shows

Cylinder Fill Factor (CFF)

so you have do a little math to see the FSP.

FSP == ( 100 - CFF )

from the Supervisor Window of DBW type

Start Ferret

This will start ferret in one of your application windows.

in the Ferret Window type

scope table "databasename.tablename" 0


this limits the output to just the table you are interested in. then type

showspace


you can capture this information before and after the load and see if adjusting the FSP during the load would help.

Also you might want to compare daily reports to see if a table becomes FLUFFIER ( higher FSP ) during the day. If it does maybe you might want to consider adjusiting the FSP to compensate for the daily load factor....or consider packing just that table more often.

you can limit the SCOPE of the packdisk to just that table by again scoping to just that table.


scope table "databasename.tablename" 0
packdisk

or

scope table "databasename.tablename" 0
packdisk fsp = x

which ever you prefer.

Also remember that if you table is at a FSP=10 (CFF=90), and you specify

packdisk fsp=15

packdisk will actually UNPACK the table ( make it fluffier).

Sometimes this is required to avoid having to run packdisk too often.