Method and system for database partition

CROSS-REFERENCE TO RELATED APPLICATION

p-0002This application claims priority under 35 U.S.C. §119 from Chinese Patent Application No. 200910136968.6 filed Apr. 30, 2009, the entire contents of which are incorporated herein by reference.

BACKGROUND OF THE INVENTION

p-00031. Field of the Invention

p-0004The present invention generally relates to a method and system for database partitioning. More particularly, this invention is related to a method and system for dynamic database partitioning.

p-00052. Description of Related Art

p-0006In current database systems, when a user's application data is loaded, data within a database will become extremely huge as the user's business grows. For example, a table which contains invoice records over ten years records can include a few million of lines of data. Querying such a huge table is very time-consuming.

p-0007In order to improve the speed and efficiency of database queries, database partitioning technology emerged. In particular, the database in a non-shared environment is broken down into separate partitions, and each partition has its own resources. Data is distributed to different partitions through Hash algorithms, and each partition is only responsible for dealing with their own data. When a user issues a database query, certain partitions can be used to process the request. The main purpose of the database partition is to improve the data-parallel processing performance, and to reduce the amount of data read/writes in specific database query operations in order to reduce the response time.

p-0008Database partitions can be a physical partition or a logical partition. Physical partitions refer to a partition deployed on a single physical machine, while logical partitions are partitions deployed on multi-processing system (Symmetric Multi Processing, referred to as SMP) machines. The database can be implemented on either a physical partition or a logical partition. If a large SMP machine is used, the database will usually adopt a logical partition; if more than one physical machine is used, the database will usually adopt physical partitions through a non-shared architecture; if multiple SMP machines are used, the database can adopt both physical and logical partitions.

p-0009Database partitioning technology has two advantages. First is the increased database performance. Continuing the above example, the invoice records which account for several millions of lines of data can be broken into ten different partitions, each containing one year's worth of records. Query time is then reduced to only one-tenth of that for a full table scan.

p-0010Second is the simplified data management. Partitioning technology can leverage the extensive experience of database administrators to enhance data management capabilities. Through the use of partitions, the administrator can simplify the implementation of specific data.

p-0011However, if a database has already been partitioned (either by a database vendor or by a developer), the administrator can not further modify the partition rules in subsequent database operations. Initialized partition methods normally only support partitions based on a hash table. This partitioning scheme has nothing to do with the application data. For example, the database can not partition by year in above example, which makes the database lack partition flexibility.

p-0012To make up for this deficiency, many database users write application code in order to achieve database partition when initializing partitions in the database. However, it is very difficult to achieve flexible data partitioning by writing application code because they normally require help from application developers. However, transaction based applications are becoming more and more popular. For most database applications, the administrator can not design a proper partitioning scheme before the database has been extensively used. As a result, with the continuous growth of business and the increasing amount of data, the database response time will rise over time, which can lead to an unacceptable user experience. In practice, the recent data query rates can be far higher than the historical data query rate. Partition rules set by the database administrator at very beginning need to be changed over time. For example, the dividing year needs to be changed from 2009 to 2010 one year later, which means data before and after 2010 should be recorded in two different partitions.

p-0013Therefore, although the prior art provides database partitioning, it can not achieve flexible data partitioning without modifying the current application code.

SUMMARY OF THE INVENTION

p-0014In order to address problems in prior arts, the present invention provides a flexible, dynamic database partition method and system. By adding a partition key into a data item of the database, the present invention can store database item(s) in certain partitions according to any provided partition rule. Therefore, the present invention provides a database partition solution which can be easily updated and customized. By using this invention, users of database can dynamically partition or re-partition the data stored in a database. Then the database user can achieve flexible data management with lower costs, and increase the speed and effectiveness of database queries. The present invention is not limited to any specific partitioning method such as logical or physical partitions. In fact, the present invention is applicable to either a logical partitioned or physical partitioned database.

p-0015According to an aspect of the present invention, a computer implemented method of partitioning data stored in a database is provided. The method includes the steps of acquiring a data partition rule, where the data partition rule is used to identify a first relationship between a data partition condition and a database partition; establishing a second relationship between the data partition condition and a data partition key based on the data partition rule and a third relationship between the database partition and the data partition key; adding the data partition key to a data item where the data item is stored in the database based on the second relationship between the data partition condition and the data partition key; and storing the data item in the database partition based on the data partition key of the data item.

p-0016According to another aspect of the present invention, a computer implemented method of querying a database when a data query request matches with a database partition is provided. The method includes the steps of acquiring the data query request; parsing the data query request; determining a data partition key which corresponds to the data query request; modifying the data query request to include the data partition key; and sending the processed data query request to the database.

