Browse Prior Art Database

Threaded Code Design for SET Operations

IP.com Disclosure Number: IPCOM000036774D
Original Publication Date: 1989-Oct-01
Included in the Prior Art Database: 2005-Jan-29
Document File: 6 page(s) / 20K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+4]

Abstract

Disclosed is a method within a database management system (DBMS) for supporting set operations in SQL statements using a threaded code architecture.

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

Page 1 of 6

Threaded Code Design for SET Operations

Disclosed is a method within a database management system (DBMS) for supporting set operations in SQL statements using a threaded code architecture.

The value of an SQL SELECT statement is a set of rows selected from a table. The operators UNION, INTERSECT and EXCEPT, corresponding to the set operations UNION, INTERSECTION and DIFFERENCE, can be used to combine rows from two operand tables (the results of two SQL SELECT statements) into a single result table.

With UNION, the result table consists of all rows which are in either operand table. With EXCEPT, the result table consists of all rows in the first operand table which are not in the second operand table. With INTERSECT, the result table consists of all rows which are in both operand tables. Duplicate rows are removed from the result table unless "ALL" is specified.

The operand tables must have the same number of columns, and the data types of the i-th columns in each operand must be compatible.

In OS/2* Extended Edition Database Services, SQL statements are compiled into an Access Section, which is composed in part of data objects and code threads. The data objects contain information about the data being operated on, and the threads contain operators that direct the execution upon the data. This type of compilation is referred to as a threaded code architecture.

Part of this architecture is a thread to execute a SELECT statement. The architecture is extended to include a SET opcode, which has as its operand a pointer to a Set Object containing the following:
1. Set Operator (i.e., UNION, INTERSECT or EXCEPT)
2. Status of current thread (Open or Closed)
3. Number of operands (2 for INTERSECT/EXCEPT, n for

UNION)
4. Current operand being executed (1..n)
5. Number of output values of the Set Operator
6. Pointer to the output values of the Set Operator
7. Status of 2nd operand for EXCEPT
8. Array of Operand Descriptors containing:

a. Pointer to thread for first fetch

b. Pointer to thread for subsequent fetches

c. Pointer to output values of the Operand

For each set operand, a thread is generated to produce the rows for a SELECT statement. For INTERSECT and EXCEPT, the rows must be sorted ascending on all fields in order of the select list. Pointers are stored for the thread addresses for the first fetch and subsequent fetches, and for a list of pointers to the descriptors of the output of the SELECT statement (each operand will have the same number of outputs as the Set Operator). The outputs of the operands are the inputs to the Set Operator. A pointer is also stored in the list of pointers to the descriptors of the outputs of the Set Operator. If duplicate rows

1

Page 2 of 6

are to be removed, the distinct filter is applied to the result of the Set Operator. This permits the distinct filter to be removed from all operands of a UNION and from the second operand of INTERSECT or EXCEPT. In addition, if the first operand of INTERSECT or EX...