Re: maximum number of columns per table

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Wed, 21 Jul 2004 11:36:04 +0200
Message-ID: <cdld3f$ed3$1@news1.tilbu1.nb.home.nl>

Jeremy wrote:

> In article <1090377455.197311_at_yasure>, Daniel Morgan says...
> 

>>Jeremy wrote:
>>
>>
>>>In article <1090301352.683762_at_yasure>, Daniel Morgan says...
>>>
>>>
>>>>There is just about no excuse for having more than 50 columns in a table.
>>>>
>>>>
>>>
>>>
>>>Why is that then? If I have an object that has (say) 80 attributes how
>>>would you suggest that it should be modeled? I am looking at a table
>>>here with 193 columns and, whilst it appears ungainly, it reflects the
>>>need.
>>
>>I'd have to know more and as I said to Galen "just about" means it is
>>not a hard and fast rule.
>>
>>But to answer your question:
>>
>>CREATE TABLE parent (
>>obj_id NUMBER,
>>obj_name VARCHAR2(30));
>>
>>CREATE TABLE attribute (
>>obj_id NUMBER,
>>attrnum NUMBER(2),
>>attrval VARCHAR2(10));
>>
>>Is one way to approach the problem.

> 
> 
> I understand your approach. But what about the overhead of reading say 
> 150 rows every time when you only wanted one 'logical' row? Sure, in 
> some cases you don't need all 150 columns - but even then, the need to 
> code the multiple reads instead of a single 'select *' adds complexity 
> to the code.
> 
> 

Start reading Oracle Concepts guide, Tuning Guide, etc. There's no overhead and Oracle will probably read the 150 rows in 1 IO operation anyway:
select * from your_1200_columns_table where id = <some unique_number>;

select p.* from parent p, attribute a
where p.obj_id = <some unique_number>
and p.obj_id = a.obj_id;

What's the problem?

-- 

Regards,
Frank van Bortel

Received on Wed Jul 21 2004 - 04:36:04 CDT