p-0017According to another aspect of the present invention, a system for partitioning data stored in a database is provided. The system includes means for acquiring a data partition rule, where the data partition rule is used to identify a first relationship between a data partition condition and a database partition; means for establishing a second relationship between the data partition condition and a data partition key based on the data partition rule and a third relationship between the database partition and the data partition key; means for adding the data partition key to a data item where the data item is stored in the database according to the second relationship between the data partition condition and the data partition key; and means for storing the data item in the database partition according to the data partition key of the data item.

p-0018According to another aspect of the present invention, a database query system is provided. The system includes means for acquiring a data query request; means for parsing the data query request; means for determining whether the data query request matches with a partition; means for looking for a data partition key corresponding to the data query request if the data query request matches with the partition; means for processing the data query request to add the data partition key to the data query request; and means for sending the processed data query request to the database.

p-0019The advantages of present invention are generally illustrated in above description. For a more complete understanding of these and other advantages, a detailed description is made as following with reference to the accompanying drawings.

BRIEF DESCRIPTION OF THE DRAWINGS

p-0020It is to be noted that the appended drawings illustrate only typical embodiments of this invention and are therefore not to be considered limiting to its scope.

p-0021<figref idrefs="DRAWINGS">FIG. 1</figref> is a flow chart for partitioning data in a database.

p-0022<figref idrefs="DRAWINGS">FIG. 2</figref> is a flow chart for pre-calculating the data partition key which corresponds to a database partition for a database that supports data partitioning.

p-0023<figref idrefs="DRAWINGS">FIG. 3</figref> is a flow chart for defining data partition key for a database that does not support data partitioning.

p-0024<figref idrefs="DRAWINGS">FIG. 4</figref> is a flow chart for adding partition keys in a batch.

p-0025<figref idrefs="DRAWINGS">FIG. 5</figref> is a flow chart for adding partition keys one by one.

p-0026<figref idrefs="DRAWINGS">FIG. 6</figref> is a flow chart for processing data items in database.

p-0027<figref idrefs="DRAWINGS">FIG. 7</figref> is a flow chart for database query based on dynamic database partitioning technology.

p-0028<figref idrefs="DRAWINGS">FIG. 8</figref> is a block diagram for partitioning data of a database.

p-0029<figref idrefs="DRAWINGS">FIG. 9</figref> is a block diagram for a database query system based on dynamic database partitioning technology.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

p-0030In the following discussion, numerous specific details are set forth to provide a thorough understanding of the present invention. It should be further noted that any particular nomenclature that follows is used merely for convenience, and thus the invention should not be limited to use solely in any specific application identified and/or implied by such nomenclature.

p-0031Unless indicated otherwise, all functions described herein can be performed in either hardware or software, or some combination thereof. In a preferred embodiment, the function is performed by a processor such as a computer or an electronic data processor in accordance with code such as computer program code, or integrated circuits that are coded to perform such function. In general, the routines executed to implement the embodiments of the invention, can be part of an operation or a specific application, component, program, module, object or sequence of instructions. The software of the present invention typically includes a multitude of instructions that will be translated by the native computer into a machine-readable format and hence executable instructions. Also programs include variables and data structures that either reside locally to the program or are found in memory or on storage devices. In addition, various programs described hereinafter can be identified based upon the application for which they are implemented in a specific embodiment of the invention. Such signal-bearing media, when carrying computer-readable instructions that direct the functions of the present invention, represent embodiments of the present invention.

p-0032<figref idrefs="DRAWINGS">FIG. 1</figref> is a flow chart for partitioning data in a database. Data partitioning rules, which indicate the relationship between the data partition condition and data partition, are obtained in step <b>101</b>. According to one embodiment of this invention, the data partition rules are defined based on data query frequency in the database. For example, the frequently queried data is put into the same data partition, while the data that is not frequently queried will be put into other partitions. Data partition rules can be defined by a database application system administrator based on their experience and understanding of the data query, and can also be defined based on certain feedback mechanisms (the feedback mechanism will be carried out later in a more detailed description).

p-0033For example, as shown in table 1 below, assuming a database application only stores a table with the name of Sales_order, which reflects the product sales status. The column values of the table include: Id, Custermer_id, Product, Year, and Status, where the Status includes three kinds of statuses: running, finished, and canceled.

