Query performance issue with 226 million records

We have a table with 226million records.

table has a composite primary key of travel id and revision

the max of records revision under each travel id is close to 5000.

now we are querying as below

select * from
(select * from travel
where travel_id=xxxx
order by travel_date desc)
where rownum <= 5;

this query runs in 20 secs.

Our SLA is below 5 sec.So created partition index on travel_id with a range of 250000 each.(we have travel id of max 32million)
This gives me the result in below 2 sec.
But since the no:of table records are not evenly distributes across each partition range,I plan to arrange the index number so that will have equal number of records under each partition.
The parttion range varied as per the number of records under each travel_id.But now i have a longer running time of 9 sec.

I just to know what the difference between these two approaches i have used to justify why the index with equal range works more faster than index partition with unequal range but equal data size?

Please advice.


Source: oracle

Leave a Reply