I would like to know how to access date column from a table and use it as date filter for another large volume table..

I have the following query that currently uses a date in the filter criteria and it gets completed in about 10 to 15 minutes.

select a,b,datec, sum(c) from table1 where datec = date '2021-01-12' group by a,b,datec

I'm trying to replace the hard coded date with a date from another table called table2. It's a small table with 1600 rows that just returns latest cycle completion date (one value) which is typically today's date minus one day for most days except for holidays when the cycle doesn't run.table1 is a view and it returns millions of rows.

I tried the following queries in order to get the date value in the filter condition:

select a,b,datec, sum(c) from table1 t1, table2 t2 where t1.datec = t2.pdate and t2.prcnm = 'TC' group by a,b,datec

select a,b,datec, sum(c) from table1 t1 inner join table2 t2 on datec = t2.pdate and t2.prcnm = 'TC' group by a,b,datec

select a,b,datec, sum(c) from table1 t1 where t1.datec = (SELECT t2.date FROM table2 t2 WHERE prcnm = 'TC') group by a,b,datec

I also tried this hint:

select a,b,datec, sum(c) from table1 t1 where t1.datec = (SELECT /*+ PRECOMPUTE_SUBQUERY */ t2.date FROM table2 t2 WHERE prcnm = 'TC') group by a,b,datec

The above queries take too long and eventually fail with this error message - "parallel query server died unexpectedly"

I am not even able to get 10 rows returned when I use the date from table2. I confirmed that table2 returns only one date and not multiple dates.

Can you please help me in understanding why the query works when hard coded date is used, but not when a date from another table is used?

thank you.

with single_date as
(  
  SELECT /*+ MATERIALIZE */ t2.date FROM table2 t2 WHERE prcnm = 'TC'
)
select /*+ leading( s, t1) */ 
a,b,datec, sum(c) 
from table1 t1 , single_date s
where t1.datec = s.date