p-0034<tables id="TABLE-US-00001" num="00001"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="offset" colwidth="14pt" align="left" /><colspec colname="1" colwidth="21pt" align="left" /><colspec colname="2" colwidth="63pt" align="left" /><colspec colname="3" colwidth="42pt" align="left" /><colspec colname="4" colwidth="28pt" align="left" /><colspec colname="5" colwidth="49pt" align="left" /><thead><row><entry /><entry namest="offset" nameend="5" rowsep="1">TABLE 1</entry></row><row><entry /><entry namest="offset" nameend="5" align="center" rowsep="1" /></row><row><entry /><entry>Id</entry><entry>Custermer_id</entry><entry>Product</entry><entry>Year</entry><entry>Status</entry></row><row><entry /><entry namest="offset" nameend="5" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>1</entry><entry>999</entry><entry>milk</entry><entry>2009</entry><entry>Running</entry></row><row><entry /><entry>2</entry><entry>888</entry><entry>candy</entry><entry>2008</entry><entry>Running</entry></row><row><entry /><entry>3</entry><entry>777</entry><entry>flour</entry><entry>2006</entry><entry>Finished</entry></row><row><entry /><entry>4</entry><entry>666</entry><entry>dish</entry><entry>2007</entry><entry>Canceled</entry></row><row><entry /><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry /><entry namest="offset" nameend="5" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0035A system administrator usually has some experience with the data query status in the database. For example, he knows the most frequently requested information is the data after 2009 or data with a Status value of “Running.” However, the system administrator can not define the most appropriate data partitioning rules without a log of database querying.

p-0036According to one embodiment of this invention, the system administrator can divide the database into three partitions: P0, P1 and P2. The system administrator manage the three partitions (not shown in the figure) in several ways: create new partitions when loading new data; and update existing partitions by modifying the existing data partition rules instead of modifying the existing partitions, to re-allocate data in the database according to the new data partition rules.

p-0037In Table 2, the system administrator wants to form partition rules by relating the relevant partition conditions to specific partitions. Be noted that the present invention does not attempt to protect any particular partition strategy, because these strategies depends entirely on the characteristics of the database application itself. The strategy is illustrated only for the purpose of describing application scenario of the present invention.

p-0038<tables id="TABLE-US-00002" num="00002"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="offset" colwidth="14pt" align="left" /><colspec colname="1" colwidth="42pt" align="left" /><colspec colname="2" colwidth="112pt" align="left" /><colspec colname="3" colwidth="49pt" align="left" /><thead><row><entry /><entry namest="offset" nameend="3" rowsep="1">TABLE 2</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row><row><entry /><entry>Partition</entry><entry /><entry /></row><row><entry /><entry>Rules</entry><entry>Partition Conditions</entry><entry>Partitions</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>R1</entry><entry>Year<2009 and Status=finished</entry><entry>P0</entry></row><row><entry /><entry>R2</entry><entry>Year<2009 and Status=canceled</entry><entry>P1</entry></row><row><entry /><entry>R3</entry><entry>Year<2009 and Status=running</entry><entry>P2</entry></row><row><entry /><entry>R4</entry><entry>Year>=2009</entry><entry>P2</entry></row><row><entry /><entry namest="offset" nameend="3" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0039In <figref idrefs="DRAWINGS">FIG. 1</figref>, the letter A indicates that the optional step of acquiring a data partition key corresponding to each partition which will be discussed later in more detail.

p-0040In step <b>103</b>, the relationship between the data partition condition and the data partition key based on the data partition rule and the relationship between the database partition and the data partition key is established. The relationship between the data partition condition and the data partition key can be recorded in a match table. It is well-known to the person skilled in the art that for a database supporting partitioning, each partition corresponds to at least one data partition key. The data partition key can flexibly manage the data stored in certain partition. In this step, the data partition key is used to identify which partitions correspond with which data partition rules. The process of acquiring data partition key will be discussed later in more detail. In an embodiment of this invention P0 corresponds PK of 3690, P1 corresponds to PK of 3712, P2 corresponds to PK of 2730.

p-0041Table 3 shows a match table which contains the relationships between data partition conditions and data partition keys. In particular, Table 3 can be implemented as in Table 3-1 or Table 3-2, wherein Table 3-1 indicates each parameter by a range, while Table 3-2 indicates each parameter by a more detailed manner. According to one embodiment of this invention, a PK only corresponds to one partition. But it is understood by the person skilled in the art that, in practice there can be multiple PK corresponding to one partition.

