CN104765731B

CN104765731B - Database inquiry optimization method and apparatus

Info

Publication number: CN104765731B
Application number: CN201410001226.3A
Authority: CN
Inventors: 李硕; 杨新颖; 周祥
Original assignee: International Business Machines Corp
Current assignee: International Business Machines Corp
Priority date: 2014-01-02
Filing date: 2014-01-02
Publication date: 2018-05-18
Anticipated expiration: 2034-01-02
Also published as: CN104765731A

Abstract

The invention discloses a kind of database inquiry optimization methods and apparatus.This method includes：Receive the first querying command, first querying command includes at least the first and second query statements, second query statement obtains the second query result from the second form, first query statement obtains the first query result by second query result and the first filter condition from the first form, which is directed toward the same foundation form in database；The definition statement for generating the 3rd form based on second query statement, for storing the 3rd query result, the 3rd query result includes at least one in the data value on first query result and record identification row the 3rd form；And replace second query statement using this definition sentence, and first query statement is rewritten based on the 3rd form, so that first querying command is converted to the second querying command.Therefore, the present invention can be calculated as realizing data query operation originally by lower.

Description

Database inquiry optimization method and apparatus

Technical field

The present invention relates to Database Systems, and more particularly, to a kind of database inquiry optimization method and apparatus.

Background technology

In recent years, with information technology and the high speed development in market, data management gradually shows total amount of data and constantly increases Add, the trend that data format and data type are more complicated.In order to enable user can in the data of magnanimity quick obtaining he Required data, database technology come into being.

In order to obtain user's required information from database, user needs to complete using querying command a series of Inquiry operation.Typically, a querying command generally includes multiple queries sentence, for specifying one suitable for database Or the querying condition of multiple data row, so that the record for meeting querying condition ought be searched in the database（Or it is referred to as, data Row）When, the data values of one or more data row among the record needed for user is returned to, as query result.

For many years, business database manufacturer has been devoted to improve the query performance of database.For this purpose, Have developed database query optimizer（optimizer）.The query optimizer is a composition portion of data base management system Point, for assessing querying command input by user, and according to the various different because usually finding most preferably of influence query performance Access path, for performing inquiry operation.

However, being limited to the prior art, query optimizer for some querying commands when performing optimization operation, performance It is unsatisfactory.Particularly, two query statements are included at least in these querying commands, the two query statements refer to same table Lattice, and the query result of a query statement is existing to look into based on the query result of another query statement come when obtaining Can not effectively it be optimized by asking optimizer.

The content of the invention

In view of problem above, it is proposed that the present invention.It is an object of the present invention to provide a kind of database inquiry optimization sides Method and equipment, it is contemplated that the characteristic of the querying command from connection across query block, saves and pass through in the prior art Data value searches index and searches all intermediate steps of RID according to index so that reduces needed for entire inquiry operation The system resource wanted.

According to an aspect of the invention, there is provided a kind of database inquiry optimization method, including：Receive the first inquiry life Order, first query statement are used to obtain the first query result from the first form, and second query statement is used for from second Form obtain the second query result, first query statement be used for by second query result and the first filter condition come The first query result is obtained from the first form, each in first form and second form is directed toward in database Same foundation form；The definition statement of 3rd form is generated based on second query statement, the 3rd form is used to deposit The 3rd query result is stored up, the 3rd query result is included in the data value on first query result and record identification row It is at least one；And in first querying command, second query statement is replaced using the definition statement, and First query statement is rewritten based on the 3rd form, so as to which first querying command is converted to the second inquiry life Order.

According to another aspect of the present invention, a kind of database inquiry optimization equipment is provided, including：Order receiving unit, For receiving the first querying command, first querying command includes at least the first query statement and the second query statement, described Second query statement is used to obtain the second query result from the second form, and first query statement is used to look by described second Result and the first filter condition are ask to obtain the first query result from the first form, in first form and second form Each be directed toward database in same foundation form；Form definition unit, for being based on the second query statement next life Into the definition statement of the 3rd form, for the 3rd form for storing the 3rd query result, the 3rd query result includes institute It states at least one in the data value on the first query result and record identification row；And order converting unit, for described In first querying command, replace second query statement using the definition statement, and based on the 3rd form come First query statement is rewritten, so as to which first querying command is converted to the second querying command.

Using database inquiry optimization method and apparatus according to the present invention, connecting certainly for query block can be crossed over to having Querying command carry out query semantics conversion, be merged into using subquery block as a temporary table in father's query block so that It compared with prior art, can be by the lower inquiry operation being calculated as in realization database originally, so as to improve inquiry The execution efficiency of order saves the run time of querying command, improves the user experience in data base querying.

Description of the drawings

Disclosure illustrative embodiments are described in more detail in conjunction with the accompanying drawings, the disclosure above-mentioned and its Its purpose, feature and advantage will be apparent, wherein, in disclosure illustrative embodiments, identical reference number Typically represent same parts.

Fig. 1 shows to be used for the block diagram for the exemplary computer system/server 12 for realizing embodiment of the present invention.

Fig. 2 shows the organisational chart of Database Systems according to embodiments of the present invention.

Fig. 3 A show the form of the first querying command according to first specific example.

Fig. 3 B show the form of the first querying command according to second specific example.

Fig. 3 C show the form of the first querying command according to third specific example.

Fig. 3 D show the form of the first querying command according to fourth specific example.

Fig. 3 E show the form of the first querying command according to fifth specific example.

Fig. 3 F show the form of the first querying command according to the 6th specific example.

Fig. 3 G show the form of the first querying command according to the 7th specific example.

Fig. 4 shows the exemplary contents of the basic form in Fig. 3 A.

Fig. 5 shows the access path generated according to the prior art.

Fig. 6 shows the flow chart of database inquiry optimization method according to embodiments of the present invention.

Fig. 7 A show the format sample of the second querying command according to a first embodiment of the present invention.

Fig. 7 B show the format sample of the second querying command according to a second embodiment of the present invention.

Fig. 7 C show the format sample of the second querying command according to a third embodiment of the present invention.

Fig. 7 D show the format sample of the second querying command according to a fourth embodiment of the present invention.

Fig. 8 A show the access path generated according to a first embodiment of the present invention.

Fig. 8 B show the access path generated according to a third embodiment of the present invention.

Fig. 9 A show the performance parameter of the access path that execution is generated according to the prior art in running environment.

Fig. 9 B show the performance parameter that the access path generated according to first embodiment is performed in running environment.

Figure 10 shows the block diagram of database inquiry optimization equipment according to embodiments of the present invention.

Specific embodiment

Some preferred embodiments of the disclosure are shown in the accompanying drawings, these are more fully described below with reference to accompanying drawings Preferred embodiment.However, it is possible to realize the disclosure in a variety of manners, should not be limited by embodiments set forth herein. On the contrary, these embodiments are provided so that the disclosure is more thorough and complete, and can be complete by the scope of the present disclosure Ground is communicated to those skilled in the art.

Those skilled in the art will appreciate that various aspects of the invention can be implemented as system, method or calculating Machine program product.Therefore, various aspects of the invention can be implemented as following form, i.e.,：Complete hardware embodiment, Complete Software Implementation（Including firmware, resident software, microcode etc.）Or the embodiment party combined in terms of hardware and software Formula may be collectively referred to as " circuit ", " module " or " system " here.In addition, in some embodiments, various aspects of the invention are also The form of the computer program product in one or more computer-readable mediums is can be implemented as, the computer-readable medium In include computer-readable program code.

Any combination of one or more computer-readable mediums may be employed.Computer-readable medium can be computer Readable signal medium or computer readable storage medium.Computer readable storage medium for example can be --- but it is unlimited In --- electricity, magnetic, optical, electromagnetic, infrared ray or semiconductor system, device or device or it is arbitrary more than combination.It calculates The more specific example of machine readable storage medium storing program for executing（Non exhaustive list）Including：Electrical connection with one or more conducting wires, just Take formula computer disks, hard disk, random access memory（RAM）, read-only memory (ROM), erasable programmable read only memory (EPROM or flash memory), optical fiber, portable compact disc read only memory (CD-ROM), light storage device, magnetic memory device or Above-mentioned any appropriate combination.In this document, computer readable storage medium can any include or store program Tangible medium, the program can be commanded the either device use or in connection of execution system, device.

