How Partitioned table works

Partitioning is nothing but dividing of datas according to the requirement to improve query performance. Consider you are having a table with large number of datas. Here in table partitioning we are going to implement horizontal partition, where the rows in the table will be based on one the column in table.

Consider you have a table without partition as below.

partition_table_working_1

Here when you perform DDL statements or select statement each time the query need to look into a single filegroup where the table is stored, there by making lot of IO operations based on the query given. If the table is much larger then there will be some delay in querying the data.

The above example is having a table Ticket where customers are able to books ticket online. In this case ticket booking can be done only for future dates and not current or previous month, hence datas less than current date will be only used for Select statement and rarely for Update or Delete. If this is the case without partitioning then there will be sure delay in response.

If you broke the table into several partitions based upon the query information then there will be improvement in performance. In the above example Serial Number column will be used as indexes and to partition the table we can use the order date since based on this column they are going to perform the DDL operations.

Lets split the table as below.

partition_table_working_2

 

When you split the table as above you need to place those divided table in a particular filegroup which can be done using partitioning scheme. In the above example the datas less than current month can be used for select statement and hence based on the information we have divided table with Order date column. The physical implementation of 3 filegroup pairs is a storage concept, not a relational concept.

Filegroup for the partitioned table

partition_table_working_3

 

Thus after partitioning the table, the datas can be queried based of the filegroup where the data has been placed. This provides us parallelism where the data can be simultaneously inserted, updated or selected faster than before. You can also specify on which Raid these filegroup should be placed.


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *