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

Hi Brian, thanks for the code. The problem is that this is a set theory problem, and I am not a mathematician. Point C is not known, and there may be many points that (what's known as a block swap) can occur in both trains (in fact this is quite likely if they share part of a corridor). In addition, the swap does not have to be at the first train's destination, or the origin of the second train.

The problem is how to stop oracle determining the full intersection on locations, and to stop when it finds _any_ shared occurence of a location.

This is my statement just for reference. I was hoping the distinct keyword would provide enough of a hint to oracle that it doesn't have to find all the shared points C.

var LOCATION1 varchar2(30);
execute :LOCATION1 := '000006';
var LOCATION2 varchar2(30);
execute :LOCATION2 := 'ANB865';

select * from
(
with TER as (select * from train_expanded_route_008), TRAINS_THRU_1 as (select distinct TRAIN_I, P_VERSION_I from TER where location_i = :LOCATION1),
TRAINS_THRU_2 as (select distinct TRAIN_I, P_VERSION_I from TER where location_i = :LOCATION2),
FULL_TR_THRU_1 as (select TRAIN_I, P_VERSION_I, ROUTE_ORDER_I, LOCATION_I from TER where (TRAIN_I, P_VERSION_I) in (select * from TRAINS_THRU_1)),
FULL_TR_THRU_2 as (select TRAIN_I, P_VERSION_I, ROUTE_ORDER_I, LOCATION_I from TER where (TRAIN_I, P_VERSION_I) in (select * from TRAINS_THRU_2))
select distinct

       FULL_TR_THRU_1.TRAIN_I T1,
       FULL_TR_THRU_1.P_VERSION_I P1,
       FULL_TR_THRU_2.TRAIN_I T2,
       FULL_TR_THRU_2.P_VERSION_I P2
       from FULL_TR_THRU_1, FULL_TR_THRU_2
       where FULL_TR_THRU_1.LOCATION_I = FULL_TR_THRU_2.LOCATION_I
       and rownum < 20