Skip to Main Content
  • Questions
  • Get date filter from a table in Oracle?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 13, 2021 - 2:49 pm UTC

Last updated: January 15, 2021 - 8:51 am UTC

Version: Oracle Database 12c

Viewed 100+ times

You Asked

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.

and we said...

Hard to know without all of the details, but perhaps try this:

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


If that doesn't work, get back to us in the review and we'll explore other options

Rating

  (1 rating)

Comments

A reader, January 14, 2021 - 2:12 pm UTC

thank you. I tried those hints, but the query still fails with the following error after running for ~40 minutes:

SQL Error [12801] [72000]: ORA-12801: error signaled in parallel query server P02L,
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_FLASH_REP

The query with hardcoded date gets completed under 15 minutes and returns rows without any errors .
Chris Saxon
January 15, 2021 - 8:51 am UTC

When you use literals, the optimizer knows which values its working with so can get much better row estimates. With a join it doesn't know which values you're accessing from table1, which can lead to incorrect row estimates.

Get the execution plan for the queries, post them here, and we can look into this further

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.