Sql-server – Does Partition Creation Order Matter

partitioningsql server

I'm in a situation where I have taken over a very large db that is primarily a single table that is partitioned. Since it hasn't been partitioned for a while, I need to re-start the partitioning, but it is a 24/7 business and it can't be out for a few hours while SPLITing the older partitions.

If I was to create the new partition for the end of this month, then go back and SPLIT the previous partitions – is there a problem with doing them out of order, other than the partition id being in the wrong order?

Is there any performance or maintenance differences if the order of the partition id is not in sequence with the partition key order?

Best Answer

...if the order of the partition id is not in sequence with the partition key order

SQL Server always maintains partition ids in partition boundary order. No matter how you split or merge partitions, the order of boundary values always matches the order of partition ids. SQL Server renumbers as necessary.

Concerning performance of the split operation itself, you will want to organize things so that the minimum (ideally, none at all) physical data movement occurs. This typically involves careful thought and testing in advance, using SWITCH and CREATE INDEX…WITH DROP_EXISTING = ON.

Review the following all by Dan Guzman:

If you need help designing the process, ask a new question with DDL for the table, indexes, partitioning scheme, function, and file groups. Also include a clear representation of the start and end states i.e. which data should go where.

Related Solutions

Mysql – Why is MySQL order by performance poor within a single partition of a partitioned table

Assuming that you range partition your table by the (auto incrementing) sequence and you limit your query by another value (day number), you might see, that a lot of partitions will be touched when you use:

EXPLAIN PARTITIONS SELECT * FROM table_name WHERE day_number=X ORDER BY...

The reason for that is, that there is no linkage between the partitioning attribute and the data filter. Since MySQL uses a different index for each partition, it needs to open all partitions to find all data matching day_number.

If you know the partition name, maybe by querying INFORMATION_SCHEMA.PARTITIONS before the real select, and you use MySQL-5.6+, you can use partition selection like

SELECT * FROM table_name PARTITION(partition_name) WHERE day_number=X ORDER BY...

But this is just chitchat based on a lot of assumtions. To give you a real answer, you need to post the query plan and more information about table structure - like Phil said before.

Adding subpartitions to existing partitioned table

A new subpartition can be added to the existing partitioned table using the following command:

ALTER TABLE PART_TEST
modify partition OCT19 
add subpartition OCT19AXCS 
values ('AXCS');

Best Answer

Related Solutions

Mysql – Why is MySQL order by performance poor within a single partition of a partitioned table

Adding subpartitions to existing partitioned table

Related Question