CN108073612A - The method and apparatus of synchronous SQL executive plans - Google Patents
The method and apparatus of synchronous SQL executive plans Download PDFInfo
- Publication number
- CN108073612A CN108073612A CN201611000840.3A CN201611000840A CN108073612A CN 108073612 A CN108073612 A CN 108073612A CN 201611000840 A CN201611000840 A CN 201611000840A CN 108073612 A CN108073612 A CN 108073612A
- Authority
- CN
- China
- Prior art keywords
- statistics
- database
- information
- storage facility
- user identifier
- 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
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR 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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Abstract
The present invention relates to a kind of method and apparatus of synchronization SQL executive plans.The above method includes：It determines and the associated data item of SQL executive plans；The statistics of database information of the data item is extracted from target storage facility located at processing plant；Obtain the correspondence of storage facility located at processing plant and test library；Target detection storehouse corresponding with the target storage facility located at processing plant is obtained according to the correspondence；By the statistics of database synchronizing information to the target detection storehouse.The method and apparatus of above-mentioned synchronization SQL executive plans can make target detection storehouse generate the SQL executive plan identical with target storage facility located at processing plant, and the data volume handled is small, and processing procedure is simple, can effectively mitigate the data processing pressure of database.
Description
Technical field
The present invention relates to database technical field, more particularly to a kind of method and apparatus of synchronization SQL executive plans.
Background technology
In the database, SQL (Structured Query Language, a structured query language) sentence represents
The data wanted, but there is no representing how to obtain data, when the SQL statement that the database in server receives
When, it can select specifically how to obtain data by calculating executory cost, final SQL statement performs to obtain correspondence by physical
The methods of data be referred to as executive plan.
SQL statement is before formal working data base performs, it usually needs is tested in test database, to SQL
The performance of sentence makes anticipation, avoids generating failure in formal working data base.Due to formal working data base and survey
It is inconsistent to try the data volume of database, often causes SQL statement in test database and holding in formal working data base
Row plan is different, performance issue occurs when SQL statement being caused to be performed in formal working data base.In conventional manner, it is
Executive plan different problems of the SQL statement in formal working data base and test database are solved, the common practice is
Keep formal working data base consistent with the data volume of test database, but data volume is big, causes complex disposal process, gives
Database causes larger data processing pressure.
The content of the invention
Based on this, it is necessary to provide a kind of method of synchronization SQL executive plans, the data volume of processing is small, processing procedure letter
It is single, it can effectively mitigate the data processing pressure of database.
A kind of device of synchronization SQL executive plans is provided in addition, there is a need to, the data volume of processing is small, processing procedure letter
It is single, it can effectively mitigate the data processing pressure of database.
A kind of method of synchronization SQL executive plans, including：
It determines and the associated data item of SQL executive plans；
The statistics of database information of the data item is extracted from target storage facility located at processing plant；
Obtain the correspondence of storage facility located at processing plant and test library；
Target detection storehouse corresponding with the target storage facility located at processing plant is obtained according to the correspondence；
By the statistics of database synchronizing information to the target detection storehouse.
In one of the embodiments, the statistics of database information of the data item is extracted in the storage facility located at processing plant from target
The step of before, further include：
Obtain user list to be audited；
Obtain target storage facility located at processing plant corresponding with each user identifier in the user list；
The statistics of database information of the data item is extracted in the storage facility located at processing plant from target, including：
The extraction institute corresponding with each user identifier from target storage facility located at processing plant corresponding with each user identifier respectively
State the statistics of database information of data item.
In one of the embodiments, the statistics of database information of the data item is extracted in the storage facility located at processing plant from target
The step of after, further include：
Obtain currently stored version value corresponding with each user identifier；
The former data of the data item corresponding with each user identifier are obtained according to the currently stored version value
Storehouse statistical information；
Respectively according to each user identifier, statistics of database information corresponding with user identifier and original database are judged
Whether statistical information is consistent；
When the statistics of database information corresponding with user identifier and original database statistical information there are it is inconsistent when, then
There are inconsistent information between statistics of database information corresponding with user identifier and original database statistical information described in obtaining
And stored, update the currently stored version value.
In one of the embodiments, it is described by the statistics of database synchronizing information to the target detection storehouse, including：
It, will the statistics of database information corresponding with user identifier and original database system according to each user identifier
There are inconsistent synchronizing informations to corresponding target detection storehouse between meter information.
In one of the embodiments, the method further includes：
The target detection storehouse is made to establish statistical information data dictionary according to the statistics of database information.
A kind of device of synchronization SQL executive plans, including：
Determining module, for determining and the associated data item of SQL executive plans；
Extraction module, for extracting the statistics of database information of the data item from target storage facility located at processing plant；
Relation acquisition module, for obtaining the correspondence of storage facility located at processing plant and test library；
Test library acquisition module, for obtaining target detection corresponding with the target storage facility located at processing plant according to the correspondence
Storehouse；
Synchronization module, for by the statistics of database synchronizing information to the target detection storehouse.
In one of the embodiments, described device further includes：
List acquisition module, for obtaining user list to be audited；
Storage facility located at processing plant acquisition module, for obtaining target production corresponding with each user identifier in the user list
Storehouse；
The extraction module, be additionally operable to respectively from target storage facility located at processing plant corresponding with each user identifier extraction with it is each
The statistics of database information of the corresponding data item of a user identifier.
In one of the embodiments, described device further includes：
Version value acquisition module, for obtaining currently stored version value corresponding with each user identifier；
Data obtaining module, for obtaining institute corresponding with each user identifier according to the currently stored version value
State the original database statistical information of data item；
Judgment module, for according to each user identifier, judging statistics of database corresponding with user identifier respectively
Whether information and original database statistical information are consistent；
Memory module, for being deposited when the statistics of database information corresponding with user identifier and original database statistical information
When inconsistent, then exist described in acquisition between statistics of database information corresponding with user identifier and original database statistical information
Inconsistent information is simultaneously stored, and updates the currently stored version value.
In one of the embodiments, the synchronization module is additionally operable to according to each user identifier, will be described with using
Family identifies between corresponding statistics of database information and original database statistical information that there are inconsistent synchronizing informations to corresponding
Target detection storehouse.
In one of the embodiments, described device further includes：
Module is established, for the target detection storehouse to be made to establish statistical information data word according to the statistics of database information
Allusion quotation.
The method and apparatus of above-mentioned synchronization SQL executive plans are closed by being extracted from target storage facility located at processing plant with SQL executive plans
The statistics of database information of the data item of connection, and according to storage facility located at processing plant and the correspondence of test library, statistics of database information is same
In step to target detection storehouse corresponding with target storage facility located at processing plant, without keeping the data volume one of target storage facility located at processing plant and target detection storehouse
It causes, only needs synchronous target storage facility located at processing plant and the statistics of database information with the associated data item of SQL executive plans in target detection storehouse,
Target detection storehouse can be made to generate the SQL executive plan identical with target storage facility located at processing plant, the data volume of processing is small, processing procedure letter
It is single, it can effectively mitigate the data processing pressure of database.
Description of the drawings
Fig. 1 is the internal structure schematic diagram of server in one embodiment；
Fig. 2 is the system architecture diagram of the method for synchronization SQL executive plans in one embodiment；
Fig. 3 is the flow diagram of the method for synchronization SQL executive plans in one embodiment；
Fig. 4 is the flow diagram for extracting statistics of database information in one embodiment according to user list；
Fig. 5 is the flow diagram that statistics of database information is stored in one embodiment；
Fig. 6 is the structure diagram of the device of synchronization SQL executive plans in one embodiment；
Fig. 7 is the structure diagram of the device of synchronization SQL executive plans in another embodiment；
Fig. 8 is the structure diagram of the device of synchronization SQL executive plans in another embodiment；
Fig. 9 is the structure diagram of the device of synchronization SQL executive plans in another embodiment.
Specific embodiment
In order to make the purpose , technical scheme and advantage of the present invention be clearer, with reference to the accompanying drawings and embodiments, it is right
The present invention is further elaborated.It should be appreciated that specific embodiment described herein is only to explain the present invention, not
For limiting the present invention.
Fig. 1 is the internal structure schematic diagram of server in one embodiment.As shown in Figure 1, the server is including passing through
Processor, non-volatile memory medium, built-in storage and the network interface of bus of uniting connection.Wherein, the server is non-volatile
Property storage medium be stored with the device of operating system, database and synchronous SQL executive plans, database system is stored in database
Information is counted, the device of synchronization SQL executive plans is used to implement a kind of side of synchronization SQL executive plans suitable for server
Method.The processor of the server supports the operation of entire server for providing calculating and control ability.The memory of the server
Reservoir provides environment for the operation of the device of the synchronous SQL executive plans in non-volatile memory medium, can in the built-in storage
Computer-readable instruction is stored, when which is performed by the processor, may be such that the processor performs
The method of synchronous SQL executive plans.Terminal of the network interface of the server for according to this with outside is communicated by network connection,
For example receive request of test execution SQL statement that terminal is sent etc..Server can be either multiple with independent server
The server cluster of server composition is realized.It will be understood by those skilled in the art that the structure shown in Fig. 1, only with
The block diagram of the relevant part-structure of application scheme does not form the limit for the server being applied thereon to application scheme
Fixed, specific server can include either combining some components or with not than more or fewer components shown in figure
Same component arrangement.
Fig. 2 is the system architecture diagram of the method for synchronization SQL executive plans in one embodiment.In the server, it may include
Centralized repository 10, storage facility located at processing plant 20 and test library 30.Centralized repository 10 determine with the associated data item of SQL executive plans and need extract number
According to the target storage facility located at processing plant 20 of storehouse statistical information, and from target storage facility located at processing plant 20 extract the data item statistics of database information.It concentrates
Storehouse 10 obtains the correspondence of storage facility located at processing plant 20 and test library 30, and corresponding with target storage facility located at processing plant 20 according to correspondence acquisition
Target detection storehouse 30, by the statistics of database synchronizing information extracted from target storage facility located at processing plant 20 to corresponding target detection storehouse 30.
As shown in figure 3, providing a kind of method of synchronization SQL executive plans, comprise the following steps：
Step S310 is determined and the associated data item of SQL executive plans.
Specifically, may be provided with centralized repository, storage facility located at processing plant and test library in server, wherein, centralized repository refers to managing
It manages and stores the database of the statistics of database information of storage facility located at processing plant, storage facility located at processing plant refers to formal working data base, and test library is then
It is for testing the test database of SQL statement.Centralized repository can be determined first and the associated data item of SQL executive plans, the data
Item may include SPM (Sql Plan Management, SQL planning management), OUTLINE (synopsis), tables of data and index
Deng.
Step S320 extracts the statistics of database information of data item from target storage facility located at processing plant.
Specifically, centralized repository can determine one or more target storage facility located at processing plants from multiple storage facility located at processing plants, target life can be first obtained
Product storehouse identifies, such as the title of target storage facility located at processing plant, number etc., according to the definite target storage facility located at processing plant of target storage facility located at processing plant mark, and from true
The statistics of database letter of the data item such as SPM, OUTLINE currently in use, tables of data and index is extracted in fixed target storage facility located at processing plant
Breath, SQL executive plans can change with the change of statistics of database information.Statistics of database information mainly describes number
According to table in storehouse, the information of size, scale and data distribution situation etc. of index, for example, the line number of table, block number, average often going
Size, the leaf blocks (leaf block) of index, maximum, the minimum value etc. of the line number of index field and index, all belong to
In statistics of database information, in addition, in the present embodiment, statistics of database information may also include currently in use in tables of data
The information such as SPM and OUTLINE.Statistics of database information is typically stored in data word segment table, and centralized repository can be every preset time
Statistics of database information is extracted from the data field of target database, and is stored, for example, centralized repository is when 12 is small, 1
The times such as it extract statistics of database information and are stored from the data field of target database.
Step S330 obtains the correspondence of storage facility located at processing plant and test library.
Specifically, the correspondence of storage facility located at processing plant and test library can be pre-established, a storage facility located at processing plant can correspond to one or more
Test library can store storage facility located at processing plant and title, number of corresponding test library etc. in table, also can directly be closed according to corresponding
System is named test library, and the correspondence of test library and storage facility located at processing plant is determined according to the title of test library, for example, storage facility located at processing plant
Entitled DB1, then the title of the corresponding test libraries of DB1 can be DB1_1, DB1_2 etc., but not limited to this.
Step S340 obtains target detection storehouse corresponding with target storage facility located at processing plant according to correspondence.
Specifically, centralized repository can obtain and produced with target according to the storage facility located at processing plant and the correspondence of test library pre-established
The corresponding target detection storehouse in storehouse is searched in the table of storage correspondence for example, can be identified according to target storage facility located at processing plant, is obtained
Target detection storehouse mark corresponding with target storage facility located at processing plant mark, target detection storehouse mark may include the title in target detection storehouse, compile
Number etc., so that it is determined that target detection storehouse corresponding with target storage facility located at processing plant, in addition, can also surveyed according to the title of target storage facility located at processing plant
It tries to search the target detection storehouse with the name-matches of target storage facility located at processing plant in storehouse.
Step S350, by statistics of database synchronizing information to target detection storehouse.
Specifically, the statistics of database letter with the associated data item of SQL executive plans that centralized repository can will be extracted and stored
Breath, is synchronized in target detection storehouse corresponding with target storage facility located at processing plant.Target detection storehouse can according to SPM currently in use,
The statistics of database information of the data item such as OUTLINE, tables of data and index, calculate different access path and executive plan into
This, so that it is determined that the executive plan of SQL, so as to ensure that target detection storehouse is consistent with the executive plan in target storage facility located at processing plant.
The method of above-mentioned synchronization SQL executive plans, by being extracted and the associated number of SQL executive plans from target storage facility located at processing plant
According to the statistics of database information of item, and according to storage facility located at processing plant and the correspondence of test library, by statistics of database synchronizing information to
In the corresponding target detection storehouse of target storage facility located at processing plant, without keeping target storage facility located at processing plant consistent with the data volume in target detection storehouse, only need
Synchronous target storage facility located at processing plant and the statistics of database information with the associated data item of SQL executive plans in target detection storehouse, you can make
Target detection storehouse generates the SQL executive plan identical with target storage facility located at processing plant, and the data volume of processing is small, and processing procedure is simple, can have
Effect mitigates the data processing pressure of database.
As shown in figure 4, in one embodiment, the database system of data item is extracted from target storage facility located at processing plant in step S320
Before counting information, further include：
Step S402 obtains user list to be audited.
Specifically, every tables of data in database has its corresponding user, user herein can be understood as data
The owner, the founder of table, each user can possess different tables of data.Centralized repository can obtain user list to be audited, and use
Each user identifier audited can be included in the list of family, user identifier may include User ID (identity, identity mark
Know number), name etc..
Step S404 obtains target storage facility located at processing plant corresponding with each user identifier in user list.
Specifically, each user identifier that centralized repository can be included in the user list to be audited according to acquisition is searched and obtained
Target storage facility located at processing plant corresponding with each user identifier is taken to identify, and is identified according to target storage facility located at processing plant and determines target storage facility located at processing plant, one
User identifier can correspond to one or more target storage facility located at processing plants.
Step S320 extracts the statistics of database information of data item from target storage facility located at processing plant, and specific bag is inserted：Step S406, point
The statistics of database of extraction data item corresponding with each user identifier not from each user identifier corresponding target storage facility located at processing plant
Information.
Specifically, centralized repository can be according to each user identifier arranged in user list, one by one from corresponding with user identifier
Target storage facility located at processing plant in find with the user mark corresponding tables of data, index etc., therefrom extract SPM currently in use,
The statistics of database information of the data item such as OUTLINE, tables of data and index, and the statistics of database information of extraction and user are marked
Knowledge is associated storage.
In the present embodiment, each user identifier in user list to be audited is marked respectively from each user
Know extraction statistics of database information in corresponding target storage facility located at processing plant, it can be according to actual demand extraction data corresponding with certain user
Storehouse statistical information realizes that storage facility located at processing plant is synchronous with the SQL executive plans of test library, and the data volume of processing is small, and processing procedure is simple, energy
Effectively mitigate the data processing pressure of database.
As shown in figure 5, in one embodiment, the database system of data item is extracted from target storage facility located at processing plant in step S320
After counting information, further include：
Step S502 obtains currently stored version value corresponding with each user identifier.
Specifically, it is extracted each number in the corresponding target storage facility located at processing plant of each user identifier of the centralized repository from user list
According to after the statistics of database information of item, it is necessary to be stored to the statistics of database information of extraction.Centralized repository can be obtained and stored
Currently stored version value corresponding with each user identifier, each currently stored version value can correspond to currently stored former data
Storehouse statistical information.
Step S504, the original database that data item corresponding with each user identifier is obtained according to currently stored version value are united
Count information.
Specifically, centralized repository can obtain and each user according to currently stored version value corresponding with each user identifier
Identify the original database statistical information of the data item such as corresponding SPM, OUTLINE, tables of data and index, original database statistical information
The currently stored statistics of database information of centralized repository is referred to, that is, the number of last extraction corresponding with each user identifier
According to storehouse statistical information.
Step S506 respectively according to each user identifier, judges statistics of database information corresponding with user identifier and original
Whether statistics of database information is consistent, if so, step S510 is performed, if it is not, then performing step S508.
Specifically, centralized repository corresponding with user identifier can will carry one by one according to each user identifier in user list
The statistics of database information and currently stored original database statistical information taken is compared, and judges therebetween with the presence or absence of not
Consistent information, for example, obtaining the statistics of database information with 001 corresponding extraction of Customs Assigned Number and currently stored former data
Storehouse statistical information, and judge one by one in statistics of database information and the currently stored original database statistical information of the extraction, table
Line number, block number, average often capable size, the information such as maximum, the minimum value of index it is whether consistent.
Step S508 is obtained and existed between statistics of database information corresponding with user identifier and original database statistical information
Inconsistent information is simultaneously stored, and updates currently stored version value.
Specifically, when centralized repository is one by one according to each user identifier in user list, corresponding with user identifier will carry
When the statistics of database information and currently stored original database statistical information taken is compared, judges to exist therebetween and differ
The information of cause can then obtain the inconsistent information and be stored.For example, centralized repository is to 001 corresponding extraction of Customs Assigned Number
After statistics of database information and currently stored original database statistical information are compared, judge to obtain the line number of table, block number and
The maximum of index is inconsistent, then by the maximum of the line number of the table in the statistics of database information of extraction, block number and index into
Row storage.After the completion of concentrating library storage, corresponding currently stored version value may be updated, it can be suitable by number by currently stored version value
Sequence is updated, for example, the currently stored version value before storage is 1.1,1.2 etc. are may be updated as after storage, but not limited to this.
Step S510, does not deal with.
In one embodiment, step S350 is by statistics of database synchronizing information to target detection storehouse, including：According to each
User identifier, by there are inconsistent letters between statistics of database information corresponding with user identifier and original database statistical information
Breath is synchronized to corresponding target detection storehouse.
Specifically, centralized repository stores the statistics of database information of extraction corresponding with user identifier and currently stored original
Between statistics of database information there are inconsistent information after, can first according to the correspondence of storage facility located at processing plant and test library obtain and should
The corresponding target detection storehouse of user identifier, and by the inconsistent synchronizing information newly stored to the corresponding target of the user's mark
In test library.
In the present embodiment, centralized repository by the way of increment storage and increment synchronization, mark by only storage and synchronous and user
The statistics of database information in corresponding statistics of database information with last time storage is known there are inconsistent information, can be reduced and be deposited
Storage and the data volume of synchronization can effectively mitigate the data processing pressure of database.
In one embodiment, the method for above-mentioned synchronization SQL executive plans, further includes：Make target detection storehouse according to data
Storehouse statistical information establishes statistical information data dictionary.
Specifically, after target detection storehouse synchronized update statistics of database information, can be established according to statistics of database information
Statistical information data dictionary, wherein, data dictionary be for the data object or the set of the description of project in data model,
Data dictionary can be defined the data item of data, data structure, data flow, data storage, processing logic, external entity etc.
And description.Statistical information data dictionary is actually the tables of data for storing statistics of database information, from statistical information data dictionary
In can obtain every statistics of database information, such as the line number of table, block number, average often capable size, the leaf of index
Maximum, minimum value of blocks, the line number of index field and index etc..Target detection storehouse is built according to statistics of database information
Vertical statistical information data dictionary, can facilitate test library therefrom to extract relevant data when being audited to SQL statement.
In the present embodiment, statistical information data dictionary is established in target detection storehouse according to statistics of database information, can facilitate
When being audited to SQL statement, relevant data are extracted from statistical information data dictionary and are audited, SQL statement can be accelerated
Review efficiency, and effectively improve SQL statement examination ＆ verification accuracy.
As shown in fig. 6, a kind of device of synchronization SQL executive plans is provided, including determining module 610, extraction module
620th, Relation acquisition module 630, test library acquisition module 640 and synchronization module 650.
Determining module 610, for determining and the associated data item of SQL executive plans.
Specifically, may be provided with centralized repository, storage facility located at processing plant and test library in server, wherein, centralized repository refers to managing
It manages and stores the database of the statistics of database information of storage facility located at processing plant, storage facility located at processing plant refers to formal working data base, and test library is then
It is for testing the test database of SQL statement.Centralized repository can be determined first and the associated data item of SQL executive plans, the data
Item may include SPM (Sql Plan Management, SQL planning management), OUTLINE (synopsis), tables of data and index
Deng.
Extraction module 620, for extracting the statistics of database information of data item from target storage facility located at processing plant.
Specifically, centralized repository can determine one or more target storage facility located at processing plants from multiple storage facility located at processing plants, target life can be first obtained
Product storehouse identifies, such as the title of target storage facility located at processing plant, number etc., according to the definite target storage facility located at processing plant of target storage facility located at processing plant mark, and from true
The statistics of database letter of the data item such as SPM, OUTLINE currently in use, tables of data and index is extracted in fixed target storage facility located at processing plant
Breath, SQL executive plans can change with the change of statistics of database information.Statistics of database information mainly describes number
According to table in storehouse, the information of size, scale and data distribution situation etc. of index, for example, the line number of table, block number, average often going
Size, the leaf blocks (leaf block) of index, maximum, the minimum value etc. of the line number of index field and index, all belong to
In statistics of database information, in addition, in the present embodiment, statistics of database information may also include currently in use in tables of data
The information such as SPM and OUTLINE.Statistics of database information is typically stored in data word segment table, and centralized repository can be every preset time
Statistics of database information is extracted from the data field of target database, and is stored, for example, centralized repository is when 12 is small, 1
The times such as it extract statistics of database information and are stored from the data field of target database.
Relation acquisition module 630, for obtaining the correspondence of storage facility located at processing plant and test library.
Specifically, the correspondence of storage facility located at processing plant and test library can be pre-established, a storage facility located at processing plant can correspond to one or more
Test library can store storage facility located at processing plant and title, number of corresponding test library etc. in table, also can directly be closed according to corresponding
System is named test library, and the correspondence of test library and storage facility located at processing plant is determined according to the title of test library, for example, storage facility located at processing plant
Entitled DB1, then the title of the corresponding test libraries of DB1 can be DB1_1, DB1_2 etc., but not limited to this.
Test library acquisition module 640, for obtaining target detection storehouse corresponding with target storage facility located at processing plant according to correspondence.
Specifically, centralized repository can obtain and produced with target according to the storage facility located at processing plant and the correspondence of test library pre-established
The corresponding target detection storehouse in storehouse is searched in the table of storage correspondence for example, can be identified according to target storage facility located at processing plant, is obtained
Target detection storehouse mark corresponding with target storage facility located at processing plant mark, target detection storehouse mark may include the title in target detection storehouse, compile
Number etc., so that it is determined that target detection storehouse corresponding with target storage facility located at processing plant, in addition, can also surveyed according to the title of target storage facility located at processing plant
It tries to search the target detection storehouse with the name-matches of target storage facility located at processing plant in storehouse.
Synchronization module 650, for by statistics of database synchronizing information to the target detection storehouse.
Specifically, the statistics of database letter with the associated data item of SQL executive plans that centralized repository can will be extracted and stored
Breath, is synchronized in target detection storehouse corresponding with target storage facility located at processing plant.Target detection storehouse can according to SPM currently in use,
The statistics of database information of the data item such as OUTLINE, tables of data and index, calculate different access path and executive plan into
This, so that it is determined that the executive plan of SQL, so as to ensure that target detection storehouse is consistent with the executive plan in target storage facility located at processing plant.
The device of above-mentioned synchronization SQL executive plans, by being extracted and the associated number of SQL executive plans from target storage facility located at processing plant
According to the statistics of database information of item, and according to storage facility located at processing plant and the correspondence of test library, by statistics of database synchronizing information to
In the corresponding target detection storehouse of target storage facility located at processing plant, without keeping target storage facility located at processing plant consistent with the data volume in target detection storehouse, only need
Synchronous target storage facility located at processing plant and the statistics of database information with the associated data item of SQL executive plans in target detection storehouse, you can make
Target detection storehouse generates the SQL executive plan identical with target storage facility located at processing plant, and the data volume of processing is small, and processing procedure is simple, can have
Effect mitigates the data processing pressure of database.
As shown in fig. 7, in one embodiment, the device of above-mentioned synchronization SQL executive plans, except including determining module
610th, extraction module 620, Relation acquisition module 630, test library acquisition module 640 and synchronization module 650 further include list acquisition
Module 660 and storage facility located at processing plant acquisition module 670.
List acquisition module 660, for obtaining user list to be audited.
Specifically, every tables of data in database has its corresponding user, user herein can be understood as data
The owner, the founder of table, each user can possess different tables of data.Centralized repository can obtain user list to be audited, and use
Each user identifier audited can be included in the list of family, user identifier may include User ID, name etc..
Storage facility located at processing plant acquisition module 670, for obtaining target storage facility located at processing plant corresponding with each user identifier in user list.
Specifically, each user identifier that centralized repository can be included in the user list to be audited according to acquisition is searched and obtained
Target storage facility located at processing plant corresponding with each user identifier is taken to identify, and is identified according to target storage facility located at processing plant and determines target storage facility located at processing plant, one
User identifier can correspond to one or more target storage facility located at processing plants.
Extraction module 620 is additionally operable to extraction and each use from target storage facility located at processing plant corresponding with each user identifier respectively
Family identifies the statistics of database information of corresponding data item.
Specifically, centralized repository can be according to each user identifier arranged in user list, one by one from corresponding with user identifier
Target storage facility located at processing plant in find with the user mark corresponding tables of data, index etc., therefrom extract SPM currently in use,
The statistics of database information of the data item such as OUTLINE, tables of data and index, and the statistics of database information of extraction and user are marked
Knowledge is associated storage.
In the present embodiment, each user identifier in user list to be audited is marked respectively from each user
Know extraction statistics of database information in corresponding target storage facility located at processing plant, it can be according to actual demand extraction data corresponding with certain user
Storehouse statistical information realizes that storage facility located at processing plant is synchronous with the SQL executive plans of test library, and the data volume of processing is small, and processing procedure is simple, energy
Effectively mitigate the data processing pressure of database.
As shown in figure 8, in one embodiment, the device of above-mentioned synchronization SQL executive plans, except including determining module
610th, extraction module 620, Relation acquisition module 630, test library acquisition module 640, synchronization module 650, list acquisition module 660
And storage facility located at processing plant acquisition module 670, further include version value acquisition module 680, data obtaining module 690, judgment module 700 and storage
Module 710.
Version value acquisition module 680, for obtaining currently stored version value corresponding with each user identifier.
Specifically, it is extracted each number in the corresponding target storage facility located at processing plant of each user identifier of the centralized repository from user list
According to after the statistics of database information of item, it is necessary to be stored to the statistics of database information of extraction.Centralized repository can be obtained and stored
Currently stored version value corresponding with each user identifier, each currently stored version value can correspond to currently stored former data
Storehouse statistical information.
Data obtaining module 690, for obtaining the number corresponding with each user identifier according to currently stored version value
According to the original database statistical information of item.
Specifically, centralized repository can obtain and each user according to currently stored version value corresponding with each user identifier
Identify the original database statistical information of the data item such as corresponding SPM, OUTLINE, tables of data and index, original database statistical information
The currently stored statistics of database information of centralized repository is referred to, that is, the number of last extraction corresponding with each user identifier
According to storehouse statistical information.
Judgment module 700, for according to each user identifier, judging statistics of database letter corresponding with user identifier respectively
Whether breath and original database statistical information are consistent.
Specifically, centralized repository corresponding with user identifier can will carry one by one according to each user identifier in user list
The statistics of database information and currently stored original database statistical information taken is compared, and judges therebetween with the presence or absence of not
Consistent information, for example, obtaining the statistics of database information with 001 corresponding extraction of Customs Assigned Number and currently stored former data
Storehouse statistical information, and judge one by one in statistics of database information and the currently stored original database statistical information of the extraction, table
Line number, block number, average often capable size, the information such as maximum, the minimum value of index it is whether consistent.
Memory module 710, for being deposited when statistics of database information corresponding with user identifier and original database statistical information
When inconsistent, then obtain to exist between statistics of database information corresponding with user identifier and original database statistical information and differ
The information of cause is simultaneously stored, and updates currently stored version value.
Specifically, when centralized repository is one by one according to each user identifier in user list, corresponding with user identifier will carry
When the statistics of database information and currently stored original database statistical information taken is compared, judges to exist therebetween and differ
The information of cause can then obtain the inconsistent information and be stored.For example, centralized repository is to 001 corresponding extraction of Customs Assigned Number
After statistics of database information and currently stored original database statistical information are compared, judge to obtain the line number of table, block number and
The maximum of index is inconsistent, then by the maximum of the line number of the table in the statistics of database information of extraction, block number and index into
Row storage.After the completion of concentrating library storage, corresponding currently stored version value may be updated, it can be suitable by number by currently stored version value
Sequence is updated, for example, the currently stored version value before storage is 1.1,1.2 etc. are may be updated as after storage, but not limited to this.
In one embodiment, synchronization module 650 are additionally operable to according to each user identifier, will be corresponding with user identifier
There are inconsistent synchronizing informations to corresponding target detection storehouse between statistics of database information and original database statistical information.
Specifically, centralized repository stores the statistics of database information of extraction corresponding with user identifier and currently stored original
Between statistics of database information there are inconsistent information after, can first according to the correspondence of storage facility located at processing plant and test library obtain and should
The corresponding target detection storehouse of user identifier, and by the inconsistent synchronizing information newly stored to the corresponding target of the user's mark
In test library.
In the present embodiment, centralized repository by the way of increment storage and increment synchronization, mark by only storage and synchronous and user
The statistics of database information in corresponding statistics of database information with last time storage is known there are inconsistent information, can be reduced and be deposited
Storage and the data volume of synchronization can effectively mitigate the data processing pressure of database.
As shown in figure 9, the device of above-mentioned synchronization SQL executive plans, except include determining module 610, extraction module 620,
Relation acquisition module 630, test library acquisition module 640, synchronization module 650, list acquisition module 660, storage facility located at processing plant acquisition module
670th, version value acquisition module 680, data obtaining module 690, judgment module 700 and memory module 710, further include and establish module
720。
Module 720 is established, for target detection storehouse to be made to establish statistical information data dictionary according to statistics of database information.
Specifically, after target detection storehouse synchronized update statistics of database information, can be established according to statistics of database information
Statistical information data dictionary, wherein, data dictionary be for the data object or the set of the description of project in data model,
Data dictionary can be defined the data item of data, data structure, data flow, data storage, processing logic, external entity etc.
And description.Statistical information data dictionary is actually the tables of data for storing statistics of database information, from statistical information data dictionary
In can obtain every statistics of database information, such as the line number of table, block number, average often capable size, the leaf of index
Maximum, minimum value of blocks, the line number of index field and index etc..Target detection storehouse is built according to statistics of database information
Vertical statistical information data dictionary, can facilitate test library therefrom to extract relevant data when being audited to SQL statement.
In the present embodiment, statistical information data dictionary is established in target detection storehouse according to statistics of database information, can facilitate
When being audited to SQL statement, relevant data are extracted from statistical information data dictionary and are audited, SQL statement can be accelerated
Review efficiency, and effectively improve SQL statement examination ＆ verification accuracy.
One of ordinary skill in the art will appreciate that realizing all or part of flow in above-described embodiment method, being can be with
Relevant hardware is instructed to complete by computer program, the program can be stored in a non-volatile computer and can be read
In storage medium, the program is upon execution, it may include such as the flow of the embodiment of above-mentioned each method.Wherein, the storage is situated between
Matter can be magnetic disc, CD, read-only memory (Read-Only Memory, ROM) etc..
Each technical characteristic of embodiment described above can be combined arbitrarily, to make description succinct, not to above-mentioned reality
It applies all possible combination of each technical characteristic in example to be all described, as long as however, the combination of these technical characteristics is not deposited
In contradiction, the scope that this specification is recorded all is considered to be.
Embodiment described above only expresses the several embodiments of the present invention, and description is more specific and detailed, but simultaneously
It cannot therefore be construed as limiting the scope of the patent.It should be pointed out that come for those of ordinary skill in the art
It says, without departing from the inventive concept of the premise, various modifications and improvements can be made, these belong to the protection of the present invention
Scope.Therefore, the protection domain of patent of the present invention should be determined by the appended claims.
Claims (10)
1. a kind of method of synchronization SQL executive plans, including：
It determines and the associated data item of SQL executive plans；
The statistics of database information of the data item is extracted from target storage facility located at processing plant；
Obtain the correspondence of storage facility located at processing plant and test library；
Target detection storehouse corresponding with the target storage facility located at processing plant is obtained according to the correspondence；
By the statistics of database synchronizing information to the target detection storehouse.
2. the method for synchronization SQL executive plans according to claim 1, which is characterized in that described from target storage facility located at processing plant
Before the step of statistics of database information of the middle extraction data item, further include：
Obtain user list to be audited；
Obtain target storage facility located at processing plant corresponding with each user identifier in the user list；
The statistics of database information of the data item is extracted in the storage facility located at processing plant from target, including：
The extraction number corresponding with each user identifier from target storage facility located at processing plant corresponding with each user identifier respectively
According to the statistics of database information of item.
3. the method for synchronization SQL executive plans according to claim 2, which is characterized in that described from target storage facility located at processing plant
After the step of statistics of database information of the middle extraction data item, further include：
Obtain currently stored version value corresponding with each user identifier；
The original database system of the data item corresponding with each user identifier is obtained according to the currently stored version value
Count information；
Respectively according to each user identifier, statistics of database information corresponding with user identifier and original database statistics are judged
Whether information is consistent；
When the statistics of database information corresponding with user identifier and original database statistical information there are it is inconsistent when, then obtain
There are inconsistent information between the statistics of database information corresponding with user identifier and original database statistical information to go forward side by side
Row storage, updates the currently stored version value.
4. the method for synchronization SQL executive plans according to claim 3, which is characterized in that described that the database is united
Synchronizing information is counted to the target detection storehouse, including：
It, will the statistics of database information corresponding with user identifier and original database statistics letter according to each user identifier
There are inconsistent synchronizing informations to corresponding target detection storehouse between breath.
5. the method for synchronization SQL executive plans according to any one of claims 1 to 4, which is characterized in that the method is also wrapped
It includes：
The target detection storehouse is made to establish statistical information data dictionary according to the statistics of database information.
6. a kind of device of synchronization SQL executive plans, which is characterized in that including：
Determining module, for determining and the associated data item of SQL executive plans；
Extraction module, for extracting the statistics of database information of the data item from target storage facility located at processing plant；
Relation acquisition module, for obtaining the correspondence of storage facility located at processing plant and test library；
Test library acquisition module, for obtaining target detection storehouse corresponding with the target storage facility located at processing plant according to the correspondence；
Synchronization module, for by the statistics of database synchronizing information to the target detection storehouse.
7. the device of synchronization SQL executive plans according to claim 6, which is characterized in that described device further includes：
List acquisition module, for obtaining user list to be audited；
Storage facility located at processing plant acquisition module, for obtaining target storage facility located at processing plant corresponding with each user identifier in the user list；
The extraction module is additionally operable to extraction and each use from target storage facility located at processing plant corresponding with each user identifier respectively
Family identifies the statistics of database information of the corresponding data item.
8. the device of synchronization SQL executive plans according to claim 7, which is characterized in that described device further includes：
Version value acquisition module, for obtaining currently stored version value corresponding with each user identifier；
Data obtaining module, for obtaining the number corresponding with each user identifier according to the currently stored version value
According to the original database statistical information of item；
Judgment module, for according to each user identifier, judging statistics of database information corresponding with user identifier respectively
And whether original database statistical information is consistent；
Memory module, for existing not when the statistics of database information corresponding with user identifier and original database statistical information
When consistent, then exist described in acquisition between statistics of database information corresponding with user identifier and original database statistical information and differ
The information of cause is simultaneously stored, and updates the currently stored version value.
9. the device of synchronization SQL executive plans according to claim 8, which is characterized in that the synchronization module is additionally operable to
According to each user identifier, will the statistics of database information corresponding with user identifier and original database statistical information it
Between there are inconsistent synchronizing informations to corresponding target detection storehouse.
10. according to the device of any synchronous SQL executive plans of claim 6 to 9, which is characterized in that described device is also
Including：
Module is established, for the target detection storehouse to be made to establish statistical information data dictionary according to the statistics of database information.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611000840.3A CN108073612A (en) | 2016-11-14 | 2016-11-14 | The method and apparatus of synchronous SQL executive plans |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611000840.3A CN108073612A (en) | 2016-11-14 | 2016-11-14 | The method and apparatus of synchronous SQL executive plans |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN108073612A true CN108073612A (en) | 2018-05-25 |
Family
ID=62161740
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201611000840.3A Pending CN108073612A (en) | 2016-11-14 | 2016-11-14 | The method and apparatus of synchronous SQL executive plans |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108073612A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109614315A (en) * | 2018-11-06 | 2019-04-12 | 武汉达梦数据库有限公司 | A kind of automatic generation method and system of data synchronism detection use-case |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102053961A (en) * | 2009-10-27 | 2011-05-11 | 中兴通讯股份有限公司 | Method and device for SQL statements and system for improving database reliability |
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
| CN103092970A (en) * | 2013-01-24 | 2013-05-08 | 华为技术有限公司 | Database operation method and device |
| CN103984726A (en) * | 2014-05-16 | 2014-08-13 | 上海新炬网络技术有限公司 | Local revision method for database execution plan |
| CN104866580A (en) * | 2015-05-26 | 2015-08-26 | 梁猛 | Method for quickly detecting impact caused by database modification to current service |
| US20150254295A1 (en) * | 2014-03-04 | 2015-09-10 | International Business Machines Corporation | Regression testing of sql execution plans for sql statements |
| CN105320679A (en) * | 2014-07-11 | 2016-02-10 | 中国移动通信集团重庆有限公司 | Data table index set generation method and device |
-
2016
- 2016-11-14 CN CN201611000840.3A patent/CN108073612A/en active Pending
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102053961A (en) * | 2009-10-27 | 2011-05-11 | 中兴通讯股份有限公司 | Method and device for SQL statements and system for improving database reliability |
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
| CN103092970A (en) * | 2013-01-24 | 2013-05-08 | 华为技术有限公司 | Database operation method and device |
| US20150254295A1 (en) * | 2014-03-04 | 2015-09-10 | International Business Machines Corporation | Regression testing of sql execution plans for sql statements |
| CN103984726A (en) * | 2014-05-16 | 2014-08-13 | 上海新炬网络技术有限公司 | Local revision method for database execution plan |
| CN105320679A (en) * | 2014-07-11 | 2016-02-10 | 中国移动通信集团重庆有限公司 | Data table index set generation method and device |
| CN104866580A (en) * | 2015-05-26 | 2015-08-26 | 梁猛 | Method for quickly detecting impact caused by database modification to current service |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109614315A (en) * | 2018-11-06 | 2019-04-12 | 武汉达梦数据库有限公司 | A kind of automatic generation method and system of data synchronism detection use-case |
| CN109614315B (en) * | 2018-11-06 | 2021-11-23 | 武汉达梦数据库股份有限公司 | Automatic generation method and system of data synchronization test case |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10572822B2 (en) | Modular memoization, tracking and train-data management of feature extraction | |
| US10452625B2 (en) | Data lineage analysis | |
| US8019795B2 (en) | Data warehouse test automation framework | |
| US9098630B2 (en) | Data selection | |
| CN104866580A (en) | Method for quickly detecting impact caused by database modification to current service | |
| CN106557486A (en) | A kind of storage method and device of data | |
| US10002142B2 (en) | Method and apparatus for generating schema of non-relational database | |
| JP6850806B2 (en) | Annotation system for extracting attributes from electronic data structures | |
| CN110795524B (en) | Main data mapping processing method and device, computer equipment and storage medium | |
| CN101882135B (en) | Data processing method and device | |
| CN105608113B (en) | Judge the method and device of POI data in text | |
| CN109472017B (en) | Method and device for obtaining relevant information of text court deeds of referee to be generated | |
| CN105630656B (en) | System robustness analysis method and device based on log model | |
| CN110209585A (en) | A kind of software test case intelligent training method, terminal and storage medium | |
| JP2020177668A (en) | User portrait processing method, user portrait processing device, server, and storage medium | |
| CN106933691A (en) | A kind of recognition methods and system for abnormity point in seasonal time series data sequence | |
| US10339035B2 (en) | Test DB data generation apparatus | |
| CN105868956A (en) | Data processing method and device | |
| CN110389941A (en) | Database method of calibration, device, equipment and storage medium | |
| CN105653546B (en) | A kind of search method and system of target topic | |
| CN109933803B (en) | Idiom information display method, idiom information display device, electronic equipment and storage medium | |
| CN103235757B (en) | Several apparatus and method that input domain tested object is tested are made based on robotization | |
| CN107426610A (en) | Video information synchronous method and device | |
| CN105335459A (en) | XBRL intelligent report platform based statement consolidation data extraction method | |
| KR102243794B1 (en) | Data integration device and data integration method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20180525 |
|
| RJ01 | Rejection of invention patent application after publication |