Re: Limit of 1050 columns for ANSI joins

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Thu, 8 Jun 2006 18:59:57 -0700
Message-ID: <WE4ig.12600$KB.6059@fed1read08>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tptg82lf8br5rh8n7kra9g7js890rvvl4c_at_4ax.com...
> On Wed, 7 Jun 2006 14:06:42 -0700, "Andreas Sheriff"
> <spamcontrol_at_iion.com> wrote:
>
>>One solution that I proposed was to create (a) separate table(s) for each
>>survey's responses, with each column being a response.
>
>
> I have seen a different approach in operation
> each record was one question, each column was a respondent. As the
> number of choices was usually below 16, it was possible to store 2
> respondents in a column, by using bit-masking.
> Worked quite nicely!
>
> --
> Sybrand Bakker, Senior Oracle DBA

I've thought of that too (offline crosstabs, freq, etc using bit masks on a multiprocessing 64 bit machine is sweet), but the nature of the surveys we deploy are complex, involved, and evolving. The questions can range anywhere between 10 to 300, the choices anywhere between 2 to 200, the respondents can range from 75 to 10,000 and the data points... Well, I don't want to talk about that. I have nightmares. "Checkboxes and grid checkboxes! Aargh!"

PS:

For the above, I meant to say:
which each row being a respondent and the columns represent the responses to respective questions.

Ex:

create table
survey_${surveyid} (

    surveyid surveys.surveyid%type constraint pk_survey${surveyid},     respondentid respondents.respondentid%type constraint resp_${surveyid} references respondents(respondentid),

    Q01 number constraint s${surveyid}_Q01_c references choices(choiceid),     Q01_text varchar2,
    Q02 number constraint s${surveyid}_Q02_c references choices(choiceid),     Q03 number constraint s${surveyid}_Q03_c references choices(choiceid),     Q04_1 number constraint s${surveyid}_Q04_1_c references choices(choiceid),

    Q04_2 number constraint s${surveyid}_Q04_2_c references choices(choiceid),,

    Q04_3 number constraint s${surveyid}_Q04_3_c references choices(choiceid),,

    Q04_4 number constraint s${surveyid}_Q04_4_c references choices(choiceid),,

    Q04_5 number constraint s${surveyid}_Q04_5_c references choices(choiceid),,

    Q04_5_text,
    Q06 constraint s${surveyid}_Q06_c references choices(choiceid), ...
)

and store this table in a 32k tablespace.

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

If this is the first email to me,
please place NOSPAM somewhere
in the subject. Received on Thu Jun 08 2006 - 20:59:57 CDT