PAGE_RANGE=Y

Rajendra Deshpande

PAGE_RANGE=Y
Hi all,

Here is an excerpt from DB2 Admin manual...

**************
consider the following query:
SELECT ..FROM T
WHERE (C1 BETWEEN '2002'AND '3280'
OR C1 BETWEEN '6000'AND '8000')
AND C2 ='6';

Assume that table T has a partitioned index on column C1 and that values of
C1
between 2002 and 3280 all appear in partitions 3 and 4 and the values
between
6000 and 8000 appear in partitions 8 and 9. Assume also that T has another
index
on column C2. DB2 could choose any of these access methods:

1. A matching index scan on column C1. The scan reads index values and data
only from partitions 3, 4, 8, and 9. (PAGE_RANGE=N)

2. A matching index scan on column C2. (DB2 might choose that if few rows
have
C2=6.) The matching index scan reads all RIDs for C2=6 from the index on C2
and corresponding data pages from partitions 3, 4, 8, and 9. (PAGE_RANGE=Y)

3. A table space scan on T. DB2 avoids reading data pages from any
partitions
except 3, 4, 8 and 9. (PAGE_RANGE=Y)
***************

My query is about point 1 above. If the scan there is restricted to
partitions 3,4,8 and 9 ONLY then why is PAGE_RANGE=N.

Any clarification is welcome.
TIA.



Terry Purcell

Re: PAGE_RANGE=Y
(in response to Rajendra Deshpande)
Rajendra,

PAGE_RANGE=N is set for the 1st access path because the leading column of
the C1 index is used to limit which rows are accessed. That is, there is
nothing special about this access path. It does not need to use a limited
partition scan, because the index already limits the access to these
partitions.

Actually the correct access path would be multi-index access due to the
boolean term predicate. The manual is vague enough about this to actually
not be wrong.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Rajendra Deshpande
Sent: Thursday, December 27, 2001 6:56 AM
To: [login to unmask email]
Subject: PAGE_RANGE=Y


Hi all,

Here is an excerpt from DB2 Admin manual...

**************
consider the following query:
SELECT ..FROM T
WHERE (C1 BETWEEN '2002'AND '3280'
OR C1 BETWEEN '6000'AND '8000')
AND C2 ='6';

Assume that table T has a partitioned index on column C1 and that values of
C1
between 2002 and 3280 all appear in partitions 3 and 4 and the values
between
6000 and 8000 appear in partitions 8 and 9. Assume also that T has another
index
on column C2. DB2 could choose any of these access methods:

1. A matching index scan on column C1. The scan reads index values and data
only from partitions 3, 4, 8, and 9. (PAGE_RANGE=N)

2. A matching index scan on column C2. (DB2 might choose that if few rows
have
C2=6.) The matching index scan reads all RIDs for C2=6 from the index on C2
and corresponding data pages from partitions 3, 4, 8, and 9. (PAGE_RANGE=Y)

3. A table space scan on T. DB2 avoids reading data pages from any
partitions
except 3, 4, 8 and 9. (PAGE_RANGE=Y)
***************

My query is about point 1 above. If the scan there is restricted to
partitions 3,4,8 and 9 ONLY then why is PAGE_RANGE=N.

Any clarification is welcome.
TIA.