Computer-readable signal media can include in a base band or as carrier wave a part propagation data-signal, Wherein carry computer-readable program code.Diversified forms may be employed in the data-signal of this propagation, including --- but It is not limited to --- electromagnetic signal, optical signal or above-mentioned any appropriate combination.Computer-readable signal media can also be Any computer-readable medium beyond computer readable storage medium, which can send, propagate or Transmission for by instruction execution system, device either device use or program in connection.

The program code included on computer-readable medium can be transmitted with any appropriate medium, including --- but it is unlimited In --- wireless, wired, optical cable, RF etc. or above-mentioned any appropriate combination.

It can write to perform the calculating that operates of the present invention with any combination of one or more programming languages Machine program code, described program design language include object oriented program language-such as Java, Smalltalk, C++ Deng further including conventional procedural programming language-such as " C " language or similar programming language.Program code can Fully to perform on the user computer, partly perform, performed as an independent software package on the user computer, Part performs or performs on a remote computer or server completely on the remote computer on the user computer for part. In the situation for being related to remote computer, remote computer can pass through the network of any kind --- including LAN (LAN) Or wide area network (WAN)-be connected to subscriber computer or, it may be connected to outer computer（Such as utilize Internet service Provider passes through Internet connection）.

Below with reference to according to the method for the embodiment of the present invention, device（System）With the flow chart of computer program product And/or the block diagram description present invention.It is it should be appreciated that each in each box and flow chart and/or block diagram of flow chart and/or block diagram The combination of box can be realized by computer program instructions.These computer program instructions can be supplied to all-purpose computer, The processor of special purpose computer or other programmable data processing units, so as to produce a kind of machine so that these computers Program instruction generates realization flow chart when being performed by computer or the processor of other programmable data processing units And/or the device of function/action specified in one or more of block diagram box.

These computer program instructions can also be stored in computer-readable medium, these instruct so that computer, Other programmable data processing units or other equipment work in a specific way, so as to be stored in computer-readable medium Instruction just produces the instruction including realizing function/action specified in one or more of flow chart and/or block diagram box Manufacture（article of manufacture）.

Computer program instructions can also be loaded into computer, other programmable data processing units or miscellaneous equipment On so that series of operation steps is performed on computer, other programmable data processing units or miscellaneous equipment, in terms of generating The process that calculation machine is realized so that the instruction that is performed on computer or other programmable devices provide realize flow chart and/ Or the process of function/action specified in one or more of block diagram box.

Fig. 1 shows to be used for the block diagram for the exemplary computer system/server 12 for realizing embodiment of the present invention. The computer system/server 12 that Fig. 1 is shown is only an example, should not be to the function and use scope of the embodiment of the present invention Bring any restrictions.

As shown in Figure 1, computer system/server 12 is showed in the form of universal computing device.Computer system/service The component of device 12 can include but is not limited to：One or more processor or processing unit 16, system storage 28, connection Different system component（Including system storage 28 and processing unit 16）Bus 18.

Bus 18 represents the one or more in a few class bus structures, including memory bus or Memory Controller, Peripheral bus, graphics acceleration port, processor or the local bus using the arbitrary bus structures in a variety of bus structures.It lifts For example, these architectures include but not limited to industry standard architecture（ISA）Bus, microchannel architecture（MAC） Bus, enhanced isa bus, Video Electronics Standards Association（VESA）Local bus and peripheral component interconnection（PCI）Bus.

Computer system/server 12 typically comprises various computing systems readable medium.These media can be appointed What usable medium that can be accessed by computer system/server 12, including volatile and non-volatile medium, it is moveable and Immovable medium.

System storage 28 can include the computer system readable media of form of volatile memory, such as arbitrary access Memory（RAM）30 and/or cache memory 32.Computer system/server 12 may further include other removable Dynamic/immovable, volatile/non-volatile computer system storage medium.Only as an example, storage system 34 can be used for Read and write immovable, non-volatile magnetic media（Fig. 1 is not shown, is commonly referred to as " hard disk drive "）.Although do not show in Fig. 1 Go out, can provide for moving non-volatile magnetic disk（Such as " floppy disk "）The disc driver of read-write and to removable Anonvolatile optical disk（Such as CD-ROM, DVD-ROM or other optical mediums）The CD drive of read-write.In these cases, Each driver can be connected by one or more data media interfaces with bus 18.Memory 28 can include at least one A program product, the program product have one group（For example, at least one）Program module, these program modules are configured to perform The function of various embodiments of the present invention.

With one group（It is at least one）Program/utility 40 of program module 42 can be stored in such as memory 28 In, such program module 42 includes --- but being not limited to --- operating system, one or more application program, other programs Module and program data may include the realization of network environment in each or certain combination in these examples.Program mould Block 42 usually performs function and/or method in embodiment described in the invention.

Computer system/server 12 can also be with one or more external equipments 14（It is such as keyboard, sensing equipment, aobvious Show device 24 etc.）Communication, can also enable a user to lead to the equipment that the computer system/server 12 interacts with one or more It letter and/or any is set with so that the computer system/server 12 communicates with one or more of the other computing device It is standby（Such as network interface card, modem etc.）Communication.This communication can pass through input/output（I/O）Interface 22 carries out.And And computer system/server 12 can also pass through network adapter 20 and one or more network（Such as LAN （LAN）, wide area network（WAN）And/or public network, such as internet）Communication.As shown in the figure, network adapter 20 passes through bus 18 communicate with other modules of computer system/server 12.It should be understood that although not shown in the drawings, computer can be combined Systems/servers 12 use other hardware and/or software module, include but not limited to：Microcode, device driver, at redundancy Manage unit, external disk drive array, RAID system, tape drive and data backup storage system etc..

In the following, Database Systems according to embodiments of the present invention will be described with reference to Figure 2.

Database Systems are to adapt to a kind of ideal data processing that the needs of data processing grow up Core institution.The high speed processing ability and mass storage of computer system/server 12 shown in FIG. 1 provide realization number According to the condition of management automation.

As shown in Fig. 2, the Database Systems are usually made of database, data administrator, hardware and software.Data depositary management Reason system（DBMS）It is the kernel software of Database Systems.DBMS provides Data Definition Language（DDL）With data manipulation language （DML）.DDL allows user to define the Advance of Three Classes Mode Structure of database, two-stage image and integrity constraint and secrecy limitation etc. Constraint；And DML allows user to realize to operations such as the addition of data, deletion, update, inquiries.

In organizational structure, which can include resolver, optimizer and running environment（runtime）.Resolver is again For receiving one or more querying commands input by user, syntactic analysis is carried out to the querying command for referred to as query analyzer, And build query tree.Optimizer is used for after querying command input by user is by parsing, and Performance Evaluation is carried out to it, and And according to the various different because usually finding optimal access path of query performance are influenced, for performing inquiry operation.Run ring Border is used to build appropriate fortune by generating the internal code of database support according to the access path of the output of optimizer Row structure, to realize the desired various operations to database of user.

As described in the background art, existing query optimizer and not perfect.For example, work as a querying command at least Including two query statements, the two query statements refer to same form, and the query result of a query statement will be based on The query result of another query statement is come when obtaining, which is difficult to complete optimization task.

It in order to make it easy to understand, will be described in following scene the defects of the query optimizer of the prior art and according to this hair The improvement of the database inquiry optimization method and apparatus of bright embodiment, wherein assume that involved database is relational database, And data base management system uses structured query language（SQL）The interface with managing is inputted as data, to the relation Database is operated.

It should be noted that although existing skill is specifically described by the scene of application SQL and relational database here Art and technical scheme, still, the invention is not limited thereto.The database can also be hierarchical data base, mesh data Other databases such as storehouse.Also, the SQL can also be replaced using other data base query languages of existing or following appearance Generation.

In the present specification, for ease of description, the specific type that existing query optimizer can not optimize can be inquired about Order is referred to as the first querying command.First querying command can include at least the first query statement and the second query statement, Second query statement is used to obtain the second query result from the second form, and first query statement is used for by described the Two query results and the first filter condition obtain the first query result, first form and second table from the first form The same foundation form in each direction database in lattice.

Typically, first query result can include the data value that the first data row collection closes, and described second looks into The data value that the second data row collection closes can be included by asking result.The first data row set and second data row set In each can include at least one data and arrange, and the data row in two data row set can be identical or different.

In addition, first query statement can include if-clause, the if-clause includes at least first mistake Filter condition, first filter condition connect first form on each data row in second data row set With second form.

