CN113868261B - Construction method and construction system of secondary partition table under openGauss platform - Google Patents
Construction method and construction system of secondary partition table under openGauss platform Download PDFInfo
- Publication number
- CN113868261B CN113868261B CN202111192242.1A CN202111192242A CN113868261B CN 113868261 B CN113868261 B CN 113868261B CN 202111192242 A CN202111192242 A CN 202111192242A CN 113868261 B CN113868261 B CN 113868261B
- Authority
- CN
- China
- Prior art keywords
- partition
- information
- secondary partition
- storing
- primary
- 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.)
- Active
Links
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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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
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
The invention belongs to the technical field of database management systems and computer software, and particularly relates to a construction method and a construction system of a secondary partition table under a openGauss platform. The method comprises the following steps: expanding the table structure of the system table pg_part, acquiring the related information of the secondary partition from the table establishment statement, and storing the acquired secondary partition information into the system table pg_part; all partition information of a secondary partition table is loaded from a system table pg_partition; and acquiring the first-level partition according to the data or the query condition, acquiring the corresponding second-level partition information under the first-level partition, and then performing read-write operation on the data. The method can well overcome the defects of high maintenance cost and low processing efficiency of a single table containing multiple layers and a large amount of data, wherein the dimension of the first-level partition table of the openGauss system is simple.
Description
Technical Field
The invention belongs to the technical field of database management systems and computer software, and particularly relates to a construction method and a construction system of a secondary partition table under a openGauss platform.
Background
Mass data are generated at the time of the informatization age, the data amount stored in the database Shan Zhangbiao becomes larger and larger along with the advancement of time, the maintenance cost is higher and higher, and meanwhile, the efficiency of processing the table data is obviously reduced.
OpenGauss is an open source relational database management system, whose kernel is derived from PostgreSQL, as an open source, free database platform, aimed at encouraging community contribution, collaboration. The partition table function has been implemented in the openGauss system at present, for some large data tables, partitioning can be performed according to the characteristics and service requirements of data, so as to facilitate data management and maintenance, but openGauss only supports one-level partition table, when the data volume under one-level partition is still very large, the problems of increased maintenance cost and reduced processing efficiency caused by data amplification cannot be thoroughly solved. Therefore, a new data management method capable of effectively reducing the data maintenance cost and improving the data processing efficiency is needed.
Disclosure of Invention
In order to overcome the defects of high maintenance cost and low processing efficiency of a single table containing multiple layers and a large amount of data of a openGauss system, the invention provides a set of solution: according to the invention, on the basis of the primary partition, the secondary partition is constructed, the data can be divided from two dimensions, different types of partition modes are combined, so that the data can be divided more flexibly, in actual operation, the constructed secondary partition table divides the similar table data into smaller secondary partitions according to the data characteristics or service requirements, so that the data is divided more finely and accurately, the efficiency of the system is obviously improved when the system processes the data on the secondary partition in a directed manner, and the maintenance cost of the data is also reduced.
In a first aspect, the present invention provides a method for constructing a secondary partition table under openGauss platforms, where the method implements secondary partition on the basis of openGauss primary partition tables, and specifically includes the following steps:
Step one: expanding a table structure of a system table pg_part for storing partition information, acquiring related information of a secondary partition from a table establishing statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein table data is actually stored in the secondary partition;
Step two: all partition information of a secondary partition table is loaded from a system table pg_partition;
Step three: when the data of the secondary partition table is inquired or processed, the corresponding primary partition is acquired according to the data or the inquiry condition, the corresponding secondary partition information under the primary partition is acquired, and finally the data is read and written according to the information of the secondary partition.
Further, the table structure of the extended system table pg_partition in the first step of the method includes adding 4 fields in the system table pg_partition to store the secondary partition related information, where the 4 fields are used to store the secondary partition type, the secondary partition key, the secondary partition template, and the primary partition information to which the secondary partition belongs, respectively.
Further, the storing the information of the secondary partition in the first step of the method specifically includes the following steps:
(1) Analyzing to obtain a grammar tree according to grammar rules of a secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises a secondary partition name, a secondary partition type, a secondary partition key, secondary partition rule information, a secondary partition template and the like;
(2) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. Whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. checking whether the rule of the secondary partition in the secondary partition template is correct or not;
(3) Storing secondary partition information
A. Converting the related secondary partition information from a memory storage structure to a table record storage structure so as to finally store the secondary partition information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises a secondary partition type, a secondary partition key and a secondary partition template besides primary partition related information;
c. A record is inserted in the system table pg_partition for each secondary partition accordingly to save key information of the secondary partition, including secondary partition name, secondary partition type, physical file oid, table space oid, belonging partition oid, secondary partition rule, and the like.
Further, in the second method step, all partition information of the secondary partition table is loaded from the system table pg_partition, and the method specifically includes the following steps:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. Each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap;
(2) Firstly, loading all primary partition information of a secondary partition table according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information;
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. Continuing to process the secondary partition information of the next primary partition;
(4) The secondary partition information structures SubpartitionMap for all primary partitions are saved to PartitionMap.
Further, the method for acquiring the secondary partition information in the third method step specifically includes the following steps:
(1) Loading relevant information of a secondary partition table, wherein the relevant information comprises partition information;
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. Searching a target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information;
(3) Obtaining complete table information of the secondary partition through the secondary partition table information and the target secondary partition information;
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
In a second aspect, the present invention provides a system for constructing a secondary partition table under openGauss platforms, where the system includes:
The system table pg_part extension module is used for extending the table structure of the system table pg_part storing the partition information, acquiring the related information of the secondary partition from the table establishment statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein the table data is actually stored in the secondary partition;
The information loading module is used for loading all partition information of the secondary partition table from the system table pg_partition;
And the data acquisition and processing module is used for acquiring the corresponding first-level partition according to the data or the query condition when the data of the second-level partition table is queried or processed, acquiring the corresponding second-level partition information under the first-level partition, and finally performing read-write operation on the data according to the information of the second-level partition.
Further, the specific working procedure of each functional module included in the above construction system is as follows:
The system table pg_part extension module working procedure:
(1) Expanding a table structure of a system table pg_part, and newly adding 4 fields in the system table pg_part to store secondary partition related information, wherein the 4 fields are respectively used for storing secondary partition types, secondary partition keys, secondary partition templates and primary partition information of the secondary partition;
(2) Analyzing to obtain a grammar tree according to grammar rules of a secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises a secondary partition name, a secondary partition type, a secondary partition key, secondary partition rule information, a secondary partition template and the like;
(3) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. Whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. checking whether the rule of the secondary partition in the secondary partition template is correct or not;
(4) Storing secondary partition information
A. Converting the related secondary partition information from a memory storage structure to a table record storage structure so as to finally store the secondary partition information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises information of a secondary partition, a secondary partition type, a secondary partition key and a secondary partition template stored in a newly added field in addition to the primary partition related information;
c. Inserting a record in the system table pg_partition for each secondary partition correspondingly to store key information of the secondary partition, wherein the key information comprises a secondary partition name, a secondary partition type, a physical file oid, a table space oid, an affiliated partition oid, a secondary partition rule and the like;
information loading module working program:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. Each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap;
(2) Firstly, loading all primary partition information of a secondary partition table according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information;
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. Continuing to process the secondary partition information of the next primary partition;
(4) Saving the secondary partition information structures SubpartitionMap of all primary partitions to PartitionMap;
Data acquisition and processing module operating program:
(1) Loading relevant information of a secondary partition table, wherein the relevant information comprises partition information;
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. Searching a target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information;
(3) Obtaining complete table information of the secondary partition through the secondary partition table information and the target secondary partition information;
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
In summary, the method can construct the secondary partition table under the openGauss platform, compared with the primary partition table, the secondary partition table can partition data in two dimensions by using different partition combination modes, is more convenient and flexible to apply, can partition similar table data into smaller secondary partitions according to data characteristics or service requirements in the actual operation process, so that the data partitioning is finer and more accurate, meanwhile, when the data quantity under a single partition is reduced, the maintenance cost of multiple functions such as partition index and the like can be correspondingly reduced, and the efficiency of processing the data under the partition can be obviously improved, thereby well overcoming the defects of simple partitioning dimension, high maintenance cost and low processing efficiency of the single partition table containing multiple layers and a large amount of data of the openGauss system.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that need to be used in the embodiments of the present invention will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments described in the present invention, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic diagram of a general flow of a secondary partition table implementation under openGauss platform of the present invention;
FIG. 2 is a flowchart illustrating the key steps for creating a secondary partition table under the openGauss platform of the present invention;
FIG. 3 is a schematic diagram of a partition information storage structure of a secondary partition table under the openGauss platform of the present invention;
FIG. 4 is a flow chart of the loading of secondary partition information of a secondary partition table under openGauss of the present invention;
FIG. 5 is a flow chart of processing secondary partition data using a secondary partition table under the openGauss platform of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the present invention more apparent, the technical solutions of the present invention will be clearly and completely described below with reference to specific embodiments and corresponding drawings. It is apparent that the described embodiments are only some embodiments of the present invention, but not all embodiments, and the present invention may be implemented or applied by different specific embodiments, and that various modifications or changes may be made in the details of the present description based on different points of view and applications without departing from the spirit of the present invention.
Meanwhile, it should be understood that the scope of the present invention is not limited to the following specific embodiments; it is also to be understood that the terminology used in the examples of the invention is for the purpose of describing particular embodiments only, and is not intended to limit the scope of the invention.
Example 1: the construction method of the secondary partition table under openGauss platform (see figures 1-5) is based on openGauss primary partition table, and the method specifically comprises the following steps:
Step one: expanding a table structure of a system table pg_part for storing partition information, acquiring related information of a secondary partition from a table establishing statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein table data is actually stored in the secondary partition; comprising the following steps:
(1) And expanding the table structure of the system table pg_part, and newly adding 4 fields in the system table pg_part to store secondary partition related information, wherein the 4 fields are respectively used for storing secondary partition types, secondary partition keys, secondary partition templates and primary partition information to which the secondary partitions belong.
(2) And analyzing to obtain a grammar tree according to grammar rules of the secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises secondary partition names, secondary partition types, secondary partition keys, secondary partition rule information, secondary partition templates and the like.
(3) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. Whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. Checking whether the secondary partition rule in the secondary partition template is correct.
(4) Storing secondary partition information
A. Converting the related secondary partition information from a memory storage structure to a table record storage structure so as to finally store the secondary partition information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises a secondary partition type, a secondary partition key and a secondary partition template besides primary partition related information;
c. A record is inserted in the system table pg_partition for each secondary partition accordingly to save key information of the secondary partition, including secondary partition name, secondary partition type, physical file oid, table space oid, belonging partition oid, secondary partition rule, and the like.
Step two: all partition information of a secondary partition table is loaded from a system table pg_partition; comprising the following steps:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap.
(2) All primary partition information of the secondary partition table is loaded according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information.
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. the second level partition information of the next level partition is processed continuously.
(4) The secondary partition information structures SubpartitionMap for all primary partitions are saved to PartitionMap.
Step three: when the data of the secondary partition table is inquired or processed, the corresponding primary partition is acquired according to the data or the inquiry condition, the corresponding secondary partition information under the primary partition is acquired, and finally the data is subjected to read-write operation according to the information of the secondary partition; comprising the following steps:
(1) And loading relevant information of the secondary partition table, wherein the relevant information comprises partition information.
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. And then searching the target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information.
(3) And obtaining the complete table information of the secondary partition through the secondary partition table information and the target secondary partition information.
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
Example 2: a build system for a secondary partition table under openGauss platforms, the build system comprising:
The system table pg_part extension module is used for extending the table structure of the system table pg_part storing the partition information, acquiring the related information of the secondary partition from the table establishment statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein the table data is actually stored in the secondary partition; the working procedure is as follows:
(1) And expanding the table structure of the system table pg_part, and newly adding 4 fields in the system table pg_part to store secondary partition related information, wherein the 4 fields are respectively used for storing secondary partition types, secondary partition keys, secondary partition templates and primary partition information to which the secondary partitions belong.
(2) And analyzing to obtain a grammar tree according to grammar rules of the secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises secondary partition names, secondary partition types, secondary partition keys, secondary partition rule information, secondary partition templates and the like.
(3) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. Whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. Checking whether the secondary partition rule in the secondary partition template is correct.
(4) Storing secondary partition information
A. Converting the related secondary partition information from a memory storage structure to a table record storage structure so as to finally store the secondary partition information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises a secondary partition type, a secondary partition key and a secondary partition template besides primary partition related information;
c. A record is inserted in the system table pg_partition for each secondary partition accordingly to save key information of the secondary partition, including secondary partition name, secondary partition type, physical file oid, table space oid, belonging partition oid, secondary partition rule, and the like.
The information loading module is used for loading all partition information of the secondary partition table from the system table pg_partition; the working procedure is as follows:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap.
(2) All primary partition information of the secondary partition table is loaded according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information.
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. the second level partition information of the next level partition is processed continuously.
(4) The secondary partition information structures SubpartitionMap for all primary partitions are saved to PartitionMap.
The data acquisition and processing module is used for acquiring a corresponding first-level partition according to data or query conditions when inquiring or processing the data of the second-level partition table, acquiring the corresponding second-level partition information under the first-level partition, and finally performing read-write operation on the data according to the information of the second-level partition; the working procedure is as follows:
(1) And loading relevant information of the secondary partition table, wherein the relevant information comprises partition information.
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. And then searching the target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information.
(3) And obtaining the complete table information of the secondary partition through the secondary partition table information and the target secondary partition information.
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
Example 3: an electronic device (electronic apparatus) for building a secondary partition table under a openGauss platform, the electronic device comprising a processor and a memory, the memory for storing a program, the processor for running the program to implement the method for building a secondary partition table under a openGauss platform as described in embodiment 1.
The embodiments of the present invention are described in a progressive manner, and the same or similar parts of the embodiments are all referred to each other, and each embodiment is mainly different from other embodiments.
The foregoing is merely exemplary of the present invention and is not intended to limit the present invention. Various modifications and variations of the present invention will be apparent to those skilled in the art. Any modification, replacement, improvement, etc. that comes within the spirit and principle of the present invention should be included in the scope of the claims of the present invention.
Claims (4)
1. The construction method of the secondary partition table under the openGauss platform is characterized by comprising the following steps of:
Step one: expanding a table structure of a system table pg_part for storing partition information, acquiring related information of a secondary partition from a table establishing statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein table data is actually stored in the secondary partition;
Step two: all partition information of a secondary partition table is loaded from a system table pg_partition;
step three: when the data of the secondary partition table is inquired or processed, the corresponding primary partition is acquired according to the data or the inquiry condition, the corresponding secondary partition information under the primary partition is acquired, and finally the data is subjected to read-write operation according to the information of the secondary partition;
The first step further comprises:
(1) Newly adding 4 fields in the system table pg_partition to store secondary partition related information, wherein the 4 fields are respectively used for storing secondary partition types, secondary partition keys, secondary partition templates and primary partition information of the secondary partition;
(2) Analyzing to obtain a grammar tree according to grammar rules of a secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises a secondary partition name, a secondary partition type, a secondary partition key, secondary partition rule information and a secondary partition template;
(3) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. checking whether the rule of the secondary partition in the secondary partition template is correct or not;
(4) Storing secondary partition related information
A. Converting the related information of the secondary partition from a memory storage structure to a table record storage structure so as to finally store the related information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises a secondary partition type, a secondary partition key and a secondary partition template besides primary partition related information;
c. inserting a record in the system table pg_partition for each secondary partition correspondingly to store key information of the secondary partition, wherein the key information comprises a secondary partition name, a secondary partition type, a physical file oid, a table space oid, an affiliated partition oid and a secondary partition rule;
in the second step, all partition information of the secondary partition table is loaded from the system table pg_partition, and the method specifically comprises the following steps:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. Each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap;
(2) Firstly, loading all primary partition information of a secondary partition table according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information;
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. Inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. Acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. Sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. Continuing to process the secondary partition information of the next primary partition;
(4) The secondary partition information structures SubpartitionMap for all primary partitions are saved to PartitionMap.
2. The method according to claim 1, wherein the obtaining the corresponding secondary partition information under the primary partition in the third step specifically includes the following steps:
(1) Loading relevant information of a secondary partition table, wherein the relevant information comprises partition information;
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. Searching a target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information;
(3) Obtaining the complete table information of the secondary partition through the related information of the secondary partition table and the target secondary partition information;
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
3. A system for building a secondary partition table under openGauss platforms, the system comprising:
The system table pg_part extension module is used for extending the table structure of the system table pg_part storing the partition information, acquiring the related information of the secondary partition from the table establishment statement, storing the acquired related information of the secondary partition into the system table pg_part, and simultaneously storing a data dictionary of the secondary partition in the system table pg_part, wherein the table data is actually stored in the secondary partition;
The information loading module is used for loading all partition information of the secondary partition table from the system table pg_partition;
the data acquisition and processing module is used for acquiring a corresponding first-level partition according to data or query conditions when inquiring or processing the data of the second-level partition table, acquiring the corresponding second-level partition information under the first-level partition, and finally performing read-write operation on the data according to the information of the second-level partition;
The system table pg_part extension module further comprises the following functions:
(1) Expanding a table structure of a system table pg_part, and newly adding 4 fields in the system table pg_part to store secondary partition related information, wherein the 4 fields are respectively used for storing secondary partition types, secondary partition keys, secondary partition templates and primary partition information of the secondary partition;
(2) Analyzing to obtain a grammar tree according to grammar rules of a secondary partition table, and obtaining secondary partition related information from the grammar tree, wherein the secondary partition related information comprises a secondary partition name, a secondary partition type, a secondary partition key, secondary partition rule information and a secondary partition template;
(3) And carrying out validity check on the acquired secondary partition related information, wherein the method comprises the following steps of:
a. whether the secondary partition type is supported;
b. Whether the secondary partition key type is supported;
c. Whether the rule between each secondary partition is correct or not, and whether the secondary partition names are repeated or not;
d. checking whether the rule of the secondary partition in the secondary partition template is correct or not;
(4) Storing secondary partition related information
A. Converting the related information of the secondary partition from a memory storage structure to a table record storage structure so as to finally store the related information into a system table pg_part;
b. Storing a piece of basic information recorded for storing the partition table in the system table pg_partition, wherein the basic information comprises a secondary partition type, a secondary partition key and a secondary partition template besides primary partition related information;
c. inserting a record in the system table pg_partition for each secondary partition correspondingly to store key information of the secondary partition, wherein the key information comprises a secondary partition name, a secondary partition type, a physical file oid, a table space oid, an affiliated partition oid and a secondary partition rule;
The information loading module further comprises the following functions:
(1) Arrangement of secondary partition information structure SubpartitionMap:
a. In the structure PartitionMap for storing partition information, a SubpartitionMap field is added to store all the corresponding secondary partition information under each partition;
b. Each primary partition rule information PARTELEMENT in the structure PartitionMap corresponds to one secondary partition information structure SubpartitionMap;
(2) Firstly, loading all primary partition information of a secondary partition table according to the original flow, wherein the information is stored in a structural body PartitionMap of the partition information;
(3) After the primary partition information is loaded, PARTELEMENT is ordered according to the primary partition rule, and then all secondary partition information corresponding to each primary partition is loaded according to PARTELEMENT sequence, including:
a. Inquiring records of all secondary partitions under the partition from a system table pg_partition according to oid of the primary partition, and acquiring basic information and rule information of the secondary partition from the records;
b. Acquiring the type of the secondary partition and the number of the secondary partition, and storing the type and the number of the secondary partition into a secondary partition information structure SubpartitionMap;
c. Acquiring secondary partition key information, converting the secondary partition key information into a memory storage structure, and then storing the memory storage structure into a secondary partition information structure SubpartitionMap;
d. Acquiring rule information of the secondary partitions one by one, converting the rule information into a memory storage structure after processing, and storing the memory storage structure into SubpartitionMap- > PARTELEMENT;
e. Sorting SubpartitionMap- > PARTELEMENT according to a secondary partition rule;
f. Continuing to process the secondary partition information of the next primary partition;
(4) The secondary partition information structures SubpartitionMap for all primary partitions are saved to PartitionMap.
4. A building system according to claim 3, wherein the data acquisition and processing module further comprises:
(1) Loading relevant information of a secondary partition table, wherein the relevant information comprises partition information;
(2) Obtaining a target secondary partition:
a. Firstly, according to target data or conditions, information of a matched target partition is searched from a partition information structure PartitionMap of a secondary partition table;
b. acquiring all secondary partition information structures SubpartitionMap of the corresponding partition according to the target partition information;
c. Searching a target secondary partition from the secondary partition information structure SubpartitionMap, and acquiring target secondary partition information;
(3) Obtaining the complete table information of the secondary partition through the related information of the secondary partition table and the target secondary partition information;
(4) And processing the data on the secondary partition according to the complete table information of the secondary partition.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111192242.1A CN113868261B (en) | 2021-10-13 | 2021-10-13 | Construction method and construction system of secondary partition table under openGauss platform |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111192242.1A CN113868261B (en) | 2021-10-13 | 2021-10-13 | Construction method and construction system of secondary partition table under openGauss platform |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113868261A CN113868261A (en) | 2021-12-31 |
| CN113868261B true CN113868261B (en) | 2024-08-06 |
Family
ID=78999081
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111192242.1A Active CN113868261B (en) | 2021-10-13 | 2021-10-13 | Construction method and construction system of secondary partition table under openGauss platform |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113868261B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115438049A (en) * | 2022-10-20 | 2022-12-06 | 中国农业银行股份有限公司 | Partition table historical data storage method and device and computer readable medium |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102486798A (en) * | 2010-12-03 | 2012-06-06 | 腾讯科技（深圳）有限公司 | Data loading method and device |
| CN112364027A (en) * | 2020-12-09 | 2021-02-12 | 北京海量数据技术股份有限公司 | Method, device and system for creating openGauss partition table index in parallel |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US10002148B2 (en) * | 2014-07-22 | 2018-06-19 | Oracle International Corporation | Memory-aware joins based in a database cluster |
| US10657116B2 (en) * | 2015-10-19 | 2020-05-19 | Oracle International Corporation | Create table for exchange |
| US11347709B2 (en) * | 2020-04-01 | 2022-05-31 | Sap Se | Hierarchical metadata enhancements for a memory management system |
-
2021
- 2021-10-13 CN CN202111192242.1A patent/CN113868261B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102486798A (en) * | 2010-12-03 | 2012-06-06 | 腾讯科技（深圳）有限公司 | Data loading method and device |
| CN112364027A (en) * | 2020-12-09 | 2021-02-12 | 北京海量数据技术股份有限公司 | Method, device and system for creating openGauss partition table index in parallel |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113868261A (en) | 2021-12-31 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110659282B (en) | Data route construction method, device, computer equipment and storage medium | |
| CN106407360B (en) | Data processing method and device | |
| CN106407303A (en) | Data storage method and apparatus, and data query method and apparatus | |
| CN111858607B (en) | Data processing method, device, electronic equipment and computer readable medium | |
| CN111159184A (en) | Metadata tracing method and device and server | |
| CN117112590A (en) | Method for generating structural query language and data query equipment | |
| CN113868261B (en) | Construction method and construction system of secondary partition table under openGauss platform | |
| CN114253995A (en) | Data tracing method, device, equipment and computer readable storage medium | |
| CN113609128A (en) | Method and device for generating database entity class, terminal equipment and storage medium | |
| CN115757479A (en) | Database query optimization method, machine-readable storage medium and computer device | |
| CN113868267A (en) | Method for injecting time sequence data, method for inquiring time sequence data and database system | |
| CN115905630A (en) | Graph database query method, device, equipment and storage medium | |
| CN111078671A (en) | Method, device, equipment and medium for modifying data table field | |
| CN108460092B (en) | Automatic generation method and system for sql query statement containing database built-in function | |
| CN112182028B (en) | Data line number query method and device based on table of distributed database | |
| CN117909301B (en) | Index-based object query method, device, equipment and medium | |
| CN109697234B (en) | Multi-attribute information query method, device, server and medium for entity | |
| CN118445302A (en) | Performance capacity assessment method and device for database management system | |
| CN115809268B (en) | Adaptive query method and device based on fragment index | |
| CN116401245A (en) | A data index construction method and system | |
| CN115292322A (en) | Data query method, device, equipment and medium | |
| CN113792055A (en) | Data processing method, electronic device and storage medium | |
| CN113868138A (en) | Method, system, equipment and storage medium for acquiring test data | |
| CN115794842B (en) | Data processing method, device, electronic equipment and medium | |
| CN117312319B (en) | Metadata-based data storage method, device, 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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |