I have what I call a 'Hamlet' query in the form:
select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs where stuffThatIs.Name = b.Name) or (b.ToBe <> 1 and a.Being not in (select Being from stuffThatIs where stuffThatIs.Name = b.Name)
The most-nested queries being identical, so I thought the database would reuse the result instead of doing 2 queries.
But in the execution plan the query shows twice with the same cost of 19%.
Can this be optimized manually? Or a hint added? I'd prefer a portable solution instead of a maximum perf one.
There are indexes on all named columns, sometimes even unique. The example is simplified, the whole
where clause is in a join condition itself etc. So please optimize just the pattern, not the whole query.