Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <43558159.5020603@sbcglobal.net>
Date: Tue, 18 Oct 2005 16:12:25 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Mozilla Thunderbird 0.7.2 (Windows/20040707)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: How to update cardinality statistics
References: <4353EA29.7070406@softhome.net> <43553F60.5080704@Sourcery.Org>
 <435543D9.20601@sbcglobal.net> <200510181606.17120.msegel@segel.com>
In-Reply-To: <200510181606.17120.msegel@segel.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

You are right I was unclear.  Let me try again.  Derby uses existing
indexes created by the user to derive histogram information to be used
in costing various query plans, and thus in chosing which indexes it
uses.  This histogram is dynamic and never needs to be updated by the
user.  All other databases that I have worked on in the past have
required users to periodically update histogram based statistics if
their data distribution changes.  I was trying to point out that this
manual update was not necessary in Derby.

I also really miss having a single index to search the documents.

Michael J. Segel wrote:

> 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 
> feature...)]
> 
> 
>>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?
>