Re: Limit of 1050 columns for ANSI joins

"Mladen Gogala" <gogala@xxxxxxxxxxxxx> wrote in message
news:BsGhg.133424$F_3.100989@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • Re: I cant find a SETUP.EXE in the SQL Plus Client ??
    ... someone else's server for testing some SELECT statements I'm ... free client because I don't own the Oracle license. ... SQL statements. ... There is no documentation with that download. ...
    (comp.databases.oracle.tools)
  • Re: How to convince my customer to use SQL2005 instaed of Oracle10g
    ... the old Sql versus Oracle debate. ... If I were you I would focus one SQL Server 2005 clearest advantages ... You mentioned Availability, so I assume your looking into HA (high ...
    (microsoft.public.sqlserver)
  • Re: How to convince my customer to use SQL2005 instaed of Oracle10g
    ... migration to Sql 2005 would be direct & straight forward, quick, and ... If I were you I would focus one SQL Server 2005 clearest advantages ... would need a 4 processor license but for Oracle you would need a 16 ... You mentioned Availability, so I assume your looking into HA (high ...
    (microsoft.public.sqlserver)
  • Re: MS SQL Server - a plethora of limitations...
    ... > Most of your objections are that MS-SQL is not Oracle. ... according to the standards. ... > There is no such syntax or concept in Standard SQL. ... Partitioning shouldn't be done automatically by the DB, ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server 2005 vs Oracle
    ... SQL and Oracle. ... SQL Server one sizes log files to handle the largest transaction and its ...
    (comp.databases.ms-sqlserver)