RE: members in a file

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