On 07/ 3/10 02:32 PM, William Oppong Senti wrote:

 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


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.

 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