Return-Path: 
 <derby-user-return-15350-apmail-db-derby-user-archive=db.apache.org@db.apache.org>
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 8992010A6E
	for <apmail-db-derby-user-archive@www.apache.org>;
 Fri, 13 Dec 2013 16:37:46 +0000 (UTC)
Received: (qmail 40614 invoked by uid 500); 13 Dec 2013 16:37:45 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 40535 invoked by uid 500); 13 Dec 2013 16:37:40 -0000
Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-user-help@db.apache.org>
list-unsubscribe: <mailto:derby-user-unsubscribe@db.apache.org>
List-Post: <mailto:derby-user@db.apache.org>
List-Id: <derby-user.db.apache.org>
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Delivered-To: mailing list derby-user@db.apache.org
Delivered-To: moderator for derby-user@db.apache.org
Received: (qmail 70607 invoked by uid 99); 13 Dec 2013 01:37:00 -0000
X-ASF-Spam-Status: No, hits=1.5 required=5.0
	tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of
 lasantha.siriwardana@gmail.com designates 209.85.215.53 as permitted sender)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20120113;
        h=mime-version:from:date:message-id:subject:to:content-type;
        bh=TQ6X/GvuZbNzc1XKlsWeFsEQtGOlH62k7IRQsnhrDEA=;
        b=WzTUTJ6pArBnNQFRrbudBLMVmITt+ep0N793ck66MTGtteRu/BwFSOO2IFNQXjHXjH
         +WjhP1opp5nEycZzdaBM0FvSYvnE1hBT+0Uscoc5aI6w03wVDZxho3U8Z+XPm/QB+M48
         GVM/TdWnx8mxljqep97LdAjLiQe4pzUf3FTMuqmwgX4RF+/VE4AHkKsu9sykKU+zhv9Y
         logzI+eiS60qS15voLMlHVVTPFD4MHBl0DaN1WfOaL6GOWrkWBTqCK+wNKn8f4AL7gs3
         N1ZFhsUpLiuAuLqqsdsIpwq9/qg3Oj4cHF3S+UUosqRU8XRVZ6ZhUH3anzxAV3QCVPLk
         MFlg==
X-Received: by 10.152.4.74 with SMTP id i10mr4951484lai.58.1386898594228; Thu,
 12 Dec 2013 17:36:34 -0800 (PST)
MIME-Version: 1.0
From: Lasantha Siriwardana <lasantha.siriwardana@gmail.com>
Date: Fri, 13 Dec 2013 09:36:14 +0800
Message-ID: 
 <CAMo7P4X21WzEvAXyszYVEBUXsRxd3xUYjphusm+9ciuKqE5SpQ@mail.gmail.com>
Subject: Derby In-Memory DB is not using any available indexes
To: derby-user@db.apache.org
Content-Type: multipart/alternative; boundary=089e0103ec240c6d4b04ed607edd
X-Virus-Checked: Checked by ClamAV on apache.org

--089e0103ec240c6d4b04ed607edd
Content-Type: text/plain; charset=ISO-8859-1

Hi All,

I've setup an derby in-memory db using derby distribution 10.10.1.1. Below
are the configurations;
Max Heap Size - 3GB
Page Cache Size - 10000
I stored nearly one million records in a table with around 50 columns and
trying to retrieve the data. But the query takes ~10sec. When I check the
explain plan, It's not used any index and the optimizer cost is very
higher. Also the scan type was heap. Please help to resolve this issue and
tuneup the query. Why doesn't derby in-memory use indexes?

Lasantha

--089e0103ec240c6d4b04ed607edd
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div><div>Hi All,<br><br></div>I&#39;ve set=
up an derby in-memory db using derby distribution 10.10.1.1. Below are the =
configurations;<br></div>Max Heap Size - 3GB<br></div>Page Cache Size - 100=
00<br>

</div>I stored nearly one million records in a table with around 50 columns=
 and trying to retrieve the data. But the query takes ~10sec. When I check =
the explain plan, It&#39;s not used any index and the optimizer cost is ver=
y higher. Also the scan type was heap. Please help to resolve this issue an=
d tuneup the query. Why doesn&#39;t derby in-memory use indexes?<br>

<br></div>Lasantha<br></div>

--089e0103ec240c6d4b04ed607edd--