Tablescan - why?

[bughunter]

I have a problem with simple query like this (PK - Primary key)

select t1.*, t2.col1, t3.col2
from
T1
inner join T2 on T2.PK = T1.col1
inner join T3 on T3.PK = T2.col1

T1 is very small table ~20-30 rows, T2 and T3 large. In query plan and
monitor output I see table scan on T3. Why?

Index T2_PK has included column col1.
Access Plan:
-----------
Total Cost: 43603.2
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3092
HSJOIN
( 2)
43602.9
10783
/------+------\
227641 3092
TBSCAN NLJOIN
( 3) ( 4)
40666.2 2926.35
10361 422.045
| /---+---\
227641 3092 1
TABLE: S TBSCAN IXSCAN
T3 ( 5) ( 8)
406.107 50.016
100 2
| |
3092 64931
SORT INDEX: S
( 6) T2_PK
406.107
100
|
3092
TBSCAN
( 7)
404.727
100
|
3092
TABLE: S
T1
Andy

P.S. UDB 8.2 FP10 win32

[Hardy]

hey, do you have any index on T3? if not how can they be used?

[bughunter]

watch closely! PK - primary key - always have a unique index. I'm try
also create index like (PK) include (col2) because this column used in
select - without success.

Andy

[Serge Rielau]

bughunter@ru wrote:

watch closely! PK - primary key - always have a unique index. I'm try
also create index like (PK) include (col2) because this column used in
select - without success.

Andy

This does look odd.
Making T1 the outer and probing T2 and T3 using the PKs is obviously the
better plan.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

What are column attributes?
Would you show us your DDLs?

[bughunter]

DDL is simple like

T1
(DEALID INTEGER NOT NULL, -- PK
APPLID INTEGER NOT NULL, -- T1.Col1
..... -- ~10 short columns like date, dec, int)

T2 (
APPLID INTEGER NOT NULL, -- PK
STOCKID CHARACTER(4) NOT NULL, -- T2.Col1
..... -- ~20 short columns like timestamp, dec, int, char)

T3 (
STOCKID CHARACTER(4) NOT NULL, -- PK
CRDDATE DATE NOT NULL, -- T3.Col1
..... -- ~20 short columns like timestamp, dec, int, char)

[Knut Stolze]

bughunter@ru wrote:

DDL is simple like

T1
(DEALID INTEGER NOT NULL, -- PK
APPLID INTEGER NOT NULL, -- T1.Col1
.... -- ~10 short columns like date, dec, int)

T2 (
APPLID INTEGER NOT NULL, -- PK
STOCKID CHARACTER(4) NOT NULL, -- T2.Col1
.... -- ~20 short columns like timestamp, dec, int, char)

T3 (
STOCKID CHARACTER(4) NOT NULL, -- PK
CRDDATE DATE NOT NULL, -- T3.Col1
.... -- ~20 short columns like timestamp, dec, int, char)

What's the data you have in there and what's your query?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[miro]

According to your explain output below DB2 seems to believe that there
are 3092 rows in table T1. You seem to think T1 is much smaller. Have
you ran runstats recently? If the size of T1 in the plan explain below
is correct, and if the index on T3_PK is not a clustering one, then it
might actually be cheaper to do the join like shown below (build the
hash table based on the result of T1 join T2, the probe it with rows
from T3).

Regards,
Miro

bughunter@ru wrote:

I have a problem with simple query like this (PK - Primary key)

select t1.*, t2.col1, t3.col2
from
T1
inner join T2 on T2.PK = T1.col1
inner join T3 on T3.PK = T2.col1

T1 is very small table ~20-30 rows, T2 and T3 large. In query plan and
monitor output I see table scan on T3. Why?

Index T2_PK has included column col1.
Access Plan:
-----------
Total Cost: 43603.2
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3092
HSJOIN
( 2)
43602.9
10783
/------+------\
227641 3092
TBSCAN NLJOIN
( 3) ( 4)
40666.2 2926.35
10361 422.045
| /---+---\
227641 3092 1
TABLE: S TBSCAN IXSCAN
T3 ( 5) ( 8)
406.107 50.016
100 2
| |
3092 64931
SORT INDEX: S
( 6) T2_PK
406.107
100
|
3092
TBSCAN
( 7)
404.727
100
|
3092
TABLE: S
T1
Andy

P.S. UDB 8.2 FP10 win32