Dreamer Dreamer - 5 months ago 10
SQL Question

Performance difference when put WHERE before or after JOIN

In

Oracle
, say if there is join between several large table, what is the performance difference between:

PLAN A:
SELECT A.a, B.b, C.c
FROM A join B on a.id = b.a
join C on b.id = c.b
WHERE A.ax = '...' and B.bx = '...' and C.cx = '....';

PLAN B:
SELECT TA.a, TB.b, TC.c
FROM (
SELECT A.a, A.id
FROM A
WHERE A.ax = '...'
) as TA
join
(
SELECT B.b, B.a, B.id
FROM B
WHERE B.bx = '...'
) as TB on TA.id = TB.a
join
(
SELECT C.c, C.b,
FROM C
WHERE C.cx = '...'
) as TC on TB.id = TC.b;


PLAN A
put condition after all table join together but
PLAN B
first generate each table's subset then join them together, can PLAN B perform better than PLAN A?

Answer

Both of your queries depend on several factors like what is indexed and what is not, index strategy, server load, data caching, the way you've written query etc. I assume the columns in where clause are indexed while answering -

I ran both type of queries and found a fairly similar result. (This could probably be because of how my tables and columns are setup).

Explain plan for Plan A

SELECT STATEMENT  FIRST_ROWS
Cost: 9  Bytes: 446  Cardinality: 14    

Explain plan for Plan B

SELECT STATEMENT  FIRST_ROWS
Cost: 12  Bytes: 448  Cardinality: 14   

Now you see both took same amount of time but Ideally Plan A is what is considered under best practice (speaking from experience, I've seen Plan A queries being used almost everywhere while Plan B not so much)

Different queries work differently under different circumstances [and Oracle (or any SQL engine) smartly picks algorithm that would work best for you.]

EDIT - I checked with larger dataset now

Plan A and Plan B both are same

SELECT STATEMENT  FIRST_ROWS
    Cost: 35,413  Bytes: 1,888,512  Cardinality: 59,016