Re: Help with my sql load performance

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 17 Apr 2007 08:13:51 -0700
Message-ID: <1176822822.925471@bubbleator.drizzle.com>

Jeremy wrote:

> In article <1176582540.138302_at_bubbleator.drizzle.com>, DA Morgan says...

>> Jeremy wrote:
>>> In article <1176479781.740881_at_bubbleator.drizzle.com>, DA Morgan says...
>>>> Again ... a table with 200 columns is a strong indication of a bad
>>>> design.
>>>>
>>> If all the attributes logically belong to the same PK, why is it
>>> indicative of a bad design?
>> The following also relate to the same PK.
>>
>> CREATE TABLE bad_design (
>> QID NUMBER(5),
>> Q1 VARCHAR2(1),
>> Q2 VARCHAR2(1),
>> Q3 VARCHAR2(1),
>> Q4 VARCHAR2(1),
>> Q5 VARCHAR2(1),
>> Q6 VARCHAR2(1),
>> Q7 VARCHAR2(1),
>> ...
>> Q199 VARCHAR2(1));
>>
>> If you don't see anything wrong with your argument let me know.
>>

> 
> Hi Daniel, first let's be clear: I haven't presented an argument, merely 
> a question. I don't understand your intended purpose of the table you 
> have suggested above. What I am asking is why a high number of columns 
> in a table is indicative of bad design. Genuine question.

Because experience has taught us that it is extremely unusual for a table in a well designed schema to contain that many columns. When I see a table with more than 25 columns I start asking questions.

That doesn't mean there is some magic number and that 200 is by definition bad. But it is certainly indicative of a problem.

The above example was only put there to illustrate that the argument: "they relate to the same PK" is not a valid one by itself. It may be true and it may be valid. But by itself it is not enough. Often what we see when we see large numbers of columns is that a large percentage are null and always will be.

Without the DDL and an understanding of what you are doing neither I, nor anyone else, can say what was done is a problem. But it is a clear warning to look with care.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Apr 17 2007 - 10:13:51 CDT