Search code examples
sqloracle-databasepartitioningdatabase-partitioning

What is a good size (# of rows) to partition a table to really benefit?

I.E. if we have got a table with 4 million rows.

Which has got a STATUS field that can assume the following value: TO_WORK, BLOCKED or WORKED_CORRECTLY.

Would you partition on a field which will change just one time (most of times from to_work to worked_correctly)? How many partitions would you create?

Solution

  • The number of rows in a table isn't generally a great metric to use to determine whether and how to partition the table.

    What problem are you trying to solve? Are you trying to improve query performance? Performance of data loads? Performance of purging your data?

    Assuming you are trying to improve query performance? Do all your queries have predicates on the STATUS column? Are they doing single row lookups of rows? Or would you want your queries to scan an entire partition?