Ora 9.2 SQL: What's the fastest way to do this set operation...

Imagine I have a database of 10,000 trains and their ordered list of (around 2 million) scheduled locations, keyed on those trains and an order number. I want to go from A to B and there are no direct trains. I need to find all the pairs of trains, one going through A and the other through B, that share AT LEAST ONE OTHER location in common, call it C.

The only way I can think of doing it is to find the set of trains T_A that go through A, and the set of trains T_B that go through B, and see if there is any intersection in their route locations.

As far as I can tell, doing the INTERSECT means actually calculating the entire intersection, even though I only need to know that at least one location is common to both trains.

Is there a fast function in oracle (9.2) that will do something like this - something along the lines of OVERLAPS.

If I do the join on LOCATION, or a count of locations in T_A that are in T_B, I get the correct answer but it is taking 10 seconds+, and I'd like to reduce that.

Thanks for any help.

Dean Received on Sun Apr 30 2006 - 00:04:57 CDT