From derby-user-return-14151-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Feb 1 13:52:56 2012 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 76E7295A2 for ; Wed, 1 Feb 2012 13:52:56 +0000 (UTC) Received: (qmail 16710 invoked by uid 500); 1 Feb 2012 13:52:56 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 16665 invoked by uid 500); 1 Feb 2012 13:52:55 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 16658 invoked by uid 99); 1 Feb 2012 13:52:55 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Feb 2012 13:52:55 +0000 X-ASF-Spam-Status: No, hits=-5.0 required=5.0 tests=RCVD_IN_DNSWL_HI,SPF_PASS,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [141.146.126.227] (HELO acsinet15.oracle.com) (141.146.126.227) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Feb 2012 13:52:45 +0000 Received: from ucsinet22.oracle.com (ucsinet22.oracle.com [156.151.31.94]) by acsinet15.oracle.com (Switch-3.4.4/Switch-3.4.4) with ESMTP id q11DqBKk006260 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Wed, 1 Feb 2012 13:52:15 GMT Received: from acsmt357.oracle.com (acsmt357.oracle.com [141.146.40.157]) by ucsinet22.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id q11DqAYM014766 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 1 Feb 2012 13:52:11 GMT Received: from abhmt112.oracle.com (abhmt112.oracle.com [141.146.116.64]) by acsmt357.oracle.com (8.12.11.20060308/8.12.11) with ESMTP id q11DqA2P004721 for ; Wed, 1 Feb 2012 07:52:10 -0600 Received: from dhcp-rmdc-twvpn-1-vpnpool-10-159-2-243.vpn.oracle.com (/10.159.2.243) by default (Oracle Beehive Gateway v4.0) with ESMTP ; Wed, 01 Feb 2012 05:52:10 -0800 Message-ID: <4F29437F.1020304@oracle.com> Date: Wed, 01 Feb 2012 05:51:59 -0800 From: Rick Hillegas User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US; rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11 MIME-Version: 1.0 To: derby-user@db.apache.org Subject: Re: Selecting a few rows is really slow when ordering by primary key References: <4F22BD4D.5040603@oracle.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Source-IP: ucsinet22.oracle.com [156.151.31.94] X-CT-RefId: str=0001.0A090208.4F294398.0044,ss=1,re=0.000,fgs=0 Hi Simon, Glad to hear that regenerating the statistics fixed your problem. More inline... On 2/1/12 4:07 AM, Simon Chatelain wrote: > ... > > But one small question remains, do you think that it is enough to run > SYSCS_UPDATE_STATIS > TICS only once, or should I plan to run this procedure at regular > interval (let's say once a week or once a month) in case the > statistics are not correctly updated as new rows are inserted in the > database ? > If you upgrade to 10.8, the statistics should be regenerated automatically. The statistics are supposed to be recalculated when the number of rows in the table is too far out of sync with the number of rows estimated by the statistics. "Too far out of sync" means that the ratio between the two numbers is more than e. If statistics are not being generated often enough, you can adjust the triggering ratio by setting the following Derby property: derby.storage.indexStats.debug.lndiffThreshold That property defaults to be 1.0. By setting it to a value between 0.0 and 1.0, you should speed up the rate at which Derby recalculates statistics. By setting the property to a larger value, you should slow down the rate at which Derby recalculates statistics. Let us know if you think that statistics are not being regenerated often enough. This is a new feature which we are eager to tune and improve. Hope this helps, -Rick