Your correct, I'm looking at comparing the data from two different dates. I used the DENSE_RANK because it was the only way I could come with to get the two most recent partitions. I could use MAX for the most recent partition, but how would I get the second most recent partition?

As for scanning all the partition, the execution plan is saying it will scan all the partitions.

You got yourself into a chicken and egg situation. In order to find out which rows are first and second in created_on descending order Oracle needs to scan whole table. Unfortunately, optimizer is not smart enough to realize it is partitioned table and do something like:

Since created_on is partitioning column and we need to rumber rows in created_on descending order, let me start from last existing partinion and if it is empty go to previous one and so on until we find not empty partition. Then number partition rows in created_on desc order and if partition has just one row repeat the process starting from previous partition. This is one of those cases where PL/SQL might be more efficient solution.

onedbguru wrote:
Your inline view must only look at the partitions you are interested in....
Not having your test data, I do not know what the data for day1 and day{n} looks like, but you need to include that in your query...

It is not my query and not my data. And again, OP is looking for an intersect between two highest dates in a table. It has nothing to do with SYSDATE.

apologies.... I thought I was replying to the OP. He wants to use partition pruning... sysdate is just a date to be used for that purpose because the partitioning is on a date column. this is an example not exact code. He will need to extrapolate from the example. Without something to filter the data, it is going to to a FTS. Guaranteed.

>
Your correct, I'm looking at comparing the data from two different dates. I used the DENSE_RANK because it was the only way I could come with to get the two most recent partitions. I could use MAX for the most recent partition, but how would I get the second most recent partition?

As for scanning all the partition, the execution plan is saying it will scan all the partitions.
>
I ask you to provide the execution plan. When you ask for help you need to provide the actual information; not your interpretation of it.

You still haven't defined your actual requirement. Is it to find the latest partition? Or is it instead to find the latest partition that actually has any data?

To find the latest partition for your use case you could write a simple function that returns the DATE representing the HIGH_VALUE for the partition with the highest PARTITION_POSITION.

onedbguru wrote:
sysdate is just a date to be used for that purpose because the partitioning is on a date column.

Yes, sysdate is just a date while OP is looking for a particular, yet unknown, dates: last date in the table and second last date in the table . If such dates would be known Oracle wouldn't have any issues pruning not needed partitions. Problem is such dates are not known upfront and oracle has to look through all partitions in order to obtain them.