CODEWITHSUNDEEP
db2query-optimization
A Programmer
A Programmer

Reputation: 378

clarification on db2 optimiser - Runstas

I had a complicated table which has only 7 columns but in production it will have many rows say more than 100,000 rows..

so for this i execute RUNSTATS for two columns one is PK and another is FK..

RUNSTATS ON TABLE WEBSS.P0029_LOCATION  WITH DISTRIBUTION ON COLUMNS (LOC_ID, OUTLET_ID);

after this when i run 

SELECT * FROM SYSCAT.COLDIST WHERE TABSCHEMA = 'WEBSS' AND TABNAME = 'P0029_LOCATION'

In result i had 60 rows.. 30 rows each for two columns.. in that i had type is Q and F.. Quantile and Frequeency..

But i need little more input on this.. on what basis they(Q and F) are defined.. on what basis do we need to optimise.

Please pour your suggestions.

Upvotes: 1

Views: 82

Answers (1)

Steven Lowenthal
Steven Lowenthal

Reputation: 656

There are two type of column statistics on DB2, simple ones where you just get the column cardinality and the number of nulls, and distribution stats as you have collected above.

I found simple statistics are better for most applications unless you do literal searches on highly skewed data.

If you have indexes defined on you PKs and FKs you get simple stats with

RUNSTATS ON MYTABLE ON KEY COLUMNS

or 

RUNSTATS ON MYTABLE ON ALL COLUMNS

The quantiles are histogram data, and you get by default I think 20 histogram values for each, and the F are the most popular values in your column, and I then you get 10 by default. You don't need distributions on a PK, as it's unique, and it's unlikely you need them on an FK as well. Stick to the simple ones first.

Upvotes: 2

Related Questions