CN114880409A - Data table filing method and related device - Google Patents
Data table filing method and related deviceDownload PDFInfo
- Publication number
- CN114880409A CN114880409ACN202210675099.XACN202210675099ACN114880409ACN 114880409 ACN114880409 ACN 114880409ACN 202210675099 ACN202210675099 ACN 202210675099ACN 114880409 ACN114880409 ACN 114880409A
- Authority
- CN
- China
- Prior art keywords
- processing
- original
- information
- data
- constraint
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Translated fromChinese
Description
Translated fromChinese技术领域technical field
本发明涉及数据库领域，特别涉及一种数据表归档方法及相关装置。The invention relates to the field of databases, in particular to a data table filing method and a related device.
背景技术Background technique
在使用关系型数据库的联机交易系统中，交易数据随着时间的推移，产生的数据量逐渐增加，需要对交易数据进行定期归档，以往通常采用从原表中筛选出需要归档的数据，写入归档表，并将原表中需归档的数据进行删除。这种方式，针对新增待归档的原表，需要编写对应的归档程序，才能实现对新增待归档的原表的归档，比较麻烦，效率低下。In an online transaction system using a relational database, the amount of transaction data generated gradually increases over time, and the transaction data needs to be archived regularly. Archive the table and delete the data to be archived in the original table. In this way, for the newly added original table to be archived, a corresponding archiving program needs to be written to realize the archiving of the newly added original table to be archived, which is troublesome and inefficient.
发明内容SUMMARY OF THE INVENTION
鉴于上述问题，本发明提供一种克服上述问题或者至少部分地解决上述问题的一种数据表归档方法及相关装置。In view of the above problems, the present invention provides a data table filing method and related apparatuses that overcome the above problems or at least partially solve the above problems.
第一方面，一种数据表归档方法，包括：In a first aspect, a data table filing method includes:
从配置表中获得待归档的原表的信息，并获得所述原表和归档表；Obtain the information of the original table to be archived from the configuration table, and obtain the original table and the archived table;
根据所述原表的信息，创建所述原表中的归档数据的中间表，其中，所述中间表同步所述原表的表名注释、列名注释和列的默认值；According to the information of the original table, an intermediate table of the archived data in the original table is created, wherein the intermediate table synchronizes the table name annotation, column name annotation and default value of the column of the original table;
对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理；Perform primary key deletion processing, invalid index processing, invalid constraint processing and new partition processing on the archive table;
将所述归档数据存储至所述归档表的新增分区；storing the archive data in a newly added partition of the archive table;
通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理。In a parallel manner, the primary key rebuilding process, the index rebuilding process and the effective constraint processing are performed on the archive table.
结合第一方面，在某些可选的实施方式中，在所述根据所述原表的信息，创建所述原表中的归档数据的中间表之后，所述方法还包括：With reference to the first aspect, in some optional embodiments, after the intermediate table of the archived data in the original table is created according to the information of the original table, the method further includes:
将所述原表中的非归档数据存储至所述中间表；storing the non-archived data in the original table to the intermediate table;
从所述原表相应的数据字典中获得所述原表的索引信息和约束信息；Obtain the index information and constraint information of the original table from the corresponding data dictionary of the original table;
根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理。According to the index information of the original table and the constraint information of the original table, in the parallel manner, corresponding primary key establishment processing, index establishment processing and effective constraint processing are performed on the intermediate table.
结合上一个实施方式，在某些可选的实施方式中，在所述根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理之后，所述方法还包括：In combination with the previous implementation, in some optional implementations, according to the index information of the original table and the constraint information of the original table, in the parallel manner, the intermediate table is correspondingly processed. After the primary key establishment process, the index establishment process and the effective constraint process, the method further includes:
删除所述原表，并将所述中间表维护为新的原表。The original table is deleted, and the intermediate table is maintained as a new original table.
可选的，在某些可选的实施方式中，所述根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理，包括：Optionally, in some optional implementation manners, according to the index information of the original table and the constraint information of the original table, the corresponding primary key establishment processing is performed on the intermediate table in the parallel manner. , indexing processing, and effective constraint processing, including:
根据所述原表的索引信息，建立所述中间表的索引；establishing an index of the intermediate table according to the index information of the original table;
根据所述原表的约束信息，生效所述中间表的主键的唯一约束和所述中间表的其它约束；According to the constraint information of the original table, the unique constraint of the primary key of the intermediate table and other constraints of the intermediate table are validated;
根据所述原表的索引信息和所述原表的约束信息，建立所述中间表的主键。The primary key of the intermediate table is established according to the index information of the original table and the constraint information of the original table.
结合第一方面，在某些可选的实施方式中，所述对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理，包括：With reference to the first aspect, in some optional implementation manners, the processing of deleting a primary key, processing an invalid index, processing an invalid constraint, and processing a new partition on the archive table includes:
删除所述归档表中的主键；delete the primary key in the archive table;
将所述归档表的索引均设置为不可见状态；setting the indexes of the archive table to an invisible state;
将所述归档表的约束均设置为不可见状态；setting the constraints of the archive table to an invisible state;
若所述归档表为分区表，则调用分区维护功能，在所述归档表中建立新的新增分区。If the archive table is a partition table, the partition maintenance function is invoked to create a new newly added partition in the archive table.
结合第一方面，在某些可选的实施方式中，所述通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理，包括：With reference to the first aspect, in some optional implementation manners, performing primary key rebuilding, index rebuilding, and validating constraint processing on the archive table in a parallel manner, including:
从所述原表对应的数据字典获得所述原表的主键的唯一约束信息，并从所述归档表对应的数据字典中获取所述归档表的索引信息和所述归档表的其它约束信息；Obtain the unique constraint information of the primary key of the original table from the data dictionary corresponding to the original table, and obtain the index information of the archive table and other constraint information of the archive table from the data dictionary corresponding to the archive table;
根据所述归档表的索引信息，并行批量重建所述归档表的索引；According to the index information of the archive table, rebuild the index of the archive table in parallel batches;
根据所述主键的唯一约束信息生效所述归档表的主键的唯一约束，并根据所述归档表的其它约束信息生效所述归档表的其它约束；The unique constraint of the primary key of the archive table is valid according to the unique constraint information of the primary key, and other constraints of the archive table are validated according to other constraint information of the archive table;
根据所述归档表的索引信息和所述主键的唯一约束信息，并行批量重建所述归档表的主键。According to the index information of the archive table and the unique constraint information of the primary key, the primary key of the archive table is rebuilt in batches in parallel.
第二方面，一种数据表归档装置，包括：数据获得单元、中间表创建单元、第一归档表处理单元、数据存储单元和第二归档表处理单元；In a second aspect, a data table filing device includes: a data obtaining unit, an intermediate table creating unit, a first filing table processing unit, a data storage unit, and a second filing table processing unit;
所述数据获得单元，用于从配置表中获得待归档的原表的信息，并获得所述原表和归档表；The data obtaining unit is used to obtain the information of the original table to be archived from the configuration table, and obtain the original table and the archived table;
所述中间表创建单元，用于根据所述原表的信息，创建所述原表中的归档数据的中间表，其中，所述中间表同步所述原表的表名注释、列名注释和列的默认值；The intermediate table creation unit is configured to create an intermediate table of archived data in the original table according to the information of the original table, wherein the intermediate table synchronizes the table name annotation, column name annotation and the default value of the column;
所述第一归档表处理单元，用于对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理；The first archive table processing unit is configured to perform primary key deletion processing, invalid index processing, invalid constraint processing and new partition processing on the archive table;
所述数据存储单元，与将所述归档数据存储至所述归档表的新增分区；the data storage unit, and a newly added partition for storing the archive data in the archive table;
所述第二归档表处理单元，用于通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理。The second archive table processing unit is configured to perform primary key rebuilding, index rebuilding, and effective constraint processing on the archive table in a parallel manner.
结合第二方面，在某些可选的实施方式中，所述装置还包括：第一数据存储单元、第一信息获得单元和中间表处理单元；With reference to the second aspect, in some optional implementation manners, the apparatus further includes: a first data storage unit, a first information obtaining unit, and an intermediate table processing unit;
所述第一数据存储单元、用于在所述根据所述原表的信息，创建所述原表中的归档数据的中间表之后，将所述原表中的非归档数据存储至所述中间表；The first data storage unit is configured to store the non-archive data in the original table to the intermediate table after the intermediate table of the archived data in the original table is created according to the information of the original table surface;
所述第一信息获得单元，用于从所述原表相应的数据字典中获得所述原表的索引信息和约束信息；The first information obtaining unit is configured to obtain the index information and constraint information of the original table from the corresponding data dictionary of the original table;
所述中间表处理单元，用于根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理。The intermediate table processing unit is configured to, according to the index information of the original table and the constraint information of the original table, perform corresponding primary key establishment processing, index establishment processing and effective constraints on the intermediate table in the parallel manner deal with.
第三方面，一种计算机可读存储介质，其上存储有程序，所述程序被处理器执行时实现上述任一项所述的数据表归档方法。In a third aspect, a computer-readable storage medium stores a program thereon, and when the program is executed by a processor, implements the data table filing method described in any one of the above.
第四方面，一种电子设备，所述电子设备包括至少一个处理器、以及与所述处理器连接的至少一个存储器、总线；其中，所述处理器、所述存储器通过所述总线完成相互间的通信；所述处理器用于调用所述存储器中的程序指令，以执行上述任一项所述的数据表归档方法。In a fourth aspect, an electronic device includes at least one processor, and at least one memory and a bus connected to the processor; wherein the processor and the memory communicate with each other through the bus. communication; the processor is configured to call program instructions in the memory to execute the data table filing method described in any one of the above.
借由上述技术方案，本发明提供的一种数据表归档方法及相关装置，可以通过从配置表中获得待归档的原表的信息，并获得所述原表和归档表；根据所述原表的信息，创建所述原表中的归档数据的中间表，其中，所述中间表同步所述原表的表名注释、列名注释和列的默认值；对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理；将所述归档数据存储至所述归档表的新增分区；通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理。由此可以看出，本发明可以通过修改配置表的方式，无需重写编写程序，即可实现相应的数据归档，效率较高，并且在数据归档的过程中可以采取并行的方式，进一步提高本发明的效率。With the above technical solution, the present invention provides a data table filing method and related device, which can obtain the original table and the archive table by obtaining the information of the original table to be archived from the configuration table; according to the original table information, create an intermediate table of the archived data in the original table, wherein the intermediate table synchronizes the table name annotations, column name annotations and default values of the columns of the original table; delete the primary key processing on the archived table , invalidation index processing, invalidation constraint processing and newly added partition processing; the archived data is stored in the newly added partition of the archived table; in parallel, the archived table is processed for rebuilding the primary key, rebuilding the index processing and validating the constraint deal with. It can be seen from this that the present invention can realize the corresponding data archiving by modifying the configuration table without rewriting the programming, with high efficiency, and can adopt a parallel method in the process of data archiving to further improve the cost efficiency of invention.
上述说明仅是本发明技术方案的概述，为了能够更清楚了解本发明的技术手段，而可依照说明书的内容予以实施，并且为了让本发明的上述和其它目的、特征和优点能够更明显易懂，以下特举本发明的具体实施方式。The above description is only an overview of the technical solutions of the present invention, in order to be able to understand the technical means of the present invention more clearly, it can be implemented according to the content of the description, and in order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand , the following specific embodiments of the present invention are given.
附图说明Description of drawings
通过阅读下文优选实施方式的详细描述，各种其他的优点和益处对于本领域普通技术人员将变得清楚明了。附图仅用于示出优选实施方式的目的，而并不认为是对本发明的限制。而且在整个附图中，用相同的参考符号表示相同的部件。在附图中：Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are for the purpose of illustrating preferred embodiments only and are not to be considered limiting of the invention. Also, the same components are denoted by the same reference numerals throughout the drawings. In the attached image:
图1示出了本发明提供的第一种数据表归档方法的流程图；Fig. 1 shows the flow chart of the first data table filing method provided by the present invention;
图2示出了本发明提供的第二种数据表归档方法的流程图；Fig. 2 shows the flow chart of the second data table filing method provided by the present invention;
图3示出了本发明提供的第三种数据表归档方法的流程图；Fig. 3 shows the flow chart of the third data table filing method provided by the present invention;
图4示出了本发明提供的第四种数据表归档方法的流程图；Fig. 4 shows the flow chart of the fourth data table filing method provided by the present invention;
图5示出了本发明提供的第五种数据表归档方法的流程图；Fig. 5 shows the flow chart of the fifth data table filing method provided by the present invention;
图6示出了本发明提供的第六种数据表归档方法的流程图；Fig. 6 shows the flow chart of the sixth data table filing method provided by the present invention;
图7示出了本发明提供的一种数据表归档装置的结构示意图；7 shows a schematic structural diagram of a data table filing device provided by the present invention;
图8示出了本发明提供的一种电子设备的结构示意图。FIG. 8 shows a schematic structural diagram of an electronic device provided by the present invention.
具体实施方式Detailed ways
下面将参照附图更详细地描述本发明的示例性实施例。虽然附图中显示了本发明的示例性实施例，然而应当理解，可以以各种形式实现本发明而不应被这里阐述的实施例所限制。相反，提供这些实施例是为了能够更透彻地理解本发明，并且能够将本发明的范围完整的传达给本领域的技术人员。Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. While exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention may be embodied in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided so that the present invention will be more thoroughly understood, and will fully convey the scope of the present invention to those skilled in the art.
如图1所示，本发明提供了一种数据表归档方法，包括：S100、S200、S300、S400和S500；As shown in FIG. 1, the present invention provides a data table filing method, including: S100, S200, S300, S400 and S500;
S100、从配置表中获得待归档的原表的信息，并获得所述原表和归档表；S100, obtain the information of the original table to be archived from the configuration table, and obtain the original table and the archived table;
可选的，本发明所说的原表是需要归档的表，即原表中的一些数据需要归档到后续所说的归档表中，本发明对此不做限制。Optionally, the original table mentioned in the present invention is a table that needs to be archived, that is, some data in the original table needs to be archived into the subsequent archive table, which is not limited in the present invention.
可选的，本发明所说的归档表是存放归档数据的表，即本发明的目标是将原表中的数据归档到归档表中，本发明对此不做限制。Optionally, the archive table mentioned in the present invention is a table for storing archived data, that is, the goal of the present invention is to archive the data in the original table into the archive table, which is not limited in the present invention.
可选的，本发明从配置表中除了可以获得原表的信息，还可以获得中间表的表名、归档表的表名和归档表是否为分区表等信息，本发明对此不做限制。Optionally, the present invention can obtain information such as the table name of the intermediate table, the table name of the archive table and whether the archive table is a partition table in addition to the information of the original table from the configuration table, which is not limited in the present invention.
S200、根据所述原表的信息，创建所述原表中的归档数据的中间表；S200, according to the information of the original table, create an intermediate table of the archived data in the original table;
其中，所述中间表同步所述原表的表名注释、列名注释和列的默认值；Wherein, the intermediate table synchronizes the table name annotation, column name annotation and default value of the column of the original table;
可选的，关系型数据库中数据表由多个列组成，每个列有一个列的名称，叫列名，列名有相应的注释信息及默认值，本发明对此不做限制。Optionally, the data table in the relational database is composed of multiple columns, each column has a column name, which is called a column name, and the column name has corresponding comment information and default values, which are not limited in the present invention.
可选的，原表中的数据，有可能一部分是已经归档过的数据，一部分是还未归档过的数据(归档数据)，本发明可以仅针对归档数据创建相应的中间表，中间表的表名可以是在S100中从配置表中获得的，本发明对此不做限制。Optionally, the data in the original table may be partly archived data and partly unarchived data (archive data), the present invention can create a corresponding intermediate table only for the archived data, the table of the intermediate table. The name may be obtained from the configuration table in S100, which is not limited in the present invention.
如图2所示，结合图1所示的实施方式，在某些可选的实施方式中，在所述S200之后，所述方法还包括：210、S220和S230；As shown in FIG. 2, in combination with the embodiment shown in FIG. 1, in some optional embodiments, after the S200, the method further includes: 210, S220, and S230;
S210、将所述原表中的非归档数据存储至所述中间表；S210, storing the non-archived data in the original table to the intermediate table;
可选的，如前所述，原表中的归档数据后续将归档至归档表，为了数据的安全性，原表中的非归档数据可以存储至中间表，以便于后续删除原表，本发明对此不做限制。Optionally, as mentioned above, the archived data in the original table will be subsequently archived to the archived table. For data security, the non-archived data in the original table can be stored in the intermediate table to facilitate subsequent deletion of the original table. There is no restriction on this.
S220、从所述原表相应的数据字典中获得所述原表的索引信息和约束信息；S220, obtaining the index information and constraint information of the original table from the corresponding data dictionary of the original table;
可选的，索引信息和约束信息均是本领域的公知概念，本发明对此不做赘述，具体请参见本领域的相关说明。需要说明的是：索引是单独的存储结构，基于数据表的一列或多列建立，指向列值的数据页的逻辑指针，类似于书本的目录；约束指对数据表中列的值的规则要求。Optionally, both the index information and the constraint information are well-known concepts in the art, which will not be repeated in the present invention. For details, please refer to the relevant description in the art. It should be noted that an index is a separate storage structure, established based on one or more columns of a data table, a logical pointer to the data page of the column value, similar to the directory of a book; constraints refer to the rule requirements for the values of the columns in the data table .
本发明所说的索引信息可以是B树索引、HASH索引、全文索引、位图索引等数据库中支持的不同结构的索引类型的信息。The index information mentioned in the present invention may be information of index types of different structures supported in databases such as B-tree index, HASH index, full-text index, and bitmap index.
本发明所说的约束信息可以包括：非空约束、唯一约束、主键约束、外键约束、默认值约束和检查约束等信息。The constraint information mentioned in the present invention may include information such as non-null constraints, unique constraints, primary key constraints, foreign key constraints, default value constraints and check constraints.
S230、根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理。S230. According to the index information of the original table and the constraint information of the original table, in the parallel manner, perform corresponding primary key establishment processing, index establishment processing and effective constraint processing on the intermediate table.
可选的，本发明可以通过同步开启多个进程的方式，并行进行相应的建立主键处理、建立索引处理和生效约束处理，本发明对此不做限制。Optionally, the present invention may perform the corresponding primary key establishment processing, index establishment processing and effective constraint processing in parallel by synchronously starting multiple processes, which is not limited in the present disclosure.
可选的，本发明对于建立主键处理、建立索引处理和生效约束处理不做限制。Optionally, the present invention does not limit the processing of establishing the primary key, the processing of establishing the index, and the processing of validating constraints.
例如，如图3所示，在某些可选的实施方式中，所述S230，包括：S231、S232和S233；For example, as shown in FIG. 3, in some optional implementation manners, the S230 includes: S231, S232 and S233;
S231、根据所述原表的索引信息，建立所述中间表的索引；S231, establishing the index of the intermediate table according to the index information of the original table;
S232、根据所述原表的约束信息，生效所述中间表的主键的唯一约束和所述中间表的其它约束；S232, validating the unique constraint of the primary key of the intermediate table and other constraints of the intermediate table according to the constraint information of the original table;
可选的，生效唯一约束和其它约束可以理解为将唯一约束和其它约束设置为可见状态，本发明对此不做限制。Optionally, validating the unique constraint and other constraints may be understood as setting the unique constraint and other constraints to a visible state, which is not limited in the present invention.
S233、根据所述原表的索引信息和所述原表的约束信息，建立所述中间表的主键。S233. Establish a primary key of the intermediate table according to the index information of the original table and the constraint information of the original table.
具体地，本发明可以将索引和唯一约束组合得到主键，本发明对此不做限制。Specifically, the present invention can combine an index and a unique constraint to obtain a primary key, which is not limited in the present invention.
可选的，本发明所说的原表的主键的唯一约束与中间表的主键的唯一约束一致，本发明对此不做限制。Optionally, the unique constraint of the primary key of the original table mentioned in the present invention is consistent with the unique constraint of the primary key of the intermediate table, which is not limited in the present invention.
可选的，如图4所示，结合上一个实施方式，在某些可选的实施方式中，在所述S233之后，所述方法还包括：S240；Optionally, as shown in FIG. 4, in combination with the previous embodiment, in some optional embodiments, after the S233, the method further includes: S240;
S240、删除所述原表，并将所述中间表维护为新的原表。S240. Delete the original table, and maintain the intermediate table as a new original table.
S300、对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理；S300. Perform primary key deletion processing, invalid index processing, invalid constraint processing, and new partition processing on the archive table;
可选的，本发明对于删除主键处理、失效索引处理、失效约束处理和新增分区处理不做限制。Optionally, the present invention does not limit the processing of deleting primary keys, processing invalid indexes, processing invalid constraints, and processing new partitions.
例如，如图5所示，结合图1所示的实施方式，在某些可选的实施方式中，所述S300，包括：S310、S320、S330和S340；For example, as shown in FIG. 5, in combination with the embodiment shown in FIG. 1, in some optional embodiments, the S300 includes: S310, S320, S330, and S340;
S310、删除所述归档表中的主键；S310, delete the primary key in the archive table;
可选的，删除主键可以减少归档表插入数据时对主键索引的维护，提高归档数据插入的效率。Optionally, deleting the primary key can reduce the maintenance of the primary key index when inserting data into the archive table, and improve the efficiency of inserting archived data.
S320、将所述归档表的索引均设置为不可见状态；S320, the index of described archive table is all set to invisible state;
可选的，将索引设置为不可见状态从而使索引不可用，进而使得数据插入时，无需进行数据校验，无需进行单条记录的索引维护，提高数据归档效率。Optionally, the index is set to an invisible state so that the index is unavailable, so that when data is inserted, data verification is not required, and index maintenance for a single record is not required, thereby improving data archiving efficiency.
S330、将所述归档表的约束均设置为不可见状态；S330, the constraints of the archive table are all set to an invisible state;
可选的，将约束设置为不可见状态从而使约束不可用，进而使得数据插入时，无需进行单条记录的约束维护，提高数据归档效率。Optionally, the constraint is set to an invisible state so that the constraint is unavailable, so that when data is inserted, there is no need to perform constraint maintenance on a single record, thereby improving the efficiency of data archiving.
S340、若所述归档表为分区表，则调用分区维护功能，在所述归档表中建立新的新增分区。S340. If the archive table is a partition table, call a partition maintenance function to create a new partition in the archive table.
可选的，如前所述，本发明可以基于在S100中获得的归档表是否为分区表等信息，判断所述归档表是否为分区表，本发明对此不做限制。Optionally, as described above, the present invention may determine whether the archive table is a partition table based on information such as whether the archive table obtained in S100 is a partition table, which is not limited in the present invention.
需要说明的是：分区是将数据表的数据根据相应的关键字分成更小的子集，分区表即为划分为多个子集的表，本发明对此不做限制。It should be noted that: partitioning is dividing the data of the data table into smaller subsets according to corresponding keywords, and the partitioning table is a table divided into multiple subsets, which is not limited in the present invention.
可选的，S310、S320、S330和S340之间没有必然的先后执行顺序，本发明对此不做具体限制，任何可行的方式均属于本发明的保护范围。Optionally, S310, S320, S330, and S340 are not necessarily executed sequentially, which is not specifically limited by the present invention, and any feasible manner falls within the protection scope of the present invention.
S400、将所述归档数据存储至所述归档表的新增分区；S400, storing the archive data in the newly added partition of the archive table;
可选的，在实际存储过程中，新增分区可以根据归档数据的数据量进行适应变化，即新增分区的大小根据归档数据的数据量自适应调整，本发明对此不做限制。Optionally, in the actual storage process, the newly added partition can be adaptively changed according to the data volume of the archived data, that is, the size of the newly added partition can be adaptively adjusted according to the data volume of the archived data, which is not limited in the present invention.
S500、通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理。S500. Perform primary key reconstruction processing, index reconstruction processing, and effective constraint processing on the archive table in a parallel manner.
可选的，如前所述，本发明可以通过同步开启多个进程的方式，并行对所述归档表进行重建主键处理、重建索引处理和生效约束处理，本发明对此不做限制。Optionally, as described above, the present invention can perform primary key rebuilding, index rebuilding, and validating constraint processing on the archive table in parallel by starting multiple processes synchronously, which is not limited by the present invention.
可选的，本发明对于重建主键处理、重建索引处理和生效约束处理的方式不做限制。Optionally, the present invention does not limit the methods of rebuilding the primary key, rebuilding the index, and processing the effective constraint.
例如，如图6所示，结合图1所示的实施方式，在某些可选的实施方式中，所述S500，包括：S510、S520、S530和S540；For example, as shown in FIG. 6, in combination with the embodiment shown in FIG. 1, in some optional embodiments, the S500 includes: S510, S520, S530, and S540;
S510、从所述原表对应的数据字典获得所述原表的主键的唯一约束信息，并从所述归档表对应的数据字典中获取所述归档表的索引信息和所述归档表的其它约束信息；S510: Obtain the unique constraint information of the primary key of the original table from the data dictionary corresponding to the original table, and obtain the index information of the archive table and other constraints of the archive table from the data dictionary corresponding to the archive table information;
S520、根据所述归档表的索引信息，并行批量重建所述归档表的索引；S520, according to the index information of the archive table, rebuild the index of the archive table in parallel batches;
S530、根据所述主键的唯一约束信息生效所述归档表的主键的唯一约束，并根据所述归档表的其它约束信息生效所述归档表的其它约束；S530, validating the unique constraint of the primary key of the archive table according to the unique constraint information of the primary key, and validating other constraints of the archive table according to other constraint information of the archive table;
S540、根据所述归档表的索引信息和所述主键的唯一约束信息，并行批量重建所述归档表的主键。S540. According to the index information of the archive table and the unique constraint information of the primary key, rebuild the primary key of the archive table in batches in parallel.
具体的，本发明可以将归档表的索引和归档表的唯一约束组合得到归档表的主键，本发明对此不做限制。Specifically, the present invention can combine the index of the archive table and the unique constraint of the archive table to obtain the primary key of the archive table, which is not limited in the present invention.
如图7所示，本发明提供了一种数据表归档装置，包括：数据获得单元100、中间表创建单元200、第一归档表处理单元300、数据存储单元400和第二归档表处理单元500；As shown in FIG. 7 , the present invention provides a data table filing device, including: a
所述数据获得单元100，用于从配置表中获得待归档的原表的信息，并获得所述原表和归档表；The
所述中间表创建单元200，用于根据所述原表的信息，创建所述原表中的归档数据的中间表，其中，所述中间表同步所述原表的表名注释、列名注释和列的默认值；The intermediate
所述第一归档表处理单元300，用于对所述归档表进行删除主键处理、失效索引处理、失效约束处理和新增分区处理；The first archive
所述数据存储单元400，与将所述归档数据存储至所述归档表的新增分区；the
所述第二归档表处理单元500，用于通过并行方式，对所述归档表进行重建主键处理、重建索引处理和生效约束处理。The second archive
结合图7所示的实施方式，在某些可选的实施方式中，所述装置还包括：第一数据存储单元、第一信息获得单元和中间表处理单元；With reference to the embodiment shown in FIG. 7, in some optional embodiments, the apparatus further includes: a first data storage unit, a first information obtaining unit, and an intermediate table processing unit;
所述第一数据存储单元、用于在所述根据所述原表的信息，创建所述原表中的归档数据的中间表之后，将所述原表中的非归档数据存储至所述中间表；The first data storage unit is configured to store the non-archive data in the original table to the intermediate table after the intermediate table of the archived data in the original table is created according to the information of the original table surface;
所述第一信息获得单元，用于从所述原表相应的数据字典中获得所述原表的索引信息和约束信息；The first information obtaining unit is configured to obtain the index information and constraint information of the original table from the corresponding data dictionary of the original table;
所述中间表处理单元，用于根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理。The intermediate table processing unit is configured to, according to the index information of the original table and the constraint information of the original table, perform corresponding primary key establishment processing, index establishment processing and effective constraints on the intermediate table in the parallel manner deal with.
结合上一个实施方式，在某些可选的实施方式中，所述装置还包括：原表删除单元；In combination with the previous embodiment, in some optional embodiments, the device further includes: an original table deletion unit;
所述原表删除单元，用于在所述根据所述原表的索引信息和所述原表的约束信息，通过所述并行方式，对所述中间表进行相应的建立主键处理、建立索引处理和生效约束处理之后，删除所述原表，并将所述中间表维护为新的原表。The original table deletion unit is configured to perform corresponding primary key establishment processing and index establishment processing on the intermediate table in the parallel manner according to the index information of the original table and the constraint information of the original table. After processing and validating constraints, the original table is deleted, and the intermediate table is maintained as a new original table.
可选的，在某些可选的实施方式中，所述中间表处理单元，包括：中间表索引建立单元、中间表约束生效单元和中间表主键建立单元；Optionally, in some optional implementation manners, the intermediate table processing unit includes: an intermediate table index establishment unit, an intermediate table constraint validation unit, and an intermediate table primary key establishment unit;
所述中间表索引建立单元，用于根据所述原表的索引信息，建立所述中间表的索引；The intermediate table index establishment unit is configured to establish an index of the intermediate table according to the index information of the original table;
所述中间表约束生效单元，用于根据所述原表的约束信息，生效所述中间表的主键的唯一约束和所述中间表的其它约束；The intermediate table constraint validation unit is configured to validate the unique constraint of the primary key of the intermediate table and other constraints of the intermediate table according to the constraint information of the original table;
所述中间表主键建立单元，用于根据所述原表的索引信息和所述原表的约束信息，建立所述中间表的主键。The intermediate table primary key establishment unit is configured to establish the primary key of the intermediate table according to the index information of the original table and the constraint information of the original table.
结合图7所示的实施方式，在某些可选的实施方式中，所述第一归档表处理单元300，包括：归档表主键删除单元、归档表索引失效单元、归档表约束失效单元和归档表新增分区单元；With reference to the embodiment shown in FIG. 7 , in some optional embodiments, the first archive
所述归档表主键删除单元，用于删除所述归档表中的主键；The primary key deletion unit of the archive table is used to delete the primary key in the archive table;
所述归档表索引失效单元，用于将所述归档表的索引均设置为不可见状态；The archive table index invalidation unit is used to set the indexes of the archive table to an invisible state;
所述归档表约束失效单元，用于将所述归档表的约束均设置为不可见状态；The archive table constraint invalidation unit is used to set the constraints of the archive table to an invisible state;
所述归档表新增分区单元，用于若所述归档表为分区表，则调用分区维护功能，在所述归档表中建立新的新增分区。The new partition unit for the archive table is configured to call a partition maintenance function if the archive table is a partition table, and create a new new partition in the archive table.
结合图7所示的实施方式，在某些可选的实施方式中，所述第二归档表处理单元500，包括：第二信息获得单元、归档表索引重建单元、归档表约束生效单元和归档表主键重建单元；With reference to the embodiment shown in FIG. 7 , in some optional embodiments, the second archive
所述第二信息获得单元，用于从所述原表对应的数据字典获得所述原表的主键的唯一约束信息，并从所述归档表对应的数据字典中获取所述归档表的索引信息和所述归档表的其它约束信息；The second information obtaining unit is configured to obtain the unique constraint information of the primary key of the original table from the data dictionary corresponding to the original table, and obtain the index information of the archive table from the data dictionary corresponding to the archive table and other constraint information of the archive table;
所述归档表索引重建单元，用于根据所述归档表的索引信息，并行批量重建所述归档表的索引；The archive table index rebuilding unit is configured to rebuild the index of the archive table in parallel batches according to the index information of the archive table;
所述归档表约束生效单元，用于根据所述主键的唯一约束信息生效所述归档表的主键的唯一约束，并根据所述归档表的其它约束信息生效所述归档表的其它约束；The archive table constraint validating unit is used for validating the unique constraint of the primary key of the filing table according to the unique constraint information of the primary key, and validating other constraints of the filing table according to other constraint information of the filing table;
所述归档表主键重建单元，用于根据所述归档表的索引信息和所述主键的唯一约束信息，并行批量重建所述归档表的主键。The archive table primary key reconstruction unit is configured to rebuild the primary key of the archive table in batches in parallel according to the index information of the archive table and the unique constraint information of the primary key.
本发明提供了一种计算机可读存储介质，其上存储有程序，所述程序被处理器执行时实现上述任一项所述的数据表归档方法。The present invention provides a computer-readable storage medium on which a program is stored, and when the program is executed by a processor, implements the data table filing method described in any one of the above.
如图8所示，本发明提供了一种电子设备70，所述电子设备70包括至少一个处理器701、以及与所述处理器701连接的至少一个存储器702、总线703；其中，所述处理器701、所述存储器702通过所述总线703完成相互间的通信；所述处理器701用于调用所述存储器702中的程序指令，以执行上述任一项所述的数据表归档方法。As shown in FIG. 8, the present invention provides an
在本发明中，诸如第一和第二等之类的关系术语仅仅用来将一个实体或者操作与另一个实体或操作区分开来，而不一定要求或者暗示这些实体或操作之间存在任何这种实际的关系或者顺序。而且，术语“包括”、“包含”或者其任何其他变体意在涵盖非排他性的包含，从而使得包括一系列要素的过程、方法、物品或者设备不仅包括那些要素，而且还包括没有明确列出的其他要素，或者是还包括为这种过程、方法、物品或者设备所固有的要素。在没有更多限制的情况下，由语句“包括一个……”限定的要素，并不排除在包括所述要素的过程、方法、物品或者设备中还存在另外的相同要素。In the present invention, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply that any such relationship exists between these entities or operations. an actual relationship or sequence. Moreover, the terms "comprising", "comprising" or any other variation thereof are intended to encompass non-exclusive inclusion such that a process, method, article or device comprising a list of elements includes not only those elements, but also includes not explicitly listed or other elements inherent to such a process, method, article or apparatus. Without further limitation, an element qualified by the phrase "comprising a..." does not preclude the presence of additional identical elements in a process, method, article or apparatus that includes the element.
本说明书中的各个实施例均采用相关的方式描述，各个实施例之间相同相似的部分互相参见即可，每个实施例重点说明的都是与其他实施例的不同之处。尤其，对于系统实施例而言，由于其基本相似于方法实施例，所以描述的比较简单，相关之处参见方法实施例的部分说明即可。Each embodiment in this specification is described in a related manner, and the same and similar parts between the various embodiments may be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, as for the system embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and for related parts, please refer to the partial descriptions of the method embodiments.
对所公开的实施例的上述说明，使本领域专业技术人员能够实现或使用本发明。对这些实施例的多种修改对本领域的专业技术人员来说将是显而易见的，本发明中所定义的一般原理可以在不脱离本发明的精神或范围的情况下，在其它实施例中实现。因此，本发明将不会被限制于本发明所示的这些实施例，而是要符合与本发明所公开的原理和新颖特点相一致的最宽的范围。The above description of the disclosed embodiments enables any person skilled in the art to make or use the present invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined in this invention may be implemented in other embodiments without departing from the spirit or scope of this invention. Thus, the present invention is not intended to be limited to the embodiments of the present invention shown, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
以上所述仅为本发明的较佳实施例而已，并非用于限定本发明的保护范围。凡在本发明的精神和原则之内所作的任何修改、等同替换、改进等，均包含在本发明的保护范围内。The above descriptions are only preferred embodiments of the present invention, and are not intended to limit the protection scope of the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention are included in the protection scope of the present invention.
Claims (10)
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210675099.XACN114880409A (en) | 2022-06-15 | 2022-06-15 | Data table filing method and related device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210675099.XACN114880409A (en) | 2022-06-15 | 2022-06-15 | Data table filing method and related device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114880409Atrue CN114880409A (en) | 2022-08-09 |
Family
ID=82681224
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210675099.XAPendingCN114880409A (en) | 2022-06-15 | 2022-06-15 | Data table filing method and related device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114880409A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116204534A (en)* | 2023-05-06 | 2023-06-02 | 深圳市华磊迅拓科技有限公司 | Data archiving method, device, equipment and storage medium |
Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106682002A (en)* | 2015-11-05 | 2017-05-17 | 中兴通讯股份有限公司 | Database synchronization method and system, source data and target data synchronization device |
| CN108255909A (en)* | 2017-07-27 | 2018-07-06 | 平安科技（深圳）有限公司 | Tables of data backup method and server based on oracle database |
| CN108470045A (en)* | 2018-03-06 | 2018-08-31 | 平安科技（深圳）有限公司 | The method and storage medium that electronic device, data chain type are filed |
| CN110489426A (en)* | 2019-08-26 | 2019-11-22 | 杭州安恒信息技术股份有限公司 | A kind of automation partition method, device and the equipment of database table |
| CN110928883A (en)* | 2018-08-31 | 2020-03-27 | 上海汽车集团股份有限公司 | A data archiving method and device |
| CN112181945A (en)* | 2020-09-28 | 2021-01-05 | 中国平安人寿保险股份有限公司 | Data archiving processing method and device, computer equipment and storage medium |
| CN112395296A (en)* | 2020-12-03 | 2021-02-23 | 平安消费金融有限公司 | Big data archiving method, device, equipment and storage medium |
| CN114003558A (en)* | 2021-11-09 | 2022-02-01 | 中国建设银行股份有限公司 | Data archiving method, device, equipment and storage medium |
- 2022
- 2022-06-15CNCN202210675099.XApatent/CN114880409A/enactivePending
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106682002A (en)* | 2015-11-05 | 2017-05-17 | 中兴通讯股份有限公司 | Database synchronization method and system, source data and target data synchronization device |
| CN108255909A (en)* | 2017-07-27 | 2018-07-06 | 平安科技（深圳）有限公司 | Tables of data backup method and server based on oracle database |
| CN108470045A (en)* | 2018-03-06 | 2018-08-31 | 平安科技（深圳）有限公司 | The method and storage medium that electronic device, data chain type are filed |
| CN110928883A (en)* | 2018-08-31 | 2020-03-27 | 上海汽车集团股份有限公司 | A data archiving method and device |
| CN110489426A (en)* | 2019-08-26 | 2019-11-22 | 杭州安恒信息技术股份有限公司 | A kind of automation partition method, device and the equipment of database table |
| CN112181945A (en)* | 2020-09-28 | 2021-01-05 | 中国平安人寿保险股份有限公司 | Data archiving processing method and device, computer equipment and storage medium |
| CN112395296A (en)* | 2020-12-03 | 2021-02-23 | 平安消费金融有限公司 | Big data archiving method, device, equipment and storage medium |
| CN114003558A (en)* | 2021-11-09 | 2022-02-01 | 中国建设银行股份有限公司 | Data archiving method, device, equipment and storage medium |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116204534A (en)* | 2023-05-06 | 2023-06-02 | 深圳市华磊迅拓科技有限公司 | Data archiving method, device, equipment and storage medium |
| CN116204534B (en)* | 2023-05-06 | 2023-07-07 | 深圳市华磊迅拓科技有限公司 | Data archiving method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109977110B (en) | Data cleaning method, device and equipment | |
| CN109165224B (en) | Indexing method for key words on block chain database | |
| US20150302111A1 (en) | Method and Apparatus for Constructing File System in Key-Value Storage System, and Electronic Device | |
| CN1754166B (en) | Navigation of a document set content space | |
| CN103678556B (en) | The method and processing equipment of columnar database processing | |
| CN111723056B (en) | Small file processing method, device, equipment and storage medium | |
| CN111753141B (en) | Data management method and related equipment | |
| CN109739828B (en) | A data processing method, device and computer-readable storage medium | |
| WO2008053493A2 (en) | Method and system for computerized management of related data records | |
| US11907251B2 (en) | Method and system for implementing distributed lobs | |
| US20220083507A1 (en) | Trust chain for official data and documents | |
| CN111046106A (en) | Cache data synchronization method, device, device and medium | |
| CN110955662A (en) | Method, computing device and storage medium for maintaining data table association relation | |
| CN114281873B (en) | A verifiable search method for medical blockchain data | |
| JP2005302038A (en) | Method and system for renaming consecutive key in b-tree | |
| CN114880409A (en) | Data table filing method and related device | |
| CN108268558A (en) | A kind of method and apparatus of data analysis | |
| JP2018045286A (en) | Pre-processing device, index addition tree data correction method, and index addition tree data correction program | |
| CN116305126A (en) | Host file detection method, electronic device and storage medium | |
| CN117425886A (en) | List-based data search with addition-only data structure | |
| CN118779285A (en) | Method, device, equipment, storage medium and program product for processing electronic files of archives | |
| CN106446080A (en) | Data query method, query service device, client device and data system | |
| WO2024022330A1 (en) | Metadata management method based on file system, and related device thereof | |
| CN115994148B (en) | Multi-table data updating method and device, electronic equipment and readable storage medium | |
| CN113568682B (en) | Rule data verification method, device, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | Application publication date:20220809 | |
| RJ01 | Rejection of invention patent application after publication |