Oracle: Relative dates and date partitioned table

Question

I have a monthly partitioned table in Oracle by a DATETIME column. This table contains > 6 billion rows. Now I want to filter this table using sysdate. Thus, I can write queries using relative date as, for instance, yesterday, last weekday, last year etc...

So, what I looking for is something like below code that works:

SELECT *
FROM BIG_PART_TABLE PARTITION FOR (DATE **TODAY**)
WHERE TRUNC(DATETIMECOLUMN) = TRUNC(SYSDATE)

But obviously, that won't work.

Do you have any ideas so I can get it to work?

Thanks!

Answer 1

You can not reliably tell in which partition Oracle needs to look, since sysdate is a volatile function (ie its value is not constant over time). Instead of writing complicated dynamic code for that, you should probably trust the database to pick the right partition in the first place.

I woul phrase your query as follows:

select *
from big_part_table partition 
where datetimecolumn >= trunc(sysdate) and datetimecolumn < trunc(sysdate) + 1

The where condition is functionaly equivalent to your original condition, an it gives the database a reasonable chance to assess which partition should be used.