I'm using an embedded java database to hold bus schedules. When a new schedule is made available I automatically load the new schedule into database tables and then delete old schedules from the database. This happens automatically without user intervention.
I have noticed that the database slows over time. I have a script which drops and rebuilds indexes (there are 10 of them) and after running this performance improves significantly. Currently I manually stop the system, run the script and then restart the system.
Question is is the a way of rebuilding all 10 indexes from within the java code ? If there was, I would do this immediately after deleting old schedules
SYSCS_UTIL.SYSCS_COMPRESS_TABLE will rebuild indexes. You can call this function on important tables during off-hours. It should not need to be done frequently. Docs are here.
However, before doing this I would make sure that the slow queries aren't resulting in full-table scans. i.e. check to make sure you're not missing an index.