CN104572990A - Method and device for searching - Google Patents
Method and device for searching Download PDFInfo
- Publication number
- CN104572990A CN104572990A CN201510003870.9A CN201510003870A CN104572990A CN 104572990 A CN104572990 A CN 104572990A CN 201510003870 A CN201510003870 A CN 201510003870A CN 104572990 A CN104572990 A CN 104572990A
- Authority
- CN
- China
- Prior art keywords
- expression formula
- search
- expression
- variable
- database
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
-
- 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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2246—Trees, e.g. B+trees
-
- 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/24—Querying
- G06F16/242—Query formulation
-
- 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/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
-
- 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/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24537—Query rewriting; Transformation of operators
Abstract
The embodiment of the invention provides method and device for searching. The method comprises the steps of acquiring a search expression for searching in a database; selecting an expression in match with the search expression from a preset expression set according to the variation and an operator of the search expression; selecting target variable from the variable of the search expression, wherein an index is created for the target variable in the database; converting the search expression into an equivalent Sargable expression according to the matched expression and the target variable; searching from the database through the index according to the Sargable expression. With the adoption of the method and device for search, the searching efficiency can be effectively increased.
Description
Technical field
The embodiment of the present invention relates to database retrieval field, and more specifically, relates to a kind of method and apparatus for retrieving.
Background technology
In the retrieving of database, usually adopt index to improve retrieval rate, such as, the most frequently used index is that B+ sets index.In index field is inquired about, inquiry velocity often can be greatly improved.But when comprising function on index field, index is difficult to play a role, substantially can only adopts order scanning and index of reference cannot be made to scan.In the business scenario of reality, index field often all comprises function, such as, judge two dates (ABS (t1-t2) <5) within 5 days, or judgement is the time (Year (t1)=2014) etc. of 2014, in these cases, index often cannot play its effect, have impact on retrieval rate.
At present, in order to address this problem, propose index functions technology, namely the function for retrieval sets up corresponding index functions, as for index ename field being built substr, create indexemp_ename_substr on eemp (substr (ename, 1,2)).But, when Data Update, due to the value sum functions of index field itself calculate after value not direct corresponding relation, the maintenance cost of index functions is very high, causes the occasion of actual use little.
Summary of the invention
The embodiment of the present invention provides a kind of method and apparatus for retrieving, and effectively can improve effectiveness of retrieval.
First aspect provides a kind of method for retrieving, and the method comprises:
Obtain expression formula for search, this expression formula for search is used for retrieving in a database;
According to variable and the operational character of this expression formula for search, concentrate from the expression formula preset the matching expression choosing this expression formula for search;
From the variable of this expression formula for search, choose target variable, this target variable has index in this database;
According to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
According to this Sargable expression formula, by this index, retrieve in this database.
In conjunction with first aspect, in the first possible implementation of first aspect, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value, comprising:
According to this matching expression and this target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in this mapping table in each expression formula that this expression formula concentrates, this each expression formula has the variable of index and the mapping relations of Sargable expression formula.
In conjunction with the first possible implementation of first aspect or first aspect, in the implementation that the second of first aspect is possible, from the variable of this expression formula for search, choose target variable, comprising:
According to the index information of this database, from the variable of this expression formula for search, choose target variable, wherein, in the index information of this database, record the variable set up in this database and have index.
In conjunction with the implementation that the first or the second of first aspect or first aspect are possible, in the third possible implementation of first aspect, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value, comprising:
When this expression formula for search be function expression or operation expression, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
The method also comprises:
When this expression formula for search is conditional expression, according to this expression formula for search, retrieve in this database.
In conjunction with the first of first aspect or first aspect to a kind of possible implementation of the people in the third possible implementation, in the 4th kind of possible implementation of first aspect, this expression formula for search is under connection scene or under inquiry scene expression formula for search.
Second aspect, provide a kind of device for retrieving, this device comprises:
Acquisition module, for obtaining expression formula for search, this expression formula for search is used for retrieving in a database;
First chooses module, for the variable of this expression formula for search that obtains according to this acquisition module and operational character, concentrates from the expression formula preset the matching expression choosing this expression formula for search;
Second chooses module, and choose target variable in the variable of this expression formula for search that obtains from this acquisition module, this target variable has index in this database;
Modular converter, for first choosing this matching expression that module chooses and this second chooses this target variable that module chooses according to this, converts Sargable expression formula of equal value to by this expression formula for search;
First retrieval module, for this Sargable expression formula according to this modular converter conversion gained, by this index, retrieves in this database.
In conjunction with first aspect, in the first possible implementation of first aspect, this modular converter specifically for, according to this matching expression and this target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in this mapping table in each expression formula that this expression formula concentrates, this each expression formula has the variable of index and the mapping relations of Sargable expression formula.
In conjunction with the first possible implementation of first aspect or first aspect, in the implementation that the second of first aspect is possible, this second choose module specifically for, according to the index information of this database, target variable is chosen from the variable of this expression formula for search, wherein, the variable set up in this database and have index is recorded in the index information of this database.
In conjunction with the implementation that the first or the second of first aspect or first aspect are possible, in the third possible implementation of first aspect, this modular converter specifically for, when this expression formula for search be function expression or operation expression, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
This device also comprises:
Second retrieval module, for when this expression formula for search is conditional expression, according to this expression formula for search, retrieves in this database.
In conjunction with the first of first aspect or first aspect to a kind of possible implementation of the people in the third possible implementation, in the 4th kind of possible implementation of first aspect, this expression formula for search is under connection scene or under inquiry scene expression formula for search.
Based on technique scheme, the method and apparatus for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, can realize indexed search, effectively can improve effectiveness of retrieval.
Accompanying drawing explanation
In order to be illustrated more clearly in the technical scheme of the embodiment of the present invention, be briefly described to the accompanying drawing used required in embodiment or description of the prior art below, apparently, accompanying drawing in the following describes is only some embodiments of the present invention, for those of ordinary skill in the art, under the prerequisite not paying creative work, other accompanying drawing can also be obtained according to these accompanying drawings.
The indicative flowchart of method for retrieving that Fig. 1 shows that the embodiment of the present invention provides.
Another indicative flowchart of method for retrieving that Fig. 2 shows that the embodiment of the present invention provides.
The indicative flowchart again of method for retrieving that Fig. 3 shows that the embodiment of the present invention provides.
The indicative flowchart again of method for retrieving that Fig. 4 shows that the embodiment of the present invention provides.
The schematic block diagram of device for retrieving that Fig. 5 shows that the embodiment of the present invention provides.
Another schematic block diagram of device for retrieving that Fig. 6 shows that the embodiment of the present invention provides.
Embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, be clearly and completely described the technical scheme in the embodiment of the present invention, obviously, described embodiment is the present invention's part embodiment, instead of whole embodiments.Based on the embodiment in the present invention, those of ordinary skill in the art, not making the every other embodiment obtained under creative work prerequisite, belong to the scope of protection of the invention.
To facilitate understanding of the present embodiment of the invention, first several relevant concept is introduced at this:
1) data base management system (DBMS)
Data base management system (DBMS) (Database Management System, referred to as " DBMS ") is the large software of a kind of manipulation and management database, for foundation, operation and maintenance database.It carries out unified management and control to database, to ensure security and the integrality of database.
2) relational database
Relational database is based upon the database on relational model of database basis.
The important element of relevant database comprises tables of data and list catenation, by various types of list catenation, information prosily can be assembled splicing.
3) index
Index is the method sorted to multiple fields of record set, be that a field creates an index in a table, another one data structure will be created, comprise field values and point to the pointer of relative recording, then this index structure is sorted, allow to carry out dichotomy sequence on the data.
The object of index accelerates inquiry in the conceived case, so index should be based upon primary key column, only constraint row, the conventional row of conditional statement.
4) Sargable expression formula
In relational database, if data base management system (DBMS) (Database Management System, referred to as " DBMS ") retrieval of index to certain expression formula in inquiry can be utilized to accelerate, this expression formula is called as Sargable expression formula, this term derives from the abbreviation of Search Argument Able, and also can be referred to as can the expression formula of SARG.
Sargable expression formula typically refers to the comparison of row and constant.If expression formula for search is sargable (can SARG) expression formula, mean that it can utilize index to accelerate completing of inquiry.
If expression formula for search can not SARG, mean that this expression formula for search can not utilize index (can not utilize at least partly) to perform full table or index scanning, the decline of query performance can be caused.Following expression formula, being can not the expression formula of SARG:
“is null”,”！＝”,”！>”,”！<”,”NOT”,”Not Exist”,”Not in”,”Not Like”,”Like500％”。In addition, row use comprise function expression formula, expression formula both sides all use the expression formula of same column, and and the expression formula that different lines (non-constant) compares is not can the expression formula of SARG.
Fig. 1 shows the method 100 for retrieving of the embodiment of the present invention, and the method 100 comprises:
S110, obtains expression formula for search, and this expression formula for search is used for retrieving in a database;
Particularly, this expression formula for search is generally made up of variable (also can be referred to as term) and various logic operational symbol, such as this expression formula for search is v1+v2<10, and wherein v1 and v2 is the variable (term) in this expression formula for search.
S120, according to variable and the operational character of this expression formula for search, concentrates from the expression formula preset the matching expression choosing this expression formula for search;
S130, chooses target variable from the variable of this expression formula for search, and this target variable has index in this database;
S140, according to this matching expression and this target variable, converts Sargable expression formula of equal value to by this expression formula for search;
S150, according to this Sargable expression formula, by this index, retrieves in this database.
Therefore, the method for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
In S110, obtain expression formula for search, particularly, this expression formula for search can for connecting the expression formula for search under scene, and such as nested circulation Nestloop Jion connects the Jion expression formula in scene; This expression formula for search can also be the expression formula for search under inquiry scene, such as, inquire about the Filter expression formula in scene.
Alternatively, in embodiments of the present invention, this expression formula for search is under connection scene or under inquiry scene expression formula for search.
Should be understood that nested circulation Nestloop Jion connects is a kind of common connected mode in relational database.In Nestloop Jion connects, two data acquisitions (tables of data) carrying out connecting become outside table (table-drive respectively, also Outer Side can be referred to as) and inner side table (by table-drive, also can be referred to as Inner Side).First every qualified data of a line in the table of process outside, every data line and inner side table carry out matching connection result afterwards, finally get results set.Particularly, in Nestloop Jion connected mode, get record successively from outside table (Outer Side), search according to Nestloop condition of contact traversal in inner side table (Inner Side), find qualified linkage record.
In nested circulation Nest Loop Join, ensure that connecting column can exist index object, can the joint efficiency of largely low raising Nest Loop Join.
In S120, concentrate from the expression formula preset the matching expression choosing this expression formula for search.Particularly, this expression formula collection preset comprises polytype expression formula, as shown in first row in table 1 (b).Such as this expression formula for search is v1+v2<10, then the matching expression that this expression formula for search is concentrated in this expression formula is a+b op c, and wherein, a corresponds to v1, b and corresponds to v2, op corresponding to <, c corresponding to 10.
Should be understood that the expression formula collection that this is preset can be systemic presupposition, or user-defined, the embodiment of the present invention does not limit this.
In S130, from the variable of this expression formula for search, choose the target variable having index in a database.
Particularly, this expression formula for search can comprise multiple variable, selects the variable having index from the plurality of variable.Such as, this expression formula for search is Sqrt (abs (v1-v2)) <10, this expression formula for search has Two Variables v1 and v2, if v1 has index in a database, then v1 is defined as the target variable of this expression formula for search; If v1 and v2 has index all in a database, then any one in v1 and v2 can be defined as the target variable of this expression formula for search, or using Two Variables v1 and v2 all as the target variable of this expression formula for search.
Alternatively, in embodiments of the present invention, from the variable of this expression formula for search, choose target variable, comprising:
According to the index information of this database, from the variable of this expression formula for search, choose target variable, wherein, in the index information of this database, record the variable set up in this database and have index.
Alternatively, in embodiments of the present invention, also whether can have index in a database by the variable detected in this expression formula for search, determine this target variable.More specifically, can adopt and existingly anyly can judge that whether a variable (also can be referred to as term) sets up the method having index in a database, determine the target variable in this expression formula for search, the embodiment of the present invention does not limit this.
In S140, according to the mapping table preset, the Sargable expression formula with this expression formula for search equivalence can be obtained; Also can based on this target variable, to this expression formula for search reverse resolution, obtain the Sargable expression formula with this expression formula for search equivalence, the embodiment of the present invention does not limit this.
Alternatively, in embodiments of the present invention, this expression formula for search, according to this matching expression and this target variable, is converted to Sargable expression formula of equal value, comprises by S140:
According to this matching expression and this target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in this mapping table in each expression formula that this expression formula concentrates, this each expression formula has the variable of index and the mapping relations of Sargable expression formula.
Particularly, this mapping table preset is as shown in table 1 (a) He (b), wherein, table 1 (a) shows the definition of operational character, in table 1 (b) first is classified as default expression formula and concentrates each expression formula (type) comprised, second is classified as the target variable that the foundation shown in each expression formula has index, 3rd is classified as the Sargable expression formula corresponding to the target variable shown in the expression formula shown in first row and secondary series, the wherein first row of every a line, has mapping relations between secondary series and the 3rd row.4th row provide corresponding example.
Table 1 (a)
| Operational character (op) | Associative operation (link_op) | Inverse operations (reverse_op) |
| > | >＝ | < |
| ＝ | ＝ | ＝ |
| < | <＝ | > |
Table 1 (b)
Such as, the third line in table 1 (b) represents, for expression formula a+b op c, wherein variable a has set up index in a database, take a as target variable, and the Sargable expression formula corresponding with it is a op (c-b).As shown in the example in table 1 (b), when expression formula for search is v1+v2=10, using the target variable of v1 as this expression formula for search, be v1=10-v2 with the Sargable expression formula of this expression formula for search v1+v2=10 equivalence.
Again such as, this expression formula for search is Sqrt (v1) <20, the matching expression of this expression formula for search chosen is concentrated to be sqrt (a) <c from the expression formula preset shown in table 1 (b) first row, wherein, a corresponds to v1, c corresponds to 20, determine that v1 is target variable, then according to the first row of fifth line in table 1 (b), mapping relations between secondary series and the 3rd row, determine that the Saegable expression formula that this expression formula for search Sqrt (v1) <20 is corresponding is v1<400.
To should be understood that in a, b, the c shown in table 1 that at least two is variable.
Should be understood that and be depicted as example with table 1 above, describe the mapping table that this is preset, do not limit the protection domain of the embodiment of the present invention, such as first row can comprise a greater variety of expression formula.In actual applications, this mapping table preset also can present with its different form, and the embodiment of the present invention is not construed as limiting this.
Alternatively, in embodiments of the present invention, this mapping table preset can be that system is predefined, also can be added by User Defined or revise.
Therefore, the method for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Alternatively, in embodiments of the present invention, S140, according to this matching expression and this target variable, converts Sargable expression formula of equal value to, comprising by this expression formula for search:
Using this target variable as dependent variable, based on this matching expression, by carrying out reverse resolution to this expression formula for search, try to achieve the Sargable expression formula with this expression formula for search equivalence.
Such as, this expression formula for search is Sqrt (abs (v1-v2)) <10, and v1 is target variable, it is then dependent variable with v1, v2 is independent variable, reverse resolution is carried out to this expression formula for search Sqrt (abs (v1-v2)) <10, obtain the Sargable expression formula that this expression formula for search is corresponding, i.e. v1<v2+square (10) andv1>v2-square (10).
Because target variable v1 has index in a database, then this Sargable expression formula v1<v2+square (10) and v1>v2-square (10), can utilize index technology to retrieve.
In S150, can according to obtain in this expression formula for search and S140 with the Sargable expression formula of this expression formula for search equivalence, the index set up in a database by this target variable, is retrieved in this database.
Should be understood that in embodiments of the present invention, after obtaining the Sargable expression formula of this expression formula for search, the existing index that utilizes can be adopted to carry out the method retrieved, according to this Sargable expression formula, realize the indexed search of this expression formula for search.
In embodiments of the present invention, by matching expression and the target variable of deterministic retrieval expression formula, the Sargable expression formula corresponding to deterministic retrieval expression formula, thus can realize, even if this expression formula for search is the expression formula comprising function, index also can be utilized to improve effectiveness of retrieval.Compared to existing index functions technology, maintenance cost is low, effectively can improve recall precision.
Alternatively, in embodiments of the present invention, this expression formula for search, according to this matching expression and this target variable, is converted to Sargable expression formula of equal value, comprises by S140:
When this expression formula for search be function expression or operation expression, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
The method 100 also comprises:
S160, when this expression formula for search is conditional expression, according to this expression formula for search, retrieves in this database.
Particularly, operation expression can be expressed as A Operator B, particularly, such as:
T1.col1＝T2.col1,T1.col1>T2.col1。
Conditional expression can be expressed as follows:
The method for retrieving that the embodiment of the present invention provides, can be applied under connecting scene, under also can be applicable to inquiry scene, simply describes below.
Fig. 2 shows the indicative flowchart of method under connection scene for retrieving that the embodiment of the present invention provides.
Particularly, as shown in Figure 2, in S21, the expression formula for search under inquiry scene is extracted; Can, in S22, judge in the set of default expression formula translation type, find the goal expression type of mating with this expression formula for search, if had, forward S23 to, if do not had, this time retrieving terminates; In S23, judge whether to increase the meeting of subregion pruning machine, wherein, branch's beta pruning refers to, table stores according to certain Field-Partitions, if inquired about in this field, DBMS only selects to read the subregion inquired about and relate to, and does not operate for the subregion do not related to, if, forward S25 to, if not, forward S24 to; In S24, judge whether to make index of reference be scanned into line retrieval, if so, forward S25 to, if not, forward S22 to; In S25, in conjunction with the goal expression type presetting expression formula conversion set and this expression formula for search, this expression formula for search is converted to Sargable expression formula, this Sargable expression formula is increased in querying condition, utilizes index to retrieve; S26, this time retrieving terminates.
Should be understood that if index of reference can be made to scan, then this expression formula is converted to Sargable expression formula and be increased in querying condition.
Fig. 3 shows method another indicative flowchart under connection scene for retrieving that the embodiment of the present invention provides.
Particularly, as shown in Figure 3, in S31, the Join expression formula (expression formula for search corresponding in the embodiment of the present invention) under Nestlloop connection scene is extracted; In S32, extract the row in this Join expression formula; In S33, judge that (Outer Side) is shown whether more than row in this Join expression formula in the outside that Nestlloop connects, if so, forward S34 to, if not, this retrieving terminates; In S34, show (Outer Side) row in this Join expression formula according to outside this, change this Join expression formula into Sargable expression formula; In S35, judge whether this Join expression formula is changed complete, if not, forwards S33 to, if so, forwards S36 to; In S36, by the Sargable expression formula that this Join expression formula of conversion obtains, be put in outside table (Outer Side), reselect searching route, retrieve.
Fig. 4 shows the indicative flowchart of method under inquiry scene for retrieving that the embodiment of the present invention provides.
As shown in Figure 4, in S41, extract the Filter expression formula (expression formula for search corresponding in the embodiment of the present invention) under inquiry connection scene; In S42, judge that whether row in this Filter expression formula are more than two row, if so, forward in S43, if not, this retrieving terminates; In S43, in the row that Filter expression formula comprises, determine to set up the conversion row of index, and arrange according to this conversion this Filter expression formula is changed, be converted to the Sargable expression formula with this Filter expression equivalence; In S44, judge whether this Filter expression formula is changed complete, if so, forwards S45 to, if not, forwards S42 to; In S45, according to the Sargable expression formula be converted to by this Filer expression formula, reselect searching route, retrieve.
Therefore, the method for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
For the ease of understanding, provide two concrete examples below.
Example 1) being applied in for the method retrieved of providing of the embodiment of the present invention connect in scene.
Suppose that the condition of contact between two tables is ABS (wb_swry.swkssj-A.swkssj) <=interval'0day 00:05'.
#explain SELECT wb_swry.zjhm,A.swkssj FROM wb_swry,(SELECTzjhm,swkssj,yycsdm FROM wb_swry WHEREzjhm＝'510100198012308300'AND swkssj>＝'2012/01/01'ANDswkssj<＝'2012/04/01')AS A WHERE wb_swry.yycsdm＝A.yycsdm ANDABS(wb_swry.swkssj-A.swkssj)<＝interval'0day 00:05'ANDwb_swry.zjhm！＝A.zjhm；
Example 2) embodiment of the present invention provide for the method retrieved be applied in inquiry scene in.
Suppose the querying condition of table be to_char (l_shipdate, ' YYYY-MM-DD')=' 2013-04-13'.#explain select*from lineitem where to_char(l_shipdate,'YYYY-MM-DD')＝'2013-04-13'；
Above composition graphs 1 to Fig. 4, describes the method for retrieving according to the embodiment of the present invention in detail, below in conjunction with Fig. 5 and Fig. 6, describes the device for retrieving according to the embodiment of the present invention.
Fig. 5 shows the schematic block diagram of device 500 for retrieving according to the embodiment of the present invention.As shown in Figure 5, this device 500 comprises:
Acquisition module 510, for obtaining expression formula for search, this expression formula for search is used for retrieving in a database;
First chooses module 520, for the variable of this expression formula for search that obtains according to this acquisition module and operational character, concentrates from the expression formula preset the matching expression choosing this expression formula for search;
Second chooses module 530, and choose target variable in the variable of this expression formula for search that obtains from this acquisition module, this target variable has index in this database;
Modular converter 540, for first choosing this matching expression that module chooses and this second chooses this target variable that module chooses according to this, converts Sargable expression formula of equal value to by this expression formula for search;
First retrieval module 550, for this Sargable expression formula according to this modular converter conversion gained, by this index, retrieves in this database.
Therefore, the device for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Alternatively, as an embodiment, this modular converter specifically for, according to this matching expression and this target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in this mapping table in each expression formula that this expression formula concentrates, this each expression formula has the variable of index and the mapping relations of Sargable expression formula.
Alternatively, as an embodiment, this second choose module specifically for, according to the index information of this database, from the variable of this expression formula for search, choose target variable, wherein, in the index information of this database, record the variable set up in this database and have index.
Alternatively, as an embodiment, this modular converter specifically for, when this expression formula for search be function expression or operation expression, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
This device also comprises:
Second retrieval module, for when this expression formula for search is conditional expression, according to this expression formula for search, retrieves in this database.
Alternatively, as an embodiment, this expression formula for search is under connection scene or under inquiry scene expression formula for search.
Therefore, the device for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Should be understood that according to the embodiment of the present invention for the above-mentioned of the modules in the device 500 retrieved and other operation and/or function respectively in order to realize the corresponding flow process of each method in Fig. 1 to Fig. 4, for simplicity, not repeating them here.
As shown in Figure 6, the embodiment of the present invention additionally provides a kind of device 600 for retrieving, and this device 600 comprises processor 610, storer 620 and bus system 630.Wherein, processor 610 and storer 620 is connected by bus system 630, this storer 620 for storing instruction, this processor 610 for performing the instruction of this storer 620 storage, for:
Obtain expression formula for search, this expression formula for search is used for retrieving in a database;
According to variable and the operational character of this expression formula for search, concentrate from the expression formula preset the matching expression choosing this expression formula for search;
From the variable of this expression formula for search, choose target variable, this target variable has index in this database;
According to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
According to this Sargable expression formula, by this index, retrieve in this database.
Therefore, the device for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Alternatively, as an embodiment, this processor 610 specifically for, according to this matching expression and this target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in this mapping table in each expression formula that this expression formula concentrates, this each expression formula has the variable of index and the mapping relations of Sargable expression formula.
Alternatively, as an embodiment, this processor 610 specifically for, according to the index information of this database, from the variable of this expression formula for search, choose target variable, wherein, in the index information of this database, record the variable set up in this database and have index.
Alternatively, as an embodiment, this processor 610 specifically for, when this expression formula for search be function expression or operation expression, according to this matching expression and this target variable, this expression formula for search is converted to Sargable expression formula of equal value;
This processor 610 also for, when this expression formula for search is conditional expression, according to this expression formula for search, retrieve in this database.
Alternatively, as an embodiment, this expression formula for search is under connection scene or under inquiry scene expression formula for search.
Therefore, the device for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Should understand, in embodiments of the present invention, this processor 610 can be CPU (central processing unit) (CentralProcessing Unit, referred to as " CPU "), this processor 610 can also be other general processors, digital signal processor (DSP), special IC (ASIC), ready-made programmable gate array (FPGA) or other programmable logic device (PLD), discrete gate or transistor logic, discrete hardware components etc.The processor etc. of general processor can be microprocessor or this processor also can be any routine.
This storer 620 can comprise ROM (read-only memory) and random access memory, and provides instruction and data to processor 610.A part for storer 620 can also comprise nonvolatile RAM.Such as, the information of all right storage device type of storer 620.
This bus system 630, except comprising data bus, can also comprise power bus, control bus and status signal bus in addition etc.But for the purpose of clearly demonstrating, in the drawings various bus is all designated as bus system 630.
In implementation procedure, each step of said method can be completed by the instruction of the integrated logic circuit of the hardware in processor 610 or software form.Step in conjunction with the method disclosed in the embodiment of the present invention can directly be presented as that hardware processor is complete, or hardware in purpose processor and software module combination complete.Software module can be positioned at random access memory, flash memory, ROM (read-only memory), in the storage medium of this area maturations such as programmable read only memory or electrically erasable programmable storer, register.This storage medium is positioned at storer 620, and processor 610 reads the information in storer 620, completes the step of said method in conjunction with its hardware.For avoiding repetition, be not described in detail here.
Therefore, the device for retrieving that the embodiment of the present invention provides, by expression formula for search is converted to Sargable expression formula, thus achieves and utilizes index to retrieve, effectively can improve effectiveness of retrieval.
Should understand, can corresponding to the subscriber equipment 500 according to the embodiment of the present invention for the device 600 retrieved according to the embodiment of the present invention, and above-mentioned and other operation of the modules in device 600 and/or function are respectively in order to realize the corresponding flow process of each method in Fig. 1 to Fig. 4, for simplicity, do not repeat them here.
Should be understood that term "and/or" herein, being only a kind of incidence relation describing affiliated partner, can there are three kinds of relations in expression, and such as, A and/or B, can represent: individualism A, exists A and B simultaneously, these three kinds of situations of individualism B.In addition, character "/" herein, general expression forward-backward correlation is to the relation liking a kind of "or".
Should understand, in various embodiments of the present invention, the size of the sequence number of above-mentioned each process does not also mean that the priority of execution sequence, and the execution sequence of each process should be determined with its function and internal logic, and should not form any restriction to the implementation process of the embodiment of the present invention.
Those of ordinary skill in the art can recognize, in conjunction with unit and the algorithm steps of each example of embodiment disclosed herein description, can realize with the combination of electronic hardware or computer software and electronic hardware.These functions perform with hardware or software mode actually, depend on application-specific and the design constraint of technical scheme.Professional and technical personnel can use distinct methods to realize described function to each specifically should being used for, but this realization should not thought and exceeds scope of the present invention.
Those skilled in the art can be well understood to, and for convenience and simplicity of description, the specific works process of the system of foregoing description, device and unit, with reference to the corresponding process in preceding method embodiment, can not repeat them here.
In several embodiments that the application provides, should be understood that disclosed system, apparatus and method can realize by another way.Such as, device embodiment described above is only schematic, such as, the division of described unit, be only a kind of logic function to divide, actual can have other dividing mode when realizing, such as multiple unit or assembly can in conjunction with or another system can be integrated into, or some features can be ignored, or do not perform.Another point, shown or discussed coupling each other or direct-coupling or communication connection can be by some interfaces, and the indirect coupling of device or unit or communication connection can be electrical, machinery or other form.
The described unit illustrated as separating component or can may not be and physically separates, and the parts as unit display can be or may not be physical location, namely can be positioned at a place, or also can be distributed in multiple network element.Some or all of unit wherein can be selected according to the actual needs to realize the object of the present embodiment scheme.
In addition, each functional unit in each embodiment of the present invention can be integrated in a processing unit, also can be that the independent physics of unit exists, also can two or more unit in a unit integrated.
If described function using the form of SFU software functional unit realize and as independently production marketing or use time, can be stored in a computer read/write memory medium.Based on such understanding, the part of the part that technical scheme of the present invention contributes to prior art in essence in other words or this technical scheme can embody with the form of software product, this computer software product is stored in a storage medium, comprising some instructions in order to make a computer equipment (can be personal computer, server, or the network equipment etc.) perform all or part of step of method described in each embodiment of the present invention.And aforesaid storage medium comprises: USB flash disk, portable hard drive, ROM (read-only memory) (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), magnetic disc or CD etc. various can be program code stored medium.
The above; be only the specific embodiment of the present invention, but protection scope of the present invention is not limited thereto, is anyly familiar with those skilled in the art in the technical scope that the present invention discloses; change can be expected easily or replace, all should be encompassed within protection scope of the present invention.Therefore, protection scope of the present invention should be as the criterion with the protection domain of described claim.
Claims (10)
1. the method for retrieving, is characterized in that, comprising:
Obtain expression formula for search, described expression formula for search is used for retrieving in a database;
According to variable and the operational character of described expression formula for search, concentrate from the expression formula preset the matching expression choosing described expression formula for search;
From the variable of described expression formula for search, choose target variable, described target variable has index in the database;
According to described matching expression and described target variable, described expression formula for search is converted to Sargable expression formula of equal value;
According to described Sargable expression formula, by described index, retrieve in the database.
2. method according to claim 1, is characterized in that, described according to described matching expression and described target variable, described expression formula for search is converted to Sargable expression formula of equal value, comprising:
According to described matching expression and described target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in described mapping table in each expression formula that described expression formula concentrates, described each expression formula has the variable of index and the mapping relations of Sargable expression formula.
3. method according to claim 1 and 2, is characterized in that, describedly from the variable of described expression formula for search, chooses target variable, comprising:
According to the index information of described database, from the variable of described expression formula for search, choose target variable, wherein, in the index information of described database, record the variable set up in the database and have index.
4. the method according to any one of claim 1-3, is characterized in that, described according to described matching expression and described target variable, described expression formula for search is converted to Sargable expression formula of equal value, comprising:
When described expression formula for search be function expression or operation expression, according to described matching expression and described target variable, described expression formula for search is converted to Sargable expression formula of equal value;
Described method also comprises:
When described expression formula for search is conditional expression, according to described expression formula for search, retrieve in the database.
5. method according to any one of claim 1 to 4, is characterized in that, described expression formula for search is under connection scene or under inquiry scene expression formula for search.
6. the device for retrieving, is characterized in that, comprising:
Acquisition module, for obtaining expression formula for search, described expression formula for search is used for retrieving in a database;
First chooses module, for the variable of described expression formula for search that obtains according to described acquisition module and operational character, concentrates from the expression formula preset the matching expression choosing described expression formula for search;
Second chooses module, and choose target variable in the variable of described expression formula for search that obtains from described acquisition module, described target variable has index in the database;
Modular converter, choosing for choosing according to described first described matching expression and described second that module chooses the described target variable that module chooses, described expression formula for search being converted to Sargable expression formula of equal value;
First retrieval module, for the described Sargable expression formula according to described modular converter conversion gained, by described index, retrieves in the database.
7. device according to claim 6, it is characterized in that, described modular converter specifically for, according to described matching expression and described target variable, by the mapping table preset, determine corresponding Sargable expression formula, wherein, the foundation recorded in described mapping table in each expression formula that described expression formula concentrates, described each expression formula has the variable of index and the mapping relations of Sargable expression formula.
8. the device according to claim 6 or 7, it is characterized in that, described second choose module specifically for, according to the index information of described database, target variable is chosen from the variable of described expression formula for search, wherein, the variable set up in the database and have index is recorded in the index information of described database.
9. the device according to any one of claim 6-8, it is characterized in that, described modular converter specifically for, when described expression formula for search be function expression or operation expression, according to described matching expression and described target variable, described expression formula for search is converted to Sargable expression formula of equal value;
Described device also comprises:
Second retrieval module, for when described expression formula for search is conditional expression, according to described expression formula for search, retrieves in the database.
10. the device according to any one of claim 6 to 9, is characterized in that, described expression formula for search is under connection scene or under inquiry scene expression formula for search.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510003870.9A CN104572990A (en) | 2015-01-05 | 2015-01-05 | Method and device for searching |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510003870.9A CN104572990A (en) | 2015-01-05 | 2015-01-05 | Method and device for searching |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN104572990A true CN104572990A (en) | 2015-04-29 |
Family
ID=53089052
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510003870.9A Pending CN104572990A (en) | 2015-01-05 | 2015-01-05 | Method and device for searching |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104572990A (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105975584A (en) * | 2016-05-03 | 2016-09-28 | 河北大学 | Mathematical expression similar distance measurement method |
| CN106649568A (en) * | 2016-11-15 | 2017-05-10 | 中国银联股份有限公司 | Database retrieval method and device |
| CN109376220A (en) * | 2018-12-12 | 2019-02-22 | 北京字节跳动网络技术有限公司 | Method and apparatus for obtaining information |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US5778353A (en) * | 1994-05-20 | 1998-07-07 | International Business Machines Corporation | Computer program product for optimizing data retrieval using index scanning |
| CN101216760A (en) * | 2007-12-28 | 2008-07-09 | 北京方正国际软件系统有限公司 | Dynamic mapping interface calling system and method |
| CN101256579A (en) * | 2008-04-08 | 2008-09-03 | 中兴通讯股份有限公司 | Method for inquesting data organization in database |
| CN101593191A (en) * | 2008-05-29 | 2009-12-02 | 国络多媒体有限公司 | The index establishing method of database, database search method and system |
-
2015
- 2015-01-05 CN CN201510003870.9A patent/CN104572990A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US5778353A (en) * | 1994-05-20 | 1998-07-07 | International Business Machines Corporation | Computer program product for optimizing data retrieval using index scanning |
| CN101216760A (en) * | 2007-12-28 | 2008-07-09 | 北京方正国际软件系统有限公司 | Dynamic mapping interface calling system and method |
| CN101256579A (en) * | 2008-04-08 | 2008-09-03 | 中兴通讯股份有限公司 | Method for inquesting data organization in database |
| CN101593191A (en) * | 2008-05-29 | 2009-12-02 | 国络多媒体有限公司 | The index establishing method of database, database search method and system |
Non-Patent Citations (1)
| Title |
|---|
| 赵洁 等: "基于JSP+SQL Server的管理信息系统性能优化", 《广东工业大学学报》 * |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105975584A (en) * | 2016-05-03 | 2016-09-28 | 河北大学 | Mathematical expression similar distance measurement method |
| CN105975584B (en) * | 2016-05-03 | 2019-02-05 | 河北大学 | A kind of mathematic(al) representation similarity distance measurement method |
| CN106649568A (en) * | 2016-11-15 | 2017-05-10 | 中国银联股份有限公司 | Database retrieval method and device |
| CN106649568B (en) * | 2016-11-15 | 2020-11-03 | 中国银联股份有限公司 | Database retrieval method and device |
| CN109376220A (en) * | 2018-12-12 | 2019-02-22 | 北京字节跳动网络技术有限公司 | Method and apparatus for obtaining information |
| CN109376220B (en) * | 2018-12-12 | 2020-12-25 | 北京字节跳动网络技术有限公司 | Method and device for acquiring information |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN105493085B (en) | Create method, system and the computer readable storage medium of the sample of database table | |
| CN104462084A (en) | Search refinement advice based on multiple queries | |
| CN103177066B (en) | Analysis and expression interpersonal relationships | |
| CN103793422A (en) | Methods for generating cube metadata and query statements on basis of enhanced star schema | |
| CN107066534B (en) | Multi-source data polymerization and system | |
| CN110309336A (en) | Image search method, device, system, server and storage medium | |
| CN104298736A (en) | Method and device for aggregating and connecting data as well as database system | |
| CN102867065B (en) | Based on Data Transform Device and the method for relevant database | |
| CN104572990A (en) | Method and device for searching | |
| US10990573B2 (en) | Fast index creation system for cloud big data database | |
| CN106294499A (en) | A kind of database data querying method and equipment | |
| CN104731969A (en) | Mass data join aggregation query method, device and system in distributed environment | |
| CN105279177A (en) | Data structure conversion method and apparatus | |
| CN103839270A (en) | Image matching method and device | |
| US20190087470A1 (en) | System and method for mining user cycle mode | |
| CN105745642A (en) | Device and method for inquiring data | |
| CN108733745A (en) | A kind of enquiry expanding method based on medical knowledge | |
| CN102193988A (en) | Method and system for retrieving node data in graphic database | |
| CN110737779A (en) | Knowledge graph construction method and device, storage medium and electronic equipment | |
| CN105550220A (en) | Fetching method and apparatus for heterogeneous system | |
| WO2019048879A1 (en) | System for detecting data relationships based on sample data | |
| CN104598485A (en) | Method and device for processing database table | |
| CN107608995A (en) | A kind of foundation of product chain object database, querying method, device and system | |
| CN101405727B (en) | Management of statistical views in a database system | |
| Peyravi | A schema selection framework for data warehouse design |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20150429 |
|
| WD01 | Invention patent application deemed withdrawn after publication |