CN105408883A - Database table column annotation - Google Patents

Abstract

According to an example, database constraint generation may include receiving data related to a table in a database, analyzing the data to determine a row count for a column of the table, and analyzing the data to determine a number of distinct values for the column of the table. A comparison value may be determined by comparing the row count to the number of distinct values. The database constraint generation may further include determining if the comparison value is within a threshold. If the comparison value is within the threshold, an annotation may be added to the column of the table such that the column is considered unique during a cardinality estimation process involving the table.

Description

Database table column is explained

Background technology

Database (such as relational database) generally includes for the table analyzed in response to inquiry.Such as, for relational database, usually use relational model to describe the set with the table of organising data item in form.Table can comprise column constraint, such as unique key (uniquekey), major key (primarykey) or external key (foreignkey).Such as, unique key can comprise one or more data attributes of data entity in table.Major key uniquely can specify the tuple (tuple) in table, because, the characteristic of major key every a line of definition list uniquely.External key is the field in the relation table that matches of the primary key column shown with another.

Accompanying drawing explanation

Feature of the present disclosure illustrates by way of example in following (multiple) figure, and is not limited in this (multiple) figure, and wherein identical numeral indicates identical key element, wherein:

Fig. 1 illustrates the framework generating equipment according to the constraint database of example of the present disclosure;

Fig. 2 illustrates and shows according to the shop of example of the present disclosure;

Fig. 3 illustrates the sales volume table according to example of the present disclosure;

Fig. 4 illustrates the supply item table according to example of the present disclosure;

Fig. 5 illustrates the intermediate result after according to the connection (join) between the shop at Fig. 2 of example of the present disclosure table with the sales volume table of Fig. 3;

Fig. 6 illustrates the intermediate result after according to the connection between the shop at Fig. 2 of example of the present disclosure table with the supply schedule of Fig. 4;

Fig. 7 illustrates the method generated for constraint database according to example of the present disclosure; And

Fig. 8 illustrates the computer system according to example of the present disclosure.

Embodiment

In order to simple and illustrative object, with reference to example, the disclosure is described by main.In the following description, numerous detail has been set forth to provide thorough understanding of the present disclosure.But, it is evident that, the disclosure can be put into practice when being not limited to these details.In other instances, the certain methods not yet described in detail and structure are in order to avoid unnecessarily make the disclosure obscure.

Throughout the disclosure, term " " and " one " are intended to represent at least one in specific factor.As used herein, term " comprises " and means to include but not limited to, term " includes " and means to include but be not limited to.Term "based" mean at least in part based on.

For the database (such as relational database) generally included for the table analyzed in response to inquiry, the information (such as unique key, major key and external key) about tabular constraint is needs usually for Optimizing Queries performance.When such table is filled, it may be heavy for adding tabular constraint.Therefore, table generation person (such as, user) may be avoided during database design, use this tabular to retrain, or cannot using existing constraint as part input statistical conversion to database.If have a mind to or by mistake omit the constraint of such tabular, then determine exact cause that query performance aspect is reduced and may be challenging and consuming time by adding that tabular constraint remedies further to this situation.

According to an example, disclosed herein a kind of constraint database and generate equipment and a kind of method generated for constraint database.Apparatus and method disclosed herein can infer table note, and it is " soft unique (soft-unique) constraint " hereinafter referred to as.The query planning that equipment disclosed herein and method can also use this inferred results to come in guide data storehouse, thus significantly improve query performance.

According to an example, this constraint database generates equipment can comprise storer, it stores the machine readable instructions in order to carry out following operation: receive the data relevant with the table in database, analyze described data to determine the row counting of the row for described table, and analyze described data to determine the different value number of the row for described table.Described storer can also store the machine readable instructions of carrying out following operation: determine fiducial value by described row counting and described different value number being compared, and determines described fiducial value whether in threshold value.If fiducial value is in threshold value, then annotation can be added to the row of described table, to insert the data of described row or to upgrade the enforcement of the unique attribute arranged described in limit.Described constraint database generates equipment can comprise the processor realizing described machine readable instructions.

According to another example, the method generated for constraint database can comprise: receive and show relevant data in database, analyze described data to determine the row counting of the row for described table, and analyze described data to determine the different value number of the row for described table.Fiducial value can be determined by being compared with described different value number by described row counting.Described method also comprises determines described fiducial value whether in threshold value.If described fiducial value is in threshold value, then annotation can be added to the row of described table, make to regard described row as unique during relating to the sets cardinal process of described table.

