Re: Limit of 1050 columns for ANSI joins

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Wed, 7 Jun 2006 20:45:12 -0700
Message-ID: <k3Nhg.11436$KB.8036@fed1read08>

"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:4ep2gtF1fu7j3U1_at_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/

Wow! That's like, so simple. Why didn't I think of that?

I did. Creating crosstabs are only one function that I need. Another function where this view is indespensible is in exporting the data to a flat file with one row per respondent. Not to mention that checkboxes complicate the problem even further because each checkbox answer is a datapoint itself.

Materialized views would be my best bet, but I'm using SE. Instead, I have to 'fake' a materialized view with an actual table and my own updating triggers / jobs. It works and the implementation has stood the test of significant load.

-- 

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

DO NOT REPLY TO THIS EMAIL
Reply only to the group.

Received on Wed Jun 07 2006 - 22:45:12 CDT