p-0042<tables id="TABLE-US-00003" num="00003"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="1" colwidth="105pt" align="left" /><colspec colname="2" colwidth="42pt" align="center" /><colspec colname="3" colwidth="49pt" align="left" /><colspec colname="4" colwidth="21pt" align="left" /><thead><row><entry namest="1" nameend="4" rowsep="1">TABLE 3-1</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row><row><entry /><entry>Parameter 1</entry><entry>Parameter 2</entry><entry /></row><row><entry>SQL Pattern</entry><entry>(Year)</entry><entry>(Status)</entry><entry>PK</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>Year<2009 and Status=Finished</entry><entry><2009</entry><entry>Finished</entry><entry>3690</entry></row><row><entry>Year<2009 and Status=Canceled</entry><entry><2009</entry><entry>Canceled</entry><entry>3712</entry></row><row><entry>Year<2009 and Status=Running</entry><entry><2009</entry><entry>Running</entry><entry>2730</entry></row><row><entry>Year>=2009</entry><entry>>=2009</entry><entry /><entry>2730</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0043<tables id="TABLE-US-00004" num="00004"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="4"><colspec colname="1" colwidth="112pt" align="left" /><colspec colname="2" colwidth="42pt" align="left" /><colspec colname="3" colwidth="42pt" align="left" /><colspec colname="4" colwidth="21pt" align="left" /><thead><row><entry namest="1" nameend="4" rowsep="1">TABLE 3-2</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row><row><entry /><entry>Parameter 1</entry><entry>Parameter 2</entry><entry /></row><row><entry>SQL Pattern</entry><entry>(Year)</entry><entry>(Status)</entry><entry>PK</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>Year < 2009 and Status = Finished</entry><entry>2008</entry><entry>Finished</entry><entry>3690</entry></row><row><entry>Year < 2009 and Status = Finished</entry><entry>2007</entry><entry>Finished</entry><entry>3690</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry>Year < 2009 and Status = Canceled</entry><entry>2008</entry><entry>Canceled</entry><entry>3712</entry></row><row><entry>Year < 2009 and Status = Canceled</entry><entry>2007</entry><entry>Canceled</entry><entry>3712</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry>Year < 2009 and Status = Running</entry><entry>2008</entry><entry>Running</entry><entry>2730</entry></row><row><entry>Year < 2009 and Status = Running</entry><entry>2007</entry><entry>Running</entry><entry>2730</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry>Year > = 2009</entry><entry>2009</entry><entry /><entry>2730</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry namest="1" nameend="4" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0044Back to <figref idrefs="DRAWINGS">FIG. 1</figref>, the letter B illustrates an optional process of processing data items in the database, which will be described later in more detail.

p-0045Next, in step <b>105</b>, the data partition key is added to data items in the database based on the relationship between the data partition condition and the data partition key. The data partition key can be added through a new column in the database. Table 4 shows a table wherein the partition key is added.

p-0046<tables id="TABLE-US-00005" num="00005"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="28pt" align="left" /><colspec colname="2" colwidth="56pt" align="left" /><colspec colname="3" colwidth="35pt" align="left" /><colspec colname="4" colwidth="28pt" align="left" /><colspec colname="5" colwidth="42pt" align="left" /><colspec colname="6" colwidth="28pt" align="left" /><thead><row><entry namest="1" nameend="6" rowsep="1">TABLE 4</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row><row><entry>Id</entry><entry>Custermer_id</entry><entry>Product</entry><entry>Year</entry><entry>Status</entry><entry>PK</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry>1</entry><entry>999</entry><entry>milk</entry><entry>2009</entry><entry>Running</entry><entry>2730</entry></row><row><entry>2</entry><entry>888</entry><entry>candy</entry><entry>2008</entry><entry>Running</entry><entry>2730</entry></row><row><entry>3</entry><entry>777</entry><entry>flour</entry><entry>2006</entry><entry>Finished</entry><entry>3690</entry></row><row><entry>4</entry><entry>666</entry><entry>dish</entry><entry>2007</entry><entry>Canceled</entry><entry>3712</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0047In <figref idrefs="DRAWINGS">FIG. 1</figref>, the letter of C corresponding to step <b>105</b> illustrates the specific step of adding data partition key will be described later in more detail. In step <b>107</b>, the data item is stored into a partition of the database based on the data partition key of the data item. For example, the data item, whose Id is 1 in Table 4, will be stored in partition P2 due to the partition key 2730 being added. Similarly, the data item, whose Id is 2, will be stored in partition P2, the data item, whose Id is 3, will be stored in partition P0, and the data item, whose Id is 4, will be stored in partition P1, etc. Finally all the items will be moved to the specified partition.

p-0048When a new data is put into the database, PK will be added according to the step showed in step <b>105</b>. Therefore the new data will be located in a specified partition. Although above describes data migration of existing data, it can be understood by the person skilled in the art that the same method can be applied for a new data that needs to be loaded according to the data partition rules.

p-0049<figref idrefs="DRAWINGS">FIG. 2</figref> is a flow chart for pre-calculating the data partition key corresponding to a partition for a database that supports data partitioning. Database partition technology in the prior art normally scatters data uniformly among different partitions using a Hash algorithm, where each partition is only responsible for processing its own data. Since the Hash algorithm is not disclosed publicly, database users have no way of knowing which data partition key is associated to which partition. For example, a user does not know which data partition key can help to allocate data into P0. Therefore, pre-calculation of which partition key corresponds to which partition is required.

