Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Limit of 1050 columns for ANSI joins

Re: Limit of 1050 columns for ANSI joins

From: Serge Rielau <>
Date: Wed, 07 Jun 2006 18:33:20 -0400
Message-ID: <>

Andreas Sheriff wrote:

> "joel garry" <> wrote in message

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


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.


Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Original text of this message