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: maintenance of apache derby db
Date Tue, 06 Jul 2010 07:47:07 GMT
On 07/ 3/10 02:32 PM, William Oppong Senti wrote:
> Hi
>
>  I am a new to, trying to develop a small program for sales management 
>  using apache derby. My problem is , when do I have to use
>
>  1. SYSCS_UTIL.SYSCS_UPDATE_STATISTICS system procedure.
>   

If some of the tables have changed significantly in size since the
indexes were created, and some queries are unreasonably slow, it may be
worthwhile executing this procedure to give the optimizer better data to
work on.

>  2. SYSCS_UTIL.SYSCS_COMPRESS_TABLE system procedure.
>  And also how do I compress all tables in a database?
>   

SYSCS_COMPRESS_TABLE should be used if you have deleted lots of rows
from a table and you want the freed space go back to the file system.
Derby doesn't shrink the files holding the tables when rows are deleted,
it only marks sections of the files as available for new rows.
SYSCS_COMPRESS_TABLE will make the files shrink.

To compress all the files in the database, you would need to query the
system tables (either with DatabaseMetaData.getTables() or by issuing a
SELECT against SYS.SYSTABLES directly) and call SYSCS_COMPRESS_TABLE on
each table in a loop.

SYSCS_COMPRESS_TABLE will also update the statistics for all indexes on
that table, so you don't need to execute SYSCS_UPDATE_STATISTICS in
addition to it.

>  3. Recreate indexes or rebuild indexes and how?
>   

You only need to recreate indexes if the indexes have become corrupt
somehow. This shouldn't happen unless there's a bug in Derby or a media
failure. If a corruption of a database file happens, the only safe way
to recover is to roll forward from a backup. But if you've had luck and
the corruption only affected an index file, recreating that index should
bring the database back to a consistent state and no data should be lost.

To recreate an index, you need to do a DROP INDEX followed by a CREATE
INDEX. If the index was created implicitly to enforce a constraint,
you'd need to drop and add that constraint instead, using ALTER TABLE
... DROP/ADD CONSTRAINT.

>  4. If you check your database for inconsistency and you encounter an error,
>  advising you to rebuild or recreate an index, how do you go about it?
>   

This wiki page explains how to check the consistency of the database:
http://wiki.apache.org/db-derby/DatabaseConsistencyCheck

If it discovers an inconsistency in an index, you could use DROP/CREATE
INDEX and ALTER TABLE, as described above, to recover. If there's a
problem in a table, recovery from backup is the only supported option.

Hope this helps,

-- 
Knut Anders


Mime
View raw message