CN104765731A

CN104765731A - Database query optimization method and equipment

Info

Publication number: CN104765731A
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: 2015-07-08
Anticipated expiration: 2034-01-02
Also published as: CN104765731B

Abstract

The invention discloses a database query optimization method and equipment. The method comprises the steps: receiving a first query command, wherein the first query command at least comprises first and second query statements; the second query statement acquires a second query result from a second sheet; the first query statement acquires a first query result from a first sheet by means of the second query result and a first filtering condition; and the first and second sheets point to the same basic sheet in a database; generating a definition statement of a third sheet based on the second query statement, wherein the third sheet is adopted to store a third query result, and the third query result comprises the first query result and at least one of numeric values on an identity recording column; and replacing the second query statement by the definition statement, rewriting the first query statement based on the third sheet, and converting the first query command into the second query command. Therefore, the data query operation is realized at lower computational cost.

Description

Database inquiry optimization method and apparatus

Technical field

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

Background technology

In recent years, along with the high speed development in infotech and market, data management presents that data total amount constantly increases gradually, data layout and the complicated all the more trend of data type.In order to make user can in the data of magnanimity the data of quick obtaining required for them, database technology is arisen at the historic moment.

In order to obtain the information needed for user from database, user needs to use querying command to complete a series of query manipulation.Typically, a querying command generally includes multiple queries statement, be used to specify the querying condition being applicable to one or more data rows in database, search with box lunch the record meeting querying condition in a database (or to be referred to as, data line) time, return the data value of the one or more data rows among this record needed for user, as Query Result.

For many years, business database manufacturer is devoted to the query performance improving database always.For this purpose, have developed database query optimizer (optimizer).This query optimizer is an ingredient of data base management system (DBMS), for assessment of the querying command of user's input, and finds best access path, for performing query manipulation according to the various Different factor affecting query performance.

But be limited to prior art, query optimizer is when performing Optimum Operation for some querying command, and its performance is unsatisfactory.Particularly, two query statements are at least comprised in these querying commands, these two query statements are with reference to same form, and when the Query Result of a query statement will obtain based on the Query Result of another query statement, existing query optimizer cannot be optimized it effectively.

Summary of the invention

In view of above problem, propose the present invention.An object of the present invention is to provide a kind of database inquiry optimization method and apparatus, it can consider the characteristic from the querying command connected of crossing over query block, save and search index by data value in the prior art and search all intermediate steps of RID according to index, make to decrease the system resource required for whole query manipulation.

According to an aspect of the present invention, provide a kind of database inquiry optimization method, comprise: receive the first querying command, described first query statement is used for obtaining the first Query Result from the first form, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form; Generate the definition statement of the 3rd form based on described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row; And in described first querying command, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form, thus described first querying command is converted to the second querying command.

According to a further aspect in the invention, provide a kind of database inquiry optimization equipment, comprise: order receiving element, for receiving the first querying command, described first querying command at least comprises the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form; Form definition unit, for generating the definition statement of the 3rd form based on described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row; And order converting unit, for in described first querying command, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form, thus described first querying command is converted to the second querying command.

Adopt according to database inquiry optimization method and apparatus of the present invention, query semantics conversion can be carried out to having the querying command certainly connected crossing over query block, subquery block is merged in father's query block as an interim form, make compared with prior art, can by the lower query manipulation be calculated to be in original fulfillment database, thus improve the execution efficiency of querying command, save the working time of querying command, improve the Consumer's Experience when data base querying.

Accompanying drawing explanation

In conjunction with the drawings disclosure illustrative embodiments is described in more detail, above-mentioned and other object of the present disclosure, Characteristics and advantages will become more obvious, wherein, in disclosure illustrative embodiments, identical reference number represents same parts usually.

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

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

Fig. 3 A shows the form of the first querying command according to the first concrete example.

Fig. 3 B shows the form of the first querying command according to the second concrete example.

Fig. 3 C shows the form of the first querying command according to the 3rd concrete example.

Fig. 3 D shows the form of the first querying command according to the 4th concrete example.

Fig. 3 E shows the form of the first querying command according to the 5th concrete example.

Fig. 3 F shows the form of the first querying command according to the 6th concrete example.

Fig. 3 G shows the form of the first querying command according to the 7th concrete example.

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

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

Fig. 6 shows the process flow diagram of the database inquiry optimization method according to the embodiment of the present invention.

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

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

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

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

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

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

Fig. 9 A shows the performance parameter performing the access path generated according to prior art in running environment.

Fig. 9 B shows the performance parameter performing the access path generated according to the first embodiment in running environment.

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

Embodiment

Show preferred implementations more of the present disclosure in the accompanying drawings, describe these preferred implementations in more detail below with reference to accompanying drawings.But, the disclosure can be realized in a variety of manners, its not should limit by the embodiment of setting forth here.On the contrary, provide these embodiments to be to make the disclosure more thorough and complete, and the scope of the present disclosure intactly can be conveyed to those skilled in the art.

Person of ordinary skill in the field knows, various aspects of the present invention can be implemented as system, method or computer program.Therefore, various aspects of the present invention can be implemented as following form, that is: hardware embodiment, completely Software Implementation (comprising firmware, resident software, microcode etc.) completely, or the embodiment that hardware and software aspect combines, " circuit ", " module " or " system " can be referred to as here.In addition, in certain embodiments, various aspects of the present invention can also be embodied as the form of the computer program in one or more computer-readable medium, comprise computer-readable program code in this computer-readable medium.

The combination in any of one or more computer-readable medium can be adopted.Computer-readable medium can be computer-readable signal media or computer-readable recording medium.Computer-readable recording medium such as may be-but not limited to-the system of electricity, magnetic, optical, electrical magnetic, infrared ray or semiconductor, device or device, or combination above arbitrarily.The example more specifically (non exhaustive list) of computer-readable recording medium comprises: the combination with the electrical connection of one or more wire, portable computer diskette, hard disk, random-access memory (ram), ROM (read-only memory) (ROM), erasable type programmable read only memory (EPROM or flash memory), optical fiber, Portable, compact dish ROM (read-only memory) (CD-ROM), light storage device, magnetic memory device or above-mentioned any appropriate.In this document, computer-readable recording medium can be any comprising or stored program tangible medium, and this program can be used by instruction execution system, device or device or be combined with it.

The data-signal that computer-readable signal media can comprise in a base band or propagate as a carrier wave part, wherein carries computer-readable program code.The data-signal of this propagation can adopt various ways, comprises the combination of---but being not limited to---electromagnetic signal, light signal or above-mentioned any appropriate.Computer-readable signal media can also be any computer-readable medium beyond computer-readable recording medium, and this computer-readable medium can send, propagates or transmit the program for being used by instruction execution system, device or device or be combined with it.

The program code that computer-readable medium comprises can with any suitable medium transmission, comprises that---but being not limited to---is wireless, wired, optical cable, RF etc., or the combination of above-mentioned any appropriate.

The computer program code operated for performing the present invention can be write with the combination in any of one or more programming languages, described programming language comprises object oriented program language-such as Java, Smalltalk, C++ etc., also comprises conventional process type programming language-such as " C " language or similar programming language.Program code can fully perform on the user computer, partly perform on the user computer, as one, independently software package performs, partly part performs on the remote computer or performs on remote computer or server completely on the user computer.In the situation relating to remote computer, remote computer can by the network of any kind---comprise LAN (Local Area Network) (LAN) or wide area network (WAN)-be connected to subscriber computer, or, outer computer (such as utilizing ISP to pass through Internet connection) can be connected to.

