Re: Limit of 1050 columns for ANSI joins

"Mladen Gogala" <gogala_at_sbcglobal.net> wrote in message news:BsGhg.133424$F_3.100989_at_newssvr29.news.prodigy.net...
> Andreas Sheriff wrote:
>
> > I don't know why I write anymore. On the good stuff, you guys are no
help.
>
> Now, this is a metaphysical question, I hope?
>
> > Point 2: I wouldn't say ANSI joins are *idiotic*. Sure, they're
different
> > and throw you for a loop in interviews, but speaking from the
perspective of
> > a programmer, auto-generating ANSI joins is much simpler and more
> > comprehensive than Oracle SQL.
>
> I'm trying not to have any auto-generated SQL on my system, as long as
> those SQL statements are not generated with auto-tuner as well. Lemme
> guess: you are using an object relational mapper? Proper object
> orientation, with object factories and alike? Objects factory will
> generate objects by dynamically creating the underlying query, thus
> causing infinite joy and fun for the DBA who has to live with all that
> hard parsing, library cache latch waits and other goodies. Have you ever
> considered adding RAC to the lot, that would really make poor sod's life
> interesting? I'm not a RACist, but I've seen global cache (dead)locks
> bring an instance to the stable state of nirvana.
>
>
> > Example: It's much easier to code a program to auto-generate:
> >
> > select [column_list]
> > from parent_table
> > left outer join table 1 on [ criteria1 ]
> > left outer join table 2 on [ criteria2 ]
> > left outer join table 3 on [ criteria3 ]
> > left outer join table 4 on [ criteria4 ]
> > ....
> > where
> > [morecreteria]
> > ...
> >
> > Than to auto-generate:
> >
> > select [ column_list ]
> > from
> > parent_table,
> > table1,
> > table2,
> > table3,
> > table4,
> > ...
> > where
> > parent_table.parenttableid = table1.parenttableid (+) and
> > parent_table.parenttableid = table2.parenttableid (+) and
> > parent_table.parenttableid = table3.parenttableid (+) and
> > parent_table.parenttableid = table4.parenttableid (+) and
> > ...
> >
> > And if you don't get that writing a program to generate ANSI joins is
easier
> > than writing a program to generate Oracle SQL, you should try writing
one
> > sometime.
>
>
> Me thinks not. Auto-generated SQL is not for me. Object orientation is
> nice as a tool, but as a religion is debilitating. I am not trying to
> build a bridge between OO world and RDBMS world, my goal is usually to
> utilize an expensive commodity like an Oracle database as efficiently
> as possible. I've never seen an optimal application based on the OO
> religion. There are rules of application design and here are some that
> I find useful:
>
> 1) Dynamic space management is bad and must be avoided at all costs.
> 2) Dynamic SQL is bad, strains the machine immensely and should be
> avoided.
> 3) Business rules should be implemented as triggers and procedures,
> their proper place is in the database, not in the application.
> 4) Adding application servers is not synonymous with scaling.
> Applications that count on scaling by adding another application
> server are wasting company money. One machine with 10 CPU's, 10GB
> of RAM and 1TB storage is cheaper then 10 machines with 1GB RAM each,
> one CPU and 100MB disk storage each.
> 5) Thou shalt always lock resources in the same order, else deadlock
> will be thy punishment.
> 6) Index your schema properly and think about the proper design. Having
> creation/modification date and username as columns in every table
> will ease pain and suffering when extracting parts of the table,
> logging, auditing and alike.
> 7) Sqlplus is not a reporting tool.
>
>
>
> Also, don't forget, this is a massive join for OLAP, so we're
> > talking about 201+ tables (1 parent table and the rest are the same
table
> > joined to the parent table. It takes a ridiculously long time to parse,
but
> > it has to be done this way because of the original application design).
>
> Have you ever considered changing the design or shooting the application
> designer?
>
> >
> > I must concede, however, that I find ANSI joins to be slower than Oracle
> > SQL, but for large, complex joins, in my honest opinion, I find it
easier
> > and more comprehensive to write ANSI joins instead of Oracle SQL.
>
> That is why I'm a DBA: my taste prevails.
>
>
> --
> Mladen Gogala
> http://www.mgogala.com

I have petitioned to have the application rewritten, but that's months down the road.

BTW, it's not based on OOP. :-D

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

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