Database
Highlighted
Fan

## Statistics on Primary Index

Hi,

How can I query for all the Primary Indexes that doesn't have statistics collected? (for all tables of all databases).

Regards
Tags (2)
6 REPLIES
Junior Contributor

## Re: Statistics on Primary Index

I use this is for missing stats on any PI/SI, you could add filter for PIs only.
I'm shure it can be further simplified, as it was created based on a query solving a different problem :-)

/***
Check for missing stats on indexes.
***/

SELECT
DatabaseName,
TableName,
ColumnList,
TypeOfIndex
FROM
(
SELECT
d.databasename AS DatabaseName,
t.tvmname AS TableName,
CASE
WHEN i.IndexNumber = 1 AND UniqueFlag = 'Y' THEN 'UPI'
WHEN i.IndexNumber = 1 AND UniqueFlag = 'N' THEN 'NUPI'
WHEN UniqueFlag = 'N' THEN 'NUSI'
ELSE 'USI'
END AS TypeOfIndex,

/*** Build a column list for multi-column indexes ***/
MAX(CASE WHEN i.FieldPosition = 1 THEN TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 2 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 3 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 4 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 5 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 6 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 7 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 8 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 9 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 10 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 11 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 12 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 13 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 14 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 15 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition = 16 THEN ',' || TRIM(c.FieldName) ELSE '' END) ||
MAX(CASE WHEN i.FieldPosition > 16 THEN ',...' ELSE '' END) AS ColumnList,

MAX(i.LastAlterTimestamp) AS LastAlterTimestamp,

COUNT(*) AS ColumnCount,

/*** NULL if there are no stats ***/
MAX(SUBSTR(i.IndexStatistics, 1, 80)) AS STATS

FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
JOIN dbc.Indexes i
ON c.tableid = i.tableid
AND c.fieldid = i.fieldid
/*** Add list of DBs to exclude***/
WHERE d.DatabaseName NOT IN ('dbc', 'dbcmngr', 'qcd', 'syslib', 'tdwm')
AND (i.IndexNumber = 1 OR i.IndexType IN ('S','K','U','H','O','V'))
GROUP BY
d.DatabaseName,
t.Tvmname,
i.IndexNumber,
TypeOfIndex
) dt

/*** If multi-column stats were collected before the index was created
those stats are NOT stored within that index definition.
Instead there are two rows with the same set of columns, but
different IndexNumbers and only the 'M' row has stats
***/
GROUP BY 1,2,3,4
HAVING MAX(STATS) IS NULL

/*** Stats on single column indexes are stored within tvfields ***/
AND NOT EXISTS
(SELECT *
FROM
dbc.dbase d
JOIN dbc.tvm t
ON d.databaseid = t.databaseid
JOIN dbc.tvfields c
ON t.tvmid = c.tableid
WHERE
dt.DatabaseName = d.databasename
AND dt.TableName = t.tvmname
AND dt.ColumnList = c.fieldname
AND c.fieldstatistics IS NOT NULL
)
;

## Re: Statistics on Primary Index

Really helpful query to get the list of Database and Tables having UPI/NUPI defined but no Stats collected on it.
Junior Supporter

## Re: Statistics on Primary Index

Hi Dnoeth,

Say i have an Primary index XX(col1,col2) on table. I can see that stats has been collected on col1,col2 as single columns. But not as a combination of column i.e col1,col2.

So, does it hamper the performance as the index is on both the columns so a stats on both column combined should have been collected ? or collecting on single column has the same affect ?

Enthusiast

## Re: Statistics on Primary Index

Hashing output of Col1 and col2 columns separately would always be different from the combined HASH output of (Col1,col2) so in my opinion the Combined stats would be more relevant for the optmizer to get the data demographics other wise the optmizer would rely upon random sampling of (col1, col2) which might differ from the actual data distribution across the AMPs.

The link below would give you a fair amount of idea about stats collection.

Thanks

Manik

Junior Supporter

## Re: Statistics on Primary Index

Hi Dnoeth,

I have a question regaring the query. The single column statistics are stored in dbc.tvfields and the statistics of the indexes (even multicolumn) is stored in dbc.indexes.

If i have stats on multicolumn but they are not indexes, they are not stored in dbc.tvfields and since they are not indexes, they are stored in dbc.indexes. So, where are they stored ?

I wish to find list of tables having no indexes defined

Thx!

Enthusiast

¡Great!, thanks