My query like this:
Select
...
...
...
from
main_table m
inner join x on x.id=m.id
inner join y on y.id=m.id
left outer join z on z.id=m.id
left outer join t on t.id=m.id
where m.date_col=to_date('20200101','yyyymmdd');
query takes 1 minute. But result is empty.
How can I get it faster.
select
...
...
...
from
main_table m
where m.date_col=to_date('20200101','yyyymmdd');
this query takes 3 seconds.
thanks in advance
To get a better understanding of the "why", one would want an explain plan output to see the access patterns and join operation choices Oracle has made. Just because your result-set is empty doesn't mean that each of the joined-to tables are. My guess is that some of the tables joined to may have some size to them and the access pattern may or may not be appropriate as well as the join choices. For example, maybe the second table, x, is quite large and Oracle is full scanning it and using a hash join. After doing all of that, no joined rows match so 0 rows are returned. Maybe there should be an index on the columns, maybe not. Again it depends rows and blocks. If not x, maybe y is large with the same scenario. Bottom line is that the result is empty but my guess is that x or y has some rows, just not matching rows causing the query to take some time. Without an explain plan, it's really hard to tell. Once viewed, optimizations could possibly be recommended.
