I making an extremely complex query on an oracle server and currently I'm stuck in not understanding why my query times out. It should be okay from the planner, but it times out.
To simplify my problem. I have the following query:
SELECT count(*)
FROM (some SQL) a
INNER JOIN (some other SQL) b
ON a.some_key=b.some_key
and a.another_key = b.another_key
"a" is a query that takes 2 seconds and returns 2127 rows and 3 columns
"b" is a query that takes 15 seconds and return 3258 rows and 5 columns
The join is not perfect so I expect at most 50.000 rows.
But this query times out after 10 minutes!!!
And I have no idea why and how this can happen. Even if I did a full cartisian product it should be okay I guess. Do you have an idea for what might happen.