Sort after index scan

[Raj]

Hi,

I have a pretty simple query like

select
t1.c1,t1.c2,t1.c2,
t2.c1,t2.c2
from t1, t2

where t1.c1 = ' xxx '
and t2.c2 >'xxx'

t1 and t2 have nonunique index on c1 and c2
t1 card 978,815,058
t2 card 52308
what i don't undertand is, for t1 there is a sort after index scan
which is spilling to the disk as causing the query to run very slowly,
why is it doing a sort after the IXSCAN???

5.58051e+07
RIDSCN
( 6)
2.61989e+06
472267
|
5.58051e+07
SORT
( 7)
2.15091e+06
249935
|
5.58051e+07
IXSCAN
( 8)
91817.1
27602.9

Thanks a lot,
Raj

[Shashi Mannepalli]

My few cents

1. Did u try creating those indexes using ALLOW REVERSE SCANS option ?
Just run db2advis on the given SQL it will give u the correct syntax of
the INDEXES.

2. Did u try increasing the SORTHEAP ?

cheers...
Shashi Mannepalli

[Raj]

Thanks for the reply
The index is created with allowscan option and our sort heap is 8000
pages on 6 logical nodes ..

[Hardy]

have you do runstats on the very table?

[Raj]

Yeah stats are current on the table ..
Hardy wrote:

have you do runstats on the very table?

[sethwai]

It looks like it is sorting the rids to make for more effecient table
access. This way it will only access the pages with the required rows.
The optimizer has determined that this will be faster than reading an
index record and then getting its' associated row one at a time. It
kind of makes sense as the > comparison may bring back a large number
of rows. It might choose not to do the sort if the index on t1
clusters the table. Does it cluster the table?

Lew

[sethwai]

Oops. I goofed on that one. I got the t1 and t2 clauses mixed up.
Sorry about that. Hmmm. Is there the chance of a large number (I'm
talking very large) of duplicate 'xxx' entries for the t1 table?

Lew

[Knut Stolze]

Raj wrote:

Hi,

I have a pretty simple query like

select
t1.c1,t1.c2,t1.c2,
t2.c1,t2.c2
from t1, t2

where t1.c1 = ' xxx '
and t2.c2 >'xxx'

t1 and t2 have nonunique index on c1 and c2
t1 card 978,815,058
t2 card 52308
what i don't undertand is, for t1 there is a sort after index scan
which is spilling to the disk as causing the query to run very slowly,
why is it doing a sort after the IXSCAN???

5.58051e+07
RIDSCN
( 6)
2.61989e+06
472267
|
5.58051e+07
SORT
( 7)
2.15091e+06
249935
|
5.58051e+07
IXSCAN
( 8)
91817.1
27602.9

What's the complete plan? Maybe DB2 is picking a sort/merge join or so?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany