Re: SQL without end

romanhodain_at_email.cz wrote:
> I know that this is very much OUTER JOIN bad I don't know why Oracle
> can't generate execution plan? Is oacle use 100% CPU to generate
> execution plan in fact? This SQL run about 14 hours. On mssql is this
> SQL complet in 5 sec. Is possible that this is a bug?

Anything is possible but you've provided no metrics. No version number, no operating system, no information on hardware resources available and usage. So while it "could" be a bug it is very premature to jump that direction.

I would suggest the following:

Dump the ANSI syntax and start by building a small SQL statement that contains the query's basics. Wrap it in parentheses (an inline view) and join it with a second query. Wrap that in parentheses and continue on including more and more of this until it either works or breaks.

Not Czech Republic language I can't comment on your business logic but my inclination is to agree with Sybrand that this problem should be fixed by coming up with a proper design. I have never seen anything in Oracle requiring this kind of logic and suspect much of your problem may relate to trying to kludge Oracle into being another company's product.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org