On Tue, Sep 28, 2010 at 8:56 AM, Sandy
<sandy9940@rediffmail.com> wrote:
Table has mobile number,status and expiry date. I can not partition on expiry date as all SELECT's are on mobile number.
Then partition on the mobile number. If your updates and queries are all tied to that, then it is the ideal candidate.
You should also make sure that you have proper indexes on each partition to let the query executer quickly decide that a given table is not of any use (you really don't want sequence scan on each partition).
You will get speedups if you can achieve one or both of getting the query panner to use the constraint exclusions to eliminate partitions and getting the index scans to quickly skip over partitions which will not have the data you want.