CN106777054A - Half-connection merging method and half-connection merge device - Google Patents
Half-connection merging method and half-connection merge device Download PDFInfo
- Publication number
- CN106777054A CN106777054A CN201611131301.3A CN201611131301A CN106777054A CN 106777054 A CN106777054 A CN 106777054A CN 201611131301 A CN201611131301 A CN 201611131301A CN 106777054 A CN106777054 A CN 106777054A
- Authority
- CN
- China
- Prior art keywords
- connection
- sentence
- subquery
- matching
- sql syntax
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
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/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
- G06F16/24542—Plan optimisation
- G06F16/24544—Join order optimisation
-
- 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
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Operations Research (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention proposes a kind of half-connection merging method and half-connection merges device, wherein, the half-connection merging method includes：The half-connection of match query subquery in SQL syntax parsing tree；If the sentence of half-connection and the sentence of subquery that match subquery are the one of which sentences of first kind sentence and Second Type sentence, the first predicate expressions to be combined are determined according to this kind of sentence；Judge whether the half-connection that this kind of sentence is changed into is identical with the type of the half-connection of matching subquery；If identical, the first predicate expressions are merged with the filter condition in the half-connection for matching subquery；If differing, the first predicate expressions are negated, and the first predicate expressions of the inverted are merged with the filter condition in the half-connection for matching subquery.By technical scheme, search space can be reduced and reduced the number of times for accessing physical table and reduced the calculating of the predicate expressions for repeating.
Description
Technical field
The present invention relates to database technical field, in particular to a kind of half-connection merging method and a kind of half-connection
Merge device.
Background technology
In the process master of database processing SQL (Structured Query Language, SQL) sentence
It is divided into three phases：First, the syntactic analysis phase, in this stage, database (data warehouse) is by the SQL languages from client
Sentence, the data structure that database (data warehouse) can be recognized is converted into by syntax parsing instrument, that is, be converted into syntactic analysis
Tree；2nd, the query optimization stage, in this stage, database (data warehouse) applies various optimizations on morphology syntactic analysis tree
Method (namely common in database inquiry rewrite), then calculate an optimal access road to participate in the meter of connection
Footpath, ultimately generates a structure to represent optimal inquiry plan；3rd, the inquiry plan stage is performed, optimal inquiry meter is performed
Draw, finally obtain result.
Wherein, in the query optimization stage, various optimizations can be carried out to syntactic analysis tree.In these optimisation strategies, there is one
Item is exactly by IN sentences or EXIST paraphrasings are into half-connection sentence and pull up.This have the advantage that：By IN sentences or
EXIST paraphrasings can provide more chances the access path smaller to find cost into after half-connection, final to obtain more
Excellent inquiry plan.
The half-connection optimization for IN sentences or EXIST sentences provided in current database (data warehouse), one
Major problem is that not accounting for merging half-connection.In some cases, it is in the presence of many in complicated SQL statement
The possibility that individual half-connection merges.And now with database (data warehouse) in, half-connection is not merged, this is just
Following 2 points of defect can be brought：
(1) in the query optimization stage, during search access path, dynamic programming algorithm is typically employed, the Dynamic Programming is calculated
The quantity exponentially level relation of the table that the complexity of method is connected with participation, therefore many tables, will increase the search of several times
Space, so as to correspondingly increase search cost.
(2) the inquiry plan stage is performed, the predicate expressions of the number of times and increase repetition for accessing physical table can be increased
Calculate, finally increase IO pressure and the consumption of cpu resource of disk.
Therefore, how to realize merging half-connection, in the query optimization stage to reduce search space and look into execution
The number of times of inquiry programming phase reduction access physical table is calculated as technology urgently to be resolved hurrily with the predicate expressions for reducing repetition
Problem.
The content of the invention
Therefore, it is an object of the present invention to propose a kind of half-connection merging method.
Merge device it is another object of the present invention to propose a kind of half-connection.
To realize above-mentioned at least one purpose, embodiment according to the first aspect of the invention, it is proposed that a kind of half-connection
Merging method, including：The half-connection of match query subquery in SQL syntax parsing tree；If matching partly connecting for the subquery
The sentence and the sentence of the subquery that connect are the one of which sentences of first kind sentence and Second Type sentence, then basis
This kind of sentence determines the first predicate expressions to be combined；Judge half-connection and the matching son that this kind of sentence change into
Whether the type of the half-connection of inquiry is identical；If identical, by first predicate expressions with match the half of the subquery
Filter condition in connection is merged；And if differ, first predicate expressions are negated, and will negate
The first predicate expressions afterwards are merged with the filter condition in the half-connection for matching the subquery.
In the technical scheme, enter with the filter condition in the half-connection for matching subquery by by the first predicate expressions
Row merges, due to predicate expressions in have a half-connection, therefore, two half-connections can be merged into one by the merging more than
Individual half-connection obtains optimal inquiry plan.By above scheme, the quantity of half-connection is on the one hand reduced, so as to reduce
The quantity of the table of connection is participated in, therefore in the access path of the optimal table of query optimization stage Search, search space can be reduced,
And then reduce the time for generating optimal access path, the final time that can reduce generation inquiry plan.On the other hand, can obtain
One inquiry plan for more optimizing, the selection rate of the predicate expressions in half-connection after merging is smaller, and half-connection
Quantity can also reduce, so performing the inquiry plan stage, can not only reduce to the access of physical table and reduce corresponding magnetic
Disk IO, but also because the merging of predicate expressions, it is possible to reduce the calculating of the predicate expressions for repeating, so as to greatly save
The expense of CPU.
In the above-mentioned technical solutions, it is preferable that the half-connection of the match query subquery in SQL syntax parsing tree
Step, including：The SQL syntax parsing tree is traveled through, to determine the half-connection and the subquery in the SQL syntax parsing tree
Whether matching condition is met；If the half-connection and the subquery in the SQL syntax parsing tree meet the matching condition,
Using the half-connection for meeting the matching condition in the SQL syntax parsing tree as the half-connection for matching the subquery；Its
In, the matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and the sentence of the subquery are institutes
State the identification code of half-connection in first kind sentence or the Second Type sentence, the SQL syntax parsing tree and described
The identification code of subquery is identical and the second predicate expressions of main inquiry that associated with the subquery in the cited master
The row of inquiry are identical with the row of the described main inquiry quoted on the condition of contact of the half-connection in the SQL syntax parsing tree.
In the technical scheme, by traveling through SQL syntax parsing tree, with more comprehensive in SQL syntax parsing tree, accurate
Inquire the half-connection that matching condition is met with subquery.
In any of the above-described technical scheme, it is preferable that the match query subquery in SQL syntax parsing tree partly connects
Before the step of connecing, the half-connection merging method also includes：The multiple obtained in the syntactic analysis tree of the subquery is crucial
Field；According to the multiple critical field, the identification code of the subquery is generated.
In the technical scheme, the mark of subquery is generated by the multiple critical fielies in the syntactic analysis tree of subquery
Code, matches so as to rapidly inquire to meet with subquery in SQL syntax parsing tree according to the identification code of the subquery
The half-connection of condition.
In any of the above-described technical scheme, it is preferable that if the sentence and the son that match the half-connection of the subquery are looked into
The sentence of inquiry is the one of which sentence of first kind sentence and Second Type sentence, then determined according to this kind of sentence to be combined
The first predicate expressions the step of, including：If matching the sentence of the half-connection of the subquery and the sentence of the subquery
It is the first kind sentence, then using the filter condition in the first kind sentence as first predicate expressions；
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the Second Type sentence, will be described
Filter condition in Second Type sentence is used as first predicate expressions.
In any of the above-described technical scheme, it is preferable that the half-connection merging method also includes：If in the SQL syntax
The half-connection for matching the subquery is not inquired in parsing tree, is then added to the SQL using the subquery as half-connection
In syntactic analysis tree；And if the sentence of the sentence and the subquery for matching the half-connection of the subquery is not described
One type statements or the Second Type sentence, then be added to the SQL syntax analysis using the subquery as half-connection
In tree.
In the technical scheme, not inquiring the half-connection of matching subquery, or matching subquery half-connection
When the sentence of sentence and subquery is not first kind sentence or Second Type sentence, added subquery as half-connection
To in SQL syntax parsing tree, so as to ensure that reliability when half-connection merges.
In any of the above-described technical scheme, it is preferable that the first kind sentence includes：IN sentences and/or ANY sentences；
The Second Type sentence includes：EXIST sentences.
In the technical scheme, first kind sentence includes：IN sentences and/or ANY sentences, Second Type sentence include：
EXIST sentences, so as to realize the merging of the half-connection to IN sentences, ANY sentences and EXIST sentences.
The second aspect of the present invention proposes a kind of half-connection and merges device, including：Query unit, in SQL syntax
The half-connection of match query subquery in parsing tree；Determining unit, if the sentence of half-connection for matching the subquery and
The sentence of the subquery is the one of which sentence of first kind sentence and Second Type sentence, then true according to this kind of sentence
Fixed first predicate expressions to be combined；Judging unit, for judging half-connection and the matching son that this kind of sentence change into
Whether the type of the half-connection of inquiry is identical；Combining unit, if judging this kind of sentence is changed into half for the judging unit
Connect identical with the type of the half-connection for matching the subquery, then by first predicate expressions with match the subquery
Half-connection in filter condition merge；And the combining unit is additionally operable to, if the judging unit judges this kind of language
The half-connection and the type of the half-connection of the matching subquery that sentence is changed into are differed, then first predicate expressions are entered
Row is negated, and the first predicate expressions of the inverted are closed with the filter condition in the half-connection for matching the subquery
And.
In the technical scheme, enter with the filter condition in the half-connection for matching subquery by by the first predicate expressions
Row merges, due to predicate expressions in have a half-connection, therefore, two half-connections can be merged into one by the merging more than
Individual half-connection obtains optimal inquiry plan.By above scheme, the quantity of half-connection is on the one hand reduced, so as to reduce
The quantity of the table of connection is participated in, therefore in the access path of the optimal table of query optimization stage Search, search space can be reduced,
And then reduce the time for generating optimal access path, the final time that can reduce generation inquiry plan.On the other hand, can obtain
One inquiry plan for more optimizing, the selection rate of the predicate expressions in half-connection after merging is smaller, and half-connection
Quantity can also reduce, so performing the inquiry plan stage, can not only reduce to the access of physical table and reduce corresponding magnetic
Disk IO, but also because the merging of predicate expressions, it is possible to reduce the calculating of the predicate expressions for repeating, so as to greatly save
The expense of CPU.
In the above-mentioned technical solutions, it is preferable that the query unit includes：Determination subelement, for traveling through the SQL languages
Whether method parsing tree, matching condition is met with the half-connection and the subquery that determine in the SQL syntax parsing tree；It is described true
Stator unit is additionally operable to, if the half-connection and the subquery in the SQL syntax parsing tree meet the matching condition, will
The half-connection for meeting the matching condition in the SQL syntax parsing tree is used as the half-connection for matching the subquery；Wherein,
The matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and the sentence of the subquery are described
The identification code and the son of the half-connection in first kind sentence or the Second Type sentence, the SQL syntax parsing tree
The identification code of inquiry is identical and the second predicate expressions of main inquiry that associated with the subquery in the cited master look into
The row of inquiry are identical with the row of the described main inquiry quoted on the condition of contact of the half-connection in the SQL syntax parsing tree.
In the technical scheme, by traveling through SQL syntax parsing tree, with more comprehensive in SQL syntax parsing tree, accurate
Inquire the half-connection that matching condition is met with subquery.
In any of the above-described technical scheme, it is preferable that the half-connection merges device and also includes：The half-connection merges dress
Putting also includes：Acquiring unit, for the multiple critical fielies in the syntactic analysis tree for obtaining the subquery；Generation unit, uses
According to the multiple critical field, the identification code of the subquery is generated.
In the technical scheme, the mark of subquery is generated by the multiple critical fielies in the syntactic analysis tree of subquery
Code, matches so as to rapidly inquire to meet with subquery in SQL syntax parsing tree according to the identification code of the subquery
The half-connection of condition.
In any of the above-described technical scheme, it is preferable that the determining unit is specifically for if match the half of the subquery
The sentence of the sentence of connection and the subquery is the first kind sentence, then by the filtering in the first kind sentence
Condition is used as first predicate expressions；If match the half-connection of the subquery sentence and the subquery sentence it is equal
It is the Second Type sentence, then using the filter condition in the Second Type sentence as first predicate expressions.
In any of the above-described technical scheme, it is preferable that the half-connection merges device and also includes：Unit is added, if for
The query unit does not inquire the half-connection for matching the subquery in the SQL syntax parsing tree, then look into the son
Inquiry is added in the SQL syntax parsing tree as half-connection；And the addition unit is additionally operable to, if matching the subquery
The sentence of half-connection and the sentence of the subquery be not the first kind sentence or the Second Type sentence, then
It is added to the subquery as half-connection in the SQL syntax parsing tree.
In the technical scheme, not inquiring the half-connection of matching subquery, or matching subquery half-connection
When the sentence of sentence and subquery is not first kind sentence or Second Type sentence, added subquery as half-connection
To in SQL syntax parsing tree, so as to ensure that reliability when half-connection merges.
In any of the above-described technical scheme, it is preferable that the first kind sentence includes：IN sentences and/or ANY sentences；
The Second Type sentence includes：EXIST sentences.
In the technical scheme, first kind sentence includes：IN sentences and/or ANY sentences, Second Type sentence include：
EXIST sentences, so as to realize the merging of the half-connection to IN sentences, ANY sentences and EXIST sentences.
By technical scheme, merged by half-connection, search sky is reduced with the query optimization stage
Between and perform the inquiry plan stage reduce access physical table number of times and reduce repeat predicate expressions calculating.
Brief description of the drawings
Fig. 1 shows the schematic flow sheet of half-connection merging method according to an embodiment of the invention；
Fig. 2 shows the schematic flow sheet of half-connection merging method according to another embodiment of the invention；
Fig. 3 shows that half-connection according to an embodiment of the invention merges the structural representation of device.
Specific embodiment
It is below in conjunction with the accompanying drawings and specific real in order to the above objects, features and advantages of the present invention can be more clearly understood that
Mode is applied to be further described in detail the present invention.It should be noted that in the case where not conflicting, the implementation of the application
Feature in example and embodiment can be mutually combined.
Many details are elaborated in the following description in order to fully understand the present invention, but, the present invention may be used also
Implemented with being different from other modes described here using other, therefore, protection scope of the present invention is not by described below
Specific embodiment limitation.
Fig. 1 shows the schematic flow sheet of half-connection merging method according to an embodiment of the invention.
As shown in figure 1, half-connection merging method according to an embodiment of the invention, including：
Step 102, the half-connection of match query subquery in SQL syntax parsing tree.
Preferably, step 102 includes：The SQL syntax parsing tree is traveled through, with determining the SQL syntax parsing tree
Whether half-connection and the subquery meet matching condition；If half-connection and the subquery in the SQL syntax parsing tree
Meet the matching condition, then using the half-connection for meeting the matching condition in the SQL syntax parsing tree as matching institute
State the half-connection of subquery；Wherein, the matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and institute
The sentence for stating subquery is the half-connection in first kind sentence or Second Type sentence, the SQL syntax parsing tree
The identification code of identification code and the subquery is identical and the second predicate expressions of main inquiry that associated with the subquery in
The described main inquiry quoted on the condition of contact of the half-connection in the row of the cited main inquiry and the SQL syntax parsing tree
Row it is identical.
By traveling through SQL syntax parsing tree, with SQL syntax parsing tree more comprehensively, inquire looked into son exactly
Inquiry meets the half-connection of matching condition.
Preferably, the first kind sentence includes：IN sentences and/or ANY sentences；The Second Type sentence includes：
EXIST sentences.The syntactic analysis tree that can change into half-connection may be contained in first kind sentence and Second Type sentence.
Preferably, before step 102, half-connection merging method also includes：Obtain the syntactic analysis tree of the subquery
In multiple critical fielies；According to the multiple critical field, the identification code of the subquery is generated.
For example, the multiple critical fielies in the syntactic analysis tree of subquery are merged into a total field；Using without symbol
Shaping number (for example, 64 shaping number) total field is encoded, using the coding of total field as subquery mark
Code.
Again for example, using signless shaping number (for example, 64 shaping number), respectively to the syntactic analysis tree of subquery
In multiple critical fielies in each critical field encoded, the coding of multiple critical fielies is merged, will merge
The coding for obtaining as subquery identification code.Subquery is generated by the multiple critical fielies in the syntactic analysis tree of subquery
Identification code and store in the syntactic analysis tree of the subquery, so as to can rapidly be existed according to the identification code of the subquery
The half-connection that matching condition is met with subquery is inquired in SQL syntax parsing tree.
Step 104, if the sentence of the sentence and the subquery for matching the half-connection of the subquery is the first kind
The one of which sentence of sentence and Second Type sentence, then determine the first predicate expressions to be combined according to this kind of sentence.
Preferably, step 104 includes：If match the half-connection of the subquery sentence and the subquery sentence it is equal
It is the first kind sentence, then using the filter condition in the first kind sentence as first predicate expressions；If
The sentence of the sentence and the subquery that match the half-connection of the subquery is the Second Type sentence, then by described
Filter condition in two type statements is used as first predicate expressions.
Preferably, half-connection merging method also includes：If not inquiring the matching son in the SQL syntax parsing tree
The half-connection of inquiry, then be added to the subquery as half-connection in the SQL syntax parsing tree；And if matching is described
The sentence of the sentence of the half-connection of subquery and the subquery is not the first kind sentence or the Second Type
Sentence, then be added to the subquery as half-connection in the SQL syntax parsing tree.
The half-connection of matching subquery, or the sentence and the language of subquery for matching the half-connection of subquery are not being inquired
When sentence is not first kind sentence or Second Type sentence, SQL syntax parsing tree is added to using subquery as half-connection
In, so as to ensure that reliability when half-connection merges.
Step 106, judge half-connection that this kind of sentence change into and the matching subquery half-connection type whether
It is identical, if it is determined that the half-connection that this kind of sentence is changed into is identical with the type of the half-connection for matching the subquery, then perform step
Rapid 108, if it is determined that the type of this kind of sentence half-connection for changing into and the half-connection for matching the subquery is differed, then perform
Step 110.
Step 108, first predicate expressions are carried out with the filter condition in the half-connection for matching the subquery
Merge.
First predicate expressions are negated by step 110, and by the first predicate expressions of the inverted with match
Filter condition in the half-connection of the subquery is merged.
Wherein, step 110 includes：By being that the first predicate expressions generate new the first predicate expressions, this new the
Operator in one predicate expressions is NOT, and the first new predicate expressions are the first predicate expressions of the inverted.
In the technical scheme, enter with the filter condition in the half-connection for matching subquery by by the first predicate expressions
Row merges, due to predicate expressions in have a half-connection, therefore, two half-connections can be merged into one by the merging more than
Individual half-connection obtains optimal inquiry plan.By above scheme, the quantity of half-connection is on the one hand reduced, so as to reduce
The quantity of the table of connection is participated in, therefore in the access path of the optimal table of query optimization stage Search, search space can be reduced,
And then reduce the time for generating optimal access path, the final time that can reduce generation inquiry plan.On the other hand, can obtain
One inquiry plan for more optimizing, the selection rate of the predicate expressions in half-connection after merging is smaller, and half-connection
Quantity can also reduce, so performing the inquiry plan stage, can not only reduce to the access of physical table and reduce corresponding magnetic
Disk IO, but also because the merging of predicate expressions, it is possible to reduce the calculating of the predicate expressions for repeating, so as to greatly save
The expense of CPU.
Fig. 2 shows the schematic flow sheet of half-connection merging method according to another embodiment of the invention.
As shown in Fig. 2 half-connection merging method according to another embodiment of the invention, including：
Step 202, travel through SQL syntax parsing tree filter condition, found out from the filter condition first kind sentence and
Second Type sentence.Wherein, first kind sentence includes：IN sentences and/or ANY sentences；Second Type sentence includes：EXIST
Sentence.
Step 204, is subquery generation identification code, and the identification code storage of subquery is arrived the syntactic analysis tree of subquery
In.
Step 206, travels through SQL syntax parsing tree, judges whether to inquire the half-connection of matching subquery, if it is determined that inquiry
Go out to match the half-connection of subquery, then perform step 208, if it is determined that not inquiring the half-connection of matching subquery, then perform step
Rapid 210.
Wherein, the half-connection and subquery of matching subquery meet following matching condition：In SQL syntax parsing tree half
The sentence of connection and the sentence of subquery are half in first kind sentence or Second Type sentence, SQL syntax parsing tree
The identification code of connection and the identification code of subquery is identical and the second predicate expressions of main inquiry that associated with subquery in institute
The row for quoting main inquiry are identical with the row of the main inquiry quoted on the condition of contact of the half-connection in SQL syntax parsing tree.
Step 208, whether the sentence of half-connection and the sentence of subquery for judging to match subquery are first kind languages
Sentence or Second Type sentence, if it is determined that the sentence of half-connection and the sentence of subquery of matching subquery are first kind languages
Sentence or Second Type sentence, then perform step 212, if it is determined that the sentence and the sentence of subquery of the half-connection of matching subquery
And inequality is first kind sentence or Second Type sentence, then perform step 210.
Step 210, is added to subquery as half-connection in SQL syntax parsing tree.
Step 212, whether the sentence of half-connection and the sentence of subquery for judging to match subquery are Second Type languages
Sentence, if it is determined that the sentence of half-connection and the sentence of subquery of matching subquery are Second Type sentences, then performs step
214, if it is determined that the sentence of half-connection and the sentence of subquery of matching subquery are not Second Type sentence, illustrate matching
The sentence of the half-connection of inquiry and the sentence of subquery are first kind sentences, then perform step 218.
Step 214, using the filter condition in Second Type sentence as the first predicate expressions.
Step 216, judge half-connection that Second Type sentence changes into and matching subquery half-connection type whether
It is identical, if it is determined that the half-connection that Second Type sentence is changed into is identical with the type of the half-connection of matching subquery, then perform step
Rapid 224, if it is determined that the type of the Second Type sentence half-connection for changing into and the half-connection for matching subquery is differed, then perform
Step 222.
Step 218, using the filter condition in first kind sentence as the first predicate expressions.
Step 220, judge half-connection that first kind sentence changes into and matching subquery half-connection type whether
It is identical, if it is determined that the half-connection that first kind sentence is changed into is identical with the type of the half-connection of matching subquery, then perform step
Rapid 224, if it is determined that the type of the half-connection of the half-connection that changes into of first kind sentence and matching subquery is differed, illustrate the
The half-connection that one type statements are changed into is anti-half-connection, then perform step 222.
First predicate expressions are negated by step 222, and the operator of the first predicate expressions of the inverted is NOT.
Step 224, the first predicate expressions are merged with the filter condition in the half-connection of matching subquery.Wherein, if
The first predicate expressions are not negated, then directly uses the first predicate expressions and the filtering rod in the half-connection for matching subquery
Part merges；If being negated to the first predicate expressions, using the first predicate expressions of the inverted with match partly connecting for subquery
Filter condition in connecing merges.
In addition, it is new that the first predicate expressions are merged for obtaining with the filter condition in the half-connection of matching subquery
Predicate expressions, the operator of the predicate expressions is AND.
Fig. 3 shows that half-connection according to an embodiment of the invention merges the structural representation of device.
As shown in figure 3, half-connection according to an embodiment of the invention merges device 300, including：Query unit 302,
Determining unit 304, judging unit 306 and combining unit 308.
Query unit 302, for the half-connection of the match query subquery in SQL syntax parsing tree.
Preferably, the query unit 302 includes：Determination subelement 3022, for traveling through the SQL syntax parsing tree,
Whether matching condition is met with the half-connection and the subquery that determine in the SQL syntax parsing tree；The determination subelement
3022 are additionally operable to, if the half-connection and the subquery in the SQL syntax parsing tree meet the matching condition, will be described
The half-connection for meeting the matching condition in SQL syntax parsing tree is used as the half-connection for matching the subquery；Wherein, it is described
Matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and the sentence of the subquery are the first kind
The identification code and the identification code of the subquery of the half-connection in sentence or Second Type sentence, the SQL syntax parsing tree
Row of the cited main inquiry and described in second predicate expressions of main inquiry that is identical and being associated with the subquery
The row of the described main inquiry quoted on the condition of contact of the half-connection in SQL syntax parsing tree are identical.
By traveling through SQL syntax parsing tree, with SQL syntax parsing tree more comprehensively, inquire looked into son exactly
Inquiry meets the half-connection of matching condition.
Preferably, the first kind sentence includes：IN sentences and/or ANY sentences；The Second Type sentence includes：
EXIST sentences.
Determining unit 304, if the sentence of half-connection and the sentence of the subquery for matching the subquery are
The one of which sentence of first kind sentence and Second Type sentence, then determine the first predicate table to be combined according to this kind of sentence
Up to formula.
Preferably, the determining unit 304 is specifically for if match the sentence and the son of the half-connection of the subquery
The sentence of inquiry is the first kind sentence, then using the filter condition in the first kind sentence as the described first meaning
Word expression formula；If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the Second Type language
Sentence, then using the filter condition in the Second Type sentence as first predicate expressions.
Judging unit 306, for judging half-connection and the half-connection of the matching subquery that this kind of sentence is changed into
Whether type is identical.
Combining unit 308, if judging that the half-connection that this kind of sentence is changed into is described with matching for the judging unit 306
The type of the half-connection of subquery is identical, then by first predicate expressions and the mistake in the half-connection for matching the subquery
Filter condition is merged；And the combining unit 308 is additionally operable to, if the judging unit 306 judges that this kind of sentence is changed into
Half-connection and the type of half-connection of the matching subquery differ, then first predicate expressions are negated,
And the first predicate expressions of the inverted are merged with the filter condition in the half-connection for matching the subquery.
In the technical scheme, enter with the filter condition in the half-connection for matching subquery by by the first predicate expressions
Row merges, due to predicate expressions in have a half-connection, therefore, two half-connections can be merged into one by the merging more than
Individual half-connection obtains optimal inquiry plan.By above scheme, the quantity of half-connection is on the one hand reduced, so as to reduce
The quantity of the table of connection is participated in, therefore in the access path of the optimal table of query optimization stage Search, search space can be reduced,
And then reduce the time for generating optimal access path, the final time that can reduce generation inquiry plan.On the other hand, can obtain
One inquiry plan for more optimizing, the selection rate of the predicate expressions in half-connection after merging is smaller, and half-connection
Quantity can also reduce, so performing the inquiry plan stage, can not only reduce to the access of physical table and reduce corresponding magnetic
Disk IO, but also because the merging of predicate expressions, it is possible to reduce the calculating of the predicate expressions for repeating, so as to greatly save
The expense of CPU.
In the above-mentioned technical solutions, it is preferable that half-connection merges device 300 and also includes：Acquiring unit 310, for obtaining
Multiple critical fielies in the syntactic analysis tree of the subquery；Generation unit 312, for according to the multiple critical field,
Generate the identification code of the subquery.
For example, the multiple critical fielies in the syntactic analysis tree of subquery can be merged into one always by generation unit 312
Field；Total field is encoded using signless shaping number (for example, 64 shaping number), the coding of total field is made
It is the identification code of subquery.
It is right respectively again for example, generation unit 312 can also use signless shaping number (for example, 64 shaping number)
Each critical field in multiple critical fielies in the syntactic analysis tree of subquery is encoded, then by multiple critical fielies
Coding is merged, identification code of the coding that merging is obtained as subquery.
The identification code of subquery is generated by the multiple critical fielies in the syntactic analysis tree of subquery and store in the son
In the syntactic analysis tree of inquiry, so as to rapidly be inquired in SQL syntax parsing tree according to the identification code of the subquery
With the half-connection that subquery meets matching condition.
In any of the above-described technical scheme, it is preferable that half-connection merges device 300 and also includes：Unit 314 is added, is used for
If the query unit 302 does not inquire the half-connection for matching the subquery in the SQL syntax parsing tree, will be described
Subquery is added in the SQL syntax parsing tree as half-connection；And the addition unit 314 is additionally operable to, if matching institute
The sentence of the sentence and the subquery of stating the half-connection of subquery is not the first kind sentence or the Equations of The Second Kind
Type sentence, then be added to the subquery as half-connection in the SQL syntax parsing tree.
The half-connection of matching subquery, or the sentence and the language of subquery for matching the half-connection of subquery are not being inquired
When sentence is not first kind sentence or Second Type sentence, SQL syntax parsing tree is added to using subquery as half-connection
In, so as to ensure that reliability when half-connection merges.
Technical scheme is described in detail above in association with accompanying drawing, by technical scheme, by half-and-half
Connection is merged, and physical table is accessed to reduce search space and be reduced in the execution inquiry plan stage in the query optimization stage
The calculating of the predicate expressions that number of times and reduction are repeated.
In the present invention, term " first ", " second " are only used for the purpose for describing, and it is not intended that indicating or implying phase
To importance；Term " multiple " represents two or more.For the ordinary skill in the art, can be according to tool
Body situation understands above-mentioned term concrete meaning in the present invention.
The preferred embodiments of the present invention are the foregoing is only, is not intended to limit the invention, for the skill of this area
For art personnel, the present invention can have various modifications and variations.It is all within the spirit and principles in the present invention, made any repair
Change, equivalent, improvement etc., should be included within the scope of the present invention.
Claims (12)
1. a kind of half-connection merging method, it is characterised in that including：
The half-connection of match query subquery in SQL syntax parsing tree；
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is first kind sentence and Equations of The Second Kind
The one of which sentence of type sentence, then determine the first predicate expressions to be combined according to this kind of sentence；
Judge whether the half-connection that this kind of sentence is changed into is identical with the type of the half-connection of the matching subquery；
If identical, first predicate expressions are closed with the filter condition in the half-connection for matching the subquery
And；And
If differing, first predicate expressions are negated, and by the first predicate expressions of the inverted with match
Filter condition in the half-connection of the subquery is merged.
2. half-connection merging method according to claim 1, it is characterised in that described to be inquired about in SQL syntax parsing tree
The step of matching the half-connection of subquery, including：
The SQL syntax parsing tree is traveled through, to determine whether half-connection and the subquery in the SQL syntax parsing tree are full
Sufficient matching condition；
If the half-connection and the subquery in the SQL syntax parsing tree meet the matching condition, by the SQL syntax
The half-connection for meeting the matching condition in parsing tree is used as the half-connection for matching the subquery；
Wherein, the matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and the sentence of the subquery
It is the identification code of the half-connection in the first kind sentence or the Second Type sentence, the SQL syntax parsing tree
It is cited in second predicate expressions of main inquiry identical with the identification code of the subquery and being associated with the subquery
The row phase of the described main inquiry quoted on the condition of contact of the half-connection in the row of the main inquiry and the SQL syntax parsing tree
Together.
3. half-connection merging method according to claim 2, it is characterised in that described to be inquired about in SQL syntax parsing tree
Before the step of matching the half-connection of subquery, the half-connection merging method also includes：
Obtain the multiple critical fielies in the syntactic analysis tree of the subquery；
According to the multiple critical field, the identification code of the subquery is generated.
4. half-connection merging method according to any one of claim 1 to 3, it is characterised in that if match the son looking into
The sentence of the sentence of the half-connection of inquiry and the subquery is the one of which language of first kind sentence and Second Type sentence
Sentence, then the step of determine the first predicate expressions to be combined according to this kind of sentence, including：
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the first kind sentence, will
Filter condition in the first kind sentence is used as first predicate expressions；
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the Second Type sentence, will
Filter condition in the Second Type sentence is used as first predicate expressions.
5. half-connection merging method according to any one of claim 1 to 3, it is characterised in that the half-connection merges
Method also includes：
If not inquiring the half-connection for matching the subquery in the SQL syntax parsing tree, using the subquery as
Half-connection is added in the SQL syntax parsing tree；And
If the sentence of the sentence and the subquery for matching the half-connection of the subquery be not the first kind sentence or
Second Type sentence described in person, then be added to the subquery as half-connection in the SQL syntax parsing tree.
6. half-connection merging method according to any one of claim 1 to 3, it is characterised in that
The first kind sentence includes：IN sentences and/or ANY sentences；
The Second Type sentence includes：EXIST sentences.
7. a kind of half-connection merges device, it is characterised in that including：
Query unit, for the half-connection of the match query subquery in SQL syntax parsing tree；
Determining unit, if the sentence of half-connection and the sentence of the subquery for matching the subquery are the first kind
The one of which sentence of sentence and Second Type sentence, then determine the first predicate expressions to be combined according to this kind of sentence；
Judging unit, for judge half-connection that this kind of sentence change into and the matching subquery half-connection type whether
It is identical；
Combining unit, if judging the half-connection and the half of the matching subquery that this kind of sentence is changed into for the judging unit
The type of connection is identical, then first predicate expressions are carried out with the filter condition in the half-connection for matching the subquery
Merge；And
The combining unit is additionally operable to, if the judging unit judges that the half-connection that this kind of sentence is changed into is looked into the matching son
The type of the half-connection of inquiry is differed, then first predicate expressions are negated, and by the first predicate table of the inverted
Merged with the filter condition in the half-connection for matching the subquery up to formula.
8. half-connection according to claim 7 merges device, it is characterised in that the query unit includes：
Determination subelement, for traveling through the SQL syntax parsing tree, with determine the half-connection in the SQL syntax parsing tree and
Whether the subquery meets matching condition；
The determination subelement is additionally operable to, if the half-connection and the subquery in the SQL syntax parsing tree meet described
With condition, then using the half-connection for meeting the matching condition in the SQL syntax parsing tree as the matching subquery
Half-connection；
Wherein, the matching condition includes：The sentence of the half-connection in the SQL syntax parsing tree and the sentence of the subquery
It is the identification code of the half-connection in the first kind sentence or the Second Type sentence, the SQL syntax parsing tree
It is cited in second predicate expressions of main inquiry identical with the identification code of the subquery and being associated with the subquery
The row phase of the described main inquiry quoted on the condition of contact of the half-connection in the row of the main inquiry and the SQL syntax parsing tree
Together.
9. half-connection according to claim 8 merges device, it is characterised in that the half-connection merges device and also includes：
Acquiring unit, for the multiple critical fielies in the syntactic analysis tree for obtaining the subquery；
Generation unit, for according to the multiple critical field, generating the identification code of the subquery.
10. the half-connection according to any one of claim 7 to 9 merges device, it is characterised in that the determining unit tool
Body is used for,
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the first kind sentence, will
Filter condition in the first kind sentence is used as first predicate expressions；
If the sentence of the sentence and the subquery for matching the half-connection of the subquery is the Second Type sentence, will
Filter condition in the Second Type sentence is used as first predicate expressions.
11. half-connection according to any one of claim 7 to 9 merges device, it is characterised in that the half-connection merges
Device also includes：
Unit is added, if not inquiring the matching subquery in the SQL syntax parsing tree for the query unit
Half-connection, then be added to the subquery as half-connection in the SQL syntax parsing tree；And
The addition unit is additionally operable to, if the sentence of the sentence and the subquery for matching the half-connection of the subquery is not
The first kind sentence or the Second Type sentence, then be added to the SQL languages using the subquery as half-connection
In method parsing tree.
12. half-connection according to any one of claim 7 to 9 merges device, it is characterised in that
The first kind sentence includes：IN sentences and/or ANY sentences；
The Second Type sentence includes：EXIST sentences.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611131301.3A CN106777054B (en) | 2016-12-09 | 2016-12-09 | Semi-connection merging method and semi-connection merging device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611131301.3A CN106777054B (en) | 2016-12-09 | 2016-12-09 | Semi-connection merging method and semi-connection merging device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106777054A true CN106777054A (en) | 2017-05-31 |
| CN106777054B CN106777054B (en) | 2020-03-27 |
Family
ID=58879712
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201611131301.3A Active CN106777054B (en) | 2016-12-09 | 2016-12-09 | Semi-connection merging method and semi-connection merging device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106777054B (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107632999A (en) * | 2017-07-24 | 2018-01-26 | 杭州沃趣科技股份有限公司 | A kind of method that multiple associated predicates are merged |
| CN112732752A (en) * | 2021-01-29 | 2021-04-30 | 上海达梦数据库有限公司 | Query statement optimization method, device, equipment and storage medium |
| CN112765286A (en) * | 2021-02-01 | 2021-05-07 | 广州海量数据库技术有限公司 | Query method and device based on relational database |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070061288A1 (en) * | 2005-09-09 | 2007-03-15 | You-Chin Fuh | Dynamic semi-join processing with runtime optimization |
| CN101739398A (en) * | 2008-11-11 | 2010-06-16 | 山东省标准化研究院 | Distributed database multi-join query optimization algorithm |
| CN102110158A (en) * | 2011-02-24 | 2011-06-29 | 上海大学 | Multi-join query optimization method for database based on improved SDD-1 (System for Distributed Database) algorithm |
| CN103164495A (en) * | 2011-12-19 | 2013-06-19 | 中国人民解放军63928部队 | Half-connection inquiry optimizing method based on periphery searching and system thereof |
-
2016
- 2016-12-09 CN CN201611131301.3A patent/CN106777054B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20070061288A1 (en) * | 2005-09-09 | 2007-03-15 | You-Chin Fuh | Dynamic semi-join processing with runtime optimization |
| CN101739398A (en) * | 2008-11-11 | 2010-06-16 | 山东省标准化研究院 | Distributed database multi-join query optimization algorithm |
| CN102110158A (en) * | 2011-02-24 | 2011-06-29 | 上海大学 | Multi-join query optimization method for database based on improved SDD-1 (System for Distributed Database) algorithm |
| CN103164495A (en) * | 2011-12-19 | 2013-06-19 | 中国人民解放军63928部队 | Half-connection inquiry optimizing method based on periphery searching and system thereof |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107632999A (en) * | 2017-07-24 | 2018-01-26 | 杭州沃趣科技股份有限公司 | A kind of method that multiple associated predicates are merged |
| CN112732752A (en) * | 2021-01-29 | 2021-04-30 | 上海达梦数据库有限公司 | Query statement optimization method, device, equipment and storage medium |
| CN112765286A (en) * | 2021-02-01 | 2021-05-07 | 广州海量数据库技术有限公司 | Query method and device based on relational database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106777054B (en) | 2020-03-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110413732B (en) | Knowledge searching method for software defect knowledge | |
| CN107644073A (en) | A kind of field consanguinity analysis method, system and device based on depth-first traversal | |
| CN105701253B (en) | The knowledge base automatic question-answering method of Chinese natural language question semanteme | |
| Yang et al. | Joint relational embeddings for knowledge-based question answering | |
| US9824083B2 (en) | System for natural language understanding | |
| CN111813802A (en) | Method for generating structured query statement based on natural language | |
| CN108182972B (en) | Intelligent coding method and system for Chinese disease diagnosis based on word segmentation network | |
| CN108052547A (en) | Natural language question-answering method and system based on question sentence and knowledge graph structural analysis | |
| CN102609402B (en) | Device and method for generation and management of ontology model based on real-time strategy | |
| CN109614432B (en) | System and method for acquiring data blood relationship based on syntactic analysis | |
| CN108182207B (en) | Intelligent coding method and system for Chinese surgical operation based on word segmentation network | |
| CN109408811B (en) | Data processing method and server | |
| CN104252533A (en) | Search method and search device | |
| KR101731579B1 (en) | Database capable of intergrated query processing and data processing method thereof | |
| CN106610999A (en) | Query processing method and device | |
| CN106777054A (en) | Half-connection merging method and half-connection merge device | |
| CN104573039A (en) | Keyword search method of relational database | |
| CN110674229A (en) | AST-based relational database SQL table relational analysis and display method | |
| CN110222124A (en) | Multidimensional data processing method and system based on OLAP | |
| CN109558166A (en) | A kind of code search method of facing defects positioning | |
| CN111914534A (en) | Semantic mapping method and system for constructing knowledge graph | |
| CN115357678A (en) | GIS automatic examination method and system based on structured natural language rule | |
| CN117056867A (en) | Multi-source heterogeneous data fusion method and system for digital twin | |
| CN114330267A (en) | Structural report template design method based on semantic association | |
| CN105095060A (en) | Device and method for generating test case by using rule set network |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20220418 Address after: Room 403, 4th floor, building 23, East District, yard 10, Xibeiwang East Road, Haidian District, Beijing 100089 Patentee after: BEIJING VSETTAN DATA TECHNOLOGY CO.,LTD. Address before: 100192 South Zone 1, floor 11, block a, No. 8 Xueqing Road (Science and technology wealth center), Haidian District, Beijing Patentee before: BEIJING VSETTAN DATA TECHNOLOGY CO.,LTD. Patentee before: Huasheng Xintai Information Industry Development Co., Ltd |