Browse Prior Art Database

Method for Minimizing Sort Composite for Hybrid Join/Nested Loop Join

IP.com Disclosure Number: IPCOM000116960D
Original Publication Date: 1995-Dec-01
Included in the Prior Art Database: 2005-Mar-31
Document File: 2 page(s) / 31K

Publishing Venue

IBM

Related People

Cheng, J: AUTHOR [+4]

Abstract

A new algorithm is disclosed to improve query performance significantly by minimizing use of a sort composite table and utilizing IO overlapping for Hybrid Join and Nested Loop Join when the composite table is accessed via an access path which provides the approximate order.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 100% of the total text.

Method for Minimizing Sort Composite for Hybrid Join/Nested Loop Join

      A new algorithm  is disclosed to improve query performance
significantly by minimizing use of a sort composite table and
utilizing IO overlapping for Hybrid Join and Nested Loop Join when
the composite table is accessed via an access path which provides the
approximate order.

      The sort on composite table for Hybrid Join or Nested loop join
can be saved if the query access path used to retrieve the outer
table rows does not destroy the physical sequence of the row order.
The fact is that the clustered index on the table is in physical
sequence.  If the join key columns used belong to the clustered index
of the outer table, then some of the access paths which retrieve rows
in physical sequence will still keep the join key columns in
sequence.  Therefore, sort on the composite or the outer table in the
join key columns sequence of the inner table is not necessary.

      The disclosed algorithm on join order class determination
improves performance by utilizing IO overlapping that is enabling.
IO
parallel processing between outer and inner tables.  It also saves
IO and CPU resources by eliminating the redundant sort, while
improving
the first record response.