Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Limit of 1050 columns for ANSI joins

Re: Limit of 1050 columns for ANSI joins

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Wed, 7 Jun 2006 12:23:11 -0700
Message-ID: <hKFhg.11417$KB.4830@fed1read08>


"hpuxrac" <johnbhurley_at_sbcglobal.net> wrote in message news:1149700083.799152.81830_at_h76g2000cwa.googlegroups.com...
>
> 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.
Received on Wed Jun 07 2006 - 14:23:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US