Oracle 2 node RAC 11G R2
I have a query (this is a simplified version, but still exhibits the same issue as the larger query) that looks like this:
SELECT t1.trip_id, t1.origin, t2.sighting_date FROM trips t1 JOIN trip_details t2 on t1.trip_id = t2.trip_id WHERE --don't the get the information if the trip *only* belongs to customerid 99 99 <> any(select customerid from customer_trip ct where ct.trip_id = t1.trip_id);
triphas trip level information -
trip_detailshas sightings for the trips -
customer_triphas which trips belong to which customers - some trips may be shared between customers and some may not, so the basic structure is just
The problem is that it keeps joining
trip_details first, and then limiting by the
99<> any.. statement. The
99 <> any.. statement greatly reduces the rows from
trip and would reduce the rows looked up in
trip_details (which is very, very large - 100x
trip). So, ideally, I'd like it join trips and the customertrip subquery first, and then do trip_details.
But I can't figure out if there's a way to specify a join order such that it does it in that order. The
LEADING hint doesn't seem to have a way to reference the subquery.
Stats are all up to date.