The Heap™ – Consultancy ©®

2:01 AM

2

In my experience, greenplum is similar enough to postgresql that many questions will have answers that are equally applicable to both. Yes, there is a risk that someone might ask a question whose answer hangs on a significant difference, and I have done that - posted a question that I did not kn...

ypercubeᵀᴹ

@PaulWhite too much energy lost discussing a non-issue, in my opinion.

Paul White

That sometimes happens on meta.

Still, it's a valid question (and answers) so people should vote.

I thought it was worth posting here because of the new contribution by someone who actually uses the product in question.

ypercubeᵀᴹ

2:21 AM

@JackDouglas I added the postgresql tag back, as I can reproduce this in 9.6 — ypercubeᵀᴹ 2 mins ago

One of the questions that was tagged with both

Paul White

I hope Jack edits his answer because I am minded to change my vote.

I feel well qualified to do so now that I have written a PG query :)

@ypercubeᵀᴹ Did you see my comment?

I'm not nagging, just making sure you've had the opportunity before I CW answer from comments.

ypercubeᵀᴹ

@PaulWhite Yes, I've seen it but didn't have much time today.

I might have tomorrow night. I'm off to sleep now

and on a trip all day tomorrow

Add a CW if you like.

Or a normal answer ;)

PG queries, MySQL config tips, you are sliding away from MS ...

How big was that earhquake really? ;)

Paul White

Ha! Excellent point.

Joe Obbish

2:37 AM

maybe you can solve my oracle issue

Paul White

Funny :)

Paul White

2:56 AM

The only Oracle problem I could solve would be what to do with all that money.

Send it to me instead!

Joe Obbish

the listed prices do seem a bit unbelievable

in any case it's more or less an unsolvable problem. their join elimination game is weak

any multi column join won't be eliminated even if the table isn't needed in the query

Paul White

Interesting. Feel free to expand. I won't reply for a while because I need to do the washing up, but will read it.

Joe Obbish

It's nothing too interesting, except that your comment about switching vendors is arguably the solution

take something like this

dbfiddle.uk/…

SQL Server will eliminate T2 from the query plan but Oracle won't

Apparently Oracle added join elimination in 10g but it only worked for non-ANSI joins. ANSI join support was added in 11g

Makes me wonder if they've been able to unify that codebase. I would naively think that non-ANSI joins or ANSI joins would get compiled into the same thing behind the scenes. Guess not?

Paul White

3:24 AM

That does seem odd. And did they really only add JOIN...ON syntax in 11g?!

@JoeObbish Oh I wasn't suggesting changing vendors, I was suggesting a donation :)

Joe Obbish

@PaulWhite No, they only added join elimination support for ANSI joins in 11g

ansi joins were introduced in 9i it looks like

2001

Paul White

OIC thanks.

Joe Obbish

my donation will be sending viewers to your blog posts

that counts, right?

I'm out, have a good evening

Paul White

@JoeObbish Oh btw, SQL Server has its own issues with simplification and multi-column FKs see example

And yes it counts :)

Toward my journey to Authority.

Jack Douglas

8:20 AM

@McNets please do — you can answer the meta question with a feature suggestion.

@PaulWhite I've voted for Phil's answer. As @ypercubeᵀᴹ says it's a bit of a non-issue — it doesn't really matter either way.

Jack Douglas

8:50 AM

@JoeObbish Join elimination seems the same with either join style (works for single column but not for multi-column): dbfiddle.uk/… dbfiddle.uk/…, is that what you'd expect?

ypercubeᵀᴹ

9:20 AM

@JoeObbish I would guess not. Non-ANSI Oracle outer joins have different limitations than ANSI outer joins.

Philᵀᴹ

10:36 AM

@JackDouglas which answer? 🤔

ypercubeᵀᴹ

10:55 AM

@Philᵀᴹ it's another Phil

Paul White

11:16 AM

One without a ᵀᴹ

Philᵀᴹ

Someone not obeying the trademark?

Paul White

9 hours ago, by Paul White

2

A: Policy on PostgreSQL spin-offs

In my experience, greenplum is similar enough to postgresql that many questions will have answers that are equally applicable to both. Yes, there is a risk that someone might ask a question whose answer hangs on a significant difference, and I have done that - posted a question that I did not kn...

Joe Obbish

4:31 PM

@PaulWhite I would be surprised if any platform did it perfectly. However, SQL Server does eliminate the table in my simple example

@JackDouglas Yeah, that was that I expected, thanks. The blog post that I read suggested as much

@ypercubeᵀᴹ Interesting, I didn't know that. Is it just a subset of the functionality of ANSI joins?

That's the impression I got from reading a few posts

Philᵀᴹ

5:53 PM

@JoeObbish Oracle didn't initially implement ANSI joins. ANSI joins are implemented in Oracle by rewriting the SQL to the "old" syntax during parsing

Jack Douglas

6:29 PM

15 hours ago, by Joe Obbish

Apparently Oracle added join elimination in 10g but it only worked for non-ANSI joins. ANSI join support was added in 11g

@Philᵀᴹ does that mean this isn't accurate? ^^^

.

I'm probably opening a can of worms here, but I've added graphs to dbfidle: dbfiddle.uk/…

McNets

7:04 PM

Hi @JackDouglas, about my comment on dbfiddle, I'd suggest to add a button to copy xml when you set statistics xml on, it's a bit difficult to copy xml text for a later use on pastetheplan.

BTW, chart is nice

@ypercubeᵀᴹ I've read your comments here: stackoverflow.com/questions/43001133/… But still not clear to me why second boundary starts with itemtype=1

Philᵀᴹ

@JackDouglas That is probably true

ypercubeᵀᴹ

7:47 PM

@Philᵀᴹ Are you sure about that? Perhaps there was some rewriting but ANSI syntax has join options that are just not possible with the old syntax.

Jack Douglas

@McNets have you tried triple-click + CTRL+C?

McNets

@JackDouglas no, but I'll try

Triple? Usually two enough