From derby-user-return-13988-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Oct 28 08:05:12 2011 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 3C2CB78F1 for ; Fri, 28 Oct 2011 08:05:12 +0000 (UTC) Received: (qmail 26383 invoked by uid 500); 28 Oct 2011 08:05:11 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 26337 invoked by uid 500); 28 Oct 2011 08:05:11 -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 Delivered-To: moderator for derby-user@db.apache.org Received: (qmail 6416 invoked by uid 99); 28 Oct 2011 04:27:25 -0000 X-ASF-Spam-Status: No, hits=3.3 required=5.0 tests=FREEMAIL_ENVFROM_END_DIGIT,FREEMAIL_FROM,FREEMAIL_REPLYTO_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 383560.74152.bm@omp1047.mail.sp2.yahoo.com DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1319776014; bh=hl1mLMJ0erD6EFzG8o9DcbQulCkgIY4u9cHd8PG5kvo=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=5uvlqANs8gQv4FUhe6yPsF2JmUW3949Nf0GZGjJC4+7R4fptxnONB7n4yTeIlPRvGLKye20fuUhWmXa24rsntNTpEvAm3n3z928fu5/u6qWAdtd4a+MU264bReFcSj3CO5ZPpEhW4Dir/GW1RZpJA+SkUdOg0biYYzMTcs7cqh0= DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=wbGtE627yebIW4d+GxZE0HD8H9rFQZRI4E5hj6mIQvwE05eBQe3KorPTcB1KEWa2vEvdL8zHtDnt8ObnUx5/ZoYakiWDVnR3zR9sVLLWrr/c3RkXufNoOf4TYBgXj0+N0XTOLjJW8OSqwjb8pvmkkFDz3gAeI7d+8kSXwSkJQOE=; X-YMail-OSG: tdiu7QoVM1lTkTwM6yKygyub5gmqTf7K8Lg.7uAX4QtOn0A kZAVtVl4yB0DtrRQ5h.6iZYXsVKmC7v_gEIJ4vSEi7fUUI9oYG35fNKizFtY jJ70OvNRELnd0HWopt8uVpiJ21ElOeAmKaIW_3p8Y24HxeEMaJKF_lsMlCGD JuxWLoEiehSOBkzbYOwCEv2osYanW1UR0YH_XzO7ciRpr5huJmVhboikhjDE .umgYe1YijAH2W72Di3AMGu37P3M9FG62H47ntEByN4_Tuwz09ZaFrbvdRW5 8arowOB0tug3phi38ypz5AJCwfOZjUz8yxRCFHmSQCCWeVQsMSemO8MMABfp exX1dJIXQx_9b6g3CpKvkV_0FDArLmFb__qkY1xrCXkleFIxCTy.01a7dmxu HSFgvpg_fu73EJ9lu60f2LsNUj.3.fIcBNoOVEwJ8wtHo0vjQdbI43RNLAyP Ozw-- X-Mailer: YahooMailWebService/0.8.114.317681 Message-ID: <1319776014.2665.YahooMailNeo@web112411.mail.gq1.yahoo.com> Date: Thu, 27 Oct 2011 21:26:54 -0700 (PDT) From: Sundar Narayanaswamy Reply-To: Sundar Narayanaswamy Subject: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE question To: "derby-user@db.apache.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1494114646-580425486-1319776014=:2665" --1494114646-580425486-1319776014=:2665 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi,=0AI am trying to use Derby database in the embedded mode in an applicat= ion. I wrote a test program where I have a =0A=0Atable (named LOCATION) wit= h 4 columns as below:create table location(id int, num int, addr varchar(40= ), zip int, primary key(id, zip))=0Acreate index loc_index on location (num= )=0A=0AI insert 10000 rows into the table, then delete all that rows. I the= n call=A0 SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE =0A=0Awith just the optio= n to purge rows so that the space left behind by deleted rowscan be reused = for future inserts. I have =0A=0Athe inserts, deletes and compress in a loo= p. After running through the loop a few times, I am noticing that the =0A= =0Anumber of allocated and unfilled pages for the primary key keeps growing= (causing database size to grow over time). The=0Alonger I run the loop, la= rger these numbers are. That does not happen for the table or the index fil= es though..=0A=0A=0ACONGLOMERATE=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 NumAllocate= dPages=A0=A0 =A0=A0 NumFreePages=A0=A0=A0 NumUnFilledPages=0ALOCATION=A0=A0= =A0 =A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 1=A0=A0=A0 =A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0=A0=A0= =A0831=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0 0=0ASQL1110= 27234806120=A0=A0=A0 =A0=A0 1342=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 294=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 =A0=A0=A0 =A0=A0 594=0ALOC_INDEX=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0=A0 1=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0=A0=A0=A0=A0 521=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 =A0=A0=A0=A0=A0 1=A0=A0=A0 =0A=0AThe primary key space continues = to grow even when I include the options to defragment_rows and truncate_end= to the above function. =0A=0ACONGLOMERATE=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 N= umAllocatedPages=A0=A0 NumFreePages=A0=A0=A0 NumUnFilledPages=0ALOCATION=A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 1=A0=A0=A0 =A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0 0=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0= 0=0ASQL111027233119770=A0=A0=A0 =A0=A0 1674=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 47=A0=A0=A0 =A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0=A0 704=0ALOC_INDEX=A0=A0=A0 =A0=A0=A0= =A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0 13=A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 357=A0=A0=A0 =A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 3=0A=0AThe SYSCS_UTIL.SYSCS= _COMPRESS_TABLE function works well and leaves no free/unfilled pages as ex= pected. However, I am concerned with the =0Aefficiency (time taken) of usin= g the compress function since there could be 10s of millions of rows in a p= roduction setup. It seems that the=0ASYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABL= E function with just the purge_rows option would work best for my situation= . Is =0Athis function not expected to free up deleted space in primary key = files ? =0A=0AThanks very much for you help,=0ASundar. --1494114646-580425486-1319776014=:2665 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi,
I am tryi= ng to use Derby database in the embedded mode in an application. I wrote a = test program where I have a
table (named LOCATION) with 4 co= lumns as below:
create table location(id int, num int, addr varchar(40= ), zip int, primary key(id, zip))=0A
create index loc_index on location= (num)
=0A

