Very slow INTERSECT under 9i - optimizer mistake?

1) As you saw, there is precious little overhead to adding a ROWNUM predicate. I wouldn't hesitate to use it at all.

2) Not that I know of, but as people love to tell me at every opportunity: I don't know everything ;). Actually, I know of nobody who ever uses INTERSECT, so I've never had the need to look into it. It would also seem then to be unlikely that they would have added special flags to affect the functionality of such a little-used feature, but who knows. Let me know if you find anything.

- Chris

Quote:

---

Originally posted by chrisrlong
Actually, I know of nobody who ever uses INTERSECT

---

Observation & supposition:

An INTERSECT can be coded as as single query using AND's or AND EXISTS. In general the CBO does OK on these and most people find the logic quite natural.

Where the CBO sometimes comes unstuck is with OR's that refer to columns in different tables (WHERE a.col1='x' OR b.col2='y' etc) under these conditions a UNION can work better, tho' it may not be so intuitive. Your ROWNUM trick (or some other) may be useful here . . . ?

Quote:

---

Originally posted by DaPi
An INTERSECT can be coded as as single query using AND's or AND EXISTS.

---

Which is why I said the optimizer always re-writes them. Once re-written, it becomes like any other join where the optimizer is free to merge logic in and out of levels and come up with whatever plan makes sense.

Quote:

---

Originally posted by DaPi
In general the CBO does OK on these and most people find the logic quite natural.

---

Considering every single join is an intersect, I personally find the use for them non-existent, but maybe that's just me.

Just pondering here, though. I wonder if the NO_MERGE hint would help with an INTERSECT. The problem is that the in-line views are hidden and not named, so it may or may not work, but it's worth a try.

- Chris

I guess I didn't express myself very well. What I meant was that people don't write INTERSECT's because "An INTERSECT can be coded as as single query using AND's or AND EXISTS . . . . and most people find the logic (i.e. the logic of AND's) quite natural."

Quote:

---

Originally posted by DaPi
I guess I didn't express myself very well. What I meant was that people don't write INTERSECT's because "An INTERSECT can be coded as as single query using AND's or AND EXISTS . . . . and most people find the logic (i.e. the logic of AND's) quite natural."

---

And if I understand Chris correctly he is saying Optimizer would rewrite your *INTERSECT* into AND, AND EXISTS join

Quote:

---

Originally posted by pando
And if I understand Chris correctly he is saying Optimizer would rewrite your *INTERSECT* into AND, AND EXISTS join

---

I wasn't disageeing! just saying why no one actually codes them!

Quote:

---

Originally posted by DaPi
I wasn't disageeing! just saying why no one actually codes them!

---

...which is exactly what I was saying in my first post, and round and round we go :D. So yes, we all agree, so let's stop arguing :)

- Chris

(I'm getting dizzy here)

Quote:

---

Originally posted by DaPi
I guess I didn't express myself very well. What I meant was that people don't write INTERSECT's because "An INTERSECT can be coded as as single query using AND's or AND EXISTS . . . . and most people find the logic (i.e. the logic of AND's) quite natural."

---

New Argument Begins Here

I've never used INTERSECT, as far as i can recall, but I'm always MINUS'ing and UNION'ing stuff -- it seems much easier and more natural than writing a join.

Flame away.

Quote:

---

Originally posted by slimdave
UNION'ing stuff -- it seems much easier and more natural than writing a join.

---

Some times sucks...CBO's A** out.

Quote:

---

Originally posted by abhaysk
Some times sucks...CBO's A** out.

---

I was hoping for a "warmer" reply than that -- pehaps I wasn't inflammatory enough in my last post, so what about ...

"What kind of idiot would write complex joins -- difficult to follow and more bug-prone -- when they could be writing an intuitive, simple little set operation? For a single column comparison, perhaps (perhaps!) the ease of writing, understanding, and maintaining a join would approach that of a set operation, but take it up to three, four, five, six etc. columns and you'd have to be crazy."

Don't make me get personal in order to get a response! (You know who you are)