Below with reference to the process flow diagram of the method according to the embodiment of the present invention, device (system) and computer program and/or block diagram, the present invention is described.Should be appreciated that the combination of each square frame in each square frame of process flow diagram and/or block diagram and process flow diagram and/or block diagram, can be realized by computer program instructions.These computer program instructions can be supplied to the processor of multi-purpose computer, special purpose computer or other programmable data treating apparatus, thus produce a kind of machine, make these computer program instructions when the processor by computing machine or other programmable data treating apparatus performs, create the device of the function/action specified in the one or more square frames in realization flow figure and/or block diagram.

Also can these computer program instructions be stored in computer-readable medium, these instructions make computing machine, other programmable data treating apparatus or other equipment work in a specific way, thus the instruction be stored in computer-readable medium just produces the manufacture (article of manufacture) of the instruction of the function/action specified in the one or more square frames comprised in realization flow figure and/or block diagram.

Also can computer program instructions be loaded on computing machine, other programmable data treating apparatus or miscellaneous equipment, make to perform sequence of operations step on computing machine, other programmable data treating apparatus or miscellaneous equipment, to produce computer implemented process, thus the instruction performed on computing machine or other programmable device is made to provide the process of the function/action specified in the one or more square frames in realization flow figure and/or block diagram.

Fig. 1 shows the block diagram of the exemplary computer system/server 12 be suitable for for realizing embodiment of the present invention.The computer system/server 12 of Fig. 1 display is only an example, should not bring any restriction to the function of the embodiment of the present invention and usable range.

As shown in Figure 1, computer system/server 12 shows with the form of universal computing device.The assembly of computer system/server 12 can include but not limited to: one or more processor or processing unit 16, system storage 28, connects the bus 18 of different system assembly (comprising system storage 28 and processing unit 16).

Bus 18 represent in a few class bus structure one or more, comprise memory bus or Memory Controller, peripheral bus, AGP, processor or use any bus-structured local bus in multiple bus structure.For example, these architectures include but not limited to ISA(Industry Standard Architecture) bus, MCA (MAC) bus, enhancement mode isa bus, VESA's (VESA) local bus and periphery component interconnection (PCI) bus.

Computer system/server 12 typically comprises various computing systems computer-readable recording medium.These media can be any usable mediums can accessed by computer system/server 12, comprise volatibility and non-volatile media, moveable and immovable medium.

System storage 28 can comprise the computer system-readable medium of volatile memory form, such as random-access memory (ram) 30 and/or cache memory 32.Computer system/server 12 may further include that other is removable/immovable, volatile/non-volatile computer system storage medium.Only as an example, storage system 34 may be used for reading and writing immovable, non-volatile magnetic media (Fig. 1 does not show, and is commonly referred to " hard disk drive ").Although not shown in Fig. 1, the disc driver that removable non-volatile magnetic disk (such as " floppy disk ") is read and write can be provided for, and to the CD drive that removable anonvolatile optical disk (such as CD-ROM, DVD-ROM or other light medium) is read and write.In these cases, each driver can be connected with bus 18 by one or more data media interfaces.Storer 28 can comprise at least one program product, and this program product has one group of (such as at least one) program module, and these program modules are configured to the function performing various embodiments of the present invention.

There is the program/utility 40 of one group of (at least one) program module 42, can be stored in such as storer 28, such program module 42 comprises---but being not limited to---operating system, one or more application program, other program module and routine data, may comprise the realization of network environment in each or certain combination in these examples.Function in program module 42 embodiment that execution is described in the invention usually and/or method.

Computer system/server 12 also can with one or more external unit 14(such as keyboard, sensing equipment, display 24 etc.) communicate, also can make with one or more devices communicating that user can be mutual with this computer system/server 12, and/or communicate with any equipment (such as network interface card, modulator-demodular unit etc.) making this computer system/server 12 can carry out communicating with other computing equipment one or more.This communication can be passed through I/O (I/O) interface 22 and carry out.Further, computer system/server 12 can also such as, be communicated by network adapter 20 and one or more network (such as Local Area Network, wide area network (WAN) and/or public network, the Internet).As shown in the figure, network adapter 20 is by bus 18 other module communication with computer system/server 12.Be understood that, although not shown, other hardware and/or software module can be used in conjunction with computer system/server 12, include but not limited to: microcode, device driver, redundant processing unit, external disk drive array, RAID system, tape drive and data backup storage system etc.

Below, with reference to Fig. 2, the Database Systems according to the embodiment of the present invention are described.

The core institution of a kind of ideal data processing that Database Systems are needs for adapting to data processing and grow up.High speed processing ability and the mass storage of the computer system/server 12 shown in Fig. 1 provide the condition realizing data management robotization.

As shown in Figure 2, these Database Systems are made up of database, data administrator, hardware and software usually.Data base management system (DBMS) (DBMS) is the kernel software of Database Systems.DBMS provides data definition language (DDL) (DDL) and data manipulation language (DML) (DML).DDL allows the Advance of Three Classes Mode Structure of user's definition database, two-stage reflection and integrity constraint and the constraints such as restriction of maintaining secrecy; And DML allows user to realize the adding of data, deletion, renewal, the operation such as inquiry.

In organizational structure, this DBMS can comprise resolver, optimizer and running environment (runtime).Resolver is also called query analyzer, for receiving one or more querying commands of user's input, carrying out grammatical analysis, and build query tree to this querying command.Optimizer is used for, after the querying command of user's input passes through to resolve, carrying out Performance Evaluation, and find best access path, for performing query manipulation according to the various Different factor affecting query performance to it.Running environment is used for building appropriate operating structure according to the access path of the output of optimizer by the internal code generating database support, to realize the various operations to database of user's expectation.

As described in the background art, existing query optimizer imperfection.Such as, when a querying command at least comprises two query statements, these two query statements are with reference to same form, and when the Query Result of a query statement will obtain based on the Query Result of another query statement, this query optimizer has been difficult to optimization task.

For the ease of understanding, the improvement of the defect of the query optimizer of prior art and the database inquiry optimization method and apparatus according to the embodiment of the present invention will be described in following scene, wherein suppose that involved database is relational database, and data base management system (DBMS) uses Structured Query Language (SQL) (SQL) as data input and the interface of management, operates this relational database.

It should be noted that, although specifically describe prior art and technical scheme of the present invention by the scene of application SQL and relational database here, the present invention is not limited thereto.This database also can be other databases such as hierarchical data base, network database.Further, this SQL also can use other data base query languages of existing or following appearance to substitute.

In this manual, for convenience of description, the particular type querying command existing query optimizer cannot be able to optimized is referred to as the first querying command.This first querying command at least can comprise the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form.

Typically, described first Query Result can comprise the data value in the first data rows set, and described second Query Result can comprise the data value in the second data rows set.Each in described first data rows set and described second data rows set can comprise at least one data rows, and the data rows in two data rows set can be identical or different.

In addition, described first query statement can comprise if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form.

For the ease of understanding, the form of this first querying command will be described in concrete example.

As shown in Figure 3A, this first querying command can be made up of the first query statement and the second query statement.

This first query statement is selected by keyword SELECT() statement that forms.SELECT statement is used for choosing data from form.This first query statement also comprises by keyword WHERE(wherein) if-clause that forms, for this second query statement of connection.WHERE clause is for specifying the filtercondition choosing data from form.In the first concrete example, described if-clause only comprises a filtercondition, i.e. the first filtercondition, described first filtercondition can by predicate IN each data rows in described second data rows set connects described first form and described second form.

