Re: left outer join

Daniel A. Morgan wrote:

> Actually ... if you were to Explain Plan the queries ... you would find
> out that the join you are trying is substantially less efficient. And,
> in fact, in almost all cases in Oracle an inner join is less efficient
> than its restatement using EXISTS.
>

sorry, but we have a quick-and-dirty-project with thousends of queries based on mssql, the customer defintively wants oracle...we cannot rewrite all that statements yet.
i rewrited a few statements, so they are understood with both databases and the rest of the statements are automaticely translated to oracle-syntax if necessary.

> You don't need a left outer join ... you need the right answer using the
> most efficient optimizer plan. If you try to do SQL Server in Oracle all
> you will do is write really lame code and bring the database to its
> knees. In fact it can get so bad it will start behaving like SQL Server.
>

i will see what i can do, thanks so far. are there any free tools that can analyze/optimize sql-statements in oracle?

> BTW The normal outer join in Oracle uses the (+) as in
>
> select t1.bla, t2.blub
> from table1 t1 table2 t2
> where t1.bla = t2.hihi (+)
>
> And I suspect for your usage that you really don't understand rownum in
> Oracle either. Be sure you do before you use it. There is no rownum < 1.
>

sure there is no rownum < 1, but i use an automatic mssql to oracle translator, i.e. replacing "top n" with "rownum <= n", works for me thus far...that there is no rownum = 0 is a known fact, but rownum = 1 or <= 1 is equivalent in this case.

-- 
Andre Grosse