Fig. 1 illustrates the framework generating equipment 100 according to the constraint database of an example.With reference to Fig. 1, equipment 100 is depicted as and comprises database analysis module 101, and it is in order to from such as namely showing 1-N with table 103() relevant database 102 receives data.Row counting determination module 104 can analytical table 103, to determine the row counting for each table 103.Different value number determination module 105 can analytical table 103, to determine different value number of each row for each table 103.Alternatively, row counting determination module 104 and different value number determination module 105 can perform corresponding analysis to the table 103 of database 102 and/or selected row.By the row counting of the particular column for a table from table 103 and different value number being compared, row counting and different value number comparison module 106 can determine whether the data for this particular column are unique.Annotation adds module 107 can compare data from row counting and the reception of different value number comparison module 106, and determines the comparison data that receive whether in threshold value 108.Like this, annotation adds module 107 and can determine that the row counting received from row counting determination module 104 is whether in the predetermined threshold number percent of the different value number received from different value number determination module 105 (namely in threshold value 108).In addition, user's input that can be relevant based on the percent difference (such as, the difference of 5%) between row counting and different value number carrys out configured threshold 108.If the comparison data received are in threshold value 108, then annotation adds module 107 and can add table note 109, it is hereinafter referred to as the soft unique constraint to suitable row (that is, following row: in this row place row counting and estimated different value number approximate match (namely within threshold value 108)).

In the actuating equipment 100 of module 101 and 104-107 and equipment 100, other assemblies of other functions various, can comprise the machine readable instructions be stored on non-transitory computer-readable medium.In addition, or alternatively, module 101 and 104-107, and other assemblies of equipment 100 can comprise the combination of hardware or machine readable instructions and hardware.

Shop table 120 according to example of the present disclosure and sales volume table 140 is respectively illustrated with reference to Fig. 1-3, Fig. 2 and 3.In database (such as database 102), connection can be used to filtering data.Such as, with reference to shop table 120 and sales volume table 140, draw quarterly and may relate to connection between shop table 120 when the filtration for shop, the state of Massachusetts and sales volume table 140 for the average sale in all shops in the state of Massachusetts (MA).For such inquiry, external key-major key (FK-PK) constraint may be the attribute that query optimizer is used for estimating radix (that is, connecting the radix of result).Radix for shop table 120 and sales volume table 140 is respectively 5 and 7 based on the line number shown for these.For storage list 120, major key is ID row, and it comprises unique ID1-5.In addition, for sales volume table 140, external key is shop ID(store_ID) row, it quotes the ID row of shop table 120.

FK-PK connection can be designated as many-one (m-1) connection, and the size no matter involved PK-shows how, and its radix is not higher than the radix of the table of involved FK-definition.Such as, for storage list 120 and sales volume table 140, the sales figure quantity of Massachusetts can not higher than the sum of sales figure.When the constraint not to connection row, this connection can become multi-to-multi (m-m) connection, and its radix can up to the cartesian product of the table involved by two (that is, the product of row).The sets cardinal that the sets cardinal of m-1 connection is generally counted as connecting than m-m is more accurate.For the connection by becoming m-1 connection, it is unique (such as, shop table 120) that this table needs on connection row.But the row of table do not need to be defined as major key, or the row of other tables do not need to be defined as external key.

Referring again to Fig. 1-3 and 5, Fig. 5 illustrate according to example of the present disclosure before completing " total (price) " of being divided into groups by row " title (Name) " and " state (State) ", the intermediate result after the connection between shop table 120 and sales volume table 140.In order to the value using shop table 120 and sales volume table 140 to determine the total sales volume (i.e. table 160) for each shop, shop table 120 and sales volume table 140 can be processed by following SQL query:

Selectstate,name,sum(price)

Fromstore,sales

Wherestore.id=sales.store_id

Groupbyname,state。

The total sales volume in each shop obtained can comprise 7 row (namely based on many-one (m-1) connection).In other words, for the total sales volume table 160 in each shop, many-one is connect, no matter involved PK-shows (such as, shop table 120) size how, the radix of the table (such as, sales volume table 140) that radix (such as 7) does not define higher than involved FK-.With reference to the total sales volume table 160 in each shop, result (i.e. state, title, total (price)) is as follows:

