mssql auto partition

Posted by largo on Fri, 03 Apr 2020 19:06:14 +0200

Details of mysql automatic partition
Why auto partition: because there are more than one million pieces of data in a table, and the query speed is very slow, the auto partition function is added to improve the query ability.
First, define the steps of partition:

  1. New backup table
  2. Delete new table partition
  3. Swap partition
  4. Delete old table partition
  5. New zoning

    The specific implementation method is as follows
    New backup table:

    set @back=concat('create table Newly build schema Name.Partition name like Table name');   
                     PREPARE stmt1 FROM @back;  
                     EXECUTE stmt1;  
                     DEALLOCATE PREPARE stmt1;  

Delete new table partition

set @deleNewPartition=concat('alter table Newly build schema Name.Partition name REMOVE PARTITIONING');            
                     PREPARE stmt1 FROM @deleNewPartition;  
                     EXECUTE stmt1;  
                     DEALLOCATE PREPARE stmt1; 

Exchange partition table

set @exchangePartition=concat('alter table Table name exchange partition ',The name of the old table partition,' with table Newly build schema Name.Create partition name);     
                     PREPARE stmt2 FROM @exchangePartition;  
                     EXECUTE stmt2;  
                     DEALLOCATE PREPARE stmt2;

Delete old table partition

SET @v_delete=concat('alter table pack_data_info drop PARTITION 'Name of the old table partition);
                    PREPARE stmt3 from @v_delete; 
                    EXECUTE stmt3;
                    DEALLOCATE PREPARE stmt3;

New zoning

SET @v_add=concat('ALTER TABLE pack_data_info ADD PARTITION (PARTITION Last partition name VALUES LESS THAN (TO_DAYS(Time of partition))');                 
                    PREPARE stmt4 from @v_add;              
                    EXECUTE stmt4;
                    DEALLOCATE PREPARE stmt4;

Bad view area

 SELECT partition_name,partition_expression,partition_description,table_rows 
 FROM information_schema.PARTITIONS 
 WHERE table_schema = SCHEMA() AND table_name='Table name'

Create schema
create schema name
What is called schema: schema can be simply understood as a library. To create a schema is to create a database. We put the data to be backed up in this schema library.

Please comment

Topics: MySQL less Database