Re: Limit of 1050 columns for ANSI joins

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Wed, 07 Jun 2006 18:33:20 -0400
Message-ID: <4ep2gtF1fu7j3U1@individual.net>

Andreas Sheriff wrote:

> "joel garry" <joel-garry_at_home.com> wrote in message
> news:1149717213.193786.227110_at_f6g2000cwb.googlegroups.com...

>> Andreas Sheriff wrote:
>>
>>> The app is an online survey management system and it stores all

> responses in


>>> one table:

>>>

>>> questionresponses:

>>> ...

>>> takerid

>>> questionid

>>> choiceid

>>> choicetext

>>> ...

>>>
>>> Many self joins have to be made to do online reporting and exporting

> data


>>> from this table; hence the magnificent ANSI join.

>>> Materialized views may help, but I'm using Standard Edition.

>>>
>>> One solution that I proposed was to create (a) separate table(s) for

> each


>>> survey's responses, with each column being a response.  Sure, it's DDL

>>> everytime someone creates a survey, but that doesn't happen as often as

>>> someone taking a survey or doing online analysis.  Separate tables would

>>> eliminate the need for magnificent ANSI self joins and speed up the

>>> application.

>>>

>>> --

>>>

>>> Andreas Sheriff

>>> Oracle 9i Certified Professional

>>> Oracle 10g Certified Professional

>>> Oracle 9i Certified PL/SQL Developer

>>> ----

>>> "If you don't eat your meat, you cannot have any pudding.
>>> "How can you have any pudding, if you don't eat your meat?"
>> Andreas, if you don't start normalizing, we'll send you to your room
>> with no supper at all!
>>
>> jg
>> --
>> @home.com is bogus.
>> Witness the man who raves at the wall
>> Making the shape of his question to heaven
>> Whether the sun will fall in the evening
>> Will he remember the lesson of giving
>> Set the controls for the heart of the sun
>> The heart of the sun
>> The heart of the sun
>>

> 
> Silly,
> 
> The tables are normalized.
> 
> The dynamic nature of a survey prevents standard normalization for the
> responses, though.
> 

Andreas,

By the looks of it what you are doing here is pivoting the table. That is each row in your table represents a column in your result set, correct?
Instead of doing self joins you could use group by with MAX and the case-expression (or decode for the lazy typer):

SELECT MAX(CASE WHEN property = 1 THEN c END) AS c1,

        MAX(CASE WHEN property = 2 THEN c END) AS c2,
        ....

GROUP BY userid

That should be orders of magnitudes faster.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Received on Wed Jun 07 2006 - 17:33:20 CDT