I have been testing with the new automatic statistics feature in 10.8.2.1 and
turned on the logging and see it being triggered as I make changes to my
database. So this appears to be working. What I am surprised at however, is
that I have tables in my database that have out of date statistics (none) as
reported by this query:
select schemaname,
tablename || ' (' || CONGLOMERATENAME || ')' as "Table (Index)",
CASE WHEN CAST
(
creationtimestamp AS varchar(24)
)
IS NULL THEN 'Recreate Index to Initialize' ELSE CAST
(
creationtimestamp AS varchar(24)
)
END
from sys.systables t
join sys.sysconglomerates c on t.tableid = c.tableid
JOIN sys.SYSSCHEMAS x on t.SCHEMAID = x.SCHEMAID
LEFT OUTER JOIN sys.sysstatistics s ON c.conglomerateid = s.referenceid
where t.tableid = c.tableid
and c.isindex = true
and t.tabletype = 'T';
The tables start empty but with indexes and then data gets added later. I
thought that probably the new automatic statistics feature would be triggered
on a query of these tables but it does not seem to be. If the table already
has statistics they seem to be updated. I could be wrong however and maybe my
query is not sufficient to trigger statistics update but I did do a query for a
specific value of primary key and saw nothing in derby.log.
So do I still need to prime these statistics myself with a call to
"syscs_util.update_statistics"?
Thanks for any information.
Brett