1)MA,S1,((52+11+100)=163)

2)MA,S2,10

3)NH,S1,20

4)NH,S2,50

5)NH,S4,25。

Referring again to Fig. 1-3,4 and 6, Fig. 4 and 6 respectively illustrates according to the supply item table 180 of example of the present disclosure and before completing the total (# supplies item (#supply_items)) of being divided into groups by row " title ", the intermediate result after the connection between shop table 120 and supply schedule 180.Supply item table 180 comprises 7 row.In addition, for supply item table 180, the value in table 180 is based on following hypothesis: same names shop will receive the supply item (supply_items) of identical number on the same day.In order to determine the value of the total supply item table 200 for same names shop place, shop table 120 and supply item table 180 can be processed by following SQL query:

Selectsum(#supply_items)

Fromstore,supply_item

Wherestore.name=supply_item.name

Groupbyname。

Total supply item table 200 at the same names shop place obtained can comprise 13 row (namely based on multi-to-multi (m-m) connection).But because this is multi-to-multi connection, namely the radix of this multi-to-multi connection can be multiplied by 7 row for supplying a table 180 for 5 row of shop table 120 up to 35().The radix 13 connect for the multi-to-multi of this example may radix 35 be significantly different from the highest.With reference to total supply item table 200 at same names shop place, result is as follows:

1)S1,((300+300+200+200+500+500+400+400)=2800)

2)S2,((1000+1000+100+100)=2200)

3)S4,50。

Based on the above-mentioned example with reference to Fig. 2-6, when the FK-PK constraint not for shop table 120, sales volume table 140 and/or supply item table 180 (namely in due course), the connection using these to show can become m-m connection.Such as, as discussed above, when the constraint not to connection row, this connection can become multi-to-multi (m-m) connection, therefore its radix can up to the cartesian product of the table involved by two, and such as pass through estimate the radix of this connection improperly and postpone query processing.

In order to identify FK-PK constraint, the row comprising the table 103 of unique data can be identified by database analysis module 101.Such as, database analysis module 101 can statistical information on the table 103 of analytical database 102.More particularly, database analysis module 101 can from all row sampled datas and build histogram come capture-data distribution.This process may be used for different value number and the row counting of collecting each row.The analysis of Corpus--based Method information, if the different value number of particular column and row counting are equal, then the data of these row can be confirmed as being unique.Therefore, major key or unique key constraint can be created on such row.Such as, for the aforementioned exemplary of shop table 120, sales volume table 140 and supply item table 180, database analysis module 101 can analyze the statistical information on these tables.This process can be used for collecting each different value number arranged of shop table 120, sales volume table 140 and supply item table 180 and counting of going.After analytic statistics information, if the different value number of particular column and row counting are equal, then the data (such as, the ID row of shop table 120) of these row can be confirmed as being unique.

But if calculate the different value number for row from the sample of data, then such calculating may comprise inaccuracy.Such as, sampling process may eliminate several repetition values.In addition, even if all data when analytic statistics information in row are unique, these data also may can not keep unique after additional data is inserted in table.Such as, for the aforementioned exemplary of shop table 120, suppose that the ID row of shop table 120 comprise 100 row, and be calculate from the sample (such as 50 row) of data for the different value number of ID row, then such calculating may comprise inaccuracy (such as, if some row in other 50 row comprises repetition ID).

In order to solve such as relevant with the insertion of additional data with the omission of several repetition values aforementioned aspect, by considering row unique during sets cardinal process, and the enforcement of unique attribute (such as unique key or major key) is limited further when insertion or the renewal of the particular column of his-and-hers watches, annotation adds module 107 can add annotation on the row of table (such as, in table 103).Such as, during the sets cardinal process of connection result, row can be counted as unique.Such as, for the aforementioned exemplary of shop table 120, suppose that the ID row of shop table 120 comprise 100 row, for this 100 row, some row comprises the ID of repetition, and during the sets cardinal process for connection result, ID row can be counted as unique.In addition, for the aforementioned exemplary of shop table 120, again suppose that the ID row of shop table 120 comprise 100 row, when the insertion arranged ID or renewal, unique attribute (such as unique key or major key) is not implemented.But annotation adds module 107 can add annotation on the row (ID for shop table 120 that such as, may comprise repetition arranges) of table.

Annotation adds module 107 and can also be expert on counting and the row of estimated different value number approximate match (namely within threshold value 108) and add annotation.Threshold value 108 can be that configurable or based on data in table 103 the type institute of user is predetermined.Such as, threshold value 108 can be configured in approximate 5% by user, makes to annotate to add module 107 and can be expert on the row of counting within approximate 5% of estimated different value number and add annotation.

Fig. 7 illustrates the process flow diagram of the method 300 generated for constraint database, and the method 300 corresponds to it and is configured in the example that the constraint database be above described in detail generates equipment 100.The constraint database that method 300 can be implemented in by way of example and not limitation about Fig. 1 generates on equipment 100.Method 300 can be practiced in other equipment.

With reference to Fig. 7, for method 300, at block 301 place, the data relevant with the table in database can be received.For example, referring to Fig. 1, database analysis module 101 can from such as namely showing 1-N with table 103() relevant database 102 receives data.

At block 302 place, these data can be analyzed to determine the row counting of the row for this table.For example, referring to Fig. 1, row counting determination module 104 can analytical table 103 with determine for each table 103 row counting.

At block 303 place, these data can be analyzed to determine the different value number of the row for this table.For example, referring to Fig. 1, different value number determination module 105 can analytical table 103 with determine for each table 103 each row different value number.

At block 304 place, fiducial value can be determined by being compared with different value number by row counting.For example, referring to Fig. 1, by the row counting of the particular column for a table from table 103 and different value number being compared, row counting and different value number comparison module 106 can determine whether the data for this particular column are unique.By by row counting and different value number compare determine fiducial value can also comprise based on row the difference counted between different value number fiducial value is defined as percent value.

At block 305 place, make the determination of fiducial value whether in threshold value.For example, referring to Fig. 1, annotation adds module 107 can compare data from row counting and the reception of different value number comparison module 106, and determines the comparison data that receive whether in threshold value 108.Threshold value can be similar to and be greater than 0%, or can input and configure based on counting the relevant user of percent difference between different value number with row.

At block 306 place, if fiducial value is in threshold value, then annotation can be added to the row of this table, and make during relating to the sets cardinal process of this table, these row are counted as unique.Such as, with reference to Fig. 1, if the comparison data received are in threshold value 108, then annotation add module 107 can be expert on counting and the suitable row of estimated different value number approximate match (namely within threshold value 108) add note 1 09(and soft unique constraint).When row annotation being added to this table can also be included in and insert the data of these row or upgrade, limit the enforcement of the unique attribute of these row.The enforcement of unique attribute of the data of these row being inserted or upgrade these row of limit can also be included in the data of these row are inserted or are upgraded time, limit the unique key of these row or the enforcement of major key.

Fig. 8 shows the computer system 400 that can use together with example described herein.Computer system represents a general-purpose platform, and it comprises the assembly that can be in server or other computer systems.Computer system 400 can be used as the platform of equipment 100.Computer system 400 can be performed by processor described herein or other hardware handles circuit, method, function and other processes.These methods, function and other processes can be embodied as storage machine readable instructions on a computer-readable medium, this computer-readable medium can be non-transitory, such as hardware storage apparatus (such as, RAM(random access memory), ROM(ROM (read-only memory)), EPROM(erasable programmable ROM), EEPROM(electrically erasable ROM), hard disk drive and flash memory).

Computer system 400 comprises processor 402, and it can realize or perform machine readable instructions, and this machine readable instructions carries out some or all in method described herein, function and other processes.Order and the data of carrying out self processor 402 are transmitted by communication bus 404.Computer system also comprises primary memory 406 and secondary data storage device 408, the all random-access memory (ram)s in this way of this primary memory 406, there, for the treatment of the machine readable instructions of device 402 and data can operationally between period resident, this secondary data storage device 408 can be non-volatile and storing machine instructions and data.Storer and data storage device are the examples of computer-readable medium.Storer 406 can comprise constraint database generation module 420, resides in the machine readable instructions performed in storer 406 and by processor 402 during this constraint database generation module 420 comprises working time.Constraint database generation module 420 can comprise module 101 and the 104-107 of the equipment shown in Fig. 1.

