Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: maximum number of columns per table

Re: maximum number of columns per table

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 21 Jul 2004 06:26:17 -0700
Message-ID: <1090416401.541921@yasure>


Cris Carampa wrote:

> Frank van Bortel wrote:
>

>>>> 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?

>
>
> As the OP stated, it's not a performance problem, it's a coding problem.
> There's nothing in the database that assures you that, for every
> ATTRNUM, all OBJ_IDs are filled, at least with a NULL value. So turning
> a dataset like this:
>
> ATTRNUM OBJ_ID ATTRVAL
> ------- ------ -------
> 1 1 AFW
> 1 3 DAE
> 2 1 CAL
> 2 2 SOP
> 2 3 MON
> 3 2 COP
>
> ... into a report like this:
>
> +---------+-----------+-----------+-----------+
> | attrnum | obj_id(1) | obj_id(2) | obj_id(3) |
> +---------+-----------+-----------+-----------+
> | 1 | AFW | n/a | DAE |
> | 2 | CAL | SOP | MON |
> | 3 | n/a | COP | n/a |
> +---------+-----------+-----------+-----------+
>
> ... requires more coding than usual. You can't do it using only SQL. I
> believe this contradicts Daniel's principle that developers should be
> focusing on getting the data out of the database.
>
> Kind regards,

But only once. What happens when a new attribute is added?

Better to invest a little effort into coding it correctly in the first place and have everything else simplified for years to come.

Daniel Morgan Received on Wed Jul 21 2004 - 08:26:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US