db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Segel" <mse...@segel.com>
Subject Re: How to update cardinality statistics
Date Tue, 18 Oct 2005 21:06:17 GMT
On Tuesday 18 October 2005 13:50, Mike Matrigali wrote:
> I 2nd Satheesh's query, it is useful to know why you care.  Derby
> unlike most other database's automatically maintains histogram
> type information about the tables (this does require indexes to
> exist).  This information is gathered directly from the indexes,
> and is automatically maintained by Derby.
I believe the issue is that the documentation may be sparse or hard to find.
In fact, a quick check on the 10.1 docs, in the Reference Manual the 
description for SYSSTATISTICS is incorrect. (It appears to be a typo as a 
replication of SYSSCHEMAS entry. see page 149).

[Seriously it would be nice if someone created an index for these docs.
(Informix used to have their indexes reference mutiple docs which was a nice 

> The row count is also automatically maintained by Derby - whether
> an index exists or not.
I don't think that the row count itself is an issue.

From looking at the original poster, he said he was adding 600 thousand rows 
in to the database and was looking for a way to update statistics. Since most 
people who are looking at Derby are also familiar with another RDBMS or two, 
they are going to try and find if similar functions exist. 

Without going in to an example, how many times have you seen on c.d.i, "Did 
you UPDATE STATISTICS..." as a reply to a performance question.

Going from memory, which is never a good thing to do, after you've tried OTC's 
bath tub gin, I believe that Informix's IDS will not use an index but do a 
sequential scan if the table has less than 10K rows? (You may want to check 
with Mohan, he'd know for sure....) While I may be off on the number of rows, 
the point is that IDS will do something to optimize for performance. The 
downside is that if you have a rapidly growing/changing database, you may 
need to update statistics so that the optimizers will perform correctly.

[Note: Derby has a much smaller footprint. This would entail that Derby lacks 
features/functionality that other databases would have. This is not 
necessarily a bad thing....]

> A goal of Derby is to be zero-admin so we are interested in problems
> that users encounter which make them think they are required to run
> admin type tools.  Do note that Derby does require users to appropriate
> secondary indexes on their tables, once those indexes are created derby
> should be able to automatically pick the correct plan.

Its a nice goal to be zero admin. however, I wonder if you're implying 
something that may be misleading.  What indexes exist when I create a table?
You said:"Derby unlike most other database's automatically maintains histogram
type information about the tables (this does require indexes to exist)."

So if I create a table foo that has column A an integer, and column B a 20 
character text field... What index on table foo exists?

Michael Segel
(312) 952-8175

View raw message