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.
Show quoted text
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.
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