Statistics impact on db2 optimizer plan Has the creation of statistics any impact on the estimated costs and the optimizer plan given by db2 optimizer. Or will (in the best case) only the execution time of a query change?

I ran something like this. Is this right/useful?

RUNSTATS ON TABLE DB2INST1.ARTISTS WITH DISTRIBUTION DEFAULT NUM_FREQVALUES 50 NUM_QUANTILES 5 AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE DB2INST1.ARTISTS ON KEY COLUMNS;
RUNSTATS ON TABLE DB2INST1.ARTIST ON COLUMNS((COUNTRY,ARTIST_NAME),(ID,ARTIST_NAME),(CONTACT_MAIL,CONTACT_PHONE),(WEBSITE,TWITTER,SOUNDCLOUD,FACEBOOK));


Two queries I ran on this database were:

SELECT * FROM ARTISTS WHERE WEBSITE IS NOT NULL AND TWITTER IS NOT NULL AND SOUNDCLOUD IS NOT NULL AND FACEBOOK IS NOT NULL
SELECT YEAR, FORMAT, count(*) AS ANZAHL, sum(TRACK_LENGTH) AS GESAMTLAENGE_sec FROM RELEASES JOIN TRACKS ON RELEASES.ID = TRACKS.RELEASE_ID GROUP BY YEAR, FORMAT ORDER BY YEAR DESC


The execution time of both queries was better than before running runstats. But the estimated cost and the optimizer plan given by db2expln were exactly the same.

db2expln command:

db2expln -q [one of the queries above] -t -g -d $dbname
Clearly yes - statistics are very important inputs for the optimizer!
This is what statistics are for.
Besides that configuation, data model (objects) etc. have an effect as well.
YOu can influence the optimization class to determine which optimizer techniques will be considered by the SQL Query Compiler and the DB2 cost based optimizer which is part of that.