|
Home > Archive > PostgreSQL Discussion > April 2006 > Oracle outer join syntax
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Oracle outer join syntax
|
|
| Stefan Nobis 2006-04-05, 9:30 am |
| 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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Csaba Nagy 2006-04-05, 9:30 am |
| 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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Stefan Nobis 2006-04-05, 11:31 am |
| | |
| Stefan Nobis 2006-04-05, 11:31 am |
| | |
| Csaba Nagy 2006-04-05, 11:31 am |
| > 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Csaba Nagy 2006-04-05, 11:31 am |
| > 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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Tom Lane 2006-04-05, 11:31 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Neil Conway 2006-04-05, 11:31 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Simon Riggs 2006-04-05, 8:25 pm |
| On Wed, 2006-04-05 at 12:19 -0400, Neil Conway wrote:
> On Wed, 2006-04-05 at 14:46 +0200, Stefan Nobis wrote:
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Stefan Nobis 2006-04-05, 8:25 pm |
| | |
| Stefan Nobis 2006-04-05, 8:25 pm |
| |
|
|
|
|