470,849 Members | 1,211 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,849 developers. It's quick & easy.

Tablescan - why?

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

Feb 17 '06 #1
7 1819
hey, do you have any index on T3? if not how can they be used?

Feb 17 '06 #2
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

Feb 17 '06 #3
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
Feb 17 '06 #4
What are column attributes?
Would you show us your DDLs?

Feb 17 '06 #5
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)

Feb 20 '06 #6
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
Feb 20 '06 #7
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

Feb 20 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Puvendran | last post: by
2 posts views Thread by JonQuark | last post: by
6 posts views Thread by Otto | last post: by
reply views Thread by Raj | last post: by
2 posts views Thread by Raj | last post: by
6 posts views Thread by db2admin | last post: by
4 posts views Thread by Raj | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.