db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peter Ondruska <peter.ondru...@gmail.com>
Subject Re: Should I leave nulls in a lookup table?
Date Sun, 03 Jun 2007 15:14:11 GMT
CHAR(1) will be same size no matter what value it holds.

On 3.6.2007, at 14:17, Leslie Software wrote:

> I have a lookup table that right now stores either 'T' or 'F' in  
> columns that are char(1).  Eventually this table will have over 70  
> columns and approach 8000 rows.  Right now for my testing I have  
> about 600 rows and four columns.
>
> Today it occurred to me that I do not need to store any 'F'  
> characters just leaving nulls would be fine and I wondered if that  
> would save me some storage space.  So I changed my table creation  
> and loading and found that my database size actually grew.  I have  
> seen changes in my database that should have shrunk its size  
> (deletion of rows) actually make it bigger so I am unsure if this  
> growth is because storing only 'T' and leaving nulls is actually  
> more expensive or not.
>
> An example of my original table creation statement (for storing  
> both 'T' and 'F'):
> create table ability_lookup (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT  
> ability_lookup_pk PRIMARY KEY,
> card_id INT NOT NULL UNIQUE,
> cantrip CHAR (1) DEFAULT 'F' NOT NULL CONSTRAINT cantrip_boolean_ck  
> CHECK (cantrip = 'T' or cantrip = 'F'),
> pumpable_offence CHAR (1) DEFAULT 'F' NOT NULL CONSTRAINT  
> pumpable_offence_boolean_ck CHECK (pumpable_offence = 'T' or  
> pumpable_offence = 'F'),
> pumpable_defense CHAR (1) DEFAULT 'F' NOT NULL CONSTRAINT  
> pumpable_defense_boolean_ck CHECK (pumpable_defense = 'T' or  
> pumpable_defense = 'F'),
> discard CHAR (1) DEFAULT 'F' NOT NULL CONSTRAINT discard_boolean_ck  
> CHECK (discard = 'T' or discard = 'F')
> )
>
> An example of my original table creation statement (for storing  
> just 'T'):
>
> create table ability_lookup (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT  
> ability_lookup_pk PRIMARY KEY,
> card_id INT NOT NULL UNIQUE,
> cantrip CHAR (1) DEFAULT NULL CONSTRAINT cantrip_boolean_ck CHECK  
> (cantrip = 'T' or cantrip IS NULL),
> pumpable_offence CHAR (1) DEFAULT NULL CONSTRAINT  
> pumpable_offence_boolean_ck CHECK (pumpable_offence = 'T' or  
> pumpable_offence IS NULL),
> pumpable_defense CHAR (1) DEFAULT NULL CONSTRAINT  
> pumpable_defense_boolean_ck CHECK (pumpable_defense = 'T' or  
> pumpable_defense IS NULL),
> discard CHAR (1) DEFAULT NULL CONSTRAINT discard_boolean_ck CHECK  
> (discard = 'T' or discard IS NULL)
> )
>
> The field card_id is for joining with a card table which has about  
> 8000 rows in it right now.
>
> So my question is: is storing only 'T' and leaving nulls any less  
> expensive than storing both 'T' and 'F' in a lookup table?
>
> Ian
>
> Note: After making changes and before creating my read-only copy that
> goes with my application I perform SYSCS_UTIL.SYSCS_COMPRESS_TABLE for
> each table:
>
> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'ABILITY_LOOKUP', 1);
>
> and ensure that the database has been shut down with the
> "shutdown=true" string and restarted to allow any clean up work to be
> done.  Performing these actions greatly reduced the size of the  
> final database after I have made changes to its content.
>
>
> --
>
> Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
>
>
>
>
>
>       Get news delivered with the All new Yahoo! Mail.  Enjoy RSS  
> feeds right on your Mail page. Start today at http:// 
> mrd.mail.yahoo.com/try_beta?.intl=ca


Mime
View raw message