Get date filter from a table in Oracle?

Question

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 sysdate and it gets completed in twenty minutes.

select a,b,datec, sum(c) from table1 
where datec = sysdate - 1 group by a,b,datec

I'm trying to replace sysdate 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 sysdate - 1 for most days except for holidays.table1 has millions of rows.

I tried the following query 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

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

I would like to know how to get the date from table2 and use it in table1 without running in to performance issues. I am not able to view the plan because table1 is a view and I don't have access to underlying objects.

thanks.

Answer 1

To me, your 2nd query:

select a.a, a.b, a.datec, sum(a.c)
from table1 a join table2 b on a.datec = b.pdate
where b.prcnm = 'TC'
group by a.a, a.b, a.datec;

with indexes on table1.datec, table2.pdate and table2.prcnm might be a starting point.

Review explain plan, see what it says. Gather statistics on both tables and all indexes.