Hi all DB2 experts! I have a que

[ayladilara]

Hi all DB2 experts!

I have a question for you regarding db2 performance! We have got a table on which there are 20 milion records. When I query the table with the following script
" SELECT DISTINCT COL1,COL2,COL3 FROM MYTABLE WHERE STOREID=12345 " it takes nearly three min. to get the result.
My question : Is this normal for db2 engine? If no, what can be done to optimaze the query?
I know the fact that the queries with DISTINCT are expensive business.
Thanks in Advance!

 

[blom0344]

Regarding the amount of records this does not look like a long time. Having an index on STOREID might make a difference, but the DISTINCT clause will still take a lot of time.
Fun to experiment: create a view that holds only records on a certain STOREID and run the DISTINCT on the view, slower,equally slow or faster.............. T. Blom
Information analyst
tbl@shimano-eu.com

 

[DBGod]

If there is an index on STOREID, DB2 may or may not use this to perform the DISTINCT action, depending on the stats for the index held in the catalog.

If the index is not used, then DB2 will execute a tablespace scan, followed by a sort and elemination of duplicate rows. If there are many rows to return, then the sort time and FETCH time will also be significant.

I would expect a runtime of 3 minutes to be reasonable in the circumstances, with no index.

Regards

Mick