Browse Prior Art Database

Handling of Transitive Closure Shared Constant Buffer during Parallelism

IP.com Disclosure Number: IPCOM000013296D
Original Publication Date: 2002-Oct-16
Included in the Prior Art Database: 2003-Jun-18
Document File: 2 page(s) / 54K

Publishing Venue

IBM

Abstract

Before describing OUR SOLUTION, we will first define some key terms. Definition: TRANSITIVE CLOSURE: In the following SQL query SELECT T1.C1 FROM T1, T2 WHERE T1.C1=T2.C1 AND T1.C1 '0328' one can infer that T2.C1 ='0328' must also be true because T1.C1 T2.C1. This is referred to as transitive closure.

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

Page 1 of 2

Handling of Transitive Closure Shared Constant Buffer during Parallelism

Before describing OUR SOLUTION, we will first define some key terms.

Definition: TRANSITIVE CLOSURE: In the following SQL query

           SELECT T1.C1 FROM T1, T2 WHERE T1.C1=T2.C1 AND T1.C1 = '0328' one can infer that T2.C1 ='0328' must also be true because T1.C1 = T2.C1.

This is referred to as transitive closure.

Definition: SHARED CONSTANT BUFFER: In the transitive closure definition, the buffer containing the constant '0328' can be shared by the predicate T1.C1='0328' and the optimizer generated predicate T1.C2='0328'.

Definition: PARTITIONING KEY: A partitioned tablespace is divided into partitions and is partitioned according to the value ranges (key) of a partitioning column or combination of columns.

PROBLEMS BEING SOLVED: In the following SQL query

SELECT T1.C1 FROM T1, T2 WHERE T1.C1=T2.C1 AND

T1.C1 = '0328'

the DB2 ® for OS/390 ® optimizer will generate an additional predicate, T1.C2 = '0328'. The predicates that will be evaluated will be T1.C1 = T2.C1, T1.C1 = '0328', and T1.C2 = '0328'.

For performance reasons, the DB2 for OS/390 optimizer shared the constant buffer in the predicate written by the user, T1.C1 = '0328', and the predicate generated by the optimizer, T1.C2 = '0328'. This scheme works fine when no parallelism is involved. But once parallelism is enabled, this sharing of the buffer between two tables may cause a problem. This is because the shared buffer may be used as part of the partitioning key as well as a local predicate. The partitioning key may need to be loaded with a different value while the local predicate...