The function of this first query statement is, with reference to the first form A pointing to basic form TEST1, when the data value on C1 row in a certain data line is among the second Query Result that the second query statement returns, return the data value in this data line on C3 row to user.In other words, this first query statement is used for carrying out query manipulation in the first form A, finds qualified one or more data value, and returns this data value to user.Further, the content item stored in this first form A is identical with the content item in basic form TEST1.Such as, this first form can be the another name (alias) of basic form TEST1 simply, or also can be mirror image or a copy etc. of basic form TEST1.

This second query statement is selected by keyword SELECT(equally) statement that forms.Further, this second query statement can comprise divided into groups by keyword GROUP BY() statement that forms and had by HAVING() clause that forms.GROUP BY statement is used in conjunction with aggregate function (that is, the COUNT(counting in this example)), to divide into groups to result set according to one or more row.

The function of this second query statement is, with reference to the second form B pointing to basic form TEST1, divides into groups according to the data value on C1 row, and adds up the number of times of a certain data value appearance, when its occurrence number is greater than 3, returns this data value to user.

Because the result based on the second query statement in this first querying command obtains the result of the first query statement, so this first query statement can be called it is father's query block, and this second query statement is called it is subquery block.Due to the same form TEST1 in the first and second form A and the B equal points relationship database in the first and second query statements, so TEST1 can be called basic form.In addition, because the first filtercondition has A.C1IN (SELECT B.C1 ...) form, so can think that the first and second form A with B are connected by implicit expression predicate A.C1=B.C1.Therefore, due to above-mentioned characteristic, can think that the first querying command is equivalent in fact a kind of querying command with the connection certainly of crossing over query block.

It should be noted that, although described the types entail of the first querying command by the first concrete example above, the present invention is not limited thereto.Such as, this first querying command can comprise the query statement more than two; Each query statement can be inserted by such as INSERT(), UPDATE(upgrades), DELETE(deletes) etc. other keywords form; Each query statement can comprise less or more filterableness keyword and/or clause; Also can with reference to other forms one or more in this each query statement; Filtercondition in WHERE clause by predicate IN or other predicates (such as, for checking the BETWEEN of data value whether in the scope of specifying ... AND(exists ... between), for checking that whether data value similar with the LIKE(of the pattern match of specifying) and for checking that the EXIST(whether data value exists exists)) in more than one data rows, be connected described first form and described second form; And the first and second query statements can obtain the Query Result in more than one data rows from the first and second forms.

As shown in Figure 3 B, in the first query statement, except the first filtercondition shown in Fig. 3 A, if-clause can also comprise the second filtercondition, described second filtercondition by logical operator AND(with) come be combined with described first filtercondition, and by comparison operator < connect C2 arrange and data value 3.Obviously, this if-clause can also comprise other filterconditions.These other filterconditions can by other logical operator (such as, OR(or), non-(NOT)) be connected with the first and second filterconditions, and can be accorded with by other comparison operators or arithmetic operator and connect other data rows and data value.

As shown in Figure 3 C, in the first query statement, the first Query Result can comprise all row in form TEST1.In the second query statement, remove the querying condition be made up of keyword GROUP BY statement, and with the addition of the if-clause be made up of keyword WHERE, for the data record that the data value removed on C2 row is less than 5.Obviously, this if-clause is optional.When not comprising this if-clause, the second form is without any the total data value returned with filtering on C1 row.

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

As shown in FIGURE 3 E, on the basis of Fig. 3 D, in the first query statement, filtercondition in WHERE clause is connected described first form and described second form by predicate IN on two data rows C1 with C3, to make to only have among the data value in a certain data line of the second form that is among the second Query Result when the data value in a certain data line of the first form on C1 row on C1 row, when data value simultaneously in this data line of the first form on C3 row is among the data value in this data line of the second form among this second Query Result on C3 row, just return the data value in this first data line on all row to user.

Fig. 3 F shows the form of the first querying command according to the 6th concrete example, and Fig. 3 G shows the form of the first querying command according to the 7th concrete example.

As illustrated in Figure 3 F, in the first query statement, on data rows C1, connect described first form and described second form by predicate EXIST.Because the first querying command shown in Fig. 3 F is equivalent in fact the first querying command shown in Fig. 3 G, so the first querying command shown in Fig. 3 G obviously meets the types entail of the first querying command, therefore, the first querying command shown in Fig. 3 F can adopt equally and optimize efficiently according to the database inquiry optimization method and apparatus of the embodiment of the present invention.

In the prior art, receive in Fig. 3 A to Fig. 3 G after the first querying command shown in any one at DBMS, first resolver carries out syntax parsing to it.Due to performance optimization cannot be completed to the first querying command, so this query optimizer itself generates unique access path according to this first querying command, and it is outputted in running environment.Finally, running environment performs this access path, to obtain the data value that user expects.

Below, the generative process of the access path of prior art is described with reference to Fig. 4 and Fig. 5.

Fig. 4 shows the exemplary contents of the basic form in Fig. 3 A, and Fig. 5 shows the access path generated according to prior art.

As shown in Figure 4, this basic form TEST1 comprises 10 data lines and 4 data rows.Each data value in tentation data row RID is unduplicated.Such as, this RID row can have unique (UNIQUE) attribute constraint, so that the every bar data record (data line) in the table of unique identification data storehouse.Alternatively, also this RID row can be set to the major key (PRIMARYKEY) of this basic form TEST1, it has the unique attribute constraint of definition automatically.In addition, suppose in basic form TEST1, at least creating on data rows C1 has index.When not reading whole table (that is, full table scan), index scanning makes database application can search data quickly.

In the prior art, query optimizer, after receiving the first querying command, will generate following access path: first perform the second query statement, then perform the first query statement according to the second Query Result.

As shown in Figure 5, in the process of execution second query statement, first using the copy of basic form TEST1 as the second form B(namely, the content in the second form B is identical with basic form TEST1).Then, the second form B is divided into groups on C1 row according to GROUP BY statement.Owing to only having three data values 1,2,3, so the second form B is divided into corresponding three groups of data by GROUP BY statement on the C1 row of the second form B.Next, according to HAVING clause, these three groups of data are filtered, to obtain the second Query Result.For convenience of description, the form shown in Fig. 4 be equivalent to complete division operation after the result view of the second form B.

Particularly, first, for the situation of C1=1, when traversing fourth line (that is, RID is X'0004') from the first row (that is, RID is X'0001') of the second form B, what can count C1=1 is counted as 4, is namely greater than 3.At this moment, can judge that data value 1 belongs to the second result for retrieval, and thus this data value be added in the temporary table for storing the second result for retrieval provisionally.

Then, for the situation of C1=2, when traversing the 7th row (that is, RID is X'0007') from the fifth line (that is, RID is X'0005') of the second form B, what can count C1=2 is counted as 2, is namely less than 3.At this moment, can judge that data value 1 does not belong to the second result for retrieval, and thus this data value not added in this temporary table.

Finally, for the situation of C1=3, when traversing the tenth row (that is, RID is X'000A') from the 7th row (that is, RID is X'0007') of the second form B, what can count C1=3 is counted as 4, is namely greater than 3.At this moment, can judge that data value 3 also belongs to the second result for retrieval, and thus this data value be added in the temporary table for storing the second result for retrieval provisionally.

Therefore, after executing the second query statement, will generate following temporary table, this temporary table comprises 2 data lines and 1 data rows, and namely this temporary table comprises two data values 1 and 3 on row C1.

Thereafter, in the process of execution first query statement, first using the copy of basic form TEST1 as the first form A(namely, the content in the first form A is identical with basic form TEST1).Then, filter line by line on the C1 row of the first form A according to WHERE clause, to find the data value be in temporary table on the C1 row of the first form A.When finding such data value, the index by creating on C1 row is found the record identifier (RID) of this data record.Such as, the RID of a certain data record can comprise this data record page number in the table and side-play amount, for accurately locating this data record in the table.Finally, inquire about according to this RID and return the data value in this data line on C3 row to user.

