Oracle 11g – Fast Individual Queries but Slow Joins

oracleoracle-11g

I've got query 1:

select department_id from USER_ACCESS
  where USER_ACCESS.USER_ID = 13170
  AND USER_ACCESS.MANAGEMENT = 1

Takes about 0.475 seconds, which is not a lot for the data set I m trying to build. Basically USER_ACCESS consists of this structure:

USER_ID | DEPARTMENT_ID | LOCATION_ID | MANAGEMENT | SENIOR_MANAGEMENT | DIRECTOR |

It's basically a fine grained control over the actions in the system, whether a user has the authority to do something for a given department/location or both or sometimes if you're senior management and which department it is as long as you're tied to the location you're good to do whatever you want to do.

Now there is query 2:

select review.department_id from review
inner join sale on sale.department_id = review.department_id
where review.year_id = 10301

which takes about 0.136 seconds, however when I join these two on the department_id the query takes about 7 seconds.

I tried putting on or the other as a subquery, I also tried using WITH clause but it all turned out more less the same performance-wise.

To add more caveats, the USER_ACCESS has multiple department_id, so query 1 could potentially return many of the same department_id, also the same case goes for review it could return many duplicate department_id.

When I use distinct(department_id) on both queries I get slightly better performance but not by a lot, my question is why are they executing so slow when they are joined and other thing is how can I make em faster?

Best Answer

Sorry it was a such a dumb thing to ask, but again if this is a dumb answer please feel free to delete it. I've used the intersect instead of inner joining the two queries as subqueries.