Oracle SQL Query Filter in JOIN ON vs. WHERE
For internal joins, is there a performance difference for applying the filter in a sentence JOIN ON
or in a sentence WHERE
? Which will be more efficient, or will the optimizer make them equal?
JOIN
SELECT u.name
FROM users u
JOIN departments d
ON u.department_id = d.id
AND d.name = 'IT'
VS
WHERE
SELECT u.name
FROM users u
JOIN departments d
ON u.department_id = d.id
WHERE d.name = 'IT'
Oracle 11gR2
source to share
There shouldn't be a difference. The optimizer must generate the same plan in both cases, and must be able to apply the predicate before, after, or during the join in either case, based on what is the most efficient approach for that particular query.
Of course, the fact that the optimizer can do something, in general, does not guarantee that the optimizer will actually do something in a particular query. As queries become more complex, it becomes impossible to thoroughly analyze all possible query plans, which means that even with perfect information and perfect code, the optimizer may not be able to do everything you would like to do. You need to take a look at the actual plans generated for the two queries to see if they are indeed identical.
source to share
I prefer to put the filter criteria in the sentence where
.
With data warehouse queries, putting filter criteria in the results join
so that the query lasts a very long time.
For example, I have Table1 indexed on the Date field and Table2 split on the Sections field , Table2 is the largest table in the query and it resides on a different database server. I'm using the drive_site hint to tell the optimizer to use Table2 sections .
select /*+driving_site(b)*/ a.key, sum(b.money) money
from schema.table1 a
join schema2.table2@dblink b
on a.key = b.key
where b.partition = to_number(to_char(:i,'yyyymm'))
and a.date = :i
group by a.key`
If I query this way it takes about 30-40 seconds to return the results.
If I don't make the request this way, it will take about 10 minutes until I cancel without any results.
source to share