Ora 9.2 SQL: What's the fastest way to do this set operation...
- From: "dean" <deanbrown3d@xxxxxxxxx>
- Date: 29 Apr 2006 22:04:57 -0700
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
.
- Follow-Ups:
- Re: Ora 9.2 SQL: What's the fastest way to do this set operation...
- From: Brian Peasland
- Re: Ora 9.2 SQL: What's the fastest way to do this set operation...
- Prev by Date: Refresh materialized view by other user then owner
- Next by Date: Re: Ora 9.2 SQL: What's the fastest way to do this set operation...
- Previous by thread: Refresh materialized view by other user then owner
- Next by thread: Re: Ora 9.2 SQL: What's the fastest way to do this set operation...
- Index(es):
Relevant Pages
|
|
