T K <[email protected]> writes: > Actually I am talking about the sppr_populate_index_data('identiffier', > 'tablename') stored proc that will execute alter table "MYSCHEMA"."SOMETABLE" > compress sequential
ALTER TABLE ... COMPRESS will lock the table exclusively, so all queries that access that table will be blocked for a while, and if the table is very large, you may experience timeouts. Derby 10.5 introduced a new system procedure[1] that updates the statistics without compressing the table. Using this procedure is faster than compressing the table, and it does not obtain an exclusive table lock, so it's somewhat better in this regard. Another issue that you may run into, is that the updating of the statistics will invalidate the compiled execution plan in all prepared statements that access the table in question. The prepared statements will be recompiled automatically when they are used, and in that process they need to access and lock rows in some of the system tables. I have sometimes seen timeouts and deadlocks as a result of this when updating the statistics while other threads are accessing the same table. [1] http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html -- Knut Anders