Particularly, first, when starting to travel through from the first row (that is, RID is X'0001') of the first form A, can find that the data value on its C1 row is 1.Because data value 1 is in temporary table, so think that this data record meets querying condition.At this moment, namely the index by creating on C1 row is found the RID(of the first row of the first form A, X'0001').Then, inquire about according to this RID and return the data value F in this first data line on C3 row to user.

The rest may be inferred.Due to the first form A the second row (namely, RID is X'0002') to fourth line (namely, RID is X'0004') in data value on C1 row be 1, so RID can be found similarly by index, to position data record, and return data value C, E and the F in this each data record on C3 row eventually to user.

Next, when traversing fifth line (that is, RID is X'0005') of the first form A, can find that the data value on its C1 row is 2.Because data value 2 is not in temporary table, so think that this data record does not meet querying condition, and jump to next line continuation inquiry.In this manner, until the total data behavior having traveled through the first form stops.

Therefore, after executing the first query statement, will generate following first Search Results, this first Search Results comprises 9 data lines and 1 data rows, and namely this temporary table will comprise nine data values F, C, E, F, E, G, H, G and F on C3 row.

By analysis, the present inventor recognizes, first the above-mentioned access path that query optimizer generates will search index according to the data value in temporary table, RID is searched again according to index, finally in basic form TEST1, obtain the data value needed for user according to RID, obviously this is that efficiency is extremely low.

Hereinafter, by for the technical matters existed in prior art, with reference to accompanying drawing, the database inquiry optimization method according to the embodiment of the present invention is described.

As shown in Figure 6, this database inquiry optimization method can comprise:

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

In an embodiment of the present invention, after DBMS receives user's querying command of user's input, first syntax parsing and query tree structure are carried out to it.After this user's querying command conforms with SQL grammatical norm in discovery, first can judge that whether this user's querying command is the first querying command according to the embodiment of the present invention.Described first querying command at least comprises the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form.

Typically, described first Query Result can comprise the data value in the first data rows set, described second Query Result comprises the data value in the second data rows set, and described first query statement comprises if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form.

When judging that this user's querying command is not the first querying command according to the embodiment of the present invention, can conventionally be optimized this user's querying command and access path generation, and this method terminates.When judging that this user's querying command meets the types entail of the first querying command, this method proceeds to step S120, continues to perform.

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

After judging that this user's querying command is the first querying command according to the embodiment of the present invention, can be optimized this first querying command based on design of the present invention.Particularly, the definition statement of the 3rd form can be generated according to described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row.

Such as, first, according to analysis result, this first querying command can be divided into multiple queries statement.For convenience of description, suppose that this first querying command only comprises two query statements as shown in Fig. 3 A to Fig. 3 G any one, i.e. the first query statement and the second query statement.

Then, described second query statement can be rewritten as the 3rd query statement, described 3rd query statement is used for obtaining described 3rd Query Result from described second form.Depend on different embodiments, described 3rd Query Result can comprise the data value in different pieces of information row or data rows set.

Finally, the definition statement of the 3rd form can be generated based on described 3rd query statement.Such as, the 3rd form can create, for storing the interim form of the 3rd Query Result temporarily in memory headroom.This interim form can be virtual tables, also can be temporary file, or even a series of data value.

In step s 130, which, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form.

After generating the 3rd form comprising the 3rd Query Result, the second original query statement can be removed from the first querying command, the definition statement of the 3rd form is added to the position of the second query statement, and come differently to rewrite the first query statement, described first querying command is converted to the second querying command after being optimized according to the embodiment of the present invention according to the 3rd different Query Result that the 3rd form stores.

Subsequently, but can according to from the first querying command in form different this functionally identical second querying command carry out generative nature can outstanding access path, and it to be outputted in running environment.Finally, running environment can perform this access path, to obtain the data value that user expects.

As can be seen here, adopt according to database inquiry optimization method and apparatus of the present invention, query semantics conversion can be carried out to having the querying command certainly connected crossing over query block, subquery block is merged in father's query block as an interim form, make compared with prior art, can by the lower query manipulation be calculated to be in original fulfillment database, thus improve the execution efficiency of querying command, save the working time of querying command, improve the Consumer's Experience when data base querying.

Hereinafter, will in different embodiments, the concrete operations in step S120 and S130 are described particularly.

First, the first embodiment of the present invention will be described.In a first embodiment, in the step s 120, described second query statement can be rewritten as the 3rd query statement, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least can comprise the data value on record identification row, and each data value in described record identification row is used for the data line identifying described data value place uniquely; And the definition statement of the 3rd form is generated based on described 3rd query statement.In step s 130, which, in described first querying command, the definition statement of the 3rd form can be utilized to replace described second query statement, meanwhile, described first filtercondition can be rewritten as and on described record identification row, connect described first form and described 3rd form by the 3rd operational symbol; And described first query statement is rewritten as obtains described first Query Result based on the annexation between described first form and described 3rd form from described first form and described 3rd form.

For convenience of description, suppose that this first querying command as shown in Figure 3A in the first embodiment of the invention, and basic form in Fig. 3 A as shown in Figure 4.

As shown in Figure 7 A, the 3rd query statement only can obtain the data value data rows RID from the second form B.Then, the data value on this data rows RID can be utilized, and by keyword WITH ... AS(will ... be defined as ...) form the definition statement of the 3rd form DSNVB (02).That is, the 3rd generated in the step s 120 form only can comprise the data value on record identification row RID, thus makes the 3rd form committed memory space as little as possible.

But, the present invention is not limited thereto.Obviously, except the data value on data rows RID, 3rd form can also preserve the data value in other data rows one or more (such as, data rows C1, C2 and/or C3) or even the data value in all data rows, so that the data of the 3rd form are more complete.

In addition, due in the first querying command as shown in Figure 3A, the if-clause be made up of WHERE clause only comprises a filtercondition, i.e. the first filtercondition, i.e. A.C1IN (SELECTB.C1 ...), so in the second generated in step s 130, which querying command, the first query statement utilizing the 3rd form DSNVB (02) to rewrite only will comprise a filtercondition.

Particularly, as shown in Figure 7 A, can utilize comparison operator=come on data rows RID connect the 3rd form DSNVB (02) and the first form A, and make revised first query statement simultaneously with reference to these two forms, Query Result needed for user can be obtained based on the annexation between two form DSNVB (02) in the first form A to make described first query statement.

Next, after the first original querying command is optimized for the second querying command, can when described if-clause comprises the first filtercondition as unique filtercondition, based on the second querying command after conversion generate access path often there is minimum assessing the cost, therefore, directly can generate access path based on the second querying command, to perform described access path in running environment, thus obtain described first Query Result from described basic form.

But, the present invention is not limited thereto.In rare cases, depend in different content in basic form and related data row whether created index, based on the first original querying command generate access path and also likely there is minimum assessing the cost.Therefore, in a first embodiment, also can generate many access path based on the second querying command after the first original querying command and conversion, and assessment of cost be carried out to many access path, to perform the access path with least cost in running environment.

Below, the generative process of the access path of the first embodiment is described with reference to Fig. 4 and Fig. 8 A.

Because the if-clause be made up of WHERE clause in a first embodiment comprises the first filtercondition as unique filtercondition, so query optimizer is after receiving the second querying command, can learn that the performance of the access path generated according to this second querying command must be better than the access path generated according to the first querying command without doubt, therefore this query optimizer directly can generate following access path according to this second querying command: first define the 3rd form DSNVB (02), then perform the first query statement.

