Re: left outer join

rownum< N is not the same as top N. try a statement that has an order by for example.
Toad is nice but you also have tkprof and explain plan from Oracle(comes with the db)
Danial is correct. Seen too many MSSQL conversions and not enough thought about how both RDBMS's are different (concurrency and transaction models are very different for example, MSSQL developers often uses a lot of temp tables, rare to use them in Oracle development. sequences vs auto id fields, ref cursor etc.
Jim

"Andre Grosse" <ag17_at_inf.tu-dresden.de> wrote in message news:3C451945.4010904_at_inf.tu-dresden.de...
> 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
>
Received on Wed Jan 16 2002 - 00:42:44 CST