Computer system 400 can comprise I/O device 410, such as keyboard, mouse, display etc.Computer system can comprise for being connected to network of network interface 412.Other known electronic packages can be added or be substituted in computer systems, which.

Described herein and illustrate be example and its some change.Term as used herein, description and accompanying drawing are set forth by means of only the mode illustrated, and do not mean that restriction.In the spirit and scope of this theme, many changes are possible, the spirit and scope intention of this theme is limited by claims (and its equivalent way), all terms in claim represent implication with its most wide in range reasonable sense, unless otherwise directed.

Claims (15)

1., for the method that constraint database generates, described method comprises:

Receive the data relevant with the table in database;

Analyze described data to determine the row counting of the row for described table;

Analyze described data to determine the different value number of the row for described table;

Fiducial value is determined by being compared with described different value number by described row counting;

Determine described fiducial value whether in threshold value; And

If described fiducial value is in threshold value, by processor, annotation is added to the row of described table, make during relating to the sets cardinal process of described table, described row are counted as unique.

2. the row the method for claim 1, wherein described annotation being added to described table also comprise:

The data of described row are being inserted or is upgrading the enforcement of the unique attribute arranged described in limit.

3. method as claimed in claim 2, wherein also comprises in the enforcement of the unique attribute inserted the data of described row or arrange described in renewal limit:

When inserting the data of described row or upgrade, limit the unique key of described row or the enforcement of major key.

4. the method for claim 1, wherein analyze described data to determine that the different value number of the row for described table also comprises:

Analyze described data to determine the different value number of each row for described table.

5. the method for claim 1, wherein determine that fiducial value also comprises by being compared with different value number by described row counting:

Based on the difference between described row counting and different value number, described fiducial value is defined as percent value.

6. method according to claim 1, wherein, described threshold value is approximate is greater than 0%.

7. the method for claim 1, also comprises:

Input to configure described threshold value based on the user that described row counts percent difference between different value number relevant.

8. constraint database generates an equipment, comprising:

Storer, it stores the machine readable instructions in order to carry out following operation:

Receive the data relevant with the table in database;

Analyze described data to determine the row counting of the row for described table;

Analyze described data to determine the different value number of the row for described table;

Fiducial value is determined by being compared with described different value number by described row counting;

Determine described fiducial value whether in threshold value; And

If fiducial value is in threshold value, then annotation is added to the row of described table, to insert the data of described row or to upgrade the enforcement of the unique attribute arranged described in limit; And

Processor, in order to realize described machine readable instructions.

9. constraint database as claimed in claim 8 generates equipment, wherein in order to add annotation the row of described table to, described machine readable instructions also in order to:

Described row are regarded as unique during relating to the sets cardinal process of described table.

10. constraint database as claimed in claim 8 generates equipment, wherein in order to insert the data of described row or upgrade the enforcement of the unique attribute arranged described in limit, described machine readable instructions also in order to:

When inserting the data of described row or upgrade, limit the unique key of described row or the enforcement of major key.

11. constraint databases as claimed in claim 8 generate equipment, wherein in order to analyze described data to determine the different value number of the row for described table, described machine readable instructions also in order to:

Analyze described data to determine the different value number of each row for described table.

12. constraint databases as claimed in claim 8 generate equipment, wherein in order to determine fiducial value by being compared with described different value number by described row counting, described machine readable instructions also in order to:

Based on the difference between described row counting and described different value number, described fiducial value is defined as percent value.

13. constraint databases as claimed in claim 8 generate equipment, and wherein, described threshold value is approximate is greater than 0%.

14. constraint databases as claimed in claim 8 generate equipment, described machine readable instructions also in order to:

Input to configure described threshold value based on the user that described row counts percent difference between different value number relevant.

15. 1 kinds of non-transitory computer-readable mediums, have the machine readable instructions be stored thereon and generate to provide constraint database, described machine readable instructions makes computing machine carry out following operation when being performed:

Receive the data relevant with the table in database;

Analyze described data to determine the row counting of the row for described table;

Analyze described data to determine the different value number of the row for described table;

Fiducial value is determined by being compared with described different value number by described row counting;

Determine described fiducial value whether in threshold value; And

If described fiducial value is in threshold value, by processor, annotation is added to the row of described table, so that:

The data of described row are being inserted or is upgrading the enforcement of the unique attribute arranged described in limit, and

Described row are regarded as unique during relating to the sets cardinal process of described table.