CN101162463A - Master-salve table data acquiring method in data-base - Google Patents
Master-salve table data acquiring method in data-base Download PDFInfo
- Publication number
- CN101162463A CN101162463A CNA2006101496866A CN200610149686A CN101162463A CN 101162463 A CN101162463 A CN 101162463A CN A2006101496866 A CNA2006101496866 A CN A2006101496866A CN 200610149686 A CN200610149686 A CN 200610149686A CN 101162463 A CN101162463 A CN 101162463A
- Authority
- CN
- China
- Prior art keywords
- master
- record
- current record
- salve
- master meter
- 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.)
- Granted
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention discloses a method for acquiring host-slave form data in database, comprising fields aiming at the relation of a host-slave form, wherein, a host form and a slave form are opened according to the same sequencing rule; a current record is extracted from the host form; a record matched with the current record of the host form is sought from the slave form; according to the same mode with antecedent steps, other slave forms are accessed in turn for acquiring the record matched with the current record of the host form; all the records gotten from the slave forms form an integral item of data with the current record of the host form; a current access position of the host form is shifted downward once, which is over until the host form retrieves and finishes extracting all the records; the form in open state is closed. The method of the present invention only has one time to open all the host and slave forms, overcoming the disadvantage that the prior proposal opens the same form time after time, decreases the comparison frequency of the whole process, overcomes the disadvantages of repeating matching and crisscross matching in the prior proposal, and improves the efficiency of acquiring data.
Description
Technical field
The present invention relates to field of software development, use relational database (Relatinal-Database), be specifically related to a kind of method that database master-salve table data are obtained efficient that improves as data storage medium.
Background technology
Relational database is used very extensive in field of software development.In relational data model, practical object exists with the form of table (Table), and the dependence of any kind contact promptly claims relation between the table.Master-salve table (claim again father from table) is a kind of common relation, and it is by a master meter and a plurality ofly form from table.Generally speaking, at master meter definition major key, be external key from table definition same meaning field, rely on main foreign key constraint that they are organized the complete information model of common expression.
With telecommunications Optical Transmission Network OTN guard system is example, as follows around the defined several tables in path:
Path: Trail (Trail_ID, UserLabel, State, Direction......)
Path end points: TrailEnds (Trail_ID, EndA, EndZ ...)
Path route segmentation essential information: TrailRoute (Trail_ID, SegmentNo, LayerRate ...)
Master meter is Trail, is TrailEnds and TrailRoute from table.They rely on the Trail_ID field to set up corresponding relation, the complete information of co-registered path notion.
Because the complicacy of getting in touch between the entity, the master-salve table memory module is widely applied in reality, thereby the data under this pattern are obtained and also just become the frequent operation of often facing.Obtain complete information this moment if desired from database, the master meter that necessary visit is related and all rely on main foreign key field to mate each several part information, to assemble correct result from table.And, add that from often more than one of table, this coupling will cause the field number of comparisons sharply to increase in case the record of table is more, may greatly increase the weight of the Database Systems burden.In sum, the master-salve table data obtains an important factor in order that becomes based on the software performance of relational database.
To be the master-salve table relationship field below from the Field Definition in order to opening relationships between the table record, for example above-mentioned Trail_ID.
Master-salve table obtains scheme and mainly contains two kinds at present:
1, vernier mode:
Use vernier to open master meter; Move down by vernier at every turn and from master meter, extract a record,, go from table 1, to retrieve respective record and obtain sub-information 1 just according to master-salve table relationship field value; Go from the sub-information 2 of table 2 retrieval; ... when all finish from table search, will obtain a complete information; And so forth, move to terminal point, obtain comprising the correct RObject as a result of all complete informations up to vernier.
2, internal memory matching way:
The master meter corresponding record retrieved out be filled up to result object RObject; To retrieve out from table 1 corresponding record, and, in the result object RObject that the first step obtains, seek the tram successively and fill in into according to master-salve table relationship field value; After finishing from all recording processing of table 1, handle successively in a similar manner from table 2, from table 3......, finish from list processing (LISP) up to all, obtain comprising the correct RObject as a result of whole complete informations.
For scheme 1, shortcoming is each the bar record to master meter, all can open a result set from table and carry out full table scan coupling, and when the scale of table acquired a certain degree, this process was very time-consuming.
For scheme 2, shortcoming is to need the coupling carried out too much in the application program internal memory, all needs to seek the RObject corresponding position from each bar record of table.
Consider that from standpoint of efficiency such scheme 1 and 2 all exists not enough, especially when more from the table number, master meter, from the table record number all than greatly the time, their defectives on efficient will become the bottleneck of program.
Summary of the invention
The objective of the invention is to overcome the shortcoming of above-mentioned existing master-salve table data capture method, and provide a kind of clearly, high efficiency method.
The present invention has following characteristics: 1) the master-salve table result for retrieval sorts according to same criterion; 2) the master-salve table current record advances synchronously, and matching times is reduced to minimum; 3) all tables that relate to are only opened once.
The master-salve table data capture method comprises the steps: in a kind of database
Step 3, at the record of from table, seeking with master meter current record coupling;
Step 4, visit other successively from table, obtain the record with master meter current record coupling, complete data of record and the common composition of master meter current record that all obtain from table according to the mode identical with step 3;
Step 5, master meter current accessed are moved once under the position, get back to step 2 and continue to carry out, and all records that retrieve up to master meter extract when finishing and finish;
Step 6, close the table that is in open mode.
The method of the invention, the master-salve table record is according to identical ranking criteria retrieval, make from every record of table only can and the master meter record coupling of adjacent sections, the number of comparisons of whole process is reduced to minimum, overcome that existing scheme is mated repeatedly, the shortcoming of cross-matched;
Master and slave table is only opened once, and the current accessed position keeps moving forward always, only travels through one time and not back and forth, has not overcome the shortcoming that existing scheme is repeatedly opened same table;
The method of the invention is applied to the Optical Transmission Network OTN guard system when obtaining path routing iinformation example, compares with common vernier obtain manner, and efficient improves about 80 times, obtains good result.
Description of drawings
Fig. 1 is an Optical Transmission Network OTN guard system path route entity graph of a relation;
Fig. 2 is a master-salve table data extract process flow diagram.
Embodiment
Below in conjunction with accompanying drawing the method for the invention is elaborated.
Master-salve table data capture method of the present invention comprises the steps:
Step 3, at the record of from table 1, seeking with master meter current record coupling;
Step 4, visit other successively from table, obtain the record with master meter current record coupling, complete data of record and the common composition of master meter current record that all obtain from table according to the mode identical with step 3;
Step 5, master meter current accessed are moved once under the position, get back to step 2 and continue to carry out;
All records that step 6, repetition above-mentioned steps retrieve up to master meter extract when finishing and finish;
Step 7, close the table that is in open mode.
Described master-salve table data capture method, wherein seek in the step 3 from the processing of table 1 and master meter current record matched data and comprise following steps:
(1) relatively from the master-salve table relationship field value of table 1 current record and master meter current record:
Situation 1: if equate, illustrate from the current record of table 1 correspondingly, after its information extraction finished, move one downwards, get back to step (1) continuation execution from the current accessed position of table 1 with the master meter current record;
Situation 2: if do not wait, according to ranking criteria, if the former should then not handle from the data of table 1 current record after the latter, access location is constant, waits for next time the MM meeting with the master meter record;
Situation 3: if do not wait,,, move one under its access location, get back to step (1) and continue to carry out if the former should then ignore the current record from table 1 before the latter according to ranking criteria;
Situation 4: if find to finish from all record extractions that table 1 retrieves, then close this table, follow-up process no longer conducts interviews to this table;
(2) above situation 2,4 takes place, finish from the matching operation of table 1 at the master meter current record, process is returned.
Described step 1 is opened master meter and from when table, record must return according to the ordering of master-salve table relationship field size, and the mode of ordering is all consistent to all tables.
The master-salve table relationship field is not limited to be made up of a field, if a plurality of field is then set first ordering with reference to row, second ordering is with reference to row ....
So that the common object in the optical transport network layer network management system---the path route is an example, describe master-salve table data acquisition of the present invention in detail below.
Elder generation's brief description related notion: so-called path, description be communication network data transport services end to end.And a complete path is made up of plurality of segments, and each segmentation all has attributes such as end points, work topology, service aisle, and these information lump together and are called the path route.
The relation that path route and institute comprise information entity in for the ease of understanding the path being also included within, is represented by dotted lines as shown in Figure 1.
Extract the complete information of path route if desired from database, require to comprise attributes such as segmentation end points, work topology, service aisle.Utilize data capture method of the present invention, can satisfy this demand well.
Date storage method:
Model among Fig. 1 divides 4 table storages in database, as shown in table 1:
| TrailRoute route segment information table | (Trail_ID, SegmentNo, LayerRate ...) path ID, fragment number, speed, |
| TrailRouteAZEnd route segmentation terminal point information table | (Trail_ID, SegmentNo, EndA, EndZ ...) path ID, fragment number, segmentation terminal A, segmentation end points Z |
| TrailRouteWorkTopo route work topology table | (Trail_ID, SegmentNo, TopoID ...) path ID, fragment number, fragmentary works topology ID, |
| TrailRouteWorkPath route service aisle table | (Trail_ID, SegmentNo, Path ...) path ID, fragment number, the fragmentary works passage, |
Table 1
These several tables belong to typical master-salve table memory module, and wherein master meter is railRoute, and the master-salve table relationship field is Trail_ID and SegmentNo.So on these two fields, define clustered index, why set up clustered index at major key, be in order to accelerate the execution speed of select statement ordering retrieval.
Data extraction method:
1, the record according to 4 tables of record retrieval mode returns and must sort according to consistent criterion.This point is guaranteed by clustered index, does not need to add extra order by clause;
2, the master-salve table relationship field is the Trail_ID and the SegmentNo field of each table in this example;
3, concrete extraction step is:
1) use 4 sql statements to open 4 result sets:
select*from?TrailRoute?where?condition_sentence
select*from?TrailRouteAZEnd?where?condition_sentence
select*from?TrailRouteWorkTopo?where?condition_sentence
select*from?TrailRouteWorkPath?where?condition_sentence
Because the reason of clustered index, all result set acquiescences are pressed Trail_ID, the SegmentNo ascending order is arranged;
2) extract a record from the TrailRoute result set, obtaining its master-salve table relationship field value is TrailID_main and SegmentNo_main, with the combination called after Key_main of these two values;
3) extract a record from the TrailRouteAZEnd result set, obtaining its master-salve table relationship field value is TrailID_1 and SegmentNo_1, with the combination called after Key_1 of these two values.The size that compares Key_1, Key_main:
(1) if Key_1=Key_main, then the TrailRouteAZEnd current record is corresponding with the TrailRoute current record, and data are filled up to cache object, the TrailRouteAZEnd result set is carried out the NextRow operation then;
(2) if Key_1＞Key_main, illustrate that the TrailRoute current record lacks corresponding record in the TrailRouteAZEnd table, do not fill in any data, the TrailRouteAZEnd result set is not carried out the NextRow operation, wait for next bar record of TrailRoute, attempt and its coupling;
(3) if Key_1＜Key_main, illustrate that the TrailRouteAZEnd current record has lost the chance (because Key_main can be increasing backward) of mating with the TrailRoute record, do not fill in any data, TrailRouteAZEnd current record current record skips over, its result set is carried out the NextRow operation, gets back to step 3) and continues to carry out.
(4) if all records of discovery TrailRouteAZEnd result set have read to finish, then close this result set, successor operation does not visit again the TrailRouteAZEnd table.
4) this visit to TrailRouteAZEnd finishes;
5) visit TrailRouteWorkTopo, TrailRouteWorkPath according to the same mode of step 3).The attribute of TrailRoute current record so far all obtains and finishes.
6) the TrailRoute result set is carried out the NextRow operation, gets back to step 2) continue to carry out, finish up to the extraction of TrailRoute result set.
Size to master-salve table relationship field value compares, and what need describe is:
1, the used ranking criteria of comparison criterion and retrieval is consistent, and relationship field is one or more all like this.
2, in this example, Key_main and Key_1 size manner of comparison is:
(1) if TrailID_main=TrailID_1, and SegmentNo_main=SegmentNo_1 thinks that then the two equates.
(2) if TrailID_main＞TrailID_1, or TrailID_main=TrailID_1 but SegmentNo_main＞SegmentNo_1 then think Key_main＞Key_1.
(3) if TrailID_main＜TrailID_1, or TrailID_main=TrailID_1 but SegmentNo_main＜SegmentNo_1 then think Key_main＜Key_1.
The flow process of whole process as shown in Figure 2.
Should be understood that, for those of ordinary skills, can make various possible changes or replacement according to the explanation and the embodiment of technical scheme of the present invention, all these changes or replacement all belong to the protection domain of claim of the present invention.
Claims (4)
1. master-salve table data capture method in the database is characterized in that, comprises the steps:
Step 1, at the master-salve table relationship field, abide by that identical ranking criteria is opened master meter and all are from table;
Step 2, master meter extract current record;
Step 3, at the record of from table, seeking with master meter current record coupling;
Step 4, visit other successively from table, obtain the record with master meter current record coupling, complete data of record and the common composition of master meter current record that all obtain from table according to the mode identical with step 3;
Step 5, master meter current accessed are moved once under the position, get back to step 2 and continue to carry out, and all records that retrieve up to master meter extract when finishing and finish;
Step 6, close the table that is in open mode.
2. master-salve table data capture method in the database as claimed in claim 1 is characterized in that:
Described master-salve table relationship field is not limited to be made up of a field.
3. master-salve table data capture method in the database as claimed in claim 1 is characterized in that:
Described master meter and all opened are from table, and record must return according to the ordering of master-salve table relationship field size, and the mode of ordering is all consistent to all tables.
4. master-salve table data capture method in the database as claimed in claim 1 is characterized in that:
Described step 3 is meant at the record of seeking from table with master meter current record coupling, relatively from showing the master-salve table relationship field value of current record and master meter current record:
(1) if corresponding with the master meter current record,, moves one downwards from the current accessed position of table then, continue relatively from showing the master-salve table relationship field value of current record and master meter current record then with information extraction from the current record of table;
(2) if from the current record of table after the current record of master meter, then do not handle from the data of table current record, access location is constant, waits for next time MM meeting with the master meter record, from the matching operation end of indicator to current record;
(3) if from the current record of table before the current record of master meter, then ignore from the current record of table, move one under its access location, continue relatively master-salve table relationship field value from table current record and master meter current record;
(4) if find from table search to all records extract and finish, then close this table, follow-up process no longer conducts interviews to this table, finishes from the matching operation of indicator to current record.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN200610149686A CN101162463B (en) | 2006-10-13 | 2006-10-13 | Master-salve table data acquiring method in data-base |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN200610149686A CN101162463B (en) | 2006-10-13 | 2006-10-13 | Master-salve table data acquiring method in data-base |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN101162463A true CN101162463A (en) | 2008-04-16 |
| CN101162463B CN101162463B (en) | 2010-05-19 |
Family
ID=39297388
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN200610149686A Active CN101162463B (en) | 2006-10-13 | 2006-10-13 | Master-salve table data acquiring method in data-base |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101162463B (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101996218A (en) * | 2009-08-21 | 2011-03-30 | 上海博科资讯股份有限公司 | Method for realizing data access authority control |
| WO2018103318A1 (en) * | 2016-12-06 | 2018-06-14 | 上海壹账通金融科技有限公司 | Distributed transaction handling method and system |
| CN109299098A (en) * | 2018-09-29 | 2019-02-01 | 北京理工大学 | The storage and its access method of a kind of table of multiple multilayer gauge outfits in the database |
| CN114547314A (en) * | 2022-04-25 | 2022-05-27 | 北京安华金和科技有限公司 | Data classification and classification method and system based on master-slave table |
-
2006
- 2006-10-13 CN CN200610149686A patent/CN101162463B/en active Active
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101996218A (en) * | 2009-08-21 | 2011-03-30 | 上海博科资讯股份有限公司 | Method for realizing data access authority control |
| CN101996218B (en) * | 2009-08-21 | 2013-07-24 | 上海博科资讯股份有限公司 | Method for realizing data access authority control |
| WO2018103318A1 (en) * | 2016-12-06 | 2018-06-14 | 上海壹账通金融科技有限公司 | Distributed transaction handling method and system |
| CN109299098A (en) * | 2018-09-29 | 2019-02-01 | 北京理工大学 | The storage and its access method of a kind of table of multiple multilayer gauge outfits in the database |
| CN109299098B (en) * | 2018-09-29 | 2022-04-12 | 北京理工大学 | Method for storing and accessing multiple tables with multilayer headers in database |
| CN114547314A (en) * | 2022-04-25 | 2022-05-27 | 北京安华金和科技有限公司 | Data classification and classification method and system based on master-slave table |
| CN114547314B (en) * | 2022-04-25 | 2022-07-05 | 北京安华金和科技有限公司 | Data classification and classification method and system based on master-slave table |
Also Published As
| Publication number | Publication date |
|---|---|
| CN101162463B (en) | 2010-05-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Xia et al. | Refreshing the sky: the compressed skycube with efficient support for frequent updates | |
| CN1552032B (en) | Database | |
| US7672976B2 (en) | Method for the reduction of image content redundancy in large image databases | |
| CN102270232B (en) | Semantic data query system with optimized storage | |
| CN101542475A (en) | System and method for searching and matching data having ideogrammatic content | |
| CN105320775A (en) | Data access method and apparatus | |
| CN101162463B (en) | Master-salve table data acquiring method in data-base | |
| CN104769585A (en) | System and method for recursively traversing the internet and other sources to identify, gather, curate, adjudicate, and qualify business identity and related data | |
| CN102893281A (en) | Information retrieval device, information retrieval method, computer program, and data structure | |
| CN108694191A (en) | The method of quick-searching component information in BIM models | |
| CN105528411A (en) | Full-text retrieval device and method for interactive electronic technical manual of shipping equipment | |
| CN104391908A (en) | Locality sensitive hashing based indexing method for multiple keywords on graphs | |
| CN109815232A (en) | A kind of method and system of retrieval, the data processing of the data rank using binary search tree | |
| CN101501687B (en) | Method for controlling a relational database system | |
| CN106484815B (en) | A kind of automatic identification optimization method based on mass data class SQL retrieval scene | |
| CN106126634A (en) | A kind of master data duplicate removal treatment method based on live industry and system | |
| CN103678513A (en) | Interactive search generation method and system | |
| Perner | Case-base maintenance by conceptual clustering of graphs | |
| Bleifuß et al. | Structured object matching across web page revisions | |
| CN103365960A (en) | Off-line searching method of structured data of electric power multistage dispatching management | |
| CN101609461B (en) | Space querying system of personal core data based on user features and method | |
| CN111782663A (en) | Aggregation index structure and aggregation index method for improving aggregation query efficiency | |
| Severance | A parametric model of alternative file structures | |
| CN110263108A (en) | A kind of keyword Skyline fuzzy query method and system based on road network | |
| Shao et al. | Batch nearest neighbor search for video retrieval |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant | ||
| EE01 | Entry into force of recordation of patent licensing contract |
Application publication date: 20080416 Assignee: SHENZHEN ZTE MICROELECTRONICS TECHNOLOGY CO., LTD. Assignor: ZTE Corporation Contract record no.: 2017440020015 Denomination of invention: Master-salve table data acquiring method in data-base Granted publication date: 20100519 License type: Common License Record date: 20170310 |
|
| EE01 | Entry into force of recordation of patent licensing contract |