SID (4/29/2009)
Could you tell me how to over come performance issue when using order by clause partition table.I have created couple partition tables with partition indexes and when I ran query including order by clause it seek index but sort 92% and same query without order by clause it doesn't sort and there is no performance issue.If I run same query with order by clause on same non partition table it works perfect.Any comment please.;-)
I don't understand your issue exactly, but remember if you are ordering on a partitioning table, you are doing a UNION ALL between each partition, then sorting. So if your index is aligned with the partition scheme, it will negate any sorting benefits that you had with a non-partitioned table. You can create a non-aligned index on that field that should give you better performance - however non-aligned indexes would prevent you from doing any partition switching.