SQL Language (MOSC)

MOSC Banner

Strange issue on selecting against a date column partition key

edited Jan 20, 2016 10:03AM in SQL Language (MOSC) 9 commentsAnswered

Hello,

We're experiencing a weird issue regarding a simple query selecting data from a partitioned table using the partition key with a literal value. Here is the query:

SELECT * FROM MOSAIC_DATA WHERE CDATE = TO_DATE('16/01/2016', 'DD/MM/YYYY');

This query returns no rows, while the partition for 16/01/2016 does exist.

CDATE is of DATE type, and the table is range-partitioned on that column (it's also subpartitioned). Basically there's a partition a day. So there are as many partitions as date calculated so far. I'm not at work so I can't give you exact details, but it starts somewhere years ago until few days in the future. So there's a partition for 01/01/2015, another for 02/01/2015, and so on until 18/01/2016 or so. There's a local index on CDATE, which has also got the partition for that date.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center