Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 8890 invoked from network); 3 Jun 2007 23:42:37 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 3 Jun 2007 23:42:37 -0000 Received: (qmail 27697 invoked by uid 500); 3 Jun 2007 23:42:40 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 27669 invoked by uid 500); 3 Jun 2007 23:42:39 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 27658 invoked by uid 99); 3 Jun 2007 23:42:39 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 Jun 2007 16:42:39 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [192.18.43.132] (HELO sca-es-mail-1.sun.com) (192.18.43.132) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 03 Jun 2007 16:42:34 -0700 Received: from fe-sfbay-10.sun.com ([192.18.43.129]) by sca-es-mail-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id l53NgBJu015413 for ; Sun, 3 Jun 2007 16:42:13 -0700 (PDT) Received: from conversion-daemon.fe-sfbay-10.sun.com by fe-sfbay-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0JJ300801318CV00@fe-sfbay-10.sun.com> (original mail from Craig.Russell@Sun.COM) for derby-user@db.apache.org; Sun, 03 Jun 2007 16:42:11 -0700 (PDT) Received: from [192.168.0.11] (c-24-6-172-77.hsd1.ca.comcast.net [24.6.172.77]) by fe-sfbay-10.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0JJ30032P36AQA00@fe-sfbay-10.sun.com> for derby-user@db.apache.org; Sun, 03 Jun 2007 16:42:11 -0700 (PDT) Date: Sun, 03 Jun 2007 16:42:09 -0700 From: Craig L Russell Subject: Re: Should I leave nulls in a lookup table? In-reply-to: <0204E0A5-EC63-4CD6-93A3-2BE911136BE2@gmail.com> Sender: Craig.Russell@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 X-Mailer: Apple Mail (2.752.3) Content-type: multipart/signed; protocol="application/pkcs7-signature"; boundary=Apple-Mail-1-743466623; micalg=sha1 References: <588077.171.qm@web31915.mail.mud.yahoo.com> <0204E0A5-EC63-4CD6-93A3-2BE911136BE2@gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org --Apple-Mail-1-743466623 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Jun 3, 2007, at 8:14 AM, Peter Ondruska wrote: > CHAR(1) will be same size no matter what value it holds. But if you allow nulls, then the database has to store the null/not null flag somewhere, thereby increasing the size of the data. Craig > > 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 > Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:Craig.Russell@sun.com P.S. A good JDO? O, Gasp! --Apple-Mail-1-743466623 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw ggJyoAMCAQICECpJVMO68ii+Xfsc1O1YYFIwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA2MTIwOTE5NTEwNVoXDTA3MTIwOTE5NTEw NVowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMNAB4Ih+ShsCt89HzqIdwEx8L4o1UHiY6V7 16mrCedfd4Y0/uI7z9Zam8ysgEh+F7aDnQEKmEsVFN35G4nPMfLU6dZYkvADwUjbq82t/dJ3FDDg Q945nHHpqECZff/S/UMho9AFfj6PZvZBAlDCJAayb4RdKIlfuvPW9YcQStQ1IfVJcVuKnC0Q+tdc a4A7zn7IzLOQohO1lTc3hXSBigEIGiGYn6Ny0wmexfA3X1WsXekFx5czd+M4GjDjswn8CNoBmnBr jOTGK1mOsXR6GSRHnly2s9xTdE4qv9qimM+7C2yzMHbKcszV7OQoLsRsZKDh+6u9wYU+TrjcY4ym bA8CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC MAAwDQYJKoZIhvcNAQEFBQADgYEAU/EpPDztnb55Fz7iGSVm1mYEVj5m2OQKTYG26POUAomCBRrt /CdBBvqYmcHUTpra0qLELHAQadYFl2v11iQkqwF5PPJs19oU/zA0m5qFnOMTAiCvel7IprIwA2r6 eJR9siaPwDRgVJ/Sj71dD+utwf+nRrNy0/7PMNK5y+ocsYQwggM/MIICqKADAgECAgENMA0GCSqG SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+ hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC 3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECECpJVMO68ii+Xfsc1O1YYFIwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDcwNjAzMjM0MjA5WjAjBgkqhkiG9w0B CQQxFgQUpQCbV0AHkK6a/jzReSTQ1pLmJS8wgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAqSVTDuvIovl37HNTtWGBSMIGHBgsqhkiG 9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAq SVTDuvIovl37HNTtWGBSMA0GCSqGSIb3DQEBAQUABIIBAGCosFqG8uk2OXKO7bprslKvCToXQ900 0QTw4Kx4YSIdVTpVy+R+PhQwwYbFai/79AUjdkZDhg3w1ibdlG6N94A7xVv6v1K8G2uRHirZuw8C dpj0KiDrRPtfyAuDuTc3pHJamvY+pJ/z2/H34F9YqJGoOVBeLnNxR/HzHcefvXyMa31L0aaXNYl2 wv6i1Iwxv0IjOTX17B3cyg6xxXgCasA9hwlot+ohtslnQ1BBIfVNU9LWKiuq55YtIux6lQ0LiWZ8 U7RB4WuKH0ll3mBA5IozLUz/+EwL1b5F5kYZrx7lUGOQNkeixRbdAnzdJV0Klm47+fiSiuODVRYr 1GN1p70AAAAAAAA= --Apple-Mail-1-743466623--