Efficient partitioned joins in a database with column-major layout

Patent metadata

Field	Value
Publication number	US-9875280-B2
Application number	US-201213594200-A
Country	US
Kind code	B2
Filing date	Aug 24, 2012
Priority date	Jun 29, 2012
Publication date	Jan 23, 2018
Grant date	Jan 23, 2018

Abstract

Embodiments of the present invention provide a database processing system for efficient partitioning of a database table with column-major layout for executing one or more join operations. One embodiment comprises a method for partitioning a database table with column-major layout, partitioning only the join-columns by limiting the partitions by size and number, executing one or more join operations for joining the partitioned columns, and optionally de-partitioning the join result to the original order by sequentially writing and randomly reading table values using P cursors.

First claim

What is claimed is:

1. A method for joining database tables in a query, comprising:
partitioning a database table with column major layout by limiting partitioning to one or more columns containing join-column values for a join operator;
executing one or more join operations for joining the partitioned columns while columns other than columns containing the join-column values are not accessed during the one or more join operations;
joining a probe table with build tables in a join graph, comprising partitioning the one or more columns containing join-columns;
undoing the partitioning in a de-partitioning process to convert a result of the joining back into original order;
forming lookup data structures on the build tables;
mapping respective join-column values of the lookup data structures to the values of any auxiliary columns that are needed in a query;
scanning the probe table, wherein for each batch of join-column values of the probe table, the values are looked up into the lookup data structures built from the build tables;
limiting the size of each partition based on cache size and limiting the number of partitions based on a translation lookaside buffer (TLB), for recursive partitioning; and
de-partitioning the partitions to obtain the original ordering by sequentially writing and randomly reading table values using P cursors, wherein P is less than the number of entries in the TLB,
wherein by not accessing other columns during joining operations, cache and memory utilization is improved, and efficiency in partitioning the database table with column major layout is improved.

2. The method of claim 1 , further comprising:
looking-up the corresponding partition from the build table;
extracting for each join-column value any auxiliary columns that are needed in the query; and
computing one or more Boolean values for each join-column value including a Boolean value indicating whether a join-column value found any match in the lookup data structures.

3. The method of claim 2 , further comprising:
after lookup of a build table, de-partitioning the partitions to re-establish the original order.

4. The method of claim 1 , wherein the number of partitions are limited to the number of entries in the TLB, and the TLB comprises a level-1 TLB.

5. The method of claim 1 , further comprising:
when more partitions are needed to keep partitions within a certain size and keep the number of partitions within a certain limit, performing recursive partitioning.

6. The method of claim 1 , wherein the lookup data structures comprise hash tables.

7. The method of claim 1 , wherein the join-columns comprise columns having one or more equality join predicates relating one or more columns of a probe table to one or more columns of a build table.

8. The method of claim 1 , wherein no other columns are accessed during the one or more join operations for joining the partitioned columns.

9. A method of partitioned join in a database, comprising:
joining a probe table with build tables in a join graph, comprising partitioning a database table with column major layout by limiting partitioning for the join-column values of one or more columns for a join operator;
executing one or more join operations for joining the partitioned columns while columns other than columns containing the join-column values are not accessed during the one or more join operations;
undoing the partitioning in a de-partitioning process to convert the join result back into original order;
forming hash tables on build tables;
mapping their respective join-column values of the hash tables to the values of any additional columns that are needed in a query, and scanning the probe table;
looking up the corresponding partition from the build table;
extracting, for each matching value of the join-columns, any auxiliary columns that are needed in the query;
computing one or more Boolean values for each value of the join-columns from the probe table including a Boolean value indicating whether that value found any match in the hash table;
after lookup in the build tables, de-partitioning the partitions;
limiting the number of partitions to the number of entries in a level-1 translation lookaside buffer (TLB);
when more partitions are needed, performing recursive partitioning; and
de-partitioning the partitions to obtain the original ordering by sequentially writing and randomly reading table values using P cursors;
wherein said partitioning includes partitioning said one or more columns such that the number and size of the partitions is limited, for each batch of values of the join-columns the values are looked up into the corresponding hash tables on the build tables, P is less than the number of entries in the TLB, by not accessing other columns during joining operations, cache and memory utilization is improved, and efficiency in partitioning the database table with column major layout is improved.

Assignees

• Arndt Stefan
• Attaluri Gopi K
• Barber Ronald J
• Lohman Guy M
• Qiao Lin
• Raman Vijayshankar
• Shekita Eugene J
• Sidle Richard S
• IBM

Inventors

• Arndt Stefan
• Attaluri Gopi K
• Barber Ronald J
• Lohman Guy M
• Qiao Lin
• Raman Vijayshankar
• Shekita Eugene J
• Sidle Richard S

Classifications

• G06F17/30486 Primary

  Physics · mapped topic

• G06F17/3033

  Physics · mapped topic

• G06F17/30466

  Physics · mapped topic

• G06F17/30498

  Physics · mapped topic

• G06F17/30315

  Physics · mapped topic