CN101145158A - Data base table partition method - Google Patents
Data base table partition method Download PDFInfo
- Publication number
- CN101145158A CN101145158A CNA2007101106338A CN200710110633A CN101145158A CN 101145158 A CN101145158 A CN 101145158A CN A2007101106338 A CNA2007101106338 A CN A2007101106338A CN 200710110633 A CN200710110633 A CN 200710110633A CN 101145158 A CN101145158 A CN 101145158A
- Authority
- CN
- China
- Prior art keywords
- subregion
- output valve
- field
- calculated
- data
- 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
Abstract
The present invention discloses a partitioned method of a database table, including the following steps: <1> the needed partitioned parameters are predefined, a certain field of the table is designated, and the output value of the field is worked out by calculation; <2> an additional field is added to the table, and the calculated output value of the selected field in <1> is deposited; <3> the table is divided into a plurality of partitions according to the output value in <1>. The output value of the recorded selected field is worked out based on <1>, contrasted with the plurality of partitions and deposited in a corresponding partition. The present invention maintains the core advantage of the prior partitioned mode that all records are well distributed, and fully enhances the autonomy, the flexibility and the adaptability of the partitioning as well.
Description
Technical field
The present invention relates to use the field of software development of database (Database), sybase for example, oracle, mssql or the like as data storage medium.
Background technology
Data query all is a basic function in large-scale relevant database application system, inquires about such as the history alarm in the telecommunication network management system.When data volume is very big, carry out the key element that efficient just becomes data query.Have a lot of methods to can be used to improve search efficiency, the table subregion is exactly one of them.It is set about from the storage organization of data, and the record burst of different characteristics is deposited, and each burst has clear and definite feature, can dwindle the scanning work amount that query manipulation need carry out to a great extent.
Existing data base management system (DBMS) DBMS (as: Sybase, oracle and Microsoft SQLServer) provide following three kinds of typical partition schemes:
1, hash subregion:
1) certain field of option table is in order to calculate the hash value;
2) at this Field Definition hash subregion;
3) the selected field hash logistic by record goes out value, is stored in corresponding subregion;
4) the corresponding different hash output valve of each subregion, the record that all hash values are identical is stored in same subregion;
2, list subregion:
1) certain field of option table is in order to subregion;
2) list by the possible different values of these fields, be divided into some subregions in advance, the corresponding value set of each subregion, set does not overlap mutually;
The selected field and the subregion that 3) will write down compare, the subregion that the value of being stored in equates;
3, Range subregion:
1) certain field of option table is in order to subregion;
2) by the possible different spans of these fields, be divided into some subregions in advance, the corresponding possible interval of each subregion;
The selected field and the subregion that 3) will write down compare, and the value of being stored in falls into interval corresponding subregion;
More than 3 kinds of typical partitioned modes, all good action is arranged to improving efficiency data query.But in the practical application, also all expose significant disadvantages simultaneously:
1), do not need the value of default selected field, so can not produce the problem of two kinds of partitioned modes of present face for the hash subregion.But because the hash algorithm that the deposit position of record is included by DBMS decision, so we can't be by the characteristics of application-specific, specify voluntarily certain bar concrete be recorded in that subregion.
2), need enumerate the selected all possible values of field in the time of owing to the definition subregion, and not all field can be accomplished this point, has therefore limited applicable surface for the list subregion.
3) for the range subregion, if the distribution of the selected field value of record is in a state that constantly changes, preassigned interval in the time of may defining subregion can make the equally distributed effect of record because of this variation has lost over time.
Just because of above shortcoming, make the application of traditional subregion in telecommunicatioin network management software be subjected to considerable restraint.
Summary of the invention
Technical matters solved by the invention is to provide a kind of database table partitioned method, can't be to solve existing different partition method by the characteristics of application-specific, and because definition preassigned interval during subregion can lose problems such as making the equally distributed effect of record because of this variation over time.
In order to address the above problem, the invention provides a kind of database table partitioned method, it is characterized in that, may further comprise the steps:
(1) pre-determine needed partitioned parameters, certain field of named list, and carry out the output valve that calculates this field;
(2), and deposit the selected field of step (1) through the output valve after calculating for table increases a field;
(3) the output valve his-and-hers watches according to step (1) are divided into some subregions, and the selected field of record is calculated output valve according to step (1), compare with described some subregions, are stored in the corresponding subregion.
Method of the present invention, wherein, described database table subregion is the list subregion based on calculated column.
Wherein, carry out described in the step (1) and calculate, comprising:
(111) value with certain field of the specified table of step (1) is converted to the integer type data;
(112) according to the number that pre-determines in the needed partitioned parameters;
(113) data that step (111) conversion is got are carried out: output valve=data % subregion number+1.
Wherein, step (3) comprising:
(311) list the output valve after as calculated of specific field in the step (1), be divided into some subregions, the corresponding output valve set of each subregion, set does not overlap mutually;
(312) the selected field that will write down compares according to step (1) calculating output valve and subregion, the subregion that the value of being stored in equates.
Method of the present invention, wherein, described database table subregion is the range subregion based on calculated column.
Wherein, carry out described in the step (1) and calculate, comprising:
(121) value with certain field of the specified table of step (1) is converted to the integer type data;
(122) according to the interval size that pre-determines in the needed partitioned parameters, the equal and opposite in direction that each is interval;
(123) data that step (121) conversion is got are carried out: the interval size of output valve scope=data % subregion.
Wherein, step (3) comprising:
(321) list the output valve scope after as calculated of specific field in the step (1), be divided into some subregions, the corresponding output valve scope of each subregion;
(322) the selected field that will write down is calculated the output valve scope according to step (1) and subregion compares, and is stored in the subregion of the correspondence that the output valve scope fallen into.
The invention provides the database table partition method after a kind of improve, the core advantage that had both kept existing partitioned mode " all records evenly to be distributed " fully improves independence, dirigibility, the adaptability of subregion again.
Description of drawings
Fig. 1 is the described partition method synoptic diagram based on calculated column of the embodiment of the invention.
Embodiment
The objective of the invention is to introduce a kind of database table partitioned method, both kept the core advantage of existing partitioned mode " all are write down evenly distributes ", fully improve independence, dirigibility, the adaptability of subregion again.Below embodiment is described in detail, but not as a limitation of the invention.
Partition method described in the embodiment of the invention is that the hash thinking is applied on list and the range subregion, and the advantage fusion with them derives a kind of list and range partitioned mode based on calculated column.
In conjunction with the accompanying drawings 1, embodiment 1 is based on the list subregion example of calculated column:
Be the specific implementation method of example explanation List subregion below with the veneer table in the telecommunications optical transport network management system.
1, preliminary work
A) in the veneer table, add a calculated column ID, and select network element to classify the subregion calculated column as;
B) according to actual needs, set the number of subregion.Be assumed to be 100.
2, carry out list subregion for the veneer table based on calculated column
A) round values that will from 1 to 100 is divided into some set on demand, and is integrated into ID according to these and lists definition list subregion;
B) insert the method that writes down:
1) network element of getting the veneer record is numbered, and is assumed to be character string type " 1001 ", and converting thereof into round values is 1001;
2) carry out specific calculating: output valve=1001%100+1=2 to 1001;
3) with the output valve 2 that calculates as the id field value that is inserted into record;
4) carry out to insert, this record will the list subregion of the value of being stored in 2 correspondences in.
C) Cha Xun method
One of common inquiry of telecommunications Optical Transmission Network OTN guard system is " obtaining the veneer that belongs to this network element according to element name ".At this moment need the element name that issues is calculated earlier, then the calculating output valve is added in the where statement, as:
Veneer under the inquiry network element " 1001 ", the condition that constructs is:
where?ID＝2?and?MeName＝‘1001’
Like this, the query analyzer of DBMS will in corresponding subregion scope, improve search efficiency with scanning limit(s) according to this condition of ID=2.
D) method for updating
Similar with querying method, need in being updated the where condition of record, the location add calculated value.As all single board states that upgrade network element " 1001 " are 3, and the condition that constructs is:
where?ID＝2?and?MeName＝’1001’
In conjunction with the accompanying drawings 1, embodiment 2 is based on the range subregion example of calculated column:
For the interval size of output valve scope=data % subregion, the interval size of Range subregion is an integer, is applicable to range (scope) subregion.The size in subregion interval has shown the span that is assigned to same subregion.
For example:, four field A/B/C/D are arranged if any a table.The A field is an integer, now will carry out range (scope) subregion on the A field.Suppose that the interval size of the subregion of determining is 100, definite simultaneously A field value then can be carried out subregion in such a way since 1:
1～100 1 district;
101～200 1 districts;
201～300 1 districts; Or the like.
For the range that describes among the embodiment in this patent (scope) subregion for example, by the HASH calculated column, then be to carry out subregion according to month, the interval size of output valve=data % subregion.For example: since on January 1st, 2007, be assigned to a district annual January, be assigned to a district annual February; As, the data in January in 2008 and the data in January, 2007 are assigned to a district.
Be the range partition method of example explanation based on calculated column with the history alarm table in the telecommunications optical transport network management system more below, concrete steps are as follows:
1, preliminary work
A) in history alarm, add a calculated column ID, and select generation time to classify the subregion calculated column as; The generation time type is that the form of varchar (14) is year (4) month (2) day (2) hour (2) minute (2), such as " 200701021020 ";
B) according to actual needs, set the interval size of subregion.Here hypothesis is want according to month under the generation time all history alarms evenly to be distributed, and then interval size is 100000000, year this position of corresponding character string.
2, carry out range subregion for the veneer table based on calculated column
A) will be by dividing the calculated value interval January to Dec, that is:
1000000～2000000 corresponding subregions 1 (January),
2000000～3000000 corresponding subregions 2 (February),
B) insert the method that writes down:
Get the generation time of history alarm record, be assumed to be " 200701021020 ", converting thereof into round values is 200701021020;
Carry out specific calculating to 200701021020:
Output valve=200701021020%100000000=1021020;
With the output valve 1021020 that calculates as the id field value that is inserted into record;
Carry out to insert, this record will the range subregion of the value of being stored in correspondence in February in.
C) Cha Xun method
One of common inquiry of telecommunications Optical Transmission Network OTN guard system is " history alarm that obtains certain time period ".At this moment need the time period that issues is worth earlier from beginning to end and calculate, then the calculating output valve is added in the where statement, as:
The history alarm record in query time on February 20th, 2007, the time period that issues arrives " 200702210000 " for " 200702200000 " from beginning to end, after these two strings converted round values to and carry out calculating respectively, the output that obtains was 2, so it is added in the where statement:
where?ID>＝2?and?ID<＝2?and......
Like this, the query analyzer of DBMS will in corresponding subregion scope, improve search efficiency with scanning limit(s) according to this condition of ID=2.
D) method for updating
Similar with querying method, need in being updated the where condition of record, the location add calculated value.All history alarms as update date " 200702210000 " are expired, and the condition that constructs is:
where?ID＝2?and......
The embodiment of the invention is described to provide a kind of scheme of list and the range subregion based on calculated column, has compared following advantage with existing partitioning technique:
1, through specific calculating, will be listed as (such as the time) can not preset value range, is transformed in the fixing value set, has overcome traditional list and range subregion and has been not easy to shortcoming in these row application.
2, specific calculation method provides sufficient dirigibility, and the subregion number that divide can be set arbitrarily as required, evenly distributes so that all are recorded in each subregion, and controls the scale of each subregion.
Certainly; the present invention also can have other various embodiments; under the situation that does not deviate from spirit of the present invention and essence thereof; those of ordinary skill in the art can make various corresponding changes and distortion according to the present invention, but these corresponding changes and distortion all should belong to the protection domain of the appended claim of the present invention.
Claims (7)
1. a database table partitioned method is characterized in that, may further comprise the steps:
(1) pre-determine needed partitioned parameters, certain field of named list, and carry out the output valve that calculates this field;
(2), and deposit the selected field of step (1) through the output valve after calculating for table increases a field;
(3) the output valve his-and-hers watches according to step (1) are divided into some subregions, and the selected field of record is calculated output valve according to step (1), compare with described some subregions, are stored in the corresponding subregion.
2. the method for claim 1 is characterized in that, described database table subregion is the list subregion based on calculated column.
3. method as claimed in claim 2 is characterized in that, carries out described in the step (1) and calculates, and comprising:
(111) value with certain field of the specified table of step (1) is converted to the integer type data;
(112) according to the number that pre-determines in the needed partitioned parameters;
(113) data that step (111) conversion is got are carried out: output valve=data % subregion number+1.
4. method as claimed in claim 3 is characterized in that, step (3) comprising:
(311) list the output valve after as calculated of specific field in the step (1), be divided into some subregions, the corresponding output valve set of each subregion, set does not overlap mutually;
(312) the selected field that will write down compares according to step (1) calculating output valve and subregion, the subregion that the value of being stored in equates.
5. the method for claim 1 is characterized in that, described database table subregion is the range subregion based on calculated column.
6. method as claimed in claim 5 is characterized in that, carries out described in the step (1) and calculates, and comprising:
(121) value with certain field of the specified table of step (1) is converted to the integer type data;
(122) according to the interval size that pre-determines in the needed partitioned parameters, the equal and opposite in direction that each is interval;
(123) data that step (121) conversion is got are carried out: the interval size of output valve scope=data % subregion.
7. method as claimed in claim 6 is characterized in that, step (3) comprising:
(321) list the output valve scope after as calculated of specific field in the step (1), be divided into some subregions, the corresponding output valve scope of each subregion;
(322) the selected field that will write down is calculated the output valve scope according to step (1) and subregion compares, and is stored in the subregion of the correspondence that the output valve scope fallen into.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2007101106338A CN101145158A (en) | 2007-06-06 | 2007-06-06 | Data base table partition method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2007101106338A CN101145158A (en) | 2007-06-06 | 2007-06-06 | Data base table partition method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN101145158A true CN101145158A (en) | 2008-03-19 |
Family
ID=39207686
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CNA2007101106338A Pending CN101145158A (en) | 2007-06-06 | 2007-06-06 | Data base table partition method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101145158A (en) |
Cited By (14)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102081651A (en) * | 2010-12-29 | 2011-06-01 | 北京像素软件科技股份有限公司 | Table division method for online game database |
| CN101876983B (en) * | 2009-04-30 | 2012-11-28 | 国际商业机器公司 | Method for partitioning database and system thereof |
| CN102799628A (en) * | 2012-06-21 | 2012-11-28 | 新浪网技术(中国)有限公司 | Method and device for data partitioning in key-value database |
| CN103207835A (en) * | 2013-04-15 | 2013-07-17 | 东莞市公安局 | Mass data storage method through self-adaptive Range partitions |
| CN103324732A (en) * | 2013-06-29 | 2013-09-25 | 优视科技有限公司 | MySQL database horizontal-sharding method, and MySQL connection establishment method and device |
| CN103336792A (en) * | 2013-06-07 | 2013-10-02 | 华为技术有限公司 | Method and device for data partition |
| CN102197372B (en) * | 2008-10-24 | 2013-11-20 | 微软公司 | Partition management in a partitioned, scalable, and available structured storage |
| CN103593447A (en) * | 2013-11-18 | 2014-02-19 | 北京国双科技有限公司 | Data processing method and device applied to database table |
| CN103838770A (en) * | 2012-11-26 | 2014-06-04 | 中国移动通信集团北京有限公司 | Logic data partition method and system |
| US9323796B2 (en) | 2013-06-07 | 2016-04-26 | Huawei Technologies Co., Ltd. | Data partitioning method and apparatus |
| CN106776598A (en) * | 2015-11-19 | 2017-05-31 | 中国移动通信集团公司 | A kind of information processing method and device |
| CN108446329A (en) * | 2018-02-13 | 2018-08-24 | 北京工业大数据创新中心有限公司 | Adaptive databases partition method and system towards industrial time series database |
| CN109960693A (en) * | 2018-11-22 | 2019-07-02 | 成都长城开发科技有限公司 | One kind being based on relevant database load curve storage method |
| CN110069485A (en) * | 2017-09-21 | 2019-07-30 | 阿里巴巴集团控股有限公司 | The method and device of method and data acquisition that Data Acquisition Model is established |
-
2007
- 2007-06-06 CN CNA2007101106338A patent/CN101145158A/en active Pending
Cited By (22)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102197372B (en) * | 2008-10-24 | 2013-11-20 | 微软公司 | Partition management in a partitioned, scalable, and available structured storage |
| CN101876983B (en) * | 2009-04-30 | 2012-11-28 | 国际商业机器公司 | Method for partitioning database and system thereof |
| US9317577B2 (en) | 2009-04-30 | 2016-04-19 | International Business Macines Corporation | Method and system for database partition |
| CN102081651B (en) * | 2010-12-29 | 2013-01-30 | 北京像素软件科技股份有限公司 | Table division method for online game database |
| CN102081651A (en) * | 2010-12-29 | 2011-06-01 | 北京像素软件科技股份有限公司 | Table division method for online game database |
| CN102799628A (en) * | 2012-06-21 | 2012-11-28 | 新浪网技术(中国)有限公司 | Method and device for data partitioning in key-value database |
| CN103838770A (en) * | 2012-11-26 | 2014-06-04 | 中国移动通信集团北京有限公司 | Logic data partition method and system |
| CN103207835A (en) * | 2013-04-15 | 2013-07-17 | 东莞市公安局 | Mass data storage method through self-adaptive Range partitions |
| CN103336792B (en) * | 2013-06-07 | 2016-11-23 | 华为技术有限公司 | Data partition method and device |
| US9323796B2 (en) | 2013-06-07 | 2016-04-26 | Huawei Technologies Co., Ltd. | Data partitioning method and apparatus |
| WO2014194679A1 (en) * | 2013-06-07 | 2014-12-11 | 华为技术有限公司 | Data partitioning method and apparatus |
| TWI503679B (en) * | 2013-06-07 | 2015-10-11 | Huawei Tech Co Ltd | Data partitioning method and device |
| CN103336792A (en) * | 2013-06-07 | 2013-10-02 | 华为技术有限公司 | Method and device for data partition |
| CN103324732A (en) * | 2013-06-29 | 2013-09-25 | 优视科技有限公司 | MySQL database horizontal-sharding method, and MySQL connection establishment method and device |
| CN103324732B (en) * | 2013-06-29 | 2016-06-29 | 优视科技有限公司 | The horizontal cutting method of MYSQL database, MYSQL connection establishment method and device |
| CN103593447A (en) * | 2013-11-18 | 2014-02-19 | 北京国双科技有限公司 | Data processing method and device applied to database table |
| CN103593447B (en) * | 2013-11-18 | 2017-02-08 | 北京国双科技有限公司 | Data processing method and device applied to database table |
| CN106776598A (en) * | 2015-11-19 | 2017-05-31 | 中国移动通信集团公司 | A kind of information processing method and device |
| CN110069485A (en) * | 2017-09-21 | 2019-07-30 | 阿里巴巴集团控股有限公司 | The method and device of method and data acquisition that Data Acquisition Model is established |
| CN110069485B (en) * | 2017-09-21 | 2024-01-23 | 阿里云计算有限公司 | Method for establishing data acquisition model and method and device for acquiring data |
| CN108446329A (en) * | 2018-02-13 | 2018-08-24 | 北京工业大数据创新中心有限公司 | Adaptive databases partition method and system towards industrial time series database |
| CN109960693A (en) * | 2018-11-22 | 2019-07-02 | 成都长城开发科技有限公司 | One kind being based on relevant database load curve storage method |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101145158A (en) | Data base table partition method | |
| CN108256088A (en) | A kind of storage method and system of the time series data based on key value database | |
| US7124146B2 (en) | Incremental cardinality estimation for a set of data values | |
| CN102063490B (en) | Database partition method and device | |
| CN102929936B (en) | Log recording method, log inquiring method and system | |
| US20120197900A1 (en) | Systems and methods for search time tree indexes | |
| US7761474B2 (en) | Indexing stored data | |
| CN102722582B (en) | System and method for integrating data on basis of reverse clearing | |
| CN109857724B (en) | Method and equipment for supporting various databases based on block chain | |
| CN103902544B (en) | A kind of data processing method and system | |
| CN102375827B (en) | Method for fast loading versioned electricity network model database | |
| CN101464895A (en) | Method, system and apparatus for updating internal memory data | |
| JP2004518226A (en) | Database system and query optimizer | |
| CN101241511A (en) | Rapid index method based on space-time data | |
| CN104657387B (en) | A kind of data query method and device | |
| CN107247734A (en) | A kind of distributed data base major key generation method and system | |
| CN106682042B (en) | A kind of relation data caching and querying method and device | |
| CN106934048A (en) | Online data moving method, agent node | |
| CN101256579A (en) | Method for inquesting data organization in database | |
| CN102982186A (en) | Method and system for maintaining range partitioning table based on oracle database system | |
| CN101963993B (en) | Method for fast searching database sheet table record | |
| CN102207935A (en) | Method and system for establishing index | |
| CN111680103A (en) | Method and device for generating ordered unique value by distributed database self-increment field | |
| CN111625543B (en) | Method for realizing globally monotonically increasing sequence based on HBase table | |
| CN101094428A (en) | Statistical system and method of querying service database of intelligent network |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C12 | Rejection of a patent application after its publication | ||
| RJ01 | Rejection of invention patent application after publication |
Open date: 20080319 |