Inner join working too long
515091Mar 31 2009 — edited Apr 3 2009First I am using Oracle 8i
The problem is next. I have two subqueries. Lets call them S1 and S2. Both have the field ID (which is of type varchar2(11) just from different tables T1 and T2).
S1 have about 8000 rows and S2 - 36000 (no problems here). We want to join these tables into one. It is simple? It should be. I wrote next query:
SELECT count(*)
FROM S1, S2
WHERE S1.ID = S2.ID
AND S1.date BETWEEN S2.from_date AND S.to_date
It is working. But it works very long. More than 30 min. I wrote another query with outer join.
SELECT count(*)
FROM S1, S2
WHERE S1.ID = S2.ID (+)
AND S1.date BETWEEN S2.from_date AND S.to_date
This one works less than one min. If i correctly understand - both queries should work for about the same amount of time. Could someone explain me what i am missing.