Sort after index scan

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raj - 13 Mar 2006 17:43 GMT

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

Reply to this Message

Shashi Mannepalli - 13 Mar 2006 19:05 GMT

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

Reply to this Message

Raj - 13 Mar 2006 20:14 GMT

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

Reply to this Message

Hardy - 14 Mar 2006 03:19 GMT

have you do runstats on the very table?

Reply to this Message

Raj - 14 Mar 2006 14:59 GMT

Yeah stats are current on the table ..

> have you do runstats on the very table?

Reply to this Message

sethwai@yahoo.com - 14 Mar 2006 15:12 GMT

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

Reply to this Message

sethwai@yahoo.com - 14 Mar 2006 15:25 GMT

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

Reply to this Message

Knut Stolze - 15 Mar 2006 08:19 GMT

> Hi,
>
[quoted text clipped - 33 lines]
> 91817.1
> 27602.9

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message