Systems And Methods For Performing Index Joins Using Auto Generative Queries

• US 20120330988A1
• Filed: 06/24/2011
• Published: 12/27/2012
• Est. Priority Date: 06/24/2011
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Christie, Douglass Adam, Keener, Gordon Lyle
• Granted Patent: US 8,694,525 B2
• Time in Patent Office: Days
• US Class Current: 707/759
• CPC Class Codes: G06F 16/24 Querying
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/901 Indexing; Data structures therefor; Storage structures for retrieval from the web G06F16/951
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques

Abstract

In accordance with the teachings described herein, systems and methods are provided for performing index joins. A database management application may receive an instruction to perform an index join operation between columns in a first table and a second table, wherein the database management application does not have direct access to an index of the first table or the second table for performing the index join operation. A query may be automatically generated by the database management application, wherein the query includes a where clause equality expression that equates an indexed column of the second table with a parameter or updatable constant. The database management application may substitute a value from a row of the first table may for the parameter or updatable constant, and cause the query to be executed on the index of the second table to fetch any one or more rows of the second table that satisfy the where clause by having an index value that matches the substituted value. The database management application may then repeat the substituting and executing operations for each row of the first table, and perform the index join operation using the fetched one or more rows.

Claims

1. A method for performing index joins, comprising:

receiving, at a query processor in a database management system, an instruction to perform an index join operation between columns in a first table and a second table, wherein the query processor does not have direct access to an index of the first table or the second table for performing the index join operation;

automatically generating a query having a where clause equality expression that equates an indexed column of the second table with a parameter or updatable constant;

substituting a value from a row of the first table for the parameter or updatable constant;

executing the query on the index of the second table to fetch any one or more rows of the second table that satisfy the where clause by having an index value that matches the substituted value;

repeating the substituting and executing steps for each row of the first table; and

performing the index join operation using the fetched one or more rows.

2. The method of claim 1, further comprising:

determining that the column to be joined from the second table is indexed,

wherein the query is automatically generated based on the determination that the column to be joined from the second table is indexed.

3. The method of claim 1, wherein the first table is non-indexed and the second table is indexed.

4. The method of claim 1, wherein both the first table and the second table are indexed.

5. The method of claim 1, wherein at least one of the first table and the second table is stored in a non-native database.

6. The method of claim 1, wherein if neither of the columns to be joined are indexed, then selecting a different method for performing the join operation.

7. The method of claim 1, wherein the query comprises:

select*from B where ?=B.Y,

wherein B is an identification of the second table, ? is the parameter, and B.Y identifies the indexed column of the second table.

8. The method of claim 1, wherein the query comprises:

select*from B where <

value>

=B.Y,

wherein B is an identification of the second table, <

value>

is the updatable constant, and B.Y identifies the indexed column of the second table.

9. The method of claim 1, wherein the query processor and the first and second table are included in a federated database system.

10. A system for performing index joins, comprising:

a database management application stored in a memory and executed by a processor, when executed the database management application being configured to;

receive an instruction to perform an index join operation between columns in a first table and a second table, wherein the database management application does not have direct access to an index of the first table or the second table for performing the index join operation;

automatically generate a query having a where clause equality expression that equates an indexed column of the second table with a parameter or updatable constant;

substitute a value from a row of the first table for the parameter or updatable constant;

cause the query to be executed on the index of the second table to fetch any one or more rows of the second table that satisfy the where clause by having an index value that matches the substituted value;

repeat the substituting and executing operations for each row of the first table; and

perform the index join operation using the fetched one or more rows.

11. The system of claim 10, wherein the database management application is further configured to determine that the column to be joined from the second table is indexed, wherein the query is automatically generated based on the determination that the column to be joined from the second table is indexed.

12. The system of claim 10, wherein the first table is non-indexed and the second table is indexed.

13. The system of claim 10, wherein both the first table and the second table are indexed.

14. The system of claim 10, wherein at least one of the first table and the second table is stored in a non-native database.

15. The system of claim 10, wherein if neither of the columns to be joined are indexed, then the database management application being configured to select a different method for performing the join operation.

16. The system of claim 10, wherein the query comprises:

select*from B where ?=B.Y,

wherein B is an identification of the second table, ? is the parameter, and B.Y identifies the indexed column of the second table.

17. The system of claim 10, wherein the query comprises:

select*from B where <

value>

=B.Y,

wherein B is an identification of the second table, <

value>

is the updatable constant, and B.Y identifies the indexed column of the second table.