In order to make it easy to understand, the form by first querying command described in specific example.

As shown in Figure 3A, which can be made of the first query statement and the second query statement.

First query statement is by keyword SELECT（Selection）The sentence of composition.SELECT statement is used for from form Choose data.First query statement is further included by keyword WHERE（Wherein）The if-clause of composition, for connect this Two query statements.WHERE clause chooses the filter condition of data for regulation from form.It is described in first specific example If-clause only include a filter condition, i.e. the first filter condition, first filter condition can by predicate IN come First form and second form are connected on each data row in second data row set.

The function of first query statement is, with reference to the first form A for being directed toward basic form TEST1, when a certain data row Data value on middle C1 row be in the second query statement return the second query result among when, returned to user in the data row Data value on C3 row.In other words, for first query statement for carrying out inquiry operation in the first form A, searching meets item One or more data values of part, and return to the data value to user.Also, the content item stored in first form A Mesh is identical with the content item in basic form TEST1.For example, first form can be simply basic form The alias of TEST1（alias）Or can also be a mirror image or copy of basic form TEST1 etc..

Second query statement is equally by keyword SELECT（Selection）The sentence of composition.Also, the second inquiry language Sentence can include by keyword GROUP BY（Grouping）The sentence of composition and by HAVING（Have）The clause of composition.GROUP BY Sentence is used to combine aggregate function（That is, the COUNT in this example（It counts））, to be carried out according to one or more row to result set Grouping.

The function of second query statement is, with reference to the second form B for being directed toward basic form TEST1, to be arranged according to C1 Data value is grouped, and counts the number that a certain data value occurs, and when its occurrence number is more than 3, being returned to user should Data value.

Due in first querying command based on the result of the second query statement come obtain the first query statement as a result, So can first query statement be known as father's query block, and second query statement is known as subquery block.Due to The first and second form A and B in first and second query statements are directed to the same form TEST1 in relational database, institute TEST1 can be known as to basic form.Further, since the first filter condition has the shape of A.C1IN (SELECT B.C1 ...) Formula, it is possible to think the first and second form A and B by implicit predicate A.C1=B.C1 to connect.Therefore, because above-mentioned spy Property, it is believed that the first querying command is substantially equivalent to a kind of querying command from connection having across query block.

It should be noted that although illustrating the types entail of the first querying command above with first specific example, It is that the invention is not limited thereto.For example, first querying command can include more than two query statement；Each query statement It can be by such as INSERT（Insertion）、UPDATE（Update）、DELETE（It deletes）It is formed Deng other keywords；Each query statement It can include fewer or more filterability keywords and/or clause；One or more can also be referred in each query statement Other a forms；Filter condition in WHERE clause passes through predicate IN or other predicates（Such as, for whether checking data value BETWEEN ... AND in specified scope（... between）, for check data value whether with specified pattern match LIKE（It is similar）And for checking EXIST that data value whether there is（In the presence of））Come on more than one data row described in connection First form and second form；And the first and second query statements can be obtained from the first and second forms more than one Query result on a data row.

As shown in Figure 3B, in the first query statement, in addition to the first filter condition shown in Fig. 3 A, if-clause is also It can include the second filter condition, second filter condition passes through logical operator AND（With）Come and first filtering rod Part combines, and by comparing operator<To connect C2 row and data value 3.Obviously, which can also include other mistakes Filter condition.Other filter conditions can pass through other logical operators（Such as, OR（Or）, it is non-（NOT））Come with first and Second filter condition connects, and can connect other data row and data by other comparison operators or arithmetic operator symbol Value.

As shown in Figure 3 C, in the first query statement, the first query result can include all row in form TEST1. In the second query statement, the querying condition being made of keyword GROUP BY sentences is removed, and is with the addition of by keyword The if-clause that WHERE is formed is less than 5 data record for the data value on removal C2 row.Obviously, which is Optionally.When not including the if-clause, the second form returns to the total data value on C1 row with not having any filtering.

As shown in Figure 3D, in the second query statement, except identical with reference to its content and basic form TEST1 Outside second form B, reference is also made to the 3rd form TEST2 that its content can be different from basic form TEST1, and except by Keyword GROUP BY sentences form querying condition outside, also added the if-clause being made of keyword WHERE, with to Data record applies other inquiry constraints.

As shown in FIGURE 3 E, on the basis of Fig. 3 D, in the first query statement, the filter condition in WHERE clause passes through Predicate IN on two data row C1 and C3 connects first form and second form, so that only when the first table Data value in a certain data row of lattice on C1 row is in a certain data row of the second form among the second query result on C1 row Data value among and meanwhile the first form data row in data value on C3 row be among second query result second When among the data value in the form data row on C3 row, the data in the first data row on all row are just returned to user Value.

Fig. 3 F show the form of the first querying command according to the 6th specific example, and Fig. 3 G are shown according to the 7th The form of first querying command of specific example.

As illustrated in Figure 3 F, in the first query statement, first table is connected on data row C1 by predicate EXIST Lattice and second form.The first inquiry life shown in Fig. 3 G is substantially equivalent to due to the first querying command shown in Fig. 3 F Order, so the first querying command shown in Fig. 3 G obviously meets the types entail of the first querying command, therefore, the shown in Fig. 3 F Database inquiry optimization method and apparatus according to embodiments of the present invention equally may be employed to carry out efficiently in one querying command Optimization.

In the prior art, after the first querying command for receiving shown in any of Fig. 3 A to Fig. 3 G in DBMS, solution Parser carries out syntax parsing to it first.Since performance optimization can not be completed to the first querying command, so the query optimizer Unique access path is generated in itself according to first querying command, and is output it in running environment.Finally, run Environment performs the access path, to obtain the desired data value of user.

In the following, the generating process of the access path of the prior art will be described with reference to figure 4 and Fig. 5.

Fig. 4 shows the exemplary contents of the basic form in Fig. 3 A, and Fig. 5 shows the visit generated according to the prior art It asks the way footpath.

As shown in figure 4, basis form TEST1 includes 10 data rows and 4 data row.Assuming that in data row RID Each data value is unduplicated.For example, RID row can have uniquely（UNIQUE）Attribute constraint, so as to unique mark number It is recorded according to every data in the table of storehouse（Data row）.Alternatively, which can also be arranged and is arranged to basis form TEST1's Major key（PRIMARY KEY）, possess the unique attribute constraint defined automatically.Moreover, it is assumed that in basic form TEST1, until Few created on data row C1 has index.Do not reading whole table（That is, full table scan）In the case of, index scanning is so that data Storehouse application program can quickly searching data.

In the prior art, query optimizer will generate following access path after the first querying command is received： The second query statement is first carried out, the first query statement is then performed according to the second query result.

As shown in figure 5, during the second query statement is performed, first using the copy of basic form TEST1 as Two form B（That is, the content in the second form B is identical with basic form TEST1）.Then, according to GROUP BY sentences The second form B is grouped on C1 row.Due to there was only three data values 1,2,3 on the C1 row of the second form B, so B points by the second form of GROUP BY sentences are corresponding three groups of data.Next, according to HAVING clause to this three groups of data into Row filtering, to obtain the second query result.For ease of description, form shown in Fig. 4 be equivalent to completed division operation it The result view of the second form B afterwards.

Specifically, first, for the situation of C1=1, when the first row from the second form B（That is, RID is X'0001'）Traversal To fourth line（That is, RID is X'0004'）When, can count C1=1 is counted as 4, i.e., more than 3.At this moment, it can be determined that go out number Belong to the second retrieval result according to value 1, and thus be added to the data value provisionally to store the interim of the second retrieval result In table.

Then, for the situation of C1=2, when the fifth line from the second form B（That is, RID is X'0005'）Traverse the 7th Row（That is, RID is X'0007'）When, can count C1=2 is counted as 2, i.e., less than 3.At this moment, it can be determined that go out data value 1 The second retrieval result is not belonging to, and because without the data value is added in the interim table.

Finally, for the situation of C1=3, when the 7th row from the second form B（That is, RID is X'0007'）Traverse the tenth Row（That is, RID is X'000A'）When, can count C1=3 is counted as 4, i.e., more than 3.At this moment, it can be determined that go out data value 3 The second retrieval result is fallen within, and thus is added to the data value provisionally to store the interim table of the second retrieval result In.

