Re: Limit of 1050 columns for ANSI joins

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