On Wednesday 19 October 2005 04:04, Øystein Grøvlen wrote: > >>>>> "MM" == Mike Matrigali <[EMAIL PROTECTED]> writes: > > MM> I 2nd Satheesh's query, it is useful to know why you care. Derby > MM> unlike most other database's automatically maintains histogram > MM> type information about the tables (this does require indexes to > MM> exist). This information is gathered directly from the indexes, > MM> and is automatically maintained by Derby. > > MM> The row count is also automatically maintained by Derby - whether > MM> an index exists or not. > > Sounds good, but I think read somewhere in the Derby documentation > that some statistical information was only updated when a query did a > sequential scan of the table. Well that doesn't make sense in light of what Mike wrote in a different e-mail.
He indicated that Derby used information gathered by an index that the DBA/user created on a table. If I understood correctly, suppose I created the following Table foo that had two columns: id an integer , and bar a char(25) field. I then create an identity index on foo.id. Derby would utilize the identity index to automatically update the table statistics. If you think about it, it kind of makes sense to do this. I mean after all, on an identity index (non null, all distinct and unique), the number of nodes would represent the number of rows in the table. Of course it would also imply some overhead on performing the inserts. No? (There is no such thing as a free lunch. Any sort of hands free automation will come at a cost.) I haven't thought a lot about it, but shooting from the hip, I would imagine that the cost of this "optimization" would increase as the tables increased in size by a log(N) factor. So Derby takes a hit when the database gets large. This of course is assuming I understood Mike's comment. -G -- Michael Segel Principal MSCC (312) 952-8175