p-0050Specifically, in step <b>203</b>, a table is created in the database. The table is an empty table, where the data partition key is stored in a column. The following command language can be used to create an empty table:

p-0051Create table table1 (col1 BIGINT) DISTRIBUTE BY HASH (col1) <br /> The command language is used to create an empty table table1, which has a column called col1, and col1 is used for storing integer data.

p-0052In step <b>205</b>, a data partition key is inserted into the table. The partition key can be a random number, for example any integer number between 1000-9999, like 3690. The purpose of inserting a random number into an empty table is to test the corresponding partition of the random number. The following is sample command language for inserting data partition key into an empty table:

p-0053Insert into table 1 values(3690); <br /> Insert into table 1 values(3712); <br /> Insert into table 1 values(2730)

p-0054In step <b>207</b>, the database is queried for partition numbers which correspond to the data partition key (3690, 3712, 2730). Specifically, this step is achieved through querying which partition will be allocated for a record containing certain data partition key (for example, a random). Therefore the relationship between the data partition key and the partition numbers can be determined. This step can be achieved through following command.

p-0055Select col1, dbpartitionnum(col1) from table1 where col1=2730

p-0056The above command language is used to return the specific partition number corresponding to data partition key 2730 in table1. Similarly, this step can be achieved through inputting all the data partition keys and querying the database for all the corresponding partition numbers. The following is sample command language which will return all partition numbers which correspond to all the data partition keys in table 1:

p-0057Select col1, dbpartitionnum(col1) from table1

p-0058According to above embodiment, partition number 0 is returned after partition key 3690 is inserted, partition number 1 is returned after partition key 3712 is inserted, partition number 2 is returned after partition key 2730 is inserted. When a random number is inserted, one is not sure which partition number will be returned. In other words, it is possible that the same partition number will be returned in response to various random numbers being inserted.

p-0059According to one embodiment of this invention, steps <b>205</b> and <b>207</b> need to be repeated until each partition has at least one corresponding partition key (not shown in the Figure). Therefore, the match table between partition and data partition key is established in Table 5 below (not shown in the Figure):

p-0060<tables id="TABLE-US-00006" num="00006"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="3"><colspec colname="offset" colwidth="42pt" align="left" /><colspec colname="1" colwidth="84pt" align="left" /><colspec colname="2" colwidth="91pt" align="left" /><thead><row><entry /><entry namest="offset" nameend="2" rowsep="1">TABLE 5</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row><row><entry /><entry>Partition</entry><entry /></row><row><entry /><entry>Number</entry><entry>Partition Key</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>0</entry><entry>3690</entry></row><row><entry /><entry>1</entry><entry>3712</entry></row><row><entry /><entry>2</entry><entry>2730</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0061It should be noted that the method shown in <figref idrefs="DRAWINGS">FIG. 2</figref> can find multiple partition keys corresponding to the same partition number. For example partition key 4623 can also correspond to partition number 0. According to one embodiment of this invention, only one partition key corresponding to a partition number is required.

p-0062<figref idrefs="DRAWINGS">FIG. 3</figref> illustrates a flow chart for defining data partition key for a database that does not support data partitioning. Currently, most large database products support partitioning, for example DB2, Oracle, and MySQL (since version 5.1). However, there are some database products that do not support partitioning. In addition, these database products do not support partition management based on data partition keys. However, it is possible to achieve the data partitioning functionality of this invention on a database that does not support data partition. In step <b>301</b>, multiple partition numbers are defined for multiple partitions, so that one database is deemed as one partition. In step <b>303</b>, the data partition key is defined to make each data partition key correspond to one partition number. A match table indicating the relationship between partition number and partition key is established as shown in Table 6 below. In one simple example, a partition number is the same as partition key.

p-0063<tables id="TABLE-US-00007" num="00007"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="3"><colspec colname="offset" colwidth="42pt" align="left" /><colspec colname="1" colwidth="84pt" align="left" /><colspec colname="2" colwidth="91pt" align="left" /><thead><row><entry /><entry namest="offset" nameend="2" rowsep="1">TABLE 6</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row><row><entry /><entry>Partition</entry><entry /></row><row><entry /><entry>number</entry><entry>Partition Key</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /><entry>0</entry><entry>0</entry></row><row><entry /><entry>1</entry><entry>1</entry></row><row><entry /><entry>2</entry><entry>2</entry></row><row><entry /><entry namest="offset" nameend="2" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0064The technical resolutions provided by <figref idrefs="DRAWINGS">FIG. 2</figref> or <b>3</b> are processed after step <b>101</b> “acquiring data partition rule” in <figref idrefs="DRAWINGS">FIG. 1</figref>. It is understood by the person skilled in the art that, the matching relationship between the partition and the data partition key can be established at any time prior to establishing the relationship between data partition rule and data partition key, and does not have to occur right after the “acquiring data partition rule” step.

