Browse Prior Art Database

SQL Set Construct Enhancement

IP.com Disclosure Number: IPCOM000110428D
Original Publication Date: 2005-Mar-25
Included in the Prior Art Database: 2005-Mar-25
Document File: 1 page(s) / 44K

Publishing Venue

IBM

Abstract

This article discusses a method to rewrite union statements to avoid duplicate processing

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

Page 1 of 1

SQL Set Construct Enhancement

Disclosed is a method to rewrite union statements to avoid duplicate processing. The invention, therefore, is to analyze the SQL statement about to be executed to determine if the two legs of the statement can be merged together to avoid two passes through the data. The analysis of the invention can be as simple as comparing to see if the file(s) being accessed are the same and the conditions being used against the files can be joined with either an AND, OR , IN or a NOT IN clause. It should be noted that we over simplified the example for purposes of this article. To exemplify take the following statement below.

     Currently , this query is run in two stages and the results are INTERSECTED together. It would be much more efficient if the database engine when parsing the statement recognized the fact that the CUSTOMER table is in both legs of the INTERSECT statement. Furthermore, the second WHERE clause could simply be and'ed together with the first WHERE clause to produce the same result. Therefore, the first query depicted below could easily be thought of in terms of the statement that follows it which is more efficient to process.

     Likewise, if the language construct being used was MINUS rather than INTERSECT, we could replace the IN clause with a NOT IN clause to produce the same result. We also recognize the need to not automatically perform the operation if it is deemed to be less efficient. First, it is imperative that we have the...