0

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.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
Francis John
  • 171
  • 2
  • 4
  • 13

1 Answers1

0

For a query of that form I would not have looked at partitioning: an index on (travel_id, travel_date desc) should get the rows in subsecond time.

For further improvement you might make some effort to physically cluster the values based on travel_id and travel_date, using a cluster or maybe an index-organised table, but this could have repercussions for the rest of the system.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96