p-0065<figref idrefs="DRAWINGS">FIG. 4</figref> is a flow chart for adding partition keys in a batch. In step <b>401</b>, a data query is formulated according to the data partition condition. For example, data items that comply with the partition condition of “Year&lt;2009 and Status=finished” are retrieved based on data partition rule R1. In step <b>403</b>, data items corresponding to the data partition condition are obtained. In step <b>405</b>, the match table (Table 3) is queried to acquire the data partition key (such as partition key 3690 corresponding to R1) that corresponds to the data partition condition. In step <b>407</b>, the data partition key is unitarily added into the data items. Table 7 below shows the data items table in which data partition keys have been added. Step <b>409</b>, returns the process back to the step <b>105</b> in <figref idrefs="DRAWINGS">FIG. 1</figref>.

p-0066<tables id="TABLE-US-00008" num="00008"><table frame="none" colsep="0" rowsep="0"><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="14pt" align="center" /><colspec colname="2" colwidth="70pt" align="center" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="35pt" align="center" /><colspec colname="5" colwidth="35pt" align="left" /><colspec colname="6" colwidth="35pt" align="center" /><thead><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row><row><entry>Id</entry><entry>Custermer_id</entry><entry>Product</entry><entry>Year</entry><entry>Status</entry><entry>PK</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row></thead><tbody valign="top"><row><entry /></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><tbody valign="top"><row><entry>Table 7-1 data item(s) table in partition P0</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="14pt" align="center" /><colspec colname="2" colwidth="70pt" align="center" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="35pt" align="center" /><colspec colname="5" colwidth="35pt" align="left" /><colspec colname="6" colwidth="35pt" align="center" /><tbody valign="top"><row><entry>3</entry><entry>777</entry><entry>flour</entry><entry>2006</entry><entry>Finished</entry><entry>3690</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><tbody valign="top"><row><entry>Table 7-2 data item(s) table in partition P1</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="14pt" align="center" /><colspec colname="2" colwidth="70pt" align="center" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="35pt" align="center" /><colspec colname="5" colwidth="35pt" align="left" /><colspec colname="6" colwidth="35pt" align="center" /><tbody valign="top"><row><entry>4</entry><entry>666</entry><entry>dish</entry><entry>2007</entry><entry>Canceled</entry><entry>3712</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="1"><colspec colname="1" colwidth="217pt" align="center" /><tbody valign="top"><row><entry>Table 7-3 data item(s) table in partition P2</entry></row></tbody></tgroup><tgroup align="left" colsep="0" rowsep="0" cols="6"><colspec colname="1" colwidth="14pt" align="center" /><colspec colname="2" colwidth="70pt" align="center" /><colspec colname="3" colwidth="28pt" align="left" /><colspec colname="4" colwidth="35pt" align="center" /><colspec colname="5" colwidth="35pt" align="left" /><colspec colname="6" colwidth="35pt" align="center" /><tbody valign="top"><row><entry>1</entry><entry>999</entry><entry>milk</entry><entry>2009</entry><entry>Running</entry><entry>2730</entry></row><row><entry>2</entry><entry>888</entry><entry>candy</entry><entry>2008</entry><entry>Running</entry><entry>2730</entry></row><row><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry><entry>. . .</entry></row><row><entry namest="1" nameend="6" align="center" rowsep="1" /></row></tbody></tgroup></table></tables>

p-0067<figref idrefs="DRAWINGS">FIG. 5</figref> is a flow chart for another embodiment of the present invention which is adding partition keys one by one. Step <b>501</b> retrieves data items one by one from the database. In step <b>503</b>, the data partition conditions which correspond to the data items are analyzed. In step <b>505</b>, the match table is queried to acquire the data partition key which corresponds to the data items. In step <b>507</b>, the data partition key is added into the data items, so that data item tables in Table 7 are formed. In step <b>509</b>, is the process returns to the step <b>105</b> of <figref idrefs="DRAWINGS">FIG. 1</figref>.

p-0068Cache is high-speed, small-capacity temporary memory, which can be achieved through a high-speed static memory chip, or integrated into the CPU chip. Cache is used for storing the most frequently accessed instructions or operational data. The content of cache can be frequently changed due to hit rate reduction. According to one embodiment of the present invention, the cache can be used to store the match table to improve subsequent data querying speed.

p-0069The data structure of column value of the data items in the database can be the same as that of the data partition conditions. Therefore parameters in the match table can be directly used to determine whether a data item complies with any of the data partition rules. However, the data structure of column value of the data items in the database can not always be the same as that of the data partition condition. In a simple example, one parameter in the data partition condition is year, such as “2002”, while in the actual data item table, more detailed information about date can be stored as column value, such as “2002.10.1”. The column value of data item in the database is inconsistent with that of data partition condition. Therefore there is a need to further process the data items.

