NON-EQUIJOIN METADATA

• US 20090019017A1
• Filed: 07/10/2007
• Published: 01/15/2009
• Est. Priority Date: 07/10/2007
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Belur, Kasturi, Chaves, Michael, Fu, Henry, Shelton, Scott
• Granted Patent: US 8,914,352 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

The subject mater herein relates to database management and, more particularly, to non-equijoin metadata. Various embodiments described herein provide systems, methods, software, and data structures to facilitate non-equijoins of data. Some such embodiments include a data store to hold metadata describing relationships between database tables. The relationships between database tables may include one or more non-equijoins of tables in the form of one or more table-column pairs of a first table being non-equal to one or more table-column pairs of a second table. In some embodiments, the metadata may be used by a query generator to build query statements including one or more non-equijoin.

Claims

1. A system comprising:

a database to hold tables of data including columns and rows; and

a data store to hold metadata describing relationships between tables, the relationships including one or more non-equijoins of tables in the form of one or more table-column pairs of a first table being non-equal to one or more table-column pairs of a second table.

2. The system of claim 1, wherein:

at least one relationship between tables described in the data store includes one or more non-equijoins of tables in the form of two table-column pairs of a first table being non-equal to two table-column pairs of a second table described in two forms, the first form consisting essentially of;

a first table-column pair of a first table+a first non-equal operator+a first table-column pair of a second table; and

a second table-column pair of the first table+a second non-equal operator+a second table-column pair of the second table; and

the second form consisting essentially of;

the first table-column pair of the second table+the opposite of the first non-equal operator+the first table-column pair of the first table; and

the second table column pair of the second table+the opposite of the second non-equal operator+the second table-column pair of the first table.

3. The system of claim 2, wherein the first table-column pair of the first table and the second table-column pair of the first table are the same table-column pair.

4. The system of claim 1, further comprising:

a query generator operable to;

receive a data request from a requester, the data request including a request for a first table column in view of the one or more columns of the second table for which a non-equijoin is described in the metadata; and

build a query statement including a representation of the non-equijoin.

5. The system of claim 4, wherein the query generator is further operable to return the query statement to the requester.

6. The system of claim 4, wherein the query generator, when building a query, determines an order for the table-column pairs of a non-equijoin and reverses non-equijoin operators if the order of table-column pairs are reversed.

7. The system of claim 1, wherein the data store comprises one or more tables in the database.

8. A method of providing non-equijoin data to a query generator, the method comprising:

storing metadata descriptive of relationships between database tables in a data store, the relationships including one or more non-equijoins of database tables in the form of one or more table-column pairs of a first database table being non-equal to one or more table-column pairs of a second database table;

receiving a metadata request from a requestor including a request for metadata for making a join between two database tables for which non-equijoin metadata exists; and

retrieving the requested metadata from the data store.

9. The method of claim 8, wherein at least one relationship between tables described in the data store includes one or more non-equijoins of tables in the form of two table-column pairs of a first table being non-equal to two table-column pairs of a second table described in two forms, the first form consisting essentially of:

a first table-column pair of a first table+a first non-equal operator+a first table-column pair of a second table; and

a second table-column pair of the first table+a second non-equal operator+a second table-column pair of the second table; and

the second form consisting essentially of;

the first table-column pair of the second table+the opposite of the first non-equal operator+the first table-column pair of the first table; and

the second table column pair of the second table+the opposite of the second non-equal operator+the second table-column pair of the first table.

10. The method of claim 9, wherein the first table-column pair of the first table and the second table-column pair of the first table are the same table-column pair.

11. The method of claim 8, wherein the request for metadata for making the join between the two database tables for which non-equijoin metadata exists includes receiving a data request including a request for a first table column in view of the one or more columns of the second table for which a non-equijoin is described in the metadata.

12. The method of claim 8, further comprising:

building a query statement including a representation of the non-equijoin.

13. The method of claim 12, further comprising:

providing the query statement to the requester for execution.

14. The method of claim 12, wherein building the query statement includes:

determining an order of the table-column pairs of a non-equijoin; and

reversing the non-equijoin operators if the determined order of table-column pairs is reversed from the way the metadata describing the non-equijoin is stored in the data store.

15. A tangible computer-readable medium, with instructions encoded thereon, which when executed, cause a suitably configured computing system including a database management system to:

maintain a data store holding metadata descriptive of relationships between database tables, the relationships including one or more non-equijoins of database tables in the form of one or more table-column pairs of a first database table being non-equal to one or more table-column pairs of a second database table;

receive a metadata request from a requestor including a request for metadata to facilitate a non-equijoin between two database tables for which non-equijoin metadata exists;

retrieve the requested metadata from the data store; and

build a query statement including a representation of the non-equijoin.

16. The tangible computer-readable medium of claim 15, wherein the encoded instructions, which when executed, further cause the computing system to:

provide the query statement to the requestor for execution.

17. The tangible computer-readable medium of claim 15, wherein at least one relationship between tables described in the data store includes one or more non-equijoins of tables in the form of two table-column pairs of a first table being non-equal to two table-column pairs of a second table described in two forms, the first form consisting essentially of:

a first table-column pair of a first table+a first non-equal operator+a first table-column pair of a second table; and

a second table-column pair of the first table+a second non-equal operator+a second table-column pair of the second table; and

the second form consisting essentially of;

the first table-column pair of the second table+the opposite of the first non-equal operator+the first table-column pair of the first table; and

the second table column pair of the second table+the opposite of the second non-equal operator+the second table-column pair of the first table.

18. The tangible computer-readable medium of claim 17, wherein the first table-column pair of the first table and the second table-column pair of the first table are the same table-column pair.

19. The tangible computer-readable medium of claim 15, wherein the encoded instructions, when executed to build the query statement, are operable to:

determine an order of the table-column pairs of a non-equijoin; and

reverse the non-equijoin operators if the determined order of table-column pairs is reversed from the way the metadata describing the non-equijoin is stored in the data store.

20. The tangible computer-readable medium of claim 15, wherein the metadata request for making the join between the two database tables for which non-equijoin metadata exists includes a request for a first table column in view of the one or more columns of the second table for which a non-equijoin is described in the metadata.