As shown in Figure 8 A, the similar process performing the second query statement in the process of the 3rd form and prior art is defined.That is, first using the copy of basic form TEST1 as the second form B.Then, the second form B is divided into groups on C1 row according to GROUP BY statement.Owing to only having three data values 1,2,3, so the second form B is divided into corresponding three groups of data by GROUP BY statement on the C1 row of the second form B.Next, according to HAVING clause, these three groups of data are filtered, to obtain the 3rd Query Result.

Particularly, first, for the situation of C1=1, when traversing fourth line (that is, RID is X'0004') from the first row (that is, RID is X'0001') of the second form B, what can count C1=1 is counted as 4, is namely greater than 3.At this moment, with in prior art differently, not data value 1 is added in the 3rd form DSNVB (02), but data value X'0001' to the X'0004' on RID row in the first row to fourth line is added in the 3rd form DSNVB (02).

Then, for the situation of C1=2, when traversing the 7th row (that is, RID is X'0007') from the fifth line (that is, RID is X'0005') of the second form B, what can count C1=2 is counted as 2, is namely less than 3.At this moment, any data value is not added in the 3rd form DSNVB (02).

Finally, for the situation of C1=3, when traversing the tenth row (that is, RID is X'000A') from the 7th row (that is, RID is X'0007') of the second form B, what can count C1=3 is counted as 4, is namely greater than 3.Further, traversal the second form B finds further, and in the tenth a line (that is, RID is X'000B'), the data value of C1 row is also 3.At this moment, data value X'0007' to the X'000B' on RID row in the 7th row to the tenth a line is added in the 3rd form DSNVB (02).

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

Thereafter, in the process of execution first query statement, first using the copy of basic form TEST1 as the first form A.Then, according to WHERE clause, the first form A is connected on RID arranges with the 3rd form DSNVB (02), to find the data value be in the 3rd form DSNVB (02) on the RID row of the first form A.When finding such data value, inquiring about according to this RID and returning the data value in this data line on C3 row to user.

Particularly, first, when starting to travel through from the first row (that is, RID is X'0001') of the first form A, can find that the data value on its RID row is X'0001'.Because data value X'0001' is in the 3rd form DSNVB (02), so think that this data record meets querying condition.At this moment, inquire about according to this RID and return the data value F in this first data line on C3 row to user.

Then, the rest may be inferred.Due to the first form A the second row (namely, RID is X'0002') to fourth line (namely, RID is X'0004') in data value on RID row be all in the 3rd form DSNVB (02), so data value C, E and the F in this each data record on C3 row can be returned to user similarly.

Next, when traversing fifth line (that is, RID is X'0005') of the first form A, can find that the data value on its RID row is X'0005'.Because data value X'0005' is not in the 3rd form DSNVB (02), so think that this data record does not meet querying condition, and jump to next line continuation inquiry.In this manner, until the total data behavior having traveled through the first form stops.

Therefore, after executing the first query statement, will generate following first Search Results, this first Search Results comprises 9 data lines and 1 data rows, and namely this temporary table will comprise nine data values F, C, E, F, E, G, H, G and F on C3 row.Obviously, the first Search Results obtained according to the second querying command is identical with the first Search Results obtained according to the first querying command.

As can be seen here, compared with prior art, in above-mentioned access path generated in a first embodiment, when definition the 3rd form, the RID of the data record that direct acquisition is required when execution the first query statement, and when execution the first query statement, in basic form TEST1, obtain the data value needed for user according to RID.

That is, first embodiment fully takes into account the characteristic from the querying command connected of crossing over query block, in the implementation of the second querying command, after judging that a certain data value belongs to the second result for retrieval, directly in the 3rd form, record comprises the RID of all data records of this data value, and inquire about according to RID and return the first Query Result to user, thus save and search index by data value and search all intermediate steps of RID according to index, make to decrease the system resource required for whole query manipulation.

Below, with reference to Fig. 9 A and Fig. 9 B, be compared with prior art the with performance boost of the first embodiment is described.

Fig. 9 A shows the performance parameter performing the access path generated according to prior art in running environment, and Fig. 9 B shows the performance parameter performing the access path generated according to the first embodiment in running environment.

As shown in Figure 9 A, based on a certain specific experiment condition, wherein supposition uses form based on a certain data form, and use a certain user's querying command as the first querying command, performing assessing the cost required for the access path that generates according to prior art in running environment is approximately 3.1E7 microsecond (ms).But as shown in Figure 9 B, based on identical experiment condition, performing assessing the cost required for the access path that generates according to the first embodiment in running environment is approximately 1.7E7 microsecond (ms).

Visible by this group experimental data, assessing the cost of the access path saving about 26% that the access path adopting the first embodiment to generate can generate than employing prior art, thus we can draw to draw a conclusion, namely the method for the first embodiment can play obvious performance boost for the querying command certainly connected crossing over query block.And, due in the query manipulation that such querying command is present in needed for user widely (according to the statistics of one group of random data, the probability of occurrence of such querying command is about about 12%), so the first embodiment is experienced greatly promoting the daily inquiry of user for database.

It should be noted that, reflected the change of search efficiency by total calculating duration of data query operation here.But the processing time, system resource etc. that also can consume according to the I/O of Database Systems and CPU (central processing unit) (CPU) that assess the cost is added up respectively.

In addition, in a first embodiment, the present inventor is also noted that typically, the RID in the 3rd form DSNVB (02) obtained may be not tactic in the process generating access path.Such as, the data value that the 3rd form DSNVB (02) comprises on data rows RID may be X'0004', X'0001', X'000A', X'0002' ...

Obviously, if obtain data value in a database according to RID simply, then certainly will cause first obtaining data value at the 4th article of data record being arranged in underlying table lattice middle, then move to the beginning of basic form, data value is obtained from the 1st article of data record, next, jump to again the rear portion of basic form, from the 10th article of data record, obtain data value, subsequently, turn back to again the front portion of basic form, from the 2nd article of data record, obtain data value, and so forth.Like this, the access speed of data form certainly will be caused to slow down, access efficiency reduces.

Therefore, in one embodiment, can adopt the list mode of (listprefetch) of looking ahead in basic form, obtain data value needed for user in a first embodiment.The access path that this list mode of looking ahead generates is not obtain data value in a database according to obtained RID simply, but first the data value on described record identification row is sorted, described basic form is sequentially accessed according to ranking results, and the data value obtained from described basic form in the described first data rows set pointed by the data value on described record identifier, as described first Query Result.

Like this, before obtaining data value in a database according to RID, the data value order that the 3rd form DSNVB (02) can be comprised on data rows RID is arranged as X'0001' to X'0004' and X'0007' to X'000B'.Then, in basic form, in turn obtain data value according to vertical order, thus accelerate the access speed of data form, improve the execution efficiency of querying command further.

Above, describe the situation that the if-clause be made up of WHERE clause in the first querying command only comprises a filtercondition in a first embodiment, below, second embodiment of the present invention will be described, and the if-clause wherein in the first query statement can comprise more than one filtercondition.

For convenience of description, suppose that this first querying command as shown in Figure 3 B in second embodiment of the invention, and basic form in Fig. 3 B 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, the if-clause be made up of WHERE clause is except comprising the first filtercondition, i.e. A.C1IN (SELECTB.C1 ...) outside, also comprise the second filtercondition, i.e. A.C2<3, but the 3rd query statement still only can obtain the data value data rows RID from the second form B.That is, the 3rd generated in the step s 120 form only can comprise the data value on record identification row RID.

In addition, because this if-clause comprises two filterconditions, so in the second generated in step s 130, which querying command, the first query statement utilizing the 3rd form DSNVB (02) to rewrite also will comprise two filterconditions, wherein said second filtercondition by logical operator AND(with) come be combined with the first filtercondition, and by comparison operator < connect C2 arrange and data value 3.

Comparison diagram 7A and Fig. 7 B can find out, the second embodiment comprises and identical step S120 and S130 in the first embodiment.But, owing to comprising two filterconditions in a second embodiment, this will cause the situation producing at least two kinds of access path, performs the situation of the second filtercondition and perform the situation of the first filtercondition after first performing the second filtercondition when execution the first query statement after namely first performing the first filtercondition when execution the first query statement.

Therefore, after the first original querying command is optimized for the second querying command, can when described if-clause at least comprises the first and second filterconditions, the set of access path is generated based on described second querying command, and calculate the executory cost of each access path in this set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus obtain described first Query Result from described basic form.

As can be seen here, compared with the first embodiment, many access path can be generated in a second embodiment.Like this, data base management system (DBMS) can be weighed further to these many access path, with in the access path of optimum, obtains the data value needed for user according to RID in basic form TEST1.Therefore, the second embodiment is not perform each filtercondition in order simply, but optimally selects their execution sequence, thus provides further query optimization effect to user.

Above, describe in a first embodiment after judging that a certain data value belongs to the second result for retrieval, in the 3rd form, record comprises the RID of all data records of this data value, and inquire about according to RID and return the situation of the first Query Result to user, below, to describe the third embodiment of the present invention, wherein after judging that a certain data value belongs to the second result for retrieval, in the 3rd form, directly record comprises first Query Result that will return to user.

In the third embodiment, in the step s 120, described second query statement can be rewritten as the 3rd query statement, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, and described 3rd Query Result at least comprises the data value in described first data rows set; And the definition statement of the 3rd form is generated based on described 3rd query statement.In step s 130, which, in described first querying command, the definition statement of the 3rd form can be utilized to replace described second query statement, simultaneously, described first filtercondition is comprised as unique filtercondition in response to described if-clause, described 3rd Query Result only can comprise the data value in described first data rows set, and in described first query statement, removes described if-clause; And described first query statement is rewritten as obtains described first Query Result from described 3rd form.

For convenience of description, suppose that this first querying command as shown in Figure 3A in third embodiment of the invention, and basic form in Fig. 3 A as shown in Figure 4.

As seen in figure 7 c, the 3rd query statement only can obtain the data value data rows C3 from the second form B.Then, the data value on this data rows C3 can be utilized form the definition statement of the 3rd form DSNVB (02).That is, the 3rd generated in the step s 120 form only can comprise the data value on data rows C3, thus makes the 3rd form committed memory space as little as possible.

But, the present invention is not limited thereto.Obviously, except the data value on data rows C3,3rd form can also preserve the data value in other data rows one or more (such as, data rows C1, C2 and/or RID) or even the data value in all data rows, so that the data of the 3rd form are more complete.Especially, in one embodiment, the 3rd form can preserve the data value of data rows RID further, to ensure as necessary, can complete other data query operation that are same or other query statements efficiently.

In addition, due in the first querying command as shown in Figure 3A, the if-clause be made up of WHERE clause only comprises a filtercondition, i.e. the first filtercondition A.C1IN (SELECTB.C1 ...), so in the second generated in step s 130, which querying command, the first query statement utilizing the 3rd form DSNVB (02) to rewrite can directly remove whole if-clause.

Particularly, as seen in figure 7 c, the data value on C3 row has been included due to the 3rd form DSNVB (02), so Query Result needed for user can be returned directly to user, and without the need to performing any filter operation again.

Next, after the first original querying command is optimized for the second querying command, can when described if-clause comprises the first filtercondition as unique filtercondition, access path is generated based on described second querying command, to perform described access path in running environment, thus obtain described first Query Result from described basic form.

Below, the generative process of the access path of the 3rd embodiment is described with reference to Fig. 4 and Fig. 8 B.

Because the if-clause be made up of WHERE clause in the third embodiment comprises the first filtercondition as unique filtercondition, so query optimizer is after receiving the second querying command, directly can generate following access path according to this second querying command equally: first define the 3rd form DSNVB (02), then perform the first query statement.

Particularly, first, for the situation of C1=1, when traversing fourth line (that is, RID is X'0004') from the first row (that is, RID is X'0001') of the second form B, what can count C1=1 is counted as 4, is namely greater than 3.At this moment, with in the first or second embodiment differently, not that data value X'0001' to the X'0004' on RID row in the first row to fourth line is added in the 3rd form DSNVB (02), but data value F, C, E and the F on C3 row in the first row to fourth line is added in the 3rd form DSNVB (02).

Finally, for the situation of C1=3, when traversing the tenth row (that is, RID is X'000A') from the 7th row (that is, RID is X'0007') of the second form B, what can count C1=3 is counted as 4, is namely greater than 3.Further, traversal the second form B finds further, and in the tenth a line (that is, RID is X'000B'), the data value of C1 row is also 3.At this moment, data value E, G, H, G and the F on C3 row in the 7th row to the tenth a line is added in the 3rd form DSNVB (02).

Therefore, the 3rd form DSNVB (02) generated comprises 9 data lines and 1 data rows, and namely the 3rd form DSNVB (02) comprises nine data values F, C, E, F, E, G, H, G and F on data rows C3.

Thereafter, in the process of execution first query statement, all data values in the 3rd form DSNVB (02) can be returned directly to user.Obviously, the first Search Results obtained according to the second querying command is identical with the first Search Results obtained according to the first querying command.

As can be seen here, compared with prior art, in above-mentioned access path generated in the third embodiment, when definition the 3rd form, the first Query Result that direct acquisition is required when execution the first query statement, and when execution the first query statement, return this first Query Result, as the data value needed for user to user.Therefore, compared with the first embodiment, the 3rd embodiment only performs a table lookup operation, further decreases required the assessing the cost when execution the first querying command, improves search efficiency substantially.

Above, the situation describing the if-clause be made up of WHERE clause in the first querying command and only comprise a filtercondition is executed in example the 3rd,, will describe the fourth embodiment of the present invention below, the if-clause wherein in the first query statement can comprise more than one filtercondition.

In the fourth embodiment, in the step s 120, described second query statement can be rewritten as the 3rd query statement, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, and described 3rd Query Result at least comprises the data value in described first data rows set; And the definition statement of the 3rd form is generated based on described 3rd query statement.In step s 130, which, in described first querying command, the definition statement of the 3rd form can be utilized to replace described second query statement, simultaneously, described first filtercondition and the second filtercondition is comprised in response to described if-clause, described 3rd Query Result can comprise the data value in described first data rows set and the 3rd data rows, and in described first query statement, removes described first filtercondition and described first operational symbol from described if-clause; And described first query statement is rewritten as obtains described first Query Result based on described second filtercondition from described 3rd form.

For convenience of description, suppose that this first querying command as shown in Figure 3 B in fourth embodiment of the invention, and basic form in Fig. 3 B 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, the if-clause be made up of WHERE clause is except comprising the first filtercondition, i.e. A.C1IN (SELECTB.C1 ...) outside, also comprise the second filtercondition, i.e. A.C2<3, so the 3rd query statement needs the data value at least obtained from the second form B data rows C2 and C3.Then, the data value on this data rows C2 and C3 can be utilized form the definition statement of the 3rd form DSNVB (02).That is, the 3rd generated in the step s 120 form only can comprise the data value on data rows C2 and C3, thus makes the 3rd form committed memory space as little as possible.Obviously, except the data value on data rows C2 and C3, the 3rd form can also preserve the data value in other data rows one or more (such as, data rows C1 and/or RID) or even the data value in all data rows.

