Oracle: Relative dates and date partitioned table

user_1720085 0
Viewed 735

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!

database-partitioning date query-optimization oracle sql
2 Answers

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.

user_106767160

Use a ranged comparison:

SELECT *
  FROM BIG_PART_TABLE
  WHERE DATETIMECOLUMN BETWEEN TRUNC(SYSDATE)
                           AND TRUNC(SYSDATE+1)

This is a pattern which is useful whenever searching for indexed date values.

user_2131360
Related