Therefore, after the second query statement has been performed, by the following interim table of generation, which includes 2 data rows It is arranged with 1 data, i.e., the interim table includes two data values 1 and 3 on row C1.

Thereafter, during the first query statement is performed, first using the copy of basic form TEST1 as the first form A（That is, the content in the first form A is identical with basic form TEST1）.Then, according to WHERE clause in the first form It is filtered line by line on the C1 row of A, to find the data value in interim table on the C1 row of the first form A.When finding During such data value, the record identifier that this data records will be found by arranging the index created in C1（RID）.Example Such as, the RID of a certain data record can be including the page number of the data record in the table and offset, for accurate in the table Ground positions the data record.Finally, inquire about according to the RID and return to the data value in the data row on C3 row to user.

Specifically, first, when the first row from the first form A（That is, RID is X'0001'）When beginning stepping through, it can be found that Data value on its C1 row is 1.Since data value 1 is in interim table, it is believed that this data record meets querying condition. At this moment, the RID of the first row of the first form A will be found by arranging the index created in C1（That is, X'0001'）.Then, root It inquires about according to the RID and returns to the data value F in the first data row on C3 row to user.

The rest may be inferred.Due to the second row of the first form A（That is, RID is X'0002'）To fourth line（That is, RID is X' 0004'）Data value on middle C1 row is 1, it is possible to find RID similarly by index, so as to data record into Row positioning, and finally return to user data value C, E and F in each data record on C3 row.

Next, when the fifth line for traversing the first form A（That is, RID is X'0005'）When, it can be found that on its C1 row Data value be 2.Since data value 2 is not in interim table, it is believed that this data record does not meet querying condition, and Next line is jumped to continue to inquire about.In this manner it is achieved that until the total data behavior for having traveled through the first form stops.

Therefore, after the first query statement has been performed, by following first search result of generation, the first search result bag 9 data rows and 1 data row are included, i.e., the interim table will include nine data values F, C, E, F, E, G, H, G and F on C3 row.

By analysis, inventors have realized that, the above-mentioned access path that query optimizer is generated will be first according to interim Data value in table indexes to search, and searches RID further according to index, is finally obtained according to RID in basic form TEST1 Data value needed for user, it is clear that this is that efficiency is extremely low.

Hereinafter, technical problem in the prior art will be directed to, is described in reference to the drawings and implements according to the present invention The database inquiry optimization method of example.

As shown in fig. 6, the database inquiry optimization method can include：

In step s 110, the first querying command is received.

In an embodiment of the present invention, after DBMS receives user's querying command input by user, first to it into Row syntax parsing and query tree structure.After finding that the user's querying command is provided in accordance with SQL syntax, this can be first determined whether Whether user's querying command is the first querying command according to embodiments of the present invention.First querying command includes at least first Query statement and the second query statement, second query statement are used to obtain the second query result from the second form, and described the One query statement is used to obtain the first query result from the first form by second query result and the first filter condition, The same foundation form in each direction database in first form and second form.

Typically, first query result can include the data value that the first data row collection closes, second inquiry As a result the data value that the second data row collection closes is included, and first query statement includes if-clause, condition Sentence includes at least first filter condition, each data of first filter condition in second data row set First form and second form are connected on row.

When judge the user's querying command not according to the embodiment of the present invention the first querying command when, can be according to existing There is technology to be optimized to the user's querying command and access path generation, and this method terminates.When judging that the user looks into When inquiry order meets the types entail of the first querying command, this method proceeds to step S120, continues to execute.

In the step s 120, the definition statement of the 3rd form is generated based on second query statement.

After judging that the user's querying command is the first querying command according to embodiments of the present invention, this can be based on The design of invention optimizes first querying command.Specifically, can be generated according to second query statement The definition statement of three forms, for the 3rd form for storing the 3rd query result, the 3rd query result includes described the It is at least one in data value on one query result and record identification row.

For example, it is possible, firstly, to first querying command is divided by multiple queries sentence according to analysis result.In order to just In description, it is assumed that first querying command only includes two query statements, i.e., the as shown in any of Fig. 3 A to Fig. 3 G One query statement and the second query statement.

It is then possible to second query statement is rewritten as the 3rd query statement, the 3rd query statement be used for from Second form obtains the 3rd query result.Depending on different embodiments, the 3rd query result can include The data value that different data arranges or data row collection closes.

Finally, can the definition statement of the 3rd form be generated based on the 3rd query statement.For example, the 3rd form It can be temporary table created in memory headroom, for the 3rd query result of interim storage.The temporary table can be Virtual tables or temporary file, even a series of data value.

In step s 130, second query statement is replaced using the definition statement, and based on the described 3rd Form rewrites first query statement.

After the 3rd form including the 3rd query result is generated, it can be removed from the first querying command original The definition statement of 3rd form is added to the position of the second query statement by the second query statement, and according to the 3rd form institute The 3rd different query results of storage differently rewrite the first query statement, so as to by first querying command Be converted to the second querying command after optimizing according to embodiments of the present invention.

It then, can be according to different in form from the first querying command but functionally identical this second is looked into It askes order and carrys out the access path that generative nature can be outstanding, and output it in running environment.Finally, running environment can perform The access path, to obtain the desired data value of user.

It can be seen that using database inquiry optimization method and apparatus according to the present invention, it can be to having across inquiry The querying command from connection of block carries out query semantics conversion, and father's inquiry is merged into using subquery block as a temporary table In block so that compared with prior art, can be by the lower inquiry operation being calculated as in realization database originally, so as to carry The execution efficiency of querying command has been risen, has saved the run time of querying command, has improved the user's body in data base querying It tests.

Hereinafter, will in different embodiments, the concrete operations being specifically described in step S120 and S130.

First, will the first embodiment of the present invention be described.It in the first embodiment, in the step s 120, can be by described in Second query statement is rewritten as the 3rd query statement, and the 3rd query statement is used to obtain the described 3rd from second form Query result, the 3rd query result can include at least the data value on record identification row, during the record identification arranges Each data value is used to uniquely identify the data row where the data value；And based on the 3rd query statement next life Into the definition statement of the 3rd form.In step s 130, in first querying command, the definition of the 3rd form can be utilized Sentence replaces second query statement, meanwhile, first filter condition can be rewritten as existing by the 3rd operator First form and the 3rd form are connected on the record identification row；And first query statement is rewritten as base Connection relation between first form and the 3rd form is obtained from first form and the 3rd form First query result.

It is assumed for convenience of description that first querying command as shown in Figure 3A, and is schemed in the first embodiment of the invention Basic form in 3A is as shown in Figure 4.

As shown in Figure 7 A, the 3rd query statement can only obtain the data value on data row RID from the second form B.So Afterwards, the data value on data row RID can be utilized, and passes through keyword WITH ... AS（Will ... it is defined as ...）To form The definition statement of 3rd form DSNVB (02).That is, the 3rd form generated in the step s 120 can only include Data value on record identification row RID, so that the 3rd form committed memory space as little as possible.

However, the invention is not limited thereto.Obviously, in addition to the data value on data row RID, the 3rd form can be with Preserve one or more other data row（For example, data row C1, C2 and/or C3）On data value, even all data row On data value, so that the data of the 3rd form are more complete.

Further, since in the first querying command as shown in Figure 3A, the if-clause being made of WHERE clause only wraps Include a filter condition, i.e. the first filter condition, i.e. A.C1IN (SELECT B.C1 ...), so being generated in step s 130 The second querying command in, still will include only a mistake using the 3rd form DSNVB (02) the first query statements rewritten Filter condition.

Specifically, as shown in Figure 7 A, can using comparison operator=come on data row RID connect the 3rd form DSNVB (02) and the first form A, and revised first query statement is caused with reference to the two forms, so that described first Query statement can be obtained in the first form A needed for user based on the connection relation between two form DSNVB (02) Query result.

Next, after the first original querying command is optimized for the second querying command, it can be in condition In the case that sentence includes the first filter condition as unique filter condition, visit is generated based on transformed second querying command Footpath ask the way often with minimum calculating cost, therefore, the second querying command can be directly based upon to generate access path, so as to The access path is performed in running environment, so as to obtain first query result from the basic form.

However, the invention is not limited thereto.In rare cases, depending on the different content and correlation in basic form Data row in whether created index, based on the first original querying command generate access path be also possible to have it is minimum Calculate cost.It therefore, in the first embodiment, can also be based on the first original querying command and transformed second inquiry life Both orders carry out assessment of cost to generate a plurality of access path to a plurality of access path, to be performed in running environment Access path with least cost.

