A select count(*) returns 13 rows as expected. Accordin= g, though, to the explain plain, there are over 85,000 which I presume is d= ead space (deleted rows). Derby does not support detached blobs. According = to the plan, it traversed all 85,000 rows (active or deleted) =3D ~32MB of = data, sequentially, to find those 13 of which 4 match the predicate.
This is truly nuts!

From: "derby@segel.com" <derby@segel.com>
To: Derby Discussion <derby-user@db.apache.org>
= Sent: Wednesday, September 23, 2009 9:22:16 PM
Subject: RE: Horrible performance - how = can I reclaim table space?

=0A=0A=0A =0A =0A=0A =0A=0A =0A=0A=0A=0A=0A=0A=0A=0A

I=E2=80=99m confused.

=0A=0A

= =0A=0A

How many= rows are actually in the table?=0AAlso the blob is stored in the same tabl= e not stored separately?

=0A=0A

(Sorry, but I=E2=80=99m working in a cou= ple=0Aof different databases so I=E2=80=99m always forgetting if=0A Derby a= llows for detached blobs =E2=80=A6)

=0A=0A

=0A= =0A

But if your= table really only has 13 rows,=0Ait will always do a sequential scan.

=0A=0A

=0A=0A
=0A=0A
=0A=0A
=0A=0A
=0A=0A
=0A=0A

From: T K= =0A[mailto:sanokistoka@yahoo.com]
=0ASent: Wednesday, September 23,=0A2009 8:04 PM
=0ATo:
derby-user@db.apache.org
= =0ASubject: Horrible perfo= rmance -=0Ahow can I reclaim table space?

=0A=0A
=0A= =0A

=0A=0A
=0A=0A
= =0A=0A

= We have a horrific=0Aperformance issue with a table of 13 rows, each one co= ntaining a very small=0Ablob, because the table is presumably full of dead = rows and we are=0Atable-scanning; here's part of the explain plan:
=0A=0A           &nbs= p;           =0ASour= ce result set:
=0A         =             &nb= sp;         =0ATable Scan Resu= ltSet for SOMETABLE at read committed isolation level using=0Ainstantaneous= share row locking chosen by the optimizer
=0A    &n= bsp;            = ;            &n= bsp;         =0ANumber of colu= mns fetched=3D4
=0A         = ;            &n= bsp;            = ;     =0ANumber of pages visited=3D8546
=0A&nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;=0ANumber of rows qualified=3D13
=0A     &= nbsp;           &nbs= p;            &= nbsp;        =0ANumber of rows visi= ted=3D85040
=0A         &nb= sp;            =             &nb= sp;    =0Aoptimizer estimated cost:   &n= bsp;   787747.94
=0A
=0ASo I assume I have over 85,000 dead= rows in the table, and compressing it does=0Anot reclaim the space. In fac= t, because we keep adding and deleting rows, the=0Aperformance gets worse b= y the hour, and according to the above plan,=0A Derby has processed over=0A= 32MB of data just to match 4 of the 13 rows. For the time being, I want to= =0Aoptimize this table scan before I resort to indices and/or reusing rows.= This=0Ais with Derby =0A10.3
=0A
=0AAny thoughts?
=0A
=0AThank= s

=0A=0A
=0A=0A
=0A=0A

&= nbsp;

=0A=0A
=0A=0A
=0A=0A

=0A=0A --0-814250333-1253755883=:70855--