Table Partitioning

Question

How advantageous is it to use partitioning of tables as compared to normal approach ?

Is there a sort of sample case or detailed comparative analysis that could statistically ( i know this is too strong a word, but it would really help if it is illustrated by some numbers ) emphasize on the utility of the process.

Thanks,

Ankur

Answer 1

You might read all the way through

this and this

Even though the articles are mysql specific, and all performance wise conclusions should not be blindly taken if you work on a different DBMS, still the articles nicely describe basic concepts and ideas together with some real numbers and examples.

Answer 2

Partitioning is extremely powerful, and is really dividing a tables data into several smaller tables - then unifying them by using a view.

For instance, 'ipnumbers' could be divided into the tables 'ipnumbers_1', 'ipnumbers_2' and 'ipnumbers_3', where the first table contains constraints so that only ipnumbers ranging from 0.0.0.0 - 50.0.0.0 are stored, and the second table contains constraints allowing only to store ranges 50.0.0.1 to 100.0.0.0, etc. Then a view could be added that unifies the partitioned tables into one:

SELECT * FROM ipnumbers_1
UNION ALL
SELECT * FROM ipnumbers_2
UNION ALL
SELECT * FROM ipnumbers_3

Now, when you query the view for a certain ipnumber:

SELECT * FROM ipnumberview WHERE ipno=60.0.0.1

..The SQL server query optimizer will know that only the table 'ipnumbers_2' must be checked, and you thus gain a massive speed improvement.

Also, schemabound views can be inserted into (automatically placing data in the correct destination table), and tables can be placed on different servers (although this is a little tricky to setup), and you can also add indexes to views.

Have fun!