By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Sort after index scan

P: n/a
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

Mar 13 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Mar 13 '06 #2

P: n/a
Raj
Thanks for the reply
The index is created with allowscan option and our sort heap is 8000
pages on 6 logical nodes ..

Mar 13 '06 #3

P: n/a
have you do runstats on the very table?

Mar 14 '06 #4

P: n/a
Raj
Yeah stats are current on the table ..
Hardy wrote:
have you do runstats on the very table?


Mar 14 '06 #5

P: n/a
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

Mar 14 '06 #6

P: n/a
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

Mar 14 '06 #7

P: n/a
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
Mar 15 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.