Hash Join (Semi)

I have written a Query that is attempting to check the presence of 1 of 2 columns in two different sets. The query at a basic level is

select data
from my_table
where column_a in
( select my_permissions
  from method_1
)

or column_b in
( select my_permissions
  from method_2
)

I noted that the Query didn't scale once the rows in my_table went above 1,000 the response times became very linear, which as I am attempting to do an "in" I did not think would be the case, an "in" should be very scalable. The CBO Implemented a Filter at the top level for the OR.

I noted that if I removed the OR, so I was only doing a single IN then the Plan changed and the CBO implemented a hash semi join that Scaled as I had hoped. A side effect of this that I noted was that the numbers for the inner query changed, the tkprof showed that each stage became 428 times less expensive.

I have been told that the introduction of the OR precludes the use of a Hash Join (Semi) and that because of this all the rest stacks up, the only thing is this really surprises me that a Query that says "give me rows where X in SetA or Y in SetB" doesn't scale up well. This seems to go against a lot of "Think set based" advice.

Does this behaviour stack up with other peoples understanding?

Does a Hash Join (Semi) become un available with the introduction of an OR?

Does the use of a Has Join (Semi) Vs. a Filter alter the Rows that would need to be fed into these operations? i.e. would you expect them to alter the rows coming out of earlier stages (as shown by tkprof) that appeared from the full explain plan to be indentical in both predicate application and plan execution?

I have complete set up tear down scripts that I can post, but thought I'd preclude them here for Wood and tree reasons. Received on Thu Dec 02 2004 - 03:44:19 CST