re:Too Many Full Outer Joins??? PL/SQL: ORA-00918: column ambiguously defined

From: AndrewRusling <andrew_rusling_at_technologyonecorp-dot-com.no-spam.invalid>
Date: 06 Oct 2005 15:06:38 GMT
Message-ID: <43453d7d$0$56747$892e7fe2@authen.white.readfreenews.net>

I recently had a problem very simliar to this. I had an sql statment that involved 17 joins. Some Inner joins, some Left Outer joins. The statement operated successfully in a SQLServer environment, but gave me an ORA-00918 error in Oracle 10 environment.

A colleague explained to me that SQL reorders the join happily, while Oracle tries all of the possible join options, hits its limit of permuations and gives me the ORA-00918 error. (Not very helpful)

I found two solutions to my problem:

Move my left outer joins to the top of my list of joins. I guess that this makes the selection process easier.
One of my joins had some sql that I was able to move to the WHERE clause.

Either solution would work. However doing both and moving my smaller tables to the top of the join list, resulted in faster operation of my query. Received on Thu Oct 06 2005 - 10:06:38 CDT