Re: Should I partition this table?

On Thu, 10 Jul 2014 11:36:27 -0700 (PDT) AlexK <alkuzo(at)gmail(dot)com> wrote:

> What would be the advantages of partitioning on ranges of ParentID? Each
> query will touch at most one partition. I might or might not get PK indexes
> one level of depth less.

You need to partition by ParentID in order for the example queries you
provided to benefit from the partitioning. You should abstain from removing
the previous comments from each email reply ... I'm not going to dig back
through this thread to find specific examples.

> I understand that I will CLUSTER these smaller tables and benefit from that.
> Other than clustering, what are other advantages?

Personally, I find the benefits of CLUSTER to be debatable. I haven't done
a lot of investigation, but my experience has been that keeping things
CLUSTERed is more work than it's worth. Certainly, if you have a table
that _never_ changes, and he access patterns dictate it, there's no reason
not to CLUSTER, but I'm not convinced that you'll benefit (again, there's
a lot of information about your application use that hasn't been provided
that's necessary to make such a determination)

--
Bill Moran <wmoran(at)potentialtech(dot)com>