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
indexes
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.
sequential.
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.
