SQL stetement on partitioned index is slow when period is between 2 partitions
I have a 11.2.0.4 db.
In the database there is a partitioned table on a date column. This column is also indexed with an index partition. The partitions are per month.
There is a specific query with the following behavior:
Running the query with date between the same month the query ends in around 18 seconds.
i.e.:
date between 01-may-2017 and 15-may-2017 takes 18 seconds
date between 01-apr-2017 and 30-apr-2017 takes 18 seconds
Running the query with date between 2 months the query ends in 40-50 minutes
i.e.:
date between 30-apr-2017 and 15-may-2017 takes 43 minutes
I have rebuild the index partition of MAY but there was not difference.