=0A
=0A
I insert 10000 rows into the table= , then delete all that rows. I then call  SYSCS_UTIL.SYSCS_INPLACE_COM= PRESS_TABLE
with just the option to purge rows so that the s= pace left behind by deleted rows can be reused for future inserts. I have <= br>
the inserts, deletes and compress in a loop. After running th= rough the loop a few times, I am noticing that the
number of= allocated and unfilled pages for the primary key keeps growing (causing da= tabase size to grow over time). The
longer I run the loop, larger= these numbers are. That does not happen for the table or the index files t= hough..

CONGLOMERATE      &= nbsp;     NumAllocatedPages =      NumFreePages  &nbs= p; NumUnFilledPages
LOCATION        &= nbsp;         &n= bsp;     1  &nbs= p;             =                <= /span>    831            &= nbsp;         0
SQL111027234806120       1342       &nbs= p;         &nbs= p;          294                       594
LOC_INDEX           = ;      &= nbsp;     1  &nb= sp;             = ;               =    521         &nb= sp;         &nb= sp;  1   

The primary key space continue= s to grow even when I include the options to defragment_rows and truncate_e= nd to the above function.

CONGLOMERATE            NumAllocatedPages   NumFreePag= es    NumUnFilledPages
LOCATION     &= nbsp;               &= nbsp;  1          =           =               = 0            = ;               0
SQL111027233119770      = ; 1674           &n= bsp;              &nb= sp; 47           &= nbsp;            
704
LOC_INDEX  =            = ;          13       &nbs= p;                  &nb= sp;     357  &nb= sp;                
  &n= bsp; 3

The SYSCS_UTIL.SYSCS_COMPRESS_TABLE function works wel= l and leaves no free/unfilled pages as expected. However, I am concerned wi= th the
efficiency (time taken) of using the compress function since the= re could be 10s of millions of rows in a production setup. It seems that th= e
SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE function with just the purge_r= ows option would work best for my situation. Is
this function not expec= ted to free up deleted space in primary key files ?

Thanks very muc= h for you help,
Sundar.

--1494114646-580425486-1319776014=:2665--