In addition, because this if-clause comprises two filterconditions, so in the second generated in step s 130, which querying command, the first query statement utilizing the 3rd form DSNVB (02) to rewrite can remove this first filtercondition and logical operator AND, and only comprises the second filtercondition C2<3 as unique filtercondition.

Comparison diagram 7C and Fig. 7 D can find out, the 4th embodiment comprises and step S120 identical in the 3rd embodiment and different step S130.Owing to comprising at least two filterconditions in the fourth embodiment, so query optimizer is after receiving the second querying command, cannot learn whether the performance of the access path generated according to this second querying command is better than the access path generated according to the first querying command at once, this is owing to depending in different content in basic form and related data row whether created index, directly generating access path according to the first querying command and may obtain higher search efficiency on the contrary.Especially, when the results set obtained when employing second filtercondition only comprises the data value of lesser amt, the first original querying command is used often than using the second querying command after changing to have higher search efficiency.

Therefore, after the first original querying command is optimized for the second querying command, can when described if-clause at least comprises the first and second filterconditions, the first set generating access path based on described first querying command (at least comprises two kinds of access path, namely the situation of the second filtercondition is performed after first performing the first filtercondition when execution the first query statement, with first perform the second filtercondition when execution the first query statement after perform the situation of the first filtercondition), and the second set of access path is generated based on described second querying command.Then, calculate the executory cost of each access path in described first set and the second set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus obtain described first Query Result from described basic form.

As can be seen here, compared with the 3rd embodiment, many access path can be generated based on original with the querying command after conversion in the fourth embodiment.Like this, data base management system (DBMS) can carry out choosing comprehensively to these many access path, to select optimum access path more objectively, thus exchanges higher querying command execution efficiency for by losing certain access path rise time.

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

The database inquiry optimization method according to the embodiment of the present invention illustrated in Fig. 6 can be realized by the database inquiry optimization equipment 100 illustrated in Figure 10.The details of the operation performed due to this equipment is identical with the method described hereinbefore, in other words, each details of the method described hereinbefore is equally applicable to described equipment, therefore here only simply describes described equipment, and omits the description to same detail.

As illustrated in FIG. 6, this database inquiry optimization equipment 100 can comprise: order receiving element 110, form definition unit 120 and order converting unit 130.In addition, this database inquiry optimization equipment 100 can also comprise: path selection unit 140.

This order receiving element 110 receives the first querying command, described first querying command at least comprises the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form.

This form definition unit 120 generates the definition statement of the 3rd form based on described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row.

This order converting unit 130 is in described first querying command, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form, thus described first querying command is converted to the second querying command.

This path selection unit 140 is after described first querying command is converted to the second querying command by described order converting unit, the first set of access path is generated based on described first querying command, the second set of access path is generated based on described second querying command, calculate the executory cost of each access path in described first set and the second set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus obtain described first Query Result from described basic form.

In the first and second embodiments as above, described second query statement can be rewritten as the 3rd query statement by described form definition unit 120, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value on described record identification row, each data value in described record identification row is used for the data line identifying described data value place uniquely, and generates the definition statement of the 3rd form based on described 3rd query statement.

Described order converting unit 130 is in described first querying command, the definition statement of the 3rd form can be utilized to replace described second query statement, simultaneously, described first filtercondition can be rewritten as and on described record identification row, connect described first form and described 3rd form by the 3rd operational symbol, and described first query statement is rewritten as obtains described first Query Result based on the annexation between described first form and described 3rd form from described first form and described 3rd form.

This path selection unit 140 can after described first querying command be converted to the second querying command by described order converting unit, the first set of access path is only generated based on described first querying command, the second set of access path is generated based on described second querying command, calculate the executory cost of each access path in described first set and the second set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus described first Query Result is obtained from described basic form.

Optionally, this path selection unit 140 can sort to the data value on described record identification row, described basic form is sequentially accessed according to ranking results, and the data value obtained from described basic form in the described first data rows set pointed by the data value on described record identifier, as described first Query Result.

As can be seen here, compared with prior art, first querying command only comprises in the first embodiment of a filtercondition characteristic from the querying command connected fully taking into account and cross over query block wherein, directly in the 3rd form, record comprises the RID of all data records of this data value, and inquire about according to RID and return the first Query Result to user, thus save and search index and the intermediate steps all according to index search RID by data value, make to decrease the system resource required for whole query manipulation.

In addition, compared with the first embodiment, first querying command at least comprises in the second embodiment of the first and second filterconditions and can generate many access path wherein, to weigh further these many access path, thus provides further query optimization effect to user.

In the 3rd embodiment as above, described second query statement can be rewritten as the 3rd query statement by described form definition unit 120, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value in described first data rows set, and generates the definition statement of the 3rd form based on described 3rd query statement.

Described order converting unit 130 can utilize the definition statement of the 3rd form to replace described second query statement, simultaneously, described first filtercondition is comprised as unique filtercondition in response to described if-clause, described 3rd Query Result at least can comprise the data value in described first data rows set, and described order converting unit 130 is in described first query statement, remove described if-clause, and described first query statement is rewritten as obtains described first Query Result from described 3rd form.

This path selection unit 140 is after described first querying command is converted to the second querying command by described order converting unit, also the first set of access path only can be generated based on described first querying command, the second set of access path is generated based on described second querying command, calculate the executory cost of each access path in described first set and the second set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus described first Query Result is obtained from described basic form.

As can be seen here, compared with prior art, first querying command comprises the first filtercondition as in the 3rd embodiment of unique filtercondition wherein, when definition the 3rd form, the first Query Result that direct acquisition is required when execution the first query statement, and when execution the first query statement, return this first Query Result, as the data value needed for user to user.Therefore, compared with the first embodiment, the 3rd embodiment only performs a table lookup operation, further decreases required the assessing the cost when execution the first querying command, improves search efficiency substantially.

In the 4th embodiment as above, described second query statement can be rewritten as the 3rd query statement by described form definition unit 120, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value in described first data rows set, and generates the definition statement of the 3rd form based on described 3rd query statement.

Described order converting unit 130 can utilize the definition statement of the 3rd form to replace described second query statement, simultaneously, described first filtercondition and the second filtercondition is comprised in response to described if-clause, described 3rd Query Result can comprise the data value in described first data rows set and the 3rd data rows, described second filtercondition is combined with described first filtercondition by the first operational symbol, and connect described 3rd data rows and conditional value by the second operational symbol, and described order converting unit 130 is in described first query statement, described first filtercondition and described first operational symbol is removed from described if-clause, and described first query statement is rewritten as and obtains described first Query Result based on described second filtercondition from described 3rd form.

As can be seen here, compared with the 3rd embodiment, the first querying command at least comprises in the 4th embodiment of the first and second filterconditions and can generate many articles of access path based on original with the querying command after conversion wherein.Like this, data base management system (DBMS) can carry out choosing comprehensively to these many access path, to select optimum access path more objectively, thus exchanges higher querying command execution efficiency for by losing certain access path rise time.

