There will be a system update on Tuesday, January 16th, 8 PM ET. You may experience a brief service interruption.
Browse Prior Art Database

Method And System For Optimizing Joins Using A Hash Access

IP.com Disclosure Number: IPCOM000196837D
Publication Date: 2010-Jun-18
Document File: 1 page(s) / 11K

Publishing Venue

The IP.com Prior Art Database


A method and system for optimizing joins using hash access is disclosed. The method avoids random access that would normally occur when a table is accessed repeatedly through a hash key.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 64% of the total text.

Page 1 of 1

Method And System For Optimizing Joins Using A Hash Access

Disclosed is a method and system for optimizing joins using hash access. In an instance,

when an inner table is accessed via a hash key rather than through an index,

the method involves obtaining the value that the inner table is to be joined on for each qualified row of the outer table. Thereafter, the obtained value is used for applying the inner table's hash function to obtain an "estimated rid". The term "estimated rid" indicates that it is not known at this point whether the qualified row being processed actually exists in the inner table. The estimated rid is the rid which the row would have if it did exist in the inner table. The estimated rids for the rows are then saved in a temporary file and sorted. The estimated rids are then read from the temporary file and their corresponding data rows in the inner table are accessed using list prefetch. If the data row corresponding to an estimated rid does not exist in the inner table, then it is skipped. Thereafter, other predicates are applied to the inner table data for each qualified row to complete the join processing.

In another instance, the method may be adapted for batch processing,

file is being read and used to repeatedly access a table using a hash key. In this case, a user defined function (UDF),

which contains the hash function of the table to be

accessed may be applied to the input file to sort the input file based on the estimated rid. Usin...