In the following, the generating process of the access path of first embodiment will be described with reference to figure 4 and Fig. 8 A.

Since the if-clause being made of in the first embodiment WHERE clause includes the first filter condition as unique Filter condition, thus query optimizer after the second querying command is received, can learn without doubt according to this second The performance for the access path that querying command is generated is somebody's turn to do necessarily better than the access path generated according to the first querying command Query optimizer directly can generate following access path according to second querying command：The 3rd form is defined first DSNVB (02), then performs the first query statement.

As shown in Figure 8 A, the process for defining the 3rd form is similar to the process for performing the second query statement in the prior art. That is, first using the copy of basic form TEST1 as the second form B.Then, according to GROUP BY sentences C1 row on to second Form B is grouped.Due to there was only three data values 1,2,3 on the C1 row of the second form B, so GROUP BY sentences are by the Two B points of forms are corresponding three groups of data.Next, this three groups of data are filtered according to HAVING clause, to obtain Three query results.

Specifically, first, for the situation of C1=1, when the first row from the second form B（That is, RID is X'0001'）Traversal To fourth line（That is, RID is X'0004'）When, can count C1=1 is counted as 4, i.e., more than 3.At this moment, in the prior art Differently, it is not that data value 1 is added in the 3rd form DSNVB (02), but by the first row into fourth line on RID row Data value X'0001' to X'0004' is added in the 3rd form DSNVB (02).

Then, for the situation of C1=2, when the fifth line from the second form B（That is, RID is X'0005'）Traverse the 7th Row（That is, RID is X'0007'）When, can count C1=2 is counted as 2, i.e., less than 3.At this moment, any data value is not added Into the 3rd form DSNVB (02).

Finally, for the situation of C1=3, when the 7th row from the second form B（That is, RID is X'0007'）Traverse the tenth Row（That is, RID is X'000A'）When, can count C1=3 is counted as 4, i.e., more than 3.Also, further the second form of traversal B has found, in the tenth a line（That is, RID is X'000B'）The data value of middle C1 row is also 3.At this moment, by the 7th row to the tenth a line Data value X'0007' to X'000B' on middle RID row is added in the 3rd form DSNVB (02).

Therefore, the 3rd form DSNVB (02) generated includes 9 data rows and 1 data row, i.e. the 3rd form DSNVB (02) includes nine data value X'0001' to X'0004' and X'0007' to X'000B' on data row RID.

Thereafter, during the first query statement is performed, first using the copy of basic form TEST1 as the first form A.Then, the first form A is connected with the 3rd form DSNVB (02) on RID row according to WHERE clause, in the first form A RID row on find the data value in the 3rd form DSNVB (02).When finding such data value, according to the RID come It inquires about and returns to the data value in the data row on C3 row to user.

Specifically, first, when the first row from the first form A（That is, RID is X'0001'）When beginning stepping through, it can be found that Data value on its RID row is X'0001'.Since data value X'0001' is in the 3rd form DSNVB (02), it is believed that This data record meets querying condition.At this moment, inquire about and returned to user in the first data row on C3 row according to the RID Data value F.

Then, and so on.Due to the second row of the first form A（That is, RID is X'0002'）To fourth line（That is, RID is X'0004'）Data value on middle RID row is in the 3rd form DSNVB (02), it is possible to which similarly being returned to user should Data value C, E and F in each data record on C3 row.

Next, when the fifth line for traversing the first form A（That is, RID is X'0005'）When, it can be found that on its RID row Data value be X'0005'.Since data value X'0005' is not in the 3rd form DSNVB (02), it is believed that this data Record does not meet querying condition, and jumps to next line and continue to inquire about.In this manner it is achieved that until the first form is traveled through Total data behavior stops.

Therefore, after the first query statement has been performed, by following first search result of generation, the first search result bag 9 data rows and 1 data row are included, i.e., the interim table will include nine data values F, C, E, F, E, G, H, G and F on C3 row. Obviously, according to obtained first search result of the second querying command with being tied according to obtained first search of the first querying command Fruit is identical.

It can be seen that compared in the prior art, in the above-mentioned access path generated in the first embodiment, defining During three forms, the RID of the required data record when performing the first query statement is directly obtained, and is looked into performing first When asking sentence, the data value according to needed for RID come the acquisition user in basic form TEST1.

That is, first embodiment fully takes into account the characteristic across query block from the querying command of connection, the In the implementation procedure of two querying commands, after judging that a certain data value belongs to the second retrieval result, directly in the 3rd form The middle RID for recording all data records for including the data value, and tied according to RID to inquire about and return to the first inquiry to user Fruit, so as to save through data value to search index and search all intermediate steps of RID according to index so that reduce whole A required system resource of inquiry operation.

In the following, the performance boost of first embodiment institute's band compared with prior art will be described with reference to figure 9A and Fig. 9 B.

Fig. 9 A show the performance parameter of the access path that execution is generated according to the prior art in running environment, and scheme 9B shows the performance parameter that the access path generated according to first embodiment is performed in running environment.

As shown in Figure 9 A, based on a certain specific experiment condition, wherein it is assumed that using table based on a certain data form Lattice, and a certain user's querying command is used to perform in running environment and given birth to according to the prior art as the first querying command Into access path it is required calculate cost be about 3.1E7 microseconds（ms）.However, as shown in Figure 9 B, based on identical reality Condition is tested, the required cost that calculates of the access path generated according to first embodiment is performed in running environment is about 1.7E7 microsecond（ms）.

By this group of experimental data as it can be seen that the access path generated using first embodiment can be than using the prior art The access path that is generated saves about 26% calculating cost, so that we can be drawn to draw a conclusion, i.e. first embodiment Method can play apparent performance boost for the querying command from connection across query block.Also, it is looked into due to such Order is ask universally present in the inquiry operation needed for user（According to the statistics of one group of random data, such querying command Probability of occurrence about 12% or so）, so first embodiment will greatly promote daily inquiry body of the user for database It tests.

It should be noted that here, the variation of search efficiency is reflected by total calculating duration of data query operation.So And calculating cost can also be according to the I/O and central processing unit of Database Systems（CPU）Processing time for being consumed, system Resource etc. is counted respectively.

In addition, in the first embodiment, the inventors have further noted that, during access path is generated, typically, institute RID in the 3rd obtained form DSNVB (02) may be not tactic.For example, the 3rd form DSNVB (02) exists The data value included on data row RID may be X'0004', X'0001', X'000A', X'0002' ....

Obviously, if simply obtaining data value in the database according to RID, certainly will cause first positioned at basis Data value is obtained in the 4th data record among form, the beginning of basic form is then moved to, is recorded from the 1st data Next, jumping to the rear portion of basic form again, data value is obtained from the 10th data record for middle acquisition data value, then, Again back to the forepart of basic form, data value is obtained from the 2nd data record, and so on.In this way, it is bound to cause data The access speed of form slows down, and access efficiency reduces.

Therefore, in one embodiment, can be prefetched in the first embodiment using list（list prefetch）'s Mode to obtain the data value needed for user in basic form.It is not simple that the list, which prefetches the access path that mode is generated, Ground obtains data value in the database according to obtained RID, but first to the data value on record identification row into Row sequence sequentially accesses the basic form according to ranking results, and obtains the record from the basic form The data value that first data row collection pointed by data value on identifier closes, as first query result.

In this way, before obtaining data value in the database, the 3rd form DSNVB (02) can be existed according to RID The data value included on data row RID is ranked sequentially as X'0001' to X'0004' and X'0007' to X'000B'.Then, in base In plinth form, data value is in turn obtained according to vertical order, so as to accelerate the access speed of data form, into One step improves the execution efficiency of querying command.

Above, the if-clause being made of in the first querying command WHERE clause is described in the first embodiment only Only include the situation of a filter condition, in the following, second embodiment of the present invention will be described, wherein the item in the first query statement Part clause can include more than one filter condition.

It is assumed for convenience of description that first querying command as shown in Figure 3B, and is schemed in second embodiment of the invention Basic form in 3B is as shown in Figure 4.

As shown in Figure 7 B, although in a second embodiment, in the first querying command shown in Fig. 3 B, by WHERE clause The if-clause of composition outside i.e. A.C1IN (SELECT B.C1 ...), further includes the second filtering except including the first filter condition Condition, i.e. A.C2<3, but the 3rd query statement still can only obtain the data value on data row RID from the second form B. That is, the 3rd form generated in the step s 120 can only include the data value on record identification row RID.