Process flow diagram in accompanying drawing and block diagram show system according to multiple embodiment of the present invention, the architectural framework in the cards of method and computer program product, function and operation.In this, each square frame in process flow diagram or block diagram can represent a part for module, program segment or a code, and a part for described module, program segment or code comprises one or more executable instruction for realizing the logic function specified.Also it should be noted that at some as in the realization of replacing, the function marked in square frame also can be different from occurring in sequence of marking in accompanying drawing.Such as, in fact two continuous print square frames can perform substantially concurrently, and they also can perform by contrary order sometimes, and this determines according to involved function.Also it should be noted that, the combination of the square frame in each square frame in block diagram and/or process flow diagram and block diagram and/or process flow diagram, can realize by the special hardware based system of the function put rules into practice or action, or can realize with the combination of specialized hardware and computer instruction.

Be described above various embodiments of the present invention, above-mentioned explanation is exemplary, and non-exclusive, and be also not limited to disclosed each embodiment.When not departing from the scope and spirit of illustrated each embodiment, many modifications and changes are all apparent for those skilled in the art.The selection of term used herein, is intended to explain best the principle of each embodiment, practical application or the technological improvement to the technology in market, or makes other those of ordinary skill of the art can understand each embodiment disclosed herein.

Claims

1. a database inquiry optimization method, comprising:

Receive the first querying command, described first querying command at least comprises the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form;

Generate the definition statement of the 3rd form based on described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row; And

In described first querying command, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form, thus described first querying command is converted to the second querying command.

2. the method for claim 1, wherein, described first Query Result comprises the data value in the first data rows set, described second Query Result comprises the data value in the second data rows set, and described first query statement comprises if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form, and

The described definition statement generating the 3rd form based on described second query statement comprises:

Described second query statement is rewritten as the 3rd query statement, and described 3rd query statement is used for obtaining described 3rd Query Result from described second form, and described 3rd Query Result at least comprises the data value in described first data rows set; And

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

3. method as claimed in claim 2, wherein, comprise described first filtercondition as unique filtercondition in response to described if-clause, described 3rd Query Result at least comprises the data value in described first data rows set, and

Describedly rewrite described first query statement based on described 3rd form and comprise:

In described first query statement, remove described if-clause; And

Described first query statement is rewritten as and obtains described first Query Result from described 3rd form.

4. method as claimed in claim 2, wherein, described first filtercondition and the second filtercondition is comprised in response to described if-clause, described 3rd Query Result comprises the data value in described first data rows set and the 3rd data rows, described second filtercondition is combined with described first filtercondition by the first operational symbol, and connect described 3rd data rows and conditional value by the second operational symbol, and

In described first query statement, from described if-clause, remove described first filtercondition and described first operational symbol; And

Described first query statement is rewritten as and obtains described first Query Result based on described second filtercondition from described 3rd form.

5. the method for claim 1, wherein, described first Query Result comprises the data value in the first data rows set, described second Query Result comprises the data value in the second data rows set, and described first query statement comprises if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form, and

Described second query statement is rewritten as the 3rd query statement, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value on described record identification row, and each data value in described record identification row is used for the data line identifying described data value place uniquely; And

6. method as claimed in claim 5, wherein, describedly rewrite described first query statement based on described 3rd form and comprise:

In described first query statement, described first filtercondition is rewritten as and on described record identification row, connects described first form and described 3rd form by the 3rd operational symbol; And

Described first query statement is rewritten as and obtains described first Query Result based on the annexation between described first form and described 3rd form from described first form and described 3rd form.

7. method as claimed in claim 5, described described first querying command is converted to the second querying command after, also comprise:

Described first filtercondition is comprised as unique filtercondition in response to described if-clause, access path is generated based on described second querying command, to perform described access path in running environment, thus obtain described first Query Result from described basic form.

8. method as claimed in claim 7, wherein, in response to the described access path of generation, data value on described record identification row is sorted, described basic form is sequentially accessed according to ranking results, and the data value obtained from described basic form in the described first data rows set pointed by the data value on described record identifier, as described first Query Result.

9. the method for claim 1, described described first querying command is converted to the second querying command after, also comprise:

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

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

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

Select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus obtain described first Query Result from described basic form.

10. a database inquiry optimization equipment, comprising:

Order receiving element, for receiving the first querying command, described first querying command at least comprises the first query statement and the second query statement, described second query statement is used for obtaining the second Query Result from the second form, described first query statement is used for obtaining the first Query Result by described second Query Result and the first filtercondition from the first form, the same foundation form in each the sensing database in described first form and described second form;

Form definition unit, for generating the definition statement of the 3rd form based on described second query statement, described 3rd form is for storing the 3rd Query Result, and described 3rd Query Result comprises at least one in the data value on described first Query Result and record identification row; And

Order converting unit, for in described first querying command, utilize described definition statement to replace described second query statement, and rewrite described first query statement based on described 3rd form, thus described first querying command is converted to the second querying command.

11. equipment as claimed in claim 10, wherein, described first Query Result comprises the data value in the first data rows set, described second Query Result comprises the data value in the second data rows set, and described first query statement comprises if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form, and

Described second query statement is rewritten as the 3rd query statement by described form definition unit, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value in described first data rows set, and generates the definition statement of the 3rd form based on described 3rd query statement.

12. equipment as claimed in claim 11, wherein, comprise described first filtercondition as unique filtercondition in response to described if-clause, described 3rd Query Result at least comprises the data value in described first data rows set, and

Described order converting unit, in described first query statement, removes described if-clause, and is rewritten as by described first query statement and obtains described first Query Result from described 3rd form.

13. equipment as claimed in claim 11, wherein, described first filtercondition and the second filtercondition is comprised in response to described if-clause, described 3rd Query Result comprises the data value in described first data rows set and the 3rd data rows, described second filtercondition is combined with described first filtercondition by the first operational symbol, and connect described 3rd data rows and conditional value by the second operational symbol, and

Described order converting unit is in described first query statement, from described if-clause, remove described first filtercondition and described first operational symbol, and described first query statement is rewritten as obtains described first Query Result based on described second filtercondition from described 3rd form.

14. equipment as claimed in claim 10, wherein, described first Query Result comprises the data value in the first data rows set, described second Query Result comprises the data value in the second data rows set, and described first query statement comprises if-clause, described if-clause at least comprises described first filtercondition, and described first filtercondition each data rows in described second data rows set connects described first form and described second form, and

Described second query statement is rewritten as the 3rd query statement by described form definition unit, described 3rd query statement is used for obtaining described 3rd Query Result from described second form, described 3rd Query Result at least comprises the data value on described record identification row, each data value in described record identification row is used for the data line identifying described data value place uniquely, and generates the definition statement of the 3rd form based on described 3rd query statement.

15. equipment as claimed in claim 14, wherein, described order converting unit is in described first query statement, described first filtercondition is rewritten as and on described record identification row, connects described first form and described 3rd form by the 3rd operational symbol, and described first query statement is rewritten as obtains described first Query Result based on the annexation between described first form and described 3rd form from described first form and described 3rd form.

16. equipment as claimed in claim 14, also comprise:

Path selection unit, after described first querying command being converted to the second querying command in described order converting unit, described first filtercondition is comprised as unique filtercondition in response to described if-clause, access path is generated based on described second querying command, to perform described access path in running environment, thus obtain described first Query Result from described basic form.

17. equipment as claimed in claim 16, wherein, described path selection unit is in response to the described access path of generation, data value on described record identification row is sorted, described basic form is sequentially accessed according to ranking results, and the data value obtained from described basic form in the described first data rows set pointed by the data value on described record identifier, as described first Query Result.

18. equipment as claimed in claim 10, also comprise:

Path selection unit, after described first querying command being converted to the second querying command in described order converting unit, the first set of access path is generated based on described first querying command, the second set of access path is generated based on described second querying command, calculate the executory cost of each access path in described first set and the second set, and select optimum access path based on described executory cost, to perform described optimum access path in running environment, thus obtain described first Query Result from described basic form.