Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
  • trip has trip level information - trip_id, origin, destination, etc
  • trip_details has sightings for the trips - trip_id, sighting_date, sighting_location, etc
  • customer_trip has which trips belong to which customers - some trips may be shared between customers and some may not, so the basic structure is just customer_id, trip_id

The problem is that it keeps joining trip and 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.

share|improve this question
    
The optimiser internally will translate the all(...) part into NOT EXISTS (). Just a comment while I ponder ;) See oracle-base.com/articles/misc/… –  Phil Sep 3 '13 at 18:20
    
@Phil - should be ANY(...) but still same issue –  rfusca Sep 3 '13 at 18:41
    
Well, changing to an exists() works actually...may want to put an answer. sqlfiddle.com/#!4/d7225/17 –  rfusca Sep 3 '13 at 18:49
    
Can you show us the explain plan for the query, along with any index in place please? –  Chris Saxon Sep 4 '13 at 11:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.