SQL Performance (MOSC)

MOSC Banner

SQL stetement on partitioned index is slow when period is between 2 partitions

edited Jun 3, 2017 5:00AM in SQL Performance (MOSC) 9 commentsAnswered

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.

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