Access path question on index scan

teldb2kals

Access path question on index scan
Hi,

I have this query :

SELECT A.COL1
,A.COL2
,B.COL1
,B.COL2
,B.COL3
,B.COL4
,B.COL5
,B.COL6
,B.COL7
,B.COL8
,C.COL1
,C.COL2
FROM TAB1 A

INNER JOIN TAB2 B
ON A.COL2 = B.COL1

LEFT OUTER JOIN TAB3 C
ON B.COL4 = C.COL1

WHERE A.COL1 = ?

TAB1 (1400 rows)
Index11 - COL1, COL2 - primary key - clusterratio 100%, firstkeycard 2,
fullkeycard 1400

TAB2 (800) rows
Index21 - COL1 - primary key - clusterratio 100%, firstkeycard 800,
fullkeycard 800
Index22 - COL4

TAB3 (4 rows)
Index31 - COL1 - primary key - clusterratio 100%, firstkeycard 4, fullkeycard 4

The accesspath shows

Table M TN AT MC ACNAME IO NUJOGCUJOG TSLK PF
TAB2 0 2 I 0 INDEX21 N NNNN NNNN IS S
TAB1 1 1 I 2 INDEX11 N NNNN NNNN IS
TAB3 1 4 I 1 INDEX31 N NNNN NNNN IS

(Tablename, Method, Tableno, Accesstype, matchcols, Indexonly, Sorts,
Locks, prefetch.)

I think the reason it doesn't go in on TAB1 first is because of the low
cardinality for col1. (correct me if I am wrong). But why does it do a non-
matching index scan of INDEX21 first instead of directly doing a tablespace
scan of TAB2 ? Not sure if I am missing something obvious here.

Also I am curious as to why tableno is appearing as 4 for TAB3 ?

Thanks in advance.

Kals

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: Access path question on index scan
(in response to teldb2kals)
> I think the reason it doesn't go in on TAB1 first is because of the low
> cardinality for col1.

Agreed.

> But why does it do a non-matching index scan of INDEX21 first
> instead of directly doing a tablespace scan of TAB2 ?

By going through table2 in the exact order of B.col1 = A.col2, followed by a
(matching) index access on (A.col2, A.col1), index look-aside on table1 can
be used since the scan order is guaranteed to be on the 2nd column of that
index, with just 2 different values for the 1st: it's as if it can just
sequentially run through the two half-indexes (A.col2, A.col1=val1) and
(A.col2, A.col1=val2) while doing the join of B with A.
So the first join is almost identical to a merge scan join (without the
additional sort).

Apparently, this (superfluous) index access is not much of an overhead since
the index is only 800 entries (a few pages?) and the RID order is almost the
same as the index order (high cluster ratio).

What surprises me is the fact that table1=A is not accessed in an index-only
way. Apparently one of those columns (A.col1 or A.col2) is VARCHAR and the
index is still PADDED (right?) Change it to NOT PADDED and you'll get an
Index Only access:
Table M TN AT MC ACNAME IO NUJOGCUJOG TSLK PF
TAB2 0 2 I 0 INDEX21 N NNNN NNNN IS S
TAB1 1 1 I 2 INDEX11 Y NNNN NNNN IS
TAB3 1 4 I 1 INDEX31 N NNNN NNNN IS

So, in summary, I believe the reason is "index lookaside".

-- Peter Vanroose.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms