CN103092970A - Database operation method and device - Google Patents
Database operation method and device Download PDFInfo
- Publication number
- CN103092970A CN103092970A CN2013100272660A CN201310027266A CN103092970A CN 103092970 A CN103092970 A CN 103092970A CN 2013100272660 A CN2013100272660 A CN 2013100272660A CN 201310027266 A CN201310027266 A CN 201310027266A CN 103092970 A CN103092970 A CN 103092970A
- Authority
- CN
- China
- Prior art keywords
- sql
- predefine
- sql statement
- executive plan
- grammer
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a database operation method and a device. The method comprises the steps of receiving a structured query language (SQL) text, judging whether SQL sentences in the SQL text is predefined SQL sentences in predefined SQL grammar, wherein the predefined SQL sentences in the predefined SQL grammar and corresponding original SQL sentences in original SQL grammar correspond to the same execution plan, if the SQL sentences are the predefined SQL sentences in the predefined SQL grammar, inquiring an execution plan bond by the SQL sentences in a corresponding relation table of the predefined SQL sentences and execution plans, executing the execution plan, and returning a corresponding result to a client side. The database operation method has generality and is free of affecting stability of a database system.
Description
Technical field
The present invention relates to database field, be specifically related to a kind of database operation method and equipment.
Background technology
Relevant database is the database that is based upon on relational model, and main relevant database product has Oracle, DB2, SQL Server, PostgreSQL and MYSQL etc. at present.Structured Query Language (SQL) (Srtuctured Query Language, SQL) be the main users routine interface language of various relevant databases, user program can be realized the operation such as inquiry, insertion, deletion, renewal to the data in various relevant databases these operations being referred to as the operation to database herein by SQL.The SQL text is embedded in and uses in the user program (source file) that various host languages (as C, C++, Java, Pathon etc.) write.At present, utilize SQL to realize that the inquiry of data and the major way of operation have dynamic sql and static embeded SQL.
Dynamic sql is that user program is called at every turn, and the SQL in user program is sent to database, and database compiles the generation executive plan to it, then carries out corresponding operating according to executive plan.The shortcoming of dynamic sql is that each run all needs compiling, and during due to each compiling, database environment may change, and same SQL may produce different executive plans, causes Database Systems unstable.
To be the SQL text be embedded in source file with the grammer of nonhost language static embeded SQL, source file is extracted through the SQL in source file after precompile, generate respectively the modification source file of having rejected SQL statement and the binding file that comprises the SQL text, then binding file is bound to database and generates executive plan.Needn't recompilate SQL during each run later on but directly call the executive plan that has generated.The shortcoming of static embeded SQL need to be the precompile source file, increases developer's workload and difficulty, and supports that at present the host language of static embeded SQL is less, and versatility is lower.
Summary of the invention
Technical matters to be solved by this invention is to provide a kind of data base query method, has versatility and can not affect the stability of Database Systems.
The application's first aspect provides a kind of database operation method, comprising:
Receive Structured Query Language (SQL) SQL text;
Judge that whether SQL statement in described SQL text is the predefine SQL statement in predefine SQL grammer, wherein, the corresponding identical executive plan of corresponding former SQL statement in the predefine SQL statement in described predefine SQL grammer and former SQL grammer;
If described SQL statement is the predefine SQL statement in predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan;
Carry out described executive plan and return to accordingly result to client.
In the possible implementation of the first of first aspect, describedly judge that whether the SQL statement in described SQL text is that predefine SQL statement in predefine SQL grammer comprises:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
If described SQL statement comprises predefine SQL syntax identifier, determine that described SQL statement is the predefine SQL statement in predefine SQL grammer.
In conjunction with the possible implementation of the first of first aspect or first aspect, in the possible implementation of the second, before described reception Structured Query Language (SQL) SQL text, described method also comprises:
Bind the corresponding former SQL statement of every predefine SQL statement in the former SQL grammer in described predefine SQL grammer;
Compile described former SQL statement, generate corresponding executive plan;
Store described executive plan, and generate the mapping table of predefine SQL statement and executive plan.
In conjunction with the possible implementation of the second of first aspect, in the third possible implementation, before the described executive plan of described storage, described method also comprises:
Whether the execution efficient that judges described executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of described executive plan lower than the execution efficient of described historical executive plan, is recovered described historical executive plan as executive plan.
In conjunction with the third possible implementation of first aspect, in the 4th kind of possible implementation, described method also comprises:
If described SQL statement is not the predefine SQL statement in predefine SQL grammer, compile described SQL statement, generate executive plan.
The application's second aspect provides a kind of database manipulation equipment, comprising:
Receiver module is used for receiving Structured Query Language (SQL) SQL text, and enables judge module after receiving described SQL text;
Described judge module, whether the SQL statement that is used for judging the described SQL text that described receiver module receives is the predefine SQL statement in predefine SQL grammer, and when being predefine SQL statement in predefine SQL grammer, described SQL statement enables enquiry module judging, wherein, the corresponding identical executive plan of corresponding former SQL statement in the predefine SQL statement in described predefine SQL grammer and former SQL grammer;
Described enquiry module, be used for when described judge module is judged described SQL statement and is the predefine SQL statement of predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan, and enable execution module;
Described execution module is used for carrying out described executive plan and returning to accordingly result to client.
In the possible implementation of the first of second aspect, described judge module specifically is used for:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
When described SQL statement comprises predefine SQL syntax identifier, determine that described predefine SQL statement is the predefine SQL statement in predefine SQL grammer.
In conjunction with second aspect or second 's the possible implementation of the first, in the possible implementation of the second, described equipment also comprises,
Binding module, every the predefine SQL statement that is used for the described predefine SQL grammer of binding before described receiver module the receives Structured Query Language (SQL) SQL text corresponding former SQL statement in the former SQL grammer, and enable collector;
Described collector is used for compiling described former SQL statement, generates corresponding executive plan, and enables memory module;
Described memory module is used for storing described executive plan, and generates the mapping table of predefine SQL statement and executive plan, and enables described receiver module.
In conjunction with the possible implementation of the second of second aspect, in the third possible implementation, described equipment also comprises:
The second judge module, be used for before described collector enables described memory module, whether the execution efficient that judges described executive plan lower than the execution efficient of historical executive plan, and the execution efficient of judging described executive plan during lower than the execution efficient of historical executive plan the enable history plan recover module;
Described historical plan recovers module, be used for before described collector enables described memory module, and the execution efficient of judging described executive plan at described the second judge module is recovered described historical executive plan as executive plan during lower than the execution efficient of described historical executive plan.
In conjunction with the third possible implementation of second aspect, in the 4th kind of possible implementation, described equipment also comprises:
The second collector is used for compiling described SQL statement when described judge module is judged described SQL statement and is not the predefine SQL statement of predefine SQL grammer, generates executive plan.
The application's third aspect provides a kind of database manipulation equipment, comprises storer and processor, wherein:
Described storer is used for storage batch processing code, and described processor is used for reading described program code from storer, carries out:
Receive Structured Query Language (SQL) SQL text;
Judge that whether SQL statement in described SQL text is the predefine SQL statement in predefine SQL grammer, wherein, in the predefine SQL statement in described predefine SQL grammer and former SQL grammer, the corresponding executive plan of corresponding former SQL statement is identical;
If described SQL statement is the predefine SQL statement in predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan;
Carry out described executive plan and return to accordingly result to client.
In the possible implementation of the first of the third aspect, described processor is carried out and is describedly judged that whether the SQL statement in described SQL text is that the concrete mode of the predefine SQL statement in predefine SQL grammer is:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
If described SQL statement comprises predefine SQL syntax identifier, determine that described SQL statement is the predefine SQL statement in predefine SQL grammer.
In conjunction with the possible implementation of the first of the third aspect or the third aspect, in the possible implementation of the second, before described reception Structured Query Language (SQL) SQL text, described processor also is used for carrying out:
Bind the corresponding former SQL statement of every predefine SQL statement in the former SQL grammer in described predefine SQL grammer;
Compile described former SQL statement, generate corresponding executive plan;
Store described executive plan, and generate the mapping table of predefine SQL statement and executive plan.
In conjunction with the possible implementation of the second of the third aspect, in the third possible implementation, before the described executive plan of described storage, described processor also is used for carrying out:
Whether the execution efficient that judges described executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of described executive plan lower than the execution efficient of described historical executive plan, is recovered described historical executive plan as executive plan.
In conjunction with the third possible implementation of the third aspect, in the 4th kind of possible implementation, described processor also is used for carrying out:
When described SQL statement is not predefine SQL statement in predefine SQL grammer, compile described SQL statement, generate executive plan.
The present invention defines predefine SQL grammer, predefine SQL statement in predefine SQL grammer is tied to corresponding executive plan, and these executive plans of persistent storage, when database when follow-up each reception comprises the SQL text of predefine SQL grammer, do not need this original SQL text is compiled and optimizes, but directly search corresponding executive plan, carry out and return results.Database operation method of the present invention has versatility and can not affect the stability of Database Systems.
Description of drawings
In order to be illustrated more clearly in the embodiment of the present invention or technical scheme of the prior art, the below will do to introduce simply to the accompanying drawing of required use in embodiment or description of the Prior Art, apparently, accompanying drawing in the following describes is only some embodiments of the present invention, for those of ordinary skills, under the prerequisite of not paying creative work, can also obtain according to these accompanying drawings other accompanying drawing.
Fig. 1 is the process flow diagram of a kind of database operation method of providing of the embodiment of the present invention;
Fig. 2 is the process flow diagram of the another kind of database operation method that provides of the embodiment of the present invention;
Fig. 3 is the structural drawing of a kind of database manipulation equipment of providing of the embodiment of the present invention;
Fig. 4 is the structural drawing of the another kind of database manipulation equipment that provides of the embodiment of the present invention;
Fig. 5 is the structural drawing of another the database manipulation equipment that provides of the embodiment of the present invention;
Fig. 6 is the structural drawing of another the database manipulation equipment that provides of the embodiment of the present invention;
Fig. 7 is the structural drawing of a kind of database manipulation equipment of providing of the embodiment of the present invention.
Embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is clearly and completely described, obviously, described embodiment is only the present invention's part embodiment, rather than whole embodiment.Based on the embodiment in the present invention, those of ordinary skills belong to the scope of protection of the invention not making the every other embodiment that obtains under the creative work prerequisite.
The user program of dynamic sql adopts pure host language to write, user program is called at every turn, and the SQL text in program is sent to database, and data stock compiles it, generate corresponding executive plan, following program 1 is the user program fragment of the employing dynamic sql that adopts Java language and write:
Program 1:
The SQL text is pure Java language as shown in program 1.In dynamic sql, each SQL compiling text is all wanted holding time, increases the explanation burden of database, has reduced the database concurrency processing power, and each compiling, because the environment of database changes, same SQL statement may generate different executive plans, causes system unstable.
In static embeded SQL, the SQL text is embedded in source file with the grammer of nonhost language, source file is extracted through the SQL in source file after precompile, generate respectively the modification source file of having rejected SQL statement and the binding file that comprises the SQL text, binding file is bound to database and generates executive plan.Meet for adopting the user program fragment that the employing static embeded SQL that the Java language of Embedded SQL method of calling (SQL Java, SQLJ) standard is write supported in Java language as program 2:
Program 2:
The SQL text is non-Java language as shown in program 2.The static embeded SQL development deployment needs the precompile source file, increases developer's workload and difficulty.The middle binding file that comprises SQL that precompile generates need to be tied to database instance.The modification source file that precompile generates is difficult to read, and also is difficult to during debugging carry out row alignment with original source files.The language that static embeded SQL is supported is less, and the language such as C, the COBOL etc. that only have some to support Embedded SQL also have the SQLJ interface of JAVA to support Static SQL.For some other language, such as Perl etc., can not use Static SQL.
Please refer to Fig. 1, the process flow diagram of a kind of database operation method that Fig. 1 provides for the embodiment of the present invention, the executive agent of the present embodiment can be database, shows as described in Figure 1, the method comprises:
101, receive Structured Query Language (SQL) SQL text.
102, judge that whether SQL statement in the SQL text is the predefine SQL statement in predefine SQL grammer, wherein, the corresponding identical executive plan of corresponding former SQL statement in the predefine SQL statement in predefine SQL grammer and former SQL grammer.
SQL is for the grammer of carrying out inquiry, but sql like language also comprises the grammer for renewal, insertion and deletion record, SQL comprises data manipulation language (DML) (Data Manipulation Language, DML) and data definition language (DDL) (Data Definition Language, DDL), wherein
Inquiry and update instruction have consisted of the DML part of SQL, and the DML instruction mainly contains:
SELECT-obtains data from database table
UPDATE-upgrades the data in database table
DELETE-deletes data from database table
INSERT INTO-data inserting in the database table
DDL partly be used for to create or Delete Table, also can index of definition (key), and the link between the regulation table, and apply constraint between table, the DDL instruction mainly contains:
CREATE DATABASE-creates new database
ALTER DATABASE-Update Table storehouse
CREATE TABLE-creates new table
ALTER TABLE-changes (change) database table
DROP TABLE-delete list
CREATE INDEX-creates index (search key)
DROP INDEX-deletes index
SQL statement in the said former SQL grammer of this paper refers to SQL statement commonly used at present, as the SQL statement that is made of above-mentioned DML instruction and DDL instruction.
The predefine SQL grammer of the present embodiment definition can for:
DML statement in former SQL grammer and DDL statement are redefined by its function or by sequence number become the predefine SQL statement, and add predefine SQL syntax identifier in its front, for example, to obtain data statement SELECT*FROM TABLE1 from database table redefines and is Query:Query1, wherein, " Query: " is predefine SQL syntax identifier, predefine SQL syntax identifier also can use other character as " QueryID: ", " Statement: ", " SQL: " etc., and Query1 is for to redefine into the predefine SQL statement by sequence number.Here also can predefine SQL syntax identifier be customized according to the difference of application data base, for example, develop same set of application program can adaptive different database product the time, when being applied to oracle database, identifier is " Query1:oracle: " to definition predefine SQL; When being applied to the DB2 database, definition predefine SQL identifier is for " Query1:DB2: " etc.
Wherein, judge in step 102 SQL statement in the SQL text be whether the predefine SQL statement in predefine SQL grammer method can for:
Judge whether this SQL statement comprises predefine SQL syntax identifier;
If this SQL statement comprises predefine SQL syntax identifier, determine that this SQL statement is the predefine SQL statement in predefine SQL grammer.
Wherein, after DML statement in above-mentioned former SQL grammer and DDL statement become the predefine SQL statement by its function or by the heavy predefine definition of sequence number, also need the predefine SQL statement is tied to corresponding executive plan, here the process of binding was completed before receiving Structured Query Language (SQL) SQL text, and the process of binding comprises:
The corresponding former SQL statement of predefine SQL statement in binding predefine SQL grammer in the former SQL grammer;
Compile former SQL statement, generate executive plan;
Storage executive plan, and the mapping table of generation predefine SQL statement and executive plan, thus this predefine SQL statement is tied to corresponding executive plan.
For example, the predefine SQL statement in binding predefine SQL grammer is as follows to the corresponding former SQL statement example in former SQL grammer:
sql=#bind?query?erp_hr_query1‘select?salary?from?emp?where?depID=？and?name=？’
Wherein, " query: " is predefine SQL syntax identifier, and " erp_hr_query1 " is the predefine SQL statement, and " select salary from emp where depID=? and name=? " be the corresponding former SQL statement in former SQL grammer.
Compile specifically comprising of former SQL statement: send the resolver in database to resolve this former SQL statement, then send the optimizer in database to be optimized, obtain at last executive plan.
The mapping table of above-mentioned generation predefine SQL statement and executive plan is as shown in table 1 below:
Table 1:
Wherein, historical executive plan is used for preserving the old version of executive plan, and alternatively, the method for this enforcement can also comprise:
Whether the execution efficient that judges executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of executive plan lower than the execution efficient of historical executive plan, is recovered historical executive plan as executive plan.
If the executive plan of up-to-date generation does not have the execution efficient of historical plan high, recover historical executive plan and replace the executive plan that predefine generates, and be kept at the mapping table of predefine SQL statement and executive plan.
For example, can recover the historical executive plan of erp_hr_query1 by following grammer, cover the executive plan of current generation:
sql=#restore?query?plan?erp__hr_query1from?history1
Value in table 1 in the executive plan row is replaced by the value in historical executive plan row, when the mapping table of next this predefine SQL statement and executive plan is called execution, will continue to use historical optimal plan rather than current newly-generated plan.
If 103 SQL statement are the predefine SQL statement in predefine SQL grammer, the executive plan that this SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan.
104, carry out this executive plan and return to accordingly result to client.
The user program fragment of program 2 for using predefine SQL grammer to write.
Program 2:
The present embodiment not only has when static embeded SQL need not call at every turn and all will compile, thereby guarantee the advantage of Database Systems stability, and have need not through precompile of dynamic sql, the convenient debugging of the row alignment of source file code when having advantages of versatility and debugging.To sum up, the present embodiment provide database operation method have versatility and can not affect the stability of Database Systems.
Please refer to Fig. 2, the process flow diagram of the another kind of database operation method that Fig. 2 provides for the embodiment of the present invention comprises:
201, client sends the SQL text to database.
The SQL text that client sends can be the SQL text of former SQL grammer, and for example " SELECT*FROM TABLE1 " can be also the SQL text of predefine SQL grammer, for example " query:query1 ".
202, create new thread, the SQL text is sent to resolver.
Database receives and creates new thread (or process, or reuse existing thread from thread pool) after the SQL text and inquire about.
203, resolver (Parser) is resolved the SQL text, judges whether the grammer of this SQL text is predefine SQL grammer.
If it is not predefine SQL grammer that 204 resolvers judge the grammer of this SQL text, this SQL text is sent to optimizer (Optimizer).
If the grammer of SQL text is not predefine SQL grammer, as " SELECT*FROM TABLE1 ", it is issued optimizer and be optimized.
205, optimizer generates executive plan, and executive plan is sent to actuator (Executor).
Wherein, optimizer receives after the SQL text according to current statistical information, adopts the algorithm generation executive plan of rule based or cost based strategy and dynamic programming.
If it is predefine SQL grammer that 206 resolvers judge the grammer of this SQL text, the executive plan that this SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan.
Wherein, the generative process of the mapping table of predefine SQL statement and executive plan repeats no more here with reference to figure 1 embodiment.
207, actuator executive plan and return to accordingly result to client.
The user program of the present embodiment client can adopt former SQL grammer programming also can adopt the programming of predefine SQL grammer, has increased the versatility of database operation method.The present embodiment not only has when static embeded SQL need not call at every turn and all will compile, thereby guarantee the advantage of Database Systems stability, and have need not through precompile of dynamic sql, the convenient debugging of the row alignment of source file code when having advantages of versatility and debugging.To sum up, the database operation method that provides of the present embodiment has versatility and can not affect the stability of Database Systems.
Please refer to Fig. 3, Fig. 3 is the structural drawing of a kind of database manipulation equipment of providing of the embodiment of the present invention, and this database manipulation equipment comprises as shown in Figure 3:
Alternatively, judge module 302 specifically is used for:
Judge whether SQL statement comprises predefine SQL syntax identifier;
When SQL statement comprises predefine SQL syntax identifier, determine that this SQL statement is the predefine SQL statement in predefine SQL grammer.
Alternatively, the database manipulation equipment that the present embodiment provides can also comprise binding module 305, collector 306 and memory module 307, as shown in Figure 4, and wherein,
Binding module 305 was used for before receiver module 301 receives the SQL texts, the corresponding former SQL statement of the predefine SQL statement in binding predefine SQL grammer in the former SQL grammer, and enable collector 306;
Alternatively, the database manipulation equipment that the present embodiment provides can also comprise that the second judge module 308 and historical plan recover module 309, as shown in Figure 5, and wherein,
The second judge module 308, be used for before collector 306 enables memory module 307, whether the execution efficient that judges executive plan lower than the execution efficient of historical executive plan, and the execution efficient of judging executive plan during lower than the execution efficient of historical executive plan the enable history plan recover module 309;
Historical plan recovers module 309, be used for before collector 306 enables memory module 307, and the execution efficient of judging executive plan at the second judge module 308 is recovered historical executive plan as executive plan during lower than the execution efficient of historical executive plan.
Alternatively, the database manipulation equipment that the present embodiment provides can also comprise the second collector 310, as shown in Figure 6, and wherein,
The second collector 310 is used for compiling described SQL statement when judge module is judged described SQL statement and is not the predefine SQL statement of predefine SQL grammer, generates executive plan.
The database inquiry facility that the present embodiment provides not only has when static embeded SQL need not call at every turn and all will compile, thereby guarantee the advantage of Database Systems stability, and have need not through precompile of dynamic sql, have advantages of versatility and when debugging the source file code the convenient debugging of row alignment, so the database manipulation equipment that provides of the present embodiment has versatility and can not affect the stability of Database Systems.
Please refer to Fig. 7, Fig. 7 is the structural drawing of a kind of database manipulation equipment of providing of the embodiment of the present invention, and this database manipulation equipment comprises storer 401 and processor 402 as shown in Figure 7, wherein:
Storer 401 is used for storage batch processing code, and processor 402 is used for from storer 401 program code reads, carries out:
Receive the SQL text;
Judge that whether SQL statement in the SQL text is the predefine SQL statement in predefine SQL grammer, wherein, in the predefine SQL statement in predefine SQL grammer and former SQL grammer, the corresponding executive plan of corresponding former SQL statement is identical;
If SQL statement is the predefine SQL statement in predefine SQL grammer, the executive plan that this SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan;
Carry out this executive plan and return to accordingly result to client.
Alternatively, processor 402 carry out the SQL statement that judges in the SQL text be whether the predefine SQL statement in predefine SQL grammer concrete mode can for:
Judge whether SQL statement comprises predefine SQL syntax identifier;
If SQL statement comprises predefine SQL syntax identifier, determine that this SQL statement is the predefine SQL statement in predefine SQL grammer.
Wherein, the definition of predefine SQL grammer repeats no more here with reference to figure 1 embodiment.
Alternatively, before receiving the SQL text, processor 402 also is used for carrying out:
The corresponding former SQL statement of predefine SQL statement in binding predefine SQL grammer in the former SQL grammer;
Compile former SQL statement, generate corresponding executive plan;
Store above-mentioned executive plan, and generate the mapping table of predefine SQL statement and executive plan, thereby the predefine SQL statement is tied to corresponding executive plan.
Wherein, the concrete grammar that binding predefine SQL statement arrives corresponding executive plan repeats no more here with reference to figure 1 embodiment.
Alternatively, before the above-mentioned executive plan of storage, processor 402 also is used for carrying out:
Whether the execution efficient that judges executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of executive plan lower than the execution efficient of historical executive plan, is recovered historical executive plan as executive plan.
Alternatively, processor 402 also is used for carrying out:
When SQL statement was not predefine SQL statement in predefine SQL grammer, the compiling SQL statement generated executive plan.
The database inquiry facility that the present embodiment provides not only has when static embeded SQL need not call at every turn and all will compile, thereby guarantee the advantage of Database Systems stability, and have need not through precompile of dynamic sql, the convenient debugging of the row alignment of source file code when having advantages of versatility and debugging.To sum up, the present embodiment provide database manipulation equipment have versatility and can not affect the stability of Database Systems.
One of ordinary skill in the art will appreciate that all or part of flow process that realizes in above-described embodiment method, to come the relevant hardware of instruction to complete by computer program, described program can be stored in a computer read/write memory medium, this program can comprise the flow process as the embodiment of above-mentioned each side method when carrying out.Wherein, described storage medium can be magnetic disc, CD, read-only store-memory body (Read-Only Memory, ROM) or random access memory (RandomAccess Memory is called for short RAM) etc.
Above disclosed is only preferred embodiment of the present invention, certainly can not limit with this interest field of the present invention, and the equivalent variations of therefore doing according to claim of the present invention still belongs to the scope that the present invention is contained.
Claims (15)
1. a database operation method, is characterized in that, comprising:
Receive Structured Query Language (SQL) SQL text;
Judge that whether SQL statement in described SQL text is the predefine SQL statement in predefine SQL grammer, wherein, the corresponding identical executive plan of corresponding former SQL statement in the predefine SQL statement in described predefine SQL grammer and former SQL grammer;
If described SQL statement is the predefine SQL statement in predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan;
Carry out described executive plan and return to accordingly result to client.
2. method according to claim 1, is characterized in that, describedly judges that whether the SQL statement in described SQL text is that predefine SQL statement in predefine SQL grammer comprises:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
If described SQL statement comprises predefine SQL syntax identifier, determine that described SQL statement is the predefine SQL statement in predefine SQL grammer.
3. method according to claim 1 and 2, is characterized in that, before described reception Structured Query Language (SQL) SQL text, described method also comprises:
Bind the corresponding former SQL statement of every predefine SQL statement in the former SQL grammer in described predefine SQL grammer;
Compile described former SQL statement, generate corresponding executive plan;
Store described executive plan, and generate the mapping table of predefine SQL statement and executive plan.
4. method according to claim 3, is characterized in that, before the described executive plan of described storage, described method also comprises:
Whether the execution efficient that judges described executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of described executive plan lower than the execution efficient of described historical executive plan, is recovered described historical executive plan as executive plan.
5. method according to claim 4, is characterized in that, described method also comprises:
If described SQL statement is not the predefine SQL statement in predefine SQL grammer, compile described SQL statement, generate executive plan.
6. a database manipulation equipment, is characterized in that, comprising:
Receiver module is used for receiving Structured Query Language (SQL) SQL text, and enables judge module after receiving described SQL text;
Described judge module, whether the SQL statement that is used for judging the described SQL text that described receiver module receives is the predefine SQL statement in predefine SQL grammer, and when being predefine SQL statement in predefine SQL grammer, described SQL statement enables enquiry module judging, wherein, the corresponding identical executive plan of corresponding former SQL statement in the predefine SQL statement in described predefine SQL grammer and former SQL grammer;
Described enquiry module, be used for when described judge module is judged described SQL statement and is the predefine SQL statement of predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan, and enable execution module;
Described execution module is used for carrying out described executive plan and returning to accordingly result to client.
7. equipment according to claim 6, is characterized in that, described judge module specifically is used for:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
When described SQL statement comprises predefine SQL syntax identifier, determine that described SQL statement is the predefine SQL statement in predefine SQL grammer.
8. according to claim 6 or 7 described equipment, is characterized in that, described equipment also comprises,
Binding module, every the predefine SQL statement that is used for the described predefine SQL grammer of binding before described receiver module the receives Structured Query Language (SQL) SQL text corresponding former SQL statement in the former SQL grammer, and enable collector;
Described collector is used for compiling described former SQL statement, generates corresponding executive plan, and enables memory module;
Described memory module is used for storing described executive plan, and generates the mapping table of predefine SQL statement and executive plan, and enables described receiver module.
9. equipment according to claim 8, is characterized in that, described equipment also comprises:
The second judge module, be used for before described collector enables described memory module, whether the execution efficient that judges described executive plan lower than the execution efficient of historical executive plan, and the execution efficient of judging described executive plan during lower than the execution efficient of historical executive plan the enable history plan recover module;
Described historical plan recovers module, be used for before described collector enables described memory module, and the execution efficient of judging described executive plan at described the second judge module is recovered described historical executive plan as executive plan during lower than the execution efficient of described historical executive plan.
10. equipment according to claim 9, is characterized in that, described equipment also comprises:
The second collector is used for compiling described SQL statement when described judge module is judged described SQL statement and is not the predefine SQL statement of predefine SQL grammer, generates executive plan.
11. a database manipulation equipment is characterized in that, comprises storer and processor, wherein:
Described storer is used for storage batch processing code, and described processor is used for reading described program code from storer, carries out:
Receive Structured Query Language (SQL) SQL text;
Judge that whether SQL statement in described SQL text is the predefine SQL statement in predefine SQL grammer, wherein, in the predefine SQL statement in described predefine SQL grammer and former SQL grammer, the corresponding executive plan of corresponding former SQL statement is identical;
If described SQL statement is the predefine SQL statement in predefine SQL grammer, the executive plan that the described predefine SQL statement of inquiry is bound from the mapping table of predefine SQL statement and executive plan;
Carry out described executive plan and return to accordingly result to client.
12. equipment according to claim 11 is characterized in that, described processor is carried out and is describedly judged that whether the SQL statement in described SQL text is that the concrete mode of the predefine SQL statement in predefine SQL grammer is:
Judge whether described SQL statement comprises predefine SQL syntax identifier;
If described SQL statement comprises predefine SQL syntax identifier, determine that described SQL statement is the predefine SQL statement in predefine SQL grammer.
13. according to claim 11 or 12 described equipment is characterized in that, before described reception Structured Query Language (SQL) SQL text, described processor also is used for carrying out:
Bind the corresponding former SQL statement of every predefine SQL statement in the former SQL grammer in described predefine SQL grammer;
Compile described former SQL statement, generate corresponding executive plan;
Store described executive plan, and generate the mapping table of predefine SQL statement and executive plan.
14. equipment according to claim 13 is characterized in that, before the described executive plan of described storage, described processor also is used for carrying out:
Whether the execution efficient that judges described executive plan is lower than the execution efficient of historical executive plan;
If the execution efficient of described executive plan lower than the execution efficient of described historical executive plan, is recovered described historical executive plan as executive plan.
15. equipment according to claim 14 is characterized in that, described processor also is used for carrying out:
When described SQL statement is not predefine SQL statement in predefine SQL grammer, compile described SQL statement, generate executive plan.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013100272660A CN103092970A (en) | 2013-01-24 | 2013-01-24 | Database operation method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2013100272660A CN103092970A (en) | 2013-01-24 | 2013-01-24 | Database operation method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103092970A true CN103092970A (en) | 2013-05-08 |
Family
ID=48205535
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2013100272660A Pending CN103092970A (en) | 2013-01-24 | 2013-01-24 | Database operation method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103092970A (en) |
Cited By (16)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103488715A (en) * | 2013-09-11 | 2014-01-01 | 北京思特奇信息技术股份有限公司 | Pre-resolution management method and system based on global configuration |
| CN103577560A (en) * | 2013-10-24 | 2014-02-12 | 华为技术有限公司 | Method and device for inputting data base operating instructions |
| CN107402991A (en) * | 2016-09-21 | 2017-11-28 | 广州特道信息科技有限公司 | A kind of method for writing semi-structured data and distributed NewSQL Database Systems |
| CN107451203A (en) * | 2017-07-07 | 2017-12-08 | 阿里巴巴集团控股有限公司 | Data bank access method and device |
| CN107798025A (en) * | 2016-09-05 | 2018-03-13 | 华为技术有限公司 | Operation, Compilation Method, device and the Database Systems of storing process |
| CN108073612A (en) * | 2016-11-14 | 2018-05-25 | 平安科技（深圳）有限公司 | The method and apparatus of synchronous SQL executive plans |
| RU2674886C2 (en) * | 2016-02-19 | 2018-12-13 | Хуавэй Текнолоджиз Ко., Лтд. | Method and device for determining sql execution plan |
| CN109213826A (en) * | 2017-06-30 | 2019-01-15 | 华为技术有限公司 | Data processing method and equipment |
| CN109614443A (en) * | 2018-11-06 | 2019-04-12 | 武汉达梦数据库有限公司 | For the DDL synchronous method and equipment of V9.7 version d B2 database |
| CN111563101A (en) * | 2020-07-11 | 2020-08-21 | 阿里云计算有限公司 | Execution plan optimization method, device, equipment and storage medium |
| CN112231374A (en) * | 2020-09-04 | 2021-01-15 | 北京南天软件有限公司 | General database drive adaptation interface system |
| WO2021078176A1 (en) * | 2019-10-25 | 2021-04-29 | 中兴通讯股份有限公司 | Storage process running method and apparatus, database system, and storage medium |
| CN113849520A (en) * | 2021-09-30 | 2021-12-28 | 平安科技（深圳）有限公司 | Intelligent identification method and device of abnormal SQL (structured query language), electronic equipment and storage medium |
| CN114168620A (en) * | 2022-02-11 | 2022-03-11 | 北京奥星贝斯科技有限公司 | Execution plan processing method and device |
| CN114996292A (en) * | 2022-05-05 | 2022-09-02 | 北京联华信科技有限公司 | Execution plan automatic change method, device, equipment and readable storage medium |
| US11544260B2 (en) | 2018-03-29 | 2023-01-03 | China Unionpay Co., Ltd. | Transaction processing method and system, and server |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1501235A (en) * | 2002-11-14 | 2004-06-02 | 深圳市中兴通讯股份有限公司上海第二 | Implement method of the man machine command based on plain text command script files |
| CN1801145A (en) * | 2005-01-06 | 2006-07-12 | 中兴通讯股份有限公司 | Method for improving database access efficiency of application system |
| CN101567006A (en) * | 2009-05-25 | 2009-10-28 | 中兴通讯股份有限公司 | Database system and distributed SQL statement execution plan reuse method |
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
-
2013
- 2013-01-24 CN CN2013100272660A patent/CN103092970A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1501235A (en) * | 2002-11-14 | 2004-06-02 | 深圳市中兴通讯股份有限公司上海第二 | Implement method of the man machine command based on plain text command script files |
| CN1801145A (en) * | 2005-01-06 | 2006-07-12 | 中兴通讯股份有限公司 | Method for improving database access efficiency of application system |
| CN101567006A (en) * | 2009-05-25 | 2009-10-28 | 中兴通讯股份有限公司 | Database system and distributed SQL statement execution plan reuse method |
| CN102436494A (en) * | 2011-11-11 | 2012-05-02 | 中国工商银行股份有限公司 | Device and method for optimizing execution plan and based on practice testing |
Cited By (26)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103488715B (en) * | 2013-09-11 | 2017-01-18 | 北京思特奇信息技术股份有限公司 | Pre-resolution management method and system based on global configuration |
| CN103488715A (en) * | 2013-09-11 | 2014-01-01 | 北京思特奇信息技术股份有限公司 | Pre-resolution management method and system based on global configuration |
| CN103577560A (en) * | 2013-10-24 | 2014-02-12 | 华为技术有限公司 | Method and device for inputting data base operating instructions |
| CN103577560B (en) * | 2013-10-24 | 2017-05-24 | 华为技术有限公司 | Method and device for inputting data base operating instructions |
| RU2674886C2 (en) * | 2016-02-19 | 2018-12-13 | Хуавэй Текнолоджиз Ко., Лтд. | Method and device for determining sql execution plan |
| US10901976B2 (en) | 2016-02-19 | 2021-01-26 | Huawei Technologies Co., Ltd. | Method and apparatus for determining SQL execution plan |
| CN107798025B (en) * | 2016-09-05 | 2020-06-02 | 华为技术有限公司 | Running and compiling method and device of stored process and database system |
| CN107798025A (en) * | 2016-09-05 | 2018-03-13 | 华为技术有限公司 | Operation, Compilation Method, device and the Database Systems of storing process |
| CN107402991B (en) * | 2016-09-21 | 2020-05-19 | 云润大数据服务有限公司 | Method for writing semi-structured data and distributed NewSQL database system |
| CN107451220A (en) * | 2016-09-21 | 2017-12-08 | 广州特道信息科技有限公司 | A kind of distributed NewSQL Database Systems |
| CN107451220B (en) * | 2016-09-21 | 2020-06-09 | 云润大数据服务有限公司 | Distributed NewSQL database system |
| CN107402991A (en) * | 2016-09-21 | 2017-11-28 | 广州特道信息科技有限公司 | A kind of method for writing semi-structured data and distributed NewSQL Database Systems |
| CN108073612A (en) * | 2016-11-14 | 2018-05-25 | 平安科技（深圳）有限公司 | The method and apparatus of synchronous SQL executive plans |
| CN109213826A (en) * | 2017-06-30 | 2019-01-15 | 华为技术有限公司 | Data processing method and equipment |
| CN109213826B (en) * | 2017-06-30 | 2021-10-22 | 华为技术有限公司 | Data processing method and device |
| CN107451203A (en) * | 2017-07-07 | 2017-12-08 | 阿里巴巴集团控股有限公司 | Data bank access method and device |
| CN107451203B (en) * | 2017-07-07 | 2020-09-01 | 阿里巴巴集团控股有限公司 | Database access method and device |
| US11544260B2 (en) | 2018-03-29 | 2023-01-03 | China Unionpay Co., Ltd. | Transaction processing method and system, and server |
| CN109614443A (en) * | 2018-11-06 | 2019-04-12 | 武汉达梦数据库有限公司 | For the DDL synchronous method and equipment of V9.7 version d B2 database |
| WO2021078176A1 (en) * | 2019-10-25 | 2021-04-29 | 中兴通讯股份有限公司 | Storage process running method and apparatus, database system, and storage medium |
| CN111563101A (en) * | 2020-07-11 | 2020-08-21 | 阿里云计算有限公司 | Execution plan optimization method, device, equipment and storage medium |
| CN112231374A (en) * | 2020-09-04 | 2021-01-15 | 北京南天软件有限公司 | General database drive adaptation interface system |
| CN113849520A (en) * | 2021-09-30 | 2021-12-28 | 平安科技（深圳）有限公司 | Intelligent identification method and device of abnormal SQL (structured query language), electronic equipment and storage medium |
| CN113849520B (en) * | 2021-09-30 | 2024-05-28 | 平安科技（深圳）有限公司 | Intelligent recognition method and device for abnormal SQL, electronic equipment and storage medium |
| CN114168620A (en) * | 2022-02-11 | 2022-03-11 | 北京奥星贝斯科技有限公司 | Execution plan processing method and device |
| CN114996292A (en) * | 2022-05-05 | 2022-09-02 | 北京联华信科技有限公司 | Execution plan automatic change method, device, equipment and readable storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103092970A (en) | Database operation method and device | |
| CN107463635B (en) | Method for inquiring picture data and distributed NewSQL database system | |
| CN102073490B (en) | Method and device for translating database language | |
| US10534590B2 (en) | Dynamic recompilation techniques for machine learning programs | |
| CN103136231B (en) | Method of data synchronization between a kind of heterogeneous database and system | |
| US6338069B1 (en) | Method and apparatus for managing functions | |
| US20140297592A1 (en) | Computer-readable medium storing program and version control method | |
| CN103984582A (en) | Method and device for hot updating | |
| US8874519B1 (en) | Method and apparatus for restoring a table in a database | |
| JPH05204988A (en) | Access method of server database | |
| CN109408493A (en) | A kind of moving method and system of data source | |
| CN103164455B (en) | The optimization method and device of data base | |
| US9535687B2 (en) | Audited builds based upon separate class dependency records | |
| CN110019111B (en) | Data processing method, data processing device, storage medium and processor | |
| CN111367893A (en) | Method and device for database version iteration | |
| US8914782B2 (en) | Optimization of declarative queries | |
| CN105677805A (en) | Data storing and reading method and device using protobuf | |
| CN103020196B (en) | The system of process database operations request | |
| CN100527131C (en) | Interdynamic access method and tool of IMS data base | |
| CN102043853A (en) | Method and device for updating stored data | |
| CN109558121A (en) | Development approach, device, equipment and the storage medium of interface drive program | |
| CN111142848B (en) | Software program compiling method, executing method and device | |
| US7624374B2 (en) | Readers and scanner design pattern | |
| CN113835690A (en) | Read-write separation multi-data source component and working method thereof | |
| CN103713887A (en) | Program code generator |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20130508 |
|
| RJ01 | Rejection of invention patent application after publication |