Data-partitioned secondary index (DPSI) partition level join

1. A method, comprising:

receiving, with a processor of a computer, a query that identifies an outer table and an inner table, wherein the inner table comprises multiple partitions;

performing a join of the outer table and the multiple partitions of the inner table using a Data-Partitioned Secondary Index (DPSI), wherein the DPSI comprises a group of indexes with each of the indexes in the group created for a different partition of the multiple partitions of the inner table, by:

assigning a different task from multiple tasks to each different partition of the multiple partitions of the inner table, wherein each of the multiple tasks is a child task of the join;

processing the outer table by:

applying filtering specified in the query;

sorting the out table into join order; and

storing the outer table in memory for shared access by each of the multiple tasks;

in response to the DPSI being clustered, performing a DPSI partition level join by executing each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results, wherein the join of the outer table to each different partition of the inner table preserves sequential pre-fetch and index look-aside while accessing that different partition of the inner table;

in response to the DPSI being unclustered, performing a partition level hybrid join by executing each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results by:

accumulating Row Identifiers (RIDs) from each inner DPSI index probe;

sorting the RIDs into data page order; and

employing list prefetch so that data page access is sequential;

merging the results from each different task; and

returning the merged results as a response to the query.

2. The method of claim 1 , further comprising:

performing a nested loop join for a new query.

3. The method of claim 1 , further comprising:

flipping a join sequence by materializing an outer table of a new query and using the outer table as an inner table of a hash join.

4. The method of claim 1 , wherein the multiple tasks are performed in parallel.

5. The method of claim 1 , wherein a Software as a Service (SaaS) is configured to perform method operations.

6. A computer system, comprising:

one or more processors, one or more computer-readable memories and one or more computer-readable, tangible storage devices; and

program instructions, stored on at least one of the one or more computer-readable, tangible storage devices for execution by at least one of the one or more processors via at least one of the one or more memories, to perform operations comprising:

receiving a query that identifies an outer table and an inner table, wherein the inner table comprises multiple partitions;

performing a join of the outer table and the multiple partitions of the inner table using a Data-Partitioned Secondary Index (DPSI), wherein the DPSI comprises a group of indexes with each of the indexes in the group created for a different partition of the multiple partitions of the inner table, by:

assigning a different task from multiple tasks to each different partition of the multiple partitions of the inner table, wherein each of the multiple tasks is a child task of the join;

processing the outer table by:

applying filtering specified in the query;

sorting the out table into join order; and

storing the outer table in memory for shared access by each of the multiple tasks;

in response to the DPSI being clustered, performing a DPSI partition level join by executing each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results, wherein the join of the outer table to each different partition of the inner table preserves sequential pre-fetch and index look-aside while accessing that different partition of the inner table;

in response to the DPSI being unclustered, performing a partition level hybrid join by executing each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results by:

accumulating Row Identifiers (RIDs) from each inner DPSI index probe;

sorting the RIDs into data page order; and

employing list prefetch so that data page access is sequential;

merging the results from each different task; and

returning the merged results as a response to the query.

7. The computer system of claim 6 , wherein the operations further comprise:

performing a nested loop join for a new query.

8. The computer system of claim 6 , wherein the operations further comprise:

flipping a join sequence by materializing an outer table of a new query and using the outer table as an inner table of a hash join.

9. The computer system of claim 6 , wherein the multiple tasks are performed in parallel.

10. The computer system of claim 6 , wherein a Software as a Service (SaaS) is configured to perform system operations.

11. A computer program product, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code executable by at least one processor to perform:

receiving a query that identifies an outer table and an inner table, wherein the inner table comprises multiple partitions;

performing a join of the outer table and the multiple partitions of the inner table using a Data-Partitioned Secondary Index (DPSI), wherein the DPSI comprises a group of indexes with each of the indexes in the group created for a different partition of the multiple partitions of the inner table, by:

assigning, by the at least one processor, a different task from multiple tasks to each different partition of the multiple partitions of the inner table, wherein each of the multiple tasks is a child task of the join;

processing, by the at least one processor, the outer table by:

applying filtering specified in the query;

sorting the out table into join order; and

storing the outer table in memory for shared access by each of the multiple tasks;

in response to the DPSI being clustered, performing a DPSI partition level join by executing, by the at least one processor, each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results, wherein the join of the outer table to each different partition of the inner table preserves sequential pre-fetch and index look-aside while accessing that different partition of the inner table;

in response to the DPSI being unclustered, performing, by the at least one processor, a partition level hybrid join by executing each different task to perform a join of the outer table in memory and the different partition of the inner table using the DPSI to generate results by:

accumulating Row Identifiers (RIDs) from each inner DPSI index probe;

sorting the RIDs into data page order; and

employing list prefetch so that data page access is sequential;

merging, by the at least one processor, the results from each different task; and

returning, by the at least one processor, the merged results as a response to the query.

12. The computer program product of claim 11 , wherein the program code is executable by the at least one processor to perform:

performing a nested loop join for a new query.

13. The computer program product of claim 11 , wherein the program code is executable by the at least one processor to perform:

flipping, by the at least one processor, a join sequence by materializing an outer of a new query table and using the outer table as an inner table of a hash join.

14. The computer program product of claim 11 , wherein a Software as a Service (SaaS) is configured to perform computer program product operations.

15. The computer program product of claim 11 , wherein the multiple tasks are performed in parallel.