Thread: Oracle outer join syntax
Hi. Is there any way (or working solution) to extend PostgreSQL to accept Oracles outer join syntax with '(+)'? Any pointers to docs on how to do this myself are also appreciated. -- Until the next mail..., Stefan.
Stefan, Why would you do that ? If you need a solution which works both on postgres and oracle, use the standard outer join syntax supported by postgres - it is supported by the latest oracle versions too. We do this here successfully... Cheers, Csaba. On Wed, 2006-04-05 at 14:46, Stefan Nobis wrote: > Hi. > > Is there any way (or working solution) to extend PostgreSQL to accept > Oracles outer join syntax with '(+)'? > > Any pointers to docs on how to do this myself are also appreciated.
Csaba Nagy <nagy@ecircle-ag.com> writes: > Why would you do that ? If you need a solution which works both on > postgres and oracle, use the standard outer join syntax supported by > postgres - it is supported by the latest oracle versions too. Because there are tons of legacy code I'd have to convert and until now the Oracle server is still Oracle8. :( -- Until the next mail..., Stefan.
Attachment
Csaba Nagy <nagy@ecircle-ag.com> writes: > Why would you do that ? If you need a solution which works both on > postgres and oracle, use the standard outer join syntax supported by > postgres - it is supported by the latest oracle versions too. BTW: You have to use the cost based optimizer (new syntax with rule based optimizer yields really bad performance). And our DBA really don't like statistics... :( -- Until the next mail..., Stefan.
Attachment
> Because there are tons of legacy code I'd have to convert and until > now the Oracle server is still Oracle8. :( I would think that patching postgres will be no less challenging than fixing the legacy code... unless you really have a few tons of it or you simply can't do it (no access for e.g.). But in any case, you will NOT be able to simply use you're legacy code designed for Oracle with postgres. You will have to rewrite some parts of it... we do support now both postgres and Oracle, and I have to say it took quite some time and a few nasty surprises until everything was stable on postgres. Just making it work will not make it work fast... postgres is a different beast, needs different tuning. Cheers, Csaba.
> BTW: You have to use the cost based optimizer (new syntax with rule > based optimizer yields really bad performance). And our DBA really > don't like statistics... :( This is kind of off topic on this list, but there are ways to force Oracle to use specific plans by carefully hinting it. And the bad news for your DBA: I think Oracle wants to deprecate the rule based optimizer completely in the future (I'm not the one who cares about Oracle here, so I won't know this for sure, but I overheard some Oracle DBA head-banging here). So better get used to the statistics... Cheers, Csaba.
Stefan Nobis <stefan-ml@snobis.de> writes:
> [ still on Oracle8 ]
> BTW: You have to use the cost based optimizer (new syntax with rule
> based optimizer yields really bad performance). And our DBA really
> don't like statistics... :(
Sounds like both your database and your DBA are dinosaurs ;-).
If you can't get your DBA to update from Oracle 8 to modern Oracle,
you've got zip chance of persuading him to deal with Postgres,
so I think you're wasting your time worrying about left-join syntax.
After you waste a man-year or so on that, he'll just find something
else to complain about.
regards, tom lane
On Wed, 2006-04-05 at 14:46 +0200, Stefan Nobis wrote: > Is there any way (or working solution) to extend PostgreSQL to accept > Oracles outer join syntax with '(+)'? Not AFAIK, and there are no plans to add support that I'm aware of. EnterpriseDB claim to have pretty good Oracle compatibility, so I'd imagine they support this syntax, although their online documentation doesn't mention it -- www.enterprisedb.com -Neil
On Wed, 2006-04-05 at 12:19 -0400, Neil Conway wrote: > On Wed, 2006-04-05 at 14:46 +0200, Stefan Nobis wrote: > > Is there any way (or working solution) to extend PostgreSQL to accept > > Oracles outer join syntax with '(+)'? > > Not AFAIK, and there are no plans to add support that I'm aware of. > EnterpriseDB claim to have pretty good Oracle compatibility, so I'd > imagine they support this syntax, although their online documentation > doesn't mention it -- www.enterprisedb.com Yes, EnterpriseDB supports this syntax and other similar Oracle-isms, as well as full PostgreSQL/ANSI syntax. This type of compatibility is aimed specifically at those people who want to greatly reduce the cost of migrating application code from Oracle to PostgreSQL. Best Regards, Simon Riggs
Csaba Nagy <nagy@ecircle-ag.com> writes: > both postgres and Oracle, and I have to say it took quite some time > and a few nasty surprises until everything was stable Thank you very much for your comments. -- Until the next mail..., Stefan.
Attachment
Tom Lane <tgl@sss.pgh.pa.us> writes: > Sounds like both your database and your DBA are dinosaurs ;-). Yes. > If you can't get your DBA to update from Oracle 8 to modern Oracle, > you've got zip chance of persuading him to deal with Postgres, Postgres is for use with an extern customer, so the client have to be able to deal with Oracle8, Jet, and (to be added real soon now :)) PostgreSQL (I hope Jet will be dropped in favour of PostgreSQL). Thanks for your comments anyway. -- Until the next mail..., Stefan.