RE: members in a file

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Responses in-line

At 04:14 PM 12/29/2007, you wrote:

> > From: BirgittaHauser
> >
> > >>And of course, statistics don't help as much in completely ad hoc
> > situations,
> > >>because the SQL engine will have no statistics on queries that have
> > never
> > been done.
> >
> > Statistic information are collected for all SQL tables, physical files and
> > access path (keyed logical files and SQL indexes). In this way statistics
> > can help for ad hoc queries in the same way as they do for queries that
> > already have been executed.
>
> But if the field is not part of an access path and you haven't done a query
> over it, are the statistics already in place?  That would imply that
> statistics are collected on every write or update to the database, which is
> not something I'd like.

Stats are not the same as access paths - or indexes. They are 
completely different, and if there is a single column index, that 
gives better information, and that is what is used --- there might 
even be - not sure - any stats over that column.

Stats are collected when the Stats Manager decides they should be - 
this involves aging of the stats, changes in the number of records, 
lots of things. The redbook (Birgitta and Elvis were on the team that 
wrote it) probably has lots more on this. Michael Cain's article 
probably does, as well. I cited those in my other post.

> Can you turn collection on for individual columns within a database?

Yes, you can select individual columns for collection - there's lots 
of stuff on this that you set up in Navigator - right-click on a 
table in the Database area to see and set statistics - and there's a 
system value to determine certain behaviors.

> > Certainly not 10% of disk space. The space to store the statistics depends
> > on the number of access paths built over the physical files/tables and the
> > number of different key values. For each access path the different key
> > values and the number of rows that satisfy this values are stored, which
> > is much less space than an index or keyed logical file takes.
>
> Actually, if the keys are unique, then this sounds like it would take nearly
> exactly the same amount of storage as an index.  Instead of the unique key
> and the RRN of the record, you would have the key and the number of
> occurrences, which would always be one.

If you have an index, you really don't need stats - all the 
information is there, and is updated on every IO that changes 
something - unlike Stats. The 3 main components of stats are all 
estimated -- cardinality, most frequent values, and distribution (how 
many in various range buckets) - none of that will take up the space 
of any index, probably - see Cain's article (8k - 12k)

> Joe

Loved the snow in chi-town - flight last night got cancelled - had to 
come home today!

Vern