Re: Limit of 1050 columns for ANSI joins

"hpuxrac" <johnbhurley@xxxxxxxxxxxxx> wrote in message
news:1149700083.799152.81830@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Mark D Powell wrote:
Mladen Gogala wrote:
On Wed, 07 Jun 2006 05:06:33 -0700, Andreas Sheriff wrote:

Yeah, yeah, so I have 1095 columns (For online surveys, those data
points
really add up, and this is a small survey...), that doesn't mean I
should be
limited, should I?
Can anyone find a reference in the Oracle documentation that states
1050 as
a column limit for ANSI joins? I see a 1000 column limit for
tables, but it
doesn't say anything about views or ANSI joins.

Then don't use ANSI joins. Oracle has the corresponding syntax, which
is
more logical and causes less problems with performance and
optimization
then ANSI joins. Personally, I find ANSI joins clumsy and plain
stupid.
ANSI joins provide the illusion that your SQL is, somehow, portable.
Oracle probably supported ANSI joins just because everybody else did,
but
those monstrosities break the spirit of SQL. Tedd Codd probably died
when
he saw ANSI join syntax being called SQL. SQL is a language that was
modeled after naive set theory, which means that it provides criteria
for
selecting various elements from the given set. Unfortunately,
developers
are somehow entranced by this idiotic construct and still prefer it
over
the nice logical Oracle syntax.

--
http://www.mgogala.com

I thought the Oracle limit of 1000 columns in a table also applied to a
view:

Maybe he doesn't think views are based on tables?


I don't know why I write anymore. On the good stuff, you guys are no help.

There are a few points that I'd like to clear up.

Point 1: Don't assume a limit of 1000 columns per view if it hasn't been
tested and / or written in documentation. A view, unlike a table, doesn't
have a corresponding segment and in fact, can be based on multiple tables
and / or other views.

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. Portability has nothing to do with it.
We're sticking with Oracle.

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. 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).

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.

Point 3: I wasn't clear about the selected columns. I'm only selecting 273
columns, but in the ANSI join clause iteself, 1095 columns are being used in
the join.

Note: I've even pruned the number of columns in the join by specifically
choosing which columns I wish to use in the join.
Example: In individual left outer join clauses I use "(select
parent_tableid, col1, col2, col3 from table1) table1".

Disclaimer: I'm not responsible for any typos, gramatical and spelling
errors errors, assumtions made about Oracle, or any other faux pas used in
this post. So don't flame me for that! :-D Delve deeper to find a
solution for the original problem.

I'm anxious to hear what you think.

--

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: Limit of 1050 columns for ANSI joins
    ... Can anyone find a reference in the Oracle documentation that states 1050 as ... doesn't say anything about views or ANSI joins. ... Oracle has the corresponding syntax, ... ANSI joins provide the illusion that your SQL is, somehow, portable. ...
    (comp.databases.oracle.server)
  • Re: Limit of 1050 columns for ANSI joins
    ... Can anyone find a reference in the Oracle documentation that states 1050 as ... I see a 1000 column limit for tables, ... doesn't say anything about views or ANSI joins. ... ANSI joins provide the illusion that your SQL is, somehow, portable. ...
    (comp.databases.oracle.server)
  • Re: Limit of 1050 columns for ANSI joins
    ... Can anyone find a reference in the Oracle documentation that states ... Then don't use ANSI joins. ... Oracle has the corresponding syntax, ... Specify a subquery that identifies columns and rows of the table ...
    (comp.databases.oracle.server)
  • Re: Limit of 1050 columns for ANSI joins
    ... Can anyone find a reference in the Oracle documentation that states 1050 as ... doesn't say anything about views or ANSI joins. ... Oracle has the corresponding syntax, ... ANSI joins provide the illusion that your SQL is, somehow, portable. ...
    (comp.databases.oracle.server)