Re: Do columns with Null data take up space?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sun, 05 Dec 2004 11:05:26 -0800
Message-ID: <1102273424.872333@yasure>

Alan wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1102128189.447748_at_yasure...
> 

>>Doug wrote:
>>
>>
>>>If I create a table with 250 columns, and only fill 8, do the other
>>>columns take up space? (My user wants to log tests. There can be as
>>>many at 250 tests in a set, but normally only about 8. My user wants
>>>one record per set so he can run some third party analytical software
>>>against the test results.) Is Oracle 8i smart enough to compress the
>>>null values down so they don't take up space if they are unused?
>>
>>If you create a table with more than four columns you are making a
>>design mistake ... think vertically ... not horizontally.
>>
>>CREATE TABLE test (
>>test_id NUMBER(10),
>>person_id NUMBER(10),
>>question_id NUMBER(3),
>>result VARCHAR2(20));
>>
>>And then you don't have sparse data to worry about either.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)

> 
> 
> Dan,
> 
> You may want to clarify that you mean four columns in a table for this
> particular situation (test results/survey).  The OP may take it as a general
> guideline as it is written now.

If clarification is required then absolutely my advice is specific to the question of 250 columns holding results when two columns would suffice. Just the thought of writing a SQL statement looking for results from 150 of those columns gives me a chill whereas with a relational design one would only need write:

WHERE question_id BETWEEN 1 and 150.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sun Dec 05 2004 - 13:05:26 CST