p-0070<figref idrefs="DRAWINGS">FIG. 6</figref> is a flow chart for processing data item(s) in database. Step <b>601</b> scans the column value of the data items. Step <b>603</b>, determines whether the data structure in the column value is consistent with that of parameters in the data partition condition. If the data structures are the same, is the process is returned in step <b>607</b>. If the data structures are different, the column value of the data items need to be processed to make the data structure consistent with the parameters in the data partition condition. For example, “2002.10.1” is processed to indicate the Year value of “2002”. The process is returned in step <b>607</b>. The value of Year identified by the processed data items can be directly used in subsequent steps to determine which partition key should be added to which data items.

p-0071<figref idrefs="DRAWINGS">FIG. 7</figref> is a flow chart for processing a database query based on dynamic database partition technology. In step <b>701</b>, a data query request is acquired. The following is a sample data query request:

p-0072Select*from Sales_order where Year=2005 and Status=‘Finished’

Data Query Request 1

p-0073In step <b>703</b>, the data query request is parsed. The process of parsing is used to determine the column value of data query request. For example, the column value of Data Query Request 1 is Year=2005 and Status=‘Finished’. Step <b>705</b>, determines whether the data query request matches with a partition, which means the data query request can be conducted by querying only one partition. Apparently, Data Query Request 1 complies with data partition rule R1, and hence is matched with only partition P0. But in practice, not all the data query requests can be matched to one partition. For example, the following Data Query Request 2 can not match with a single partition.

p-0074Select*from Sales_order where Product=‘telephone’

Data Query Request 2

p-0075In practice, data query requests can be matched with multiple partitions. For example, the following data query request can match both P0 and P2:

p-0076Select*from Sales_order where Year>2005 and Status=‘Finished’

Data Query Request 3

p-0077It is understood by the person skilled in the art that sometimes the data query request can match all the partitions in the database. This is illustrated in following data query request:

p-0078Select*from Sales_order where Year>2005

Data Query Request 4

p-0079In step <b>707</b>, if a data query request matches at least one partition, the data partition key corresponding to the data query request will be searched. This step can be achieved through searching a match table as exemplified in Table 3. For example, according to the match table in Table 3, the data partition key with regards to the Data Query Request 1 should be 3690, and the data partition key with regards to the Data Query Request 3 should be 3690 and 2730.

p-0080In step <b>709</b>, the data query request to add the data partition key to the data query request is processed. For example, Data Query Request 1 can be modified as follows:

p-0081Select*from Sales_order where Year=2005 and Status=‘Finished’ and PK=3690 <br /> And the Data Query Request 3 can be modified as follows: <br /> Select*from Sales_order where Year>2005 and Status=‘Finished’ and PK=3690,2730

p-0082In step of <b>711</b>, the processed data query request is sent to the database. If the data query request matches at least one data partition, the data query request sent to the database is the request with the data partition key added. Therefore a search will only be conducted within particular matched partition, instead of all partitions. If the data query request does not match any data partitions, the data query request sent to the database is the request without any partition keys added, which is the original query request. Therefore, a search will be conducted across all partitions of the database.

p-0083Step <b>713</b> is the optional step of recording the feedback information of the database query. The feedback information can help to determine whether the current data partition rule is reasonable or not, and recommend a reasonable data partition rule which will optimize database query performance. In practice, it is very difficult for many system administrators to determine the most ideal data partition rule at very beginning unless they have a very careful design at the very beginning. Therefore feedback information can help the system administrators correct the initial partition rule design.

p-0084Specifically, the feedback information can include at least one of following pieces of information: a data query condition in data query request, such as where Year=2005 and Status=‘Finished’, the number of data items returned from the database according to the data query condition, the response time for returning the data items, the partition queried in practice. This feedback information can be used to determine which items are frequently queried and which are not. In step <b>715</b>, statistics are made of off the feedback information. The statistics can include at least one of the following: statistics of the data queries with same data query condition, statistics of which data items are hit for each data query, statistics of the response time for returning the data items corresponding to each data query condition, statistics of queried times of each partition in practice.

p-0085<figref idrefs="DRAWINGS">FIG. 8</figref> is a block diagram for partitioning data in a database. The system for partitioning data stored in a database <b>801</b> includes means for acquiring data partition rule <b>803</b>, means for establishing corresponding relationship between data partition condition and data partition key <b>805</b>, means for adding the data partition key to data items <b>807</b>, and means for storing the data items <b>809</b>.

