db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Questions about compress table
Date Tue, 15 Jul 2008 02:41:35 GMT
Kathey Marsden wrote:
> http://db.apache.org/derby/docs/dev/ref/rrefaltertablecompress.html says 
> a non sequential compress uses "an amount equal to approximately two 
> times the used space plus the unused, allocated space"
> and it says SEQUENTIAL uses less space.  How much space does SEQUENTIAL 
> use?
> Also is there a general guideline for how much longer SEQUENTIAL will take?
> Thanks
> Kathey
It hard to give absolutes.  Non sequential builds everything at once, so
the space used is:
1) all the original space of the base table + all the original space of 
all the indexes
2) all the space of the new base table + all the new space of all the 
3) all the space that has been overflowed to disk as part of the sort to 
  build the indexes.  In the worst case this will be close to the space 
used by the new indexes in #2.

Non-sequential is still going to need 1+2.  Because it builds one index
at a time the only additional space used will be the sort needed for
the "current" index it is building.  This is in temp space and can be 
deleted immediately as each index is built.

The extra time is basically dependent on the size of the base table, and 
how long it takes to do a sequential scan of it.  If it takes N to do
a sequential scan of the base table and there are Y indexes, then it
will take approximately N * Y-1 more time for non-sequential vs. 

Note that no matter what the system is going to hold an exclusive lock 
on the whole table for the entire compress process, whether it is 
sequential or non-sequential.

View raw message