Further, since the if-clause includes two filter conditions, so the second inquiry generated in step s 130 In order, the first query statement rewritten using the 3rd form DSNVB (02) will also include two filter conditions, wherein described Second filter condition passes through logical operator AND（With）To be combined with the first filter condition, and by comparing operator<To connect Connect C2 row and data value 3.

Comparison diagram 7A and Fig. 7 B, which can be seen that second embodiment, includes the step S120 identical with first embodiment And S130.However, due to including two filter conditions in a second embodiment, this will result at least two access path Situation performs the situation of the second filter condition after the first filter condition is first carried out when performing the first query statement and is holding The situation that the first filter condition is performed after the second filter condition is first carried out during the first query statement of row.

It therefore, can be in the if-clause after the first original querying command is optimized for the second querying command In the case of including at least the first and second filter conditions, the set of access path is generated based on second querying command, And the executory cost of each access path in the set is calculated, and optimal access road is selected based on the executory cost Footpath, to perform the optimal access path in running environment, so as to obtain first inquiry from the basic form As a result.

It can be seen that compared with first embodiment, a plurality of access path can be generated in a second embodiment.In this way, number This plurality of access path can further be weighed according to base management system, in optimal access path, according to RID come The data value needed for user is obtained in basic form TEST1.Therefore, second embodiment be not simply perform in order it is each Filter condition, but their execution sequence is optimally selected, so as to provide a user further query optimization effect.

Above, describe in the first embodiment after judging that a certain data value belongs to the second retrieval result, Record includes the RID of all data records of the data value in three forms, and inquires about according to RID and return to first to user The situation of query result, in the following, the third embodiment of the present invention will be described, wherein judging that a certain data value belongs to the second inspection After hitch fruit, directly record includes the first query result to be returned to user in the 3rd form.

In the third embodiment, in the step s 120, second query statement can be rewritten as the 3rd query statement, 3rd query statement is used to obtain the 3rd query result from second form, and the 3rd query result at least wraps Include the data value that the first data row collection closes；And the definition language of the 3rd form is generated based on the 3rd query statement Sentence.In step s 130, in first querying command, described second can be replaced using the definition statement of the 3rd form Query statement, meanwhile, include first filter condition as unique filter condition in response to the if-clause, described the Three query results can only include the data value that first data row collection closes, and in first query statement, Remove the if-clause；And first query statement is rewritten as to obtain the first inquiry knot from the 3rd form Fruit.

It is assumed for convenience of description that first querying command as shown in Figure 3A, and is schemed in third embodiment of the invention Basic form in 3A is as shown in Figure 4.

As seen in figure 7 c, the 3rd query statement can only obtain the data value on data row C3 from the second form B.So Afterwards, the definition statement of the 3rd form DSNVB (02) can be formed using the data value on data row C3.That is, The 3rd form generated in step S120 can only include the data value on data row C3, so that the 3rd form is tried one's best Committed memory space less.

However, the invention is not limited thereto.Obviously, in addition to the data value on data row C3, the 3rd form can be with Preserve one or more other data row（For example, data row C1, C2 and/or RID）On data value, even all data row On data value, so that the data of the 3rd form are more complete.Especially, in one embodiment, the 3rd form can be with The data value of data row RID is further preserved, to ensure as necessary, can efficiently complete same or other query statements Other data query operations.

Further, since in the first querying command as shown in Figure 3A, the if-clause being made of WHERE clause only wraps A filter condition, i.e. the first filter condition A.C1IN (SELECT B.C1 ...) are included, so generated in step s 130 In two querying commands, entire condition can be directly removed using the 3rd form DSNVB (02) the first query statements rewritten Sentence.

Specifically, as seen in figure 7 c, the data value on C3 row has been included due to the 3rd form DSNVB (02), so It can be directly to the query result needed for user return user, without performing any filter operation again.

Next, after the first original querying command is optimized for the second querying command, it can be in condition In the case that sentence includes the first filter condition as unique filter condition, road is accessed to generate based on second querying command Footpath, to perform the access path in running environment, so as to obtain first query result from the basic form.

In the following, the generating process of the access path of 3rd embodiment will be described with reference to figure 4 and Fig. 8 B.

Since the if-clause being made of in the third embodiment WHERE clause includes the first filter condition as unique Filter condition, so query optimizer after the second querying command is received, equally can be ordered directly according to second inquiry It makes generating following access path：The 3rd form DSNVB (02) is defined first, then performs the first query statement.

Specifically, first, for the situation of C1=1, when the first row from the second form B（That is, RID is X'0001'）Traversal To fourth line（That is, RID is X'0004'）When, can count C1=1 is counted as 4, i.e., more than 3.At this moment, with the first or second In embodiment differently, it is not that data value X'0001' to X'0004' by the first row into fourth line on RID row is added to In three form DSNVB (02), but data value F, C, E and F by the first row into fourth line on C3 row are added to the 3rd form In DSNVB (02).

Finally, for the situation of C1=3, when the 7th row from the second form B（That is, RID is X'0007'）Traverse the tenth Row（That is, RID is X'000A'）When, can count C1=3 is counted as 4, i.e., more than 3.Also, further the second form of traversal B has found, in the tenth a line（That is, RID is X'000B'）The data value of middle C1 row is also 3.At this moment, by the 7th row to the tenth a line Data value E, G, H, G and F on middle C3 row are added in the 3rd form DSNVB (02).

Therefore, the 3rd form DSNVB (02) generated includes 9 data rows and 1 data row, i.e. the 3rd form DSNVB (02) includes nine data values F, C, E, F, E, G, H, G and F on data row C3.

Thereafter, during the first query statement is performed, can directly be returned to user in the 3rd form DSNVB (02) All data values.Obviously, according to obtained first search result of the second querying command and according to the first querying command Obtained first search result is identical.

It can be seen that compared in the prior art, in the above-mentioned access path generated in the third embodiment, defining During three forms, required first query result when performing the first query statement is directly obtained, and is looked into performing first When asking sentence, first query result is returned to user, as the data value needed for user.Therefore, compared with first embodiment, 3rd embodiment only performs a table lookup operation, further reduces required when performing the first querying command Cost is calculated, maximumlly improves search efficiency.

Above, the if-clause that describes in example and be made of in the first querying command WHERE clause is applied only the 3rd Include the situation of a filter condition, in the following, the fourth embodiment of the present invention will be described, wherein the condition in the first query statement Clause can include more than one filter condition.

In the fourth embodiment, in the step s 120, second query statement can be rewritten as the 3rd query statement, 3rd query statement is used to obtain the 3rd query result from second form, and the 3rd query result at least wraps Include the data value that the first data row collection closes；And the definition language of the 3rd form is generated based on the 3rd query statement Sentence.In step s 130, in first querying command, described second can be replaced using the definition statement of the 3rd form Query statement, meanwhile, include first filter condition and the second filter condition in response to the if-clause, the described 3rd looks into First data row set and the data value on the 3rd data row can be included by asking result, and in first query statement In, first filter condition and first operator are removed from the if-clause；And inquire about language by described first Sentence is rewritten as obtaining first query result from the 3rd form based on second filter condition.

It is assumed for convenience of description that first querying command as shown in Figure 3B, and is schemed in fourth embodiment of the invention Basic form in 3B is as shown in Figure 4.

As illustrated in fig. 7d, due in the fourth embodiment, in the first querying command shown in Fig. 3 B, by WHERE clause The if-clause of composition outside i.e. A.C1IN (SELECT B.C1 ...), further includes the second filtering except including the first filter condition Condition, i.e. A.C2<3, so the 3rd query statement needs at least obtain the data value on data row C2 and C3 from the second form B. It is then possible to the definition statement for forming the 3rd form DSNVB (02) using the data value on the data row C2 and C3.Namely It says, the 3rd form generated in the step s 120 can only include the data value on data row C2 and C3, so that the Three forms committed memory space as little as possible.Obviously, in addition to the data value on data row C2 and C3, the 3rd form may be used also To preserve one or more other data row（For example, data row C1 and/or RID）On data value, even all data row On data value.

Further, since the if-clause includes two filter conditions, so the second inquiry generated in step s 130 In order, first filter condition and logic can be removed using the 3rd form DSNVB (02) the first query statements rewritten Operator AND, and only include the second filter condition C2<3 are used as unique filter condition.

