September 18, 2014 at 10:16 am
We have designed partition with BIGINT range. of 1 millions each of 10 millions of ID. If I get new ID > 10 millions
this will move to new range . I need a script which will automtically check this and add a new range for the existing partition table .
Cheers,
Got an idea..share it !!
DBA_Vishal
September 18, 2014 at 11:01 am
I wouldn't recommend creating partitions when they are needed. Would your query have to wait until the partition is created until it could insert its record?
It would be better to create the partitions in advance and have a stored procedure check them nightly and add new partitions if the number of free partitions drops below a certain number.
September 18, 2014 at 7:15 pm
as Data loading is a continuous process we are expecting high volume data in future. t will be difficult to create these partition in advance , hence I was planning to have 10 partitions now and as on data get added for each 1 millions it will add on a new parttion range and go on .
Is it possible?
Cheers,
Got an idea..share it !!
DBA_Vishal
September 19, 2014 at 1:48 am
Why would it be difficult? You can initially create the partitions, specifying more than you need.
Then run this script regularly:-
SELECT
t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id,
i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue, p.rows
FROM
sys.tables AS t
INNER JOIN
sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN
sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE
t.name = 'YOUR TABLE NAME'
AND
i.type <= 1
ORDER BY p.partition_number;
This will show you all the partitions in the table. From this you can see which are in use.
I would then build a stored procedure to add new partitions once the number of free partitions drops below a certain number.
This would mean that your data inserts would never have to wait for a partition to be created.
September 19, 2014 at 2:37 am
Thank you , I will follow as you suggested.
Could also please share me script to add the new partition , I am not good with writing script.
Cheers,
Got an idea..share it !!
DBA_Vishal
September 19, 2014 at 2:43 am
Without knowing your database schema I can't really write you a script to manage your partitions.
I would recommend that you do some research, write a script yourself and then test it to death on a development machine.
I wrote a few posts about partitioning, you can find them here:- http://dbafromthecold.wordpress.com/2014/06/04/partitioning-basics-part-1/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply