db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Running stats while online
Date Fri, 21 Aug 2009 10:55:32 GMT
T K <sanokistoka@yahoo.com> 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

View raw message