Subquery is taking much longer then normal

Question

I encounter a problem with optimization.

When I use a query like this:

Select * (around 100 columns)
from x
where RepoDate = '2020-05-18'

It's taking around 0.2 seconds.

Unless I'm using query like this:

Select * (around 100 columns)
from x
where RepoDate = (select max(RepoDate) from y)

It takes around 1 hour. Table y has only dates (2020-05-17, 2020-05-18, ... )

Can you tell me why there is so much difference in time to execute?

`'2020-05-18'` is not a `DATE` to begin with, as it is a string. Secondly, `SQL Developer` is a tool, not sure what it has to do with the performance of your query. Thirdly, we neither have your `table/data` nor any `explain plan` or `performance metrics` to tell why your query is *slow/fast/whatever*? — Lalit Kumar B, May 20 '20 at 20:29
We cannot give an answer until you provide more details. Please read [this excellent post on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). — APC, May 21 '20 at 06:43
How may rows are in `Y`? How many in `X`? What are the two execution plans? — William Robertson, May 21 '20 at 07:01

score 0 · Answer 1 · answered Nov 16 '21 at 18:09

Technically, you are comparing a simple first query with a query having another subquery that might be processing a heavy table "y" already.

So these two queries are not the same to start with. We need to have explain plans to have an estimation of the cost of the subquery first (i.e. how many rows, index usage etc.) then we move to the parent query.

Subquery is taking much longer then normal

1 Answers1