I know that I can't shrink the table after deleting records from a table and I have to recreate the table using a temporary one. This was true for few months
ago, Is there any update or service pack to get over this problem?
because it's not just taking space, it's also slowing down the queries.
Are your Metamorph indexes up to date? LIKE etc. searches that depend on Metamorph indexes will degrade in performance the more inserts/deletes/updates are done to a table, until the indexes are updated.
Re-run the same "create metamorph ..." SQL you ran to make the index in the first place. Do this periodically, after some significant changes to the table have been made. The chkind program will attempt to do this automatically after a certain threshold, but it may be disabled on your system, or you haven't reached its threshold.
yah, I use the create metamorph statment everytime I've a batch of updates, but it takes long time to refresh the index even if there are only few records in the table as the table physical size is big.
Right, the new records' index data must be merged with the existing which can take some time.
Removing slack/free space from the table would probably only have marginal effect on speed. Reorganizing the query might be more useful. What's the full SQL and Metamorph (including terms) query you're doing?
I'm not sure, but I think source1Tamer is having a similar problem I am having. I do updates/inserts/deletes to the data and re-index every 4 hours. Recently, my queries have been getting unusually slow. Upon investigation, I noticed that my tables and indexes were unusually large as far as disk space goes. To test this, I rebuilt the database from scratch and the size was cut from 4.45 GB to 2.09 GB. What causes this unneded growth in the tables and indexes? Is there some sort of command to sort of truncate them so that I don't have to periodically rebuild them?
Deleted/updated blocks are tracked by the SQL engine in the table. They are re-used for new data where possible, but any unused ones are still maintained in the table (this is necessary for the SQL engine). Copying the table to a new one is the only current method for completely compacting a table.
As you update/delete/insert records may be moved around. Free space within the table file is generally reused. Depending on the nature of your operations you can sometimes get excessive fragmentation with a lot of unused blocks in the table. Compacting requires taking the table offline for the duration. Use copydbf to create a compact version of the original. Then drop the indices on the original. Replace the original table file with the compact version. Rebuild all indices on that table.