Comparison diagram 7C and Fig. 7 D, which can be seen that fourth embodiment, includes the step S120 identical with 3rd embodiment and not Same step S130.Due to including at least two filter conditions in the fourth embodiment, so query optimizer is receiving the After two querying commands, the performance of the access path generated according to second querying command can not be learnt whether better than root at once According to the access path that the first querying command is generated, this is because depending on different content and related data in basic form Index whether has been created in row, directly may obtain higher inquiry effect instead according to the first querying command to generate access path Rate.Especially when the results set obtained using the second filter condition only includes small number of data value, original is used The first querying command to begin is often than using transformed second querying command to have higher search efficiency.

It therefore, can be in the if-clause after the first original querying command is optimized for the second querying command In the case of including at least the first and second filter conditions, the first of access path the collection is generated based on first querying command It closes（Including at least two kinds of access path, i.e., perform the second mistake after first carrying out the first filter condition when performing the first query statement The situation of filter condition and the feelings that the first filter condition is performed after the second filter condition are first carried out when performing the first query statement Condition）, and the second set of access path is generated based on second querying command.Then, calculate the first set and The executory cost of each access path in second set, and optimal access path is selected based on the executory cost, with Just the optimal access path is performed in running environment, so as to obtain first query result from the basic form.

It can be seen that compared with 3rd embodiment, it in the fourth embodiment can be based on inquiry original and after conversion It orders to generate a plurality of access path.In this way, data base management system can carry out choosing comprehensively to this plurality of access path, with Optimal access path is more objectively selected, so as to higher to exchange for by losing certain access path generated time Querying command execution efficiency.

Hereinafter, will database inquiry optimization equipment according to embodiments of the present invention be described.

The illustrated database inquiry optimization methods according to embodiments of the present invention of Fig. 6 can pass through the illustrated numbers of Figure 10 Optimize equipment 100 according to library inquiry to realize.Since the details of the operation of equipment execution is identical with the method being described above, In other words, each details for the method being described above is equally applicable to the equipment, therefore herein only to the equipment It is simply described, and omits the description to same detail.

As illustrated in FIG. 6, which can include：Order receiving unit 110, form are fixed Adopted unit 120 and order converting unit 130.In addition, the database inquiry optimization equipment 100 can also include：Path selection list Member 140.

The order receiving unit 110 receives the first querying command, and first querying command includes at least the first inquiry language Sentence and the second query statement, second query statement are used to obtain the second query result, first inquiry from the second form Sentence is used to obtain the first query result from the first form by second query result and the first filter condition, and described the The same foundation form in each direction database in one form and second form.

The definition statement that the form definition unit 120 generates the 3rd form based on second query statement, described For storing the 3rd query result, the 3rd query result is included on first query result and record identification row three forms Data value in it is at least one.

The order converting unit 130 replaces described second using the definition statement in first querying command Query statement, and first query statement is rewritten based on the 3rd form, so as to will first querying command turn It is changed to the second querying command.

First querying command is converted to the second inquiry life by the path selection unit 140 in the order converting unit After order, the first set of access path is generated based on first querying command, based on the second querying command next life Into the second set of access path, the executory cost of each access path in the first set and second set is calculated, and And optimal access path is selected based on the executory cost, to perform the optimal access path in running environment, from And obtain first query result from the basic form.

In the first and second embodiment as described above, the form definition unit 120 can be inquired about described second Paraphrasing is the 3rd query statement, and the 3rd query statement is used to obtain the 3rd inquiry knot from second form Fruit, the 3rd query result include at least the data value on record identification row, each in record identification row Data value is used to uniquely identifying data row where the data value, and generates the 3rd based on the 3rd query statement The definition statement of form.

The order converting unit 130 in first querying command, can using the definition statement of the 3rd form come Second query statement being replaced, meanwhile, first filter condition can be rewritten as through the 3rd operator in the note First form and the 3rd form are connected on record identity column, and first query statement is rewritten as based on described Connection relation between first form and the 3rd form obtains described from first form and the 3rd form One query result.

First querying command can be converted to second in the order converting unit and looked by the path selection unit 140 After asking order, first querying command is based only on to generate the first set of access path, based on the described second inquiry It orders to generate the second set of access path, calculates the execution of each access path in the first set and second set Cost, and optimal access path is selected based on the executory cost, to perform the optimal access in running environment Path, so as to obtain first query result from the basic form.

Optionally, the data value which can arrange the record identification is ranked up, according to row Sequence result sequentially accesses the basic form, and obtains the data on the record identifier from the basic form The data value that pointed first data row collection of value closes, as first query result.

It can be seen that compared in the prior art, the first querying command only includes the of filter condition wherein The characteristic across the querying command from connection of query block is fully taken into account in one embodiment, bag is recorded directly in the 3rd form The RID of all data records of the data value is included, and inquires about according to RID and returns to the first query result to user, so as to It saves and index is searched and according to intermediate steps all index search RID by data value so that reduce entire inquiry operation Required system resource.

In addition, compared with first embodiment, the first querying command includes at least the first and second filter conditions wherein A plurality of access path can be generated in second embodiment, further to weigh to this plurality of access path, so as to Family provides further query optimization effect.

In 3rd embodiment as described above, the form definition unit 120 can change second query statement The 3rd query statement is written as, the 3rd query statement is used to obtain the 3rd query result from second form, described 3rd query result includes at least the data value that first data row collection closes, and based on the 3rd query statement next life Into the definition statement of the 3rd form.

The order converting unit 130 can replace second query statement using the definition statement of the 3rd form, Meanwhile include first filter condition as unique filter condition, the 3rd inquiry knot in response to the if-clause Fruit can include at least the data value that first data row collection closes, and the order converting unit 130 is described first In query statement, the if-clause is removed, and first query statement is rewritten as to obtain institute from the 3rd form State the first query result.

First querying command is converted to the second inquiry life by the path selection unit 140 in the order converting unit After order, first querying command can also be based only on to generate the first set of access path, looked into based on described second Order is ask to generate the second set of access path, calculates holding for each access path in the first set and second set Row cost, and optimal access path is selected based on the executory cost, to perform the optimal visit in running environment It asks the way footpath, so as to obtain first query result from the basic form.

It can be seen that compared in the prior art, the first querying command includes the first filter condition as unique wherein Filter condition 3rd embodiment in, when defining three forms, directly obtain required when performing the first query statement The first query result, and when performing the first query statement, first query result is returned to user, as needed for user Data value.Therefore, compared with first embodiment, 3rd embodiment only performs a table lookup operation, further Reduce the calculating cost required when performing the first querying command, maximumlly improve search efficiency.

In fourth embodiment as described above, the form definition unit 120 can change second query statement The 3rd query statement is written as, the 3rd query statement is used to obtain the 3rd query result from second form, described 3rd query result includes at least the data value that first data row collection closes, and based on the 3rd query statement next life Into the definition statement of the 3rd form.

The order converting unit 130 can replace second query statement using the definition statement of the 3rd form, Meanwhile including first filter condition and the second filter condition in response to the if-clause, the 3rd query result can To include the data value on the first data row set and the 3rd data row, second filter condition passes through the first operator To be combined with first filter condition, and the 3rd data row and condition value are connected by the second operator, and The order converting unit 130 removes first filter condition in first query statement from the if-clause With first operator, and first query statement is rewritten as based on second filter condition come from the described 3rd Form obtains first query result.

It can be seen that compared with 3rd embodiment, the first querying command includes at least the first and second filtering rods wherein Can a plurality of access path be generated based on original and after conversion querying command in the fourth embodiment of part.In this way, data Base management system can carry out choosing comprehensively to this plurality of access path, more objectively to select optimal access path, So as to exchange higher querying command execution efficiency for by losing certain access path generated time.

Flow chart and block diagram in attached drawing show the system, method and computer journey of multiple embodiments according to the present invention Architectural framework in the cards, function and the operation of sequence product.In this regard, each box in flow chart or block diagram can generation The part of one module of table, program segment or code, a part for the module, program segment or code include one or more use In the executable instruction of logic function as defined in realization.It should also be noted that it is marked at some as in the realization replaced in box The function of note can also be occurred with being different from the order marked in attached drawing.For example, two continuous boxes can essentially base Originally it is performed in parallel, they can also be performed in the opposite order sometimes, this is depending on involved function.It is also noted that It is the combination of each box in block diagram and/or flow chart and the box in block diagram and/or flow chart, can uses and perform rule The group of specialized hardware and computer instruction is realized or can used to fixed function or the dedicated hardware based system of action It closes to realize.

