SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE and reindexing

[Dan Karp <dk...@zimbra.com>]

I think we may be getting hit with DERBY-269 (stale index cardinality causes table scans).  The command listed in that JIRA ("alter table <table-name> compress [sequential]") isn't valid.  Calling SYSCS_UTIL.SYSCS_COMPRESS_TABLE works, but takes a huge amount of CPU, disk I/O, and disk space.  Is there a simpler, straightforward way to force recalculation of these indexes?

https://issues.apache.org/jira/browse/DERBY-269

Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE and reindexing

[Francois Orsini <fr...@gmail.com>]

I believe a full table scan updates the internal row count for that table
but will not cause index cardinality (additional info) to be updated for
each index on that table...

http://db.apache.org/derby/docs/10.3/tuning/ctunstats848901.html
http://db.apache.org/derby/docs/dev/tuning/tuning-single.html#ctunstats46438

On Nov 6, 2007 4:13 PM, Stanley Bradbury <St...@gmail.com> wrote:

> Dan Karp wrote:
> > I think we may be getting hit with DERBY-269 (stale index cardinality
> causes table scans).  The command listed in that JIRA ("alter table
> <table-name> compress [sequential]") isn't valid.  Calling
> SYSCS_UTIL.SYSCS_COMPRESS_TABLE works, but takes a huge amount of CPU, disk
> I/O, and disk space.  Is there a simpler, straightforward way to force
> recalculation of these indexes?
> >
> > https://issues.apache.org/jira/browse/DERBY-269
> >
> >
> According to the documentation: Cardinality statistics are updated when
> a table scan is performed so a " select * from <table> " should do it.
>
>
>

Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE and reindexing

[Mike Matrigali <mi...@sbcglobal.net>]

Stanley Bradbury wrote:
> Dan Karp wrote:
>> I think we may be getting hit with DERBY-269 (stale index cardinality 
>> causes table scans).  The command listed in that JIRA ("alter table 
>> <table-name> compress [sequential]") isn't valid.  Calling 
>> SYSCS_UTIL.SYSCS_COMPRESS_TABLE works, but takes a huge amount of CPU, 
>> disk I/O, and disk space.  Is there a simpler, straightforward way to 
>> force recalculation of these indexes?
>>
>> https://issues.apache.org/jira/browse/DERBY-269
>>
>>   
> According to the documentation: Cardinality statistics are updated when 
> a table scan is performed so a " select * from <table> " should do it.
> 
> 
> 
can you point out this in the documentation, it sounds wrong.  We do 
update that stat that is the number of the rows in the table, but we 
don't update what I usually refer to as the cardinality stats - ie. the 
stats on the average number of duplicates per key value.  This requires
some sort of "ordered" scan with extra processing during the scan to
compare each row to it's neighbor and would add too much processing for
a scan.

Re: SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE and reindexing

[Stanley Bradbury <St...@gmail.com>]

Dan Karp wrote:
> I think we may be getting hit with DERBY-269 (stale index cardinality causes table scans).  The command listed in that JIRA ("alter table <table-name> compress [sequential]") isn't valid.  Calling SYSCS_UTIL.SYSCS_COMPRESS_TABLE works, but takes a huge amount of CPU, disk I/O, and disk space.  Is there a simpler, straightforward way to force recalculation of these indexes?
>
> https://issues.apache.org/jira/browse/DERBY-269
>
>   
According to the documentation: Cardinality statistics are updated when 
a table scan is performed so a " select * from <table> " should do it.