Tables Missing Statistics

Database
Enthusiast

Tables Missing Statistics

We have implemented a process by which we call a stored proc after each table load that collects stats on the target table if currentPerm has changed by more than 10%.  Part of this process involves loading some driver tables.  If any table in any of the user databases is missing stats (not stats on an empty table but no stats ever collected), the databasename and tablename are added to a process that collects on some common fields. 

Dieter created a view called statsInfo14 that used to provide that information until 14.00.  That logic was not added because of the complexity involved in the 14.00 stats changes.  Has anyone come up with an easy way to determine, by database, tables that have not had any collect stats statements run?  I have come up with the following that seems to work ok, but thought I would check to see if there was an easier way:

sel distinct trim(d.databasename), '|', trim(d.tablename)

from dbc.tables d

where ((trim(d.databasename) like '%STAGE%'

and (substr(d.tablename,length(trim(trailing from d.tablename))-1,1) ='P'

or  trim(d.tablename) like '%_S1'))

or (trim(d.databasename) = 'databasea'

or trim(d.databasename) = 'databaseb'

or trim(d.databasename) = 'databasec')

and (trim(d.tablename) not like '%_BKUP%'

and trim(d.tablename) not like '%_TMP%'

and trim(d.tablename) not like '%_BC%'

and trim(d.tablename) not like '%_ORIG%'

and trim(d.tablename) not like '%_TEMP%'

and trim(d.tablename) not like '%_OLD%'

and trim(d.tablename) not like '%_BKP%'

and trim(d.tablename) not like '%_TEST%'

and trim(d.tablename) not like '%INFA_%'))

and d.tablekind='T'

minus

sel distinct trim(s.databasename), '|', trim(s.tablename)

from dbc.statsv s

where trim(s.databasename) like '%STAGE%'

and (substr(s.tablename,length(trim(trailing from s.tablename))-1,1) = 'P'

or  trim(s.tablename) like '%_S1')

or (trim(s.databasename) = 'databasea'

or trim(s.databasename) = 'databaseb'

or trim(s.databasename) = 'databasec')

and (trim(s.tablename) not like '%_BKUP%'

and trim(s.tablename) not like '%_BC%'

and trim(s.tablename) not like '%_TMP%'

and trim(s.tablename) not like '%_TEMP%'

and trim(s.tablename) not like '%_OLD%'

and trim(s.tablename) not like '%_BKP%'

and trim(s.tablename) not like '%_TEST%'

and trim(s.tablename) not like '%INFA_%')

order by 1,2;

Thanks,

Joe