p-0086Specifically, means for acquiring data partition rule <b>803</b> is used to acquire a data partition rule, which is referred to in Table 2. Optionally, means <b>803</b> can obtain manually inputted data partition rule from a manual input device <b>815</b>, or can obtain data partition rule from a feedback statistics unit <b>817</b>. The feedback statistics unit <b>817</b> is used to make statistics about the feedback information of database query, and can be used to update/create data partition rule.

p-0087Means for establishing relationships between data partition conditions and data partition keys <b>805</b> is used to build relationships between the data partition conditions and data partition keys based on the data partition rule and the relationship between partitions and the data partition keys. The relationship between the data partition conditions and data partition keys will be recorded in the match table, which will be stored in the metadata storage <b>811</b>. Specifically, by determining the relationships between the data partition rule and partition in the above Table 5 or Table 6, the relationships between the data partition condition and the data partition key in above Table 3 can be built. Further, the data partition key can be used to determine which partition data items complying with certain conditions need to be stored.

p-0088Means for adding the data partition key to data items <b>807</b> is used to add corresponding data partition key to the data items in the database based on the data partition rule. The data partition key can be used as a new column value to extend the table in the database. Means for storing the data items <b>809</b> is used to store the data items in a particular partition of the database <b>813</b> according to the data partition key. The data items which have had data partition key added can be referred in the above Table 7.

p-0089Optionally, partitions in the database <b>813</b> can be configured with different physical resources. Providing better physical resources for some partitions can further improve query efficiency in the partitions. According to one embodiment of present invention, historical data that is not frequently queried are stored in P0 and P1, while the recent data or data that is frequently queried is stored in P2. P2 is configured with better physical resources, such as a faster CPU, faster disk and faster memory to achieve faster I/O speed and faster processing capability which reduces query response time in P2.

p-0090<figref idrefs="DRAWINGS">FIG. 9</figref> is a block diagram for database query system based on dynamic database partitioning technology. The database query system <b>901</b> includes means for acquiring a data query request <b>903</b>, means for parsing the data query request <b>905</b>, means for determining whether the data query request match with a partition <b>907</b>, means for looking for the data partition key <b>909</b>, means for adding the data partition key to the data query request <b>911</b>, and means for sending the processed data query request <b>913</b>. Optionally, an embodiment of the present invention further includes a feedback record unit <b>915</b> and a feedback statistics unit <b>917</b>.

p-0091Specifically, means for acquiring a data query request <b>903</b> is used to acquire a data query request inputted by a user through a UI. Means for parsing the data query request <b>905</b>, is used to parse the data query request to acquire its column value. Means for determining whether the data query request matches with a partition <b>907</b> is used to determine whether the data query request can match with at least one partition based on the parsed column value of the data query request and the data partition rule.

p-0092In another words, means <b>907</b> is used to determine whether the query can be finished by searching at least one partition instead of all partitions. Means for looking for the data partition key <b>909</b> is used to look for a data partition key corresponding to the data query request if the determining means <b>907</b> determines that the data query request matches at least one partition. Specifically, the looking means <b>909</b> can obtain corresponding data partition key through the match table exemplified in Table 3 stored in the metadata storage <b>919</b>.

p-0093Means for adding the data partition key to the data query request <b>911</b> is used to add the data partition key to the data query request. Means for sending the processed data query request <b>913</b> is used to send data query request to the database <b>921</b>. In the event that the data query request matches at least one partition, the sending means <b>913</b> is used to send the data query request with data partition key added to the data query request, so that the search is conducted within certain matching partitions, instead of all the partitions. In the event that data query request does not match any partition, the sending means <b>913</b> will send a data query request to the database without data partition key added to data query request, in another words, the original query request. Therefore, the search will be conducted within all the partitions of the entire database. Feedback record unit <b>915</b> is used to record feedback information of the database query. Feedback statistics unit <b>917</b> is used to make statistics for the feedback information.

p-0094Furthermore, each of the above operations can be implemented by executing program code stored a computer program product. The program product defines functions of each embodiment and contains a variety of signals, including, but not limited to: (i) information permanently stored on non-writable storage media; (ii) information stored on writable storage media; or (iii) information conveyed to a computer by a communications medium, such as through a computer or telephone network, including wireless communications. The latter embodiment specifically includes information downloaded from the Internet and other networks.

p-0095Embodiments in the present invention provide many advantages, including those that are already illustrated in this specification, and those that can be generalized from the technical solutions. No matter whether an embodiment can realize all advantages or not, and no matter what substantial improvement is achieved as a result of these advantages, no limitation shall be made to the present invention. While the foregoing is directed to specific embodiments of the present invention for the purpose of illustration, other and further embodiments of the invention can be readily devised by one person skilled in the art without departing from the basic scope of the invention which is defined by the claims.