Various embodiments of the present invention are described above, above description is exemplary, and non-exclusive, and It is not limited to disclosed each embodiment.In the case of without departing from the scope and spirit of illustrated each embodiment, for this skill Many modifications and changes will be apparent from for the those of ordinary skill in art field.The selection of term used herein, purport It is best explaining the principle of each embodiment, practical application or to the technological improvement of the technology in market or is leading this technology Other those of ordinary skill in domain are understood that each embodiment disclosed herein.

Claims

1. a kind of database inquiry optimization method, including：

The first querying command is received, first querying command includes at least the first query statement and the second query statement, described Second query statement is used to obtain the second query result from the second form, and first query statement is used to look by described second Result and the first filter condition are ask to obtain the first query result from the first form, in first form and second form Each be directed toward database in same foundation form；

The definition statement of 3rd form is generated based on second query statement, the 3rd form is inquired about for storing the 3rd As a result, it is at least one in the data value that the 3rd query result includes first query result and record identification arranges, Each data value in the record identification row is used to uniquely identify the data row where the data value；And

In first querying command, second query statement is replaced using the definition statement, and based on described 3rd form rewrites first query statement, so as to which first querying command is converted to the second querying command.

2. the method for claim 1, wherein first query result includes the data that the first data row collection closes Value, second query result includes the data value that the second data row collection closes, and first query statement includes condition Clause, the if-clause include at least first filter condition, and first filter condition is arranged in second data to be collected First form and second form are connected on each data row in conjunction, and

It is described to be included based on second query statement to generate the definition statement of the 3rd form：

Second query statement is rewritten as the 3rd query statement, the 3rd query statement is used to obtain from second form The 3rd query result is obtained, the 3rd query result includes at least the data value that first data row collection closes；And

The definition statement of 3rd form is generated based on the 3rd query statement.

3. method as claimed in claim 2, wherein, include first filter condition as only in response to the if-clause One filter condition, the 3rd query result include at least the data value that first data row collection closes, and

It is described to be included based on the 3rd form to rewrite first query statement：

In first query statement, the if-clause is removed；And

First query statement is rewritten as to obtain first query result from the 3rd form.

4. method as claimed in claim 2, wherein, include first filter condition and second in response to the if-clause Filter condition, the 3rd query result include the data value in first data row set and the 3rd data row, and described the Two filter conditions are combined by the first operator with first filter condition, and are connected by the second operator described 3rd data arrange and condition value, and

In first query statement, first filter condition and first computing are removed from the if-clause Symbol；And

First query statement is rewritten as to obtain described first from the 3rd form based on second filter condition Query result.

5. the method for claim 1, wherein first query result includes the data that the first data row collection closes Value, second query result includes the data value that the second data row collection closes, and first query statement includes condition Clause, the if-clause include at least first filter condition, and first filter condition is arranged in second data to be collected First form and second form are connected on each data row in conjunction, and

Second query statement is rewritten as the 3rd query statement, the 3rd query statement is used to obtain from second form The 3rd query result is obtained, the 3rd query result includes at least the data value on record identification row；And

6. method as claimed in claim 5, wherein, it is described that the first query statement bag is rewritten based on the 3rd form It includes：

In first query statement, first filter condition is rewritten as through the 3rd operator in the record identification First form and the 3rd form are connected on row；And

By first query statement be rewritten as based on the connection relation between first form and the 3rd form come from First form and the 3rd form obtain first query result.

7. method as claimed in claim 5, it is described first querying command is converted into the second querying command after, also Including：

Include first filter condition as unique filter condition in response to the if-clause, based on the described second inquiry It orders to generate access path, to perform the access path in running environment, so as to obtain from the basic form First query result.

8. the method for claim 7, wherein, in response to generating the access path, the record identification is arranged Data value is ranked up, and the basic form is sequentially accessed according to ranking results, and is obtained from the basic form The data value that first data row collection pointed by data value on the record identification row closes, as the described first inquiry As a result.

9. the method as described in claim 1, it is described first querying command is converted into the second querying command after, also Including：

The first set of access path is generated based on first querying command；

The second set of access path is generated based on second querying command；

Calculate the executory cost of each access path in the first set and second set；And

Optimal access path is selected based on the executory cost, to perform the optimal access path in running environment, So as to obtain first query result from the basic form.

10. a kind of database inquiry optimization equipment, including：

Order receiving unit, for receiving the first querying command, first querying command include at least the first query statement and Second query statement, second query statement are used to obtain the second query result, first query statement from the second form For obtaining the first query result, first table from the first form by second query result and the first filter condition The same foundation form in each direction database in lattice and second form；

Form definition unit, for generating the definition statement of the 3rd form, the 3rd table based on second query statement For lattice for storing the 3rd query result, the 3rd query result includes the number on first query result and record identification row According at least one in value, each data value in the record identification row is where for uniquely identifying the data value Data row；And

Order converting unit, in first querying command, replacing second inquiry using the definition statement Sentence, and first query statement is rewritten based on the 3rd form, so as to which first querying command is converted to Second querying command.

11. equipment as claimed in claim 10, wherein, first query result includes the data that the first data row collection closes Value, second query result includes the data value that the second data row collection closes, and first query statement includes condition Clause, the if-clause include at least first filter condition, and first filter condition is arranged in second data to be collected First form and second form are connected on each data row in conjunction, and

Second query statement is rewritten as the 3rd query statement by the form definition unit, and the 3rd query statement is used for The 3rd query result is obtained from second form, the 3rd query result includes at least first data row set On data value, and the definition statement of the 3rd form is generated based on the 3rd query statement.

12. equipment as claimed in claim 11, wherein, include the first filter condition conduct in response to the if-clause Unique filter condition, the 3rd query result include at least the data value that first data row collection closes, and

The order converting unit removes the if-clause, and described first is inquired about in first query statement Paraphrasing is to obtain first query result from the 3rd form.

13. equipment as claimed in claim 11, wherein, include first filter condition and the in response to the if-clause Two filter conditions, the 3rd query result includes the data value in first data row set and the 3rd data row, described Second filter condition is combined by the first operator with first filter condition, and by the second operator to connect The 3rd data row and condition value are stated, and

The order converting unit removes first filter condition in first query statement from the if-clause With first operator, and first query statement is rewritten as based on second filter condition come from the described 3rd Form obtains first query result.

14. equipment as claimed in claim 10, wherein, first query result includes the data that the first data row collection closes Value, second query result includes the data value that the second data row collection closes, and first query statement includes condition Clause, the if-clause include at least first filter condition, and first filter condition is arranged in second data to be collected First form and second form are connected on each data row in conjunction, and

Second query statement is rewritten as the 3rd query statement by the form definition unit, and the 3rd query statement is used for The 3rd query result is obtained from second form, the 3rd query result is included at least on record identification row Data value, and the definition statement of the 3rd form is generated based on the 3rd query statement.

15. equipment as claimed in claim 14, wherein, the order converting unit is in first query statement, by institute The first filter condition is stated to be rewritten as connecting first form and described the on record identification row by the 3rd operator Three forms, and first query statement is rewritten as closing based on the connection between first form and the 3rd form It is to obtain first query result from first form and the 3rd form.

16. equipment as claimed in claim 14, further includes：

Path selection unit, for the order converting unit by first querying command be converted to the second querying command it Afterwards, first filter condition is included as unique filter condition in response to the if-clause, based on the described second inquiry It orders to generate access path, to perform the access path in running environment, so as to obtain from the basic form First query result.

17. equipment as claimed in claim 16, wherein, the path selection unit is right in response to generating the access path Data value on record identification row is ranked up, and sequentially accesses the basic form according to ranking results, and from The data that first data row collection pointed by the data value on the record identification row closes are obtained in the basis form Value, as first query result.

18. equipment as claimed in claim 10, further includes：

Path selection unit, for the order converting unit by first querying command be converted to the second querying command it Afterwards, the first set of access path is generated based on first querying command, is visited based on second querying command to generate It asks the way the second set in footpath, calculates the executory cost of each access path in the first set and second set, and base Optimal access path is selected in the executory cost, to perform the optimal access path in running environment, thus from First query result is obtained in the basis form.