Re: maximum number of columns per table

From: Cris Carampa <cris119_at_operamail.com>
Date: Wed, 21 Jul 2004 14:33:33 +0200
Message-ID: <cdlnrq$rh7$1@lacerta.tiscalinet.it>

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,

-- 
Cris Carampa (spamto:cris119_at_operamail.com)

What once seemed black and white
turns to so many shades of gray...

Received on Wed Jul 21 2004 - 07:33:33 CDT