Recently, I had a table which was able to substantially benefit from compression. I determined that I had a problem by setting derby.logQueryPlan to TRUE, running a "select *" query against the table, and then viewing the page and row count statistics that were emitted in the query plan, where I saw that the optimizer was expecting to process many more pages than I thought needed to be in the table.
Compressing the table made my problem go away, and the optimizer returned to choosing my desired query plans. But I am left with the desire for an easier way to figure out whether my table needs compression or not. I tried reading the manual, for example I found http://db.apache.org/derby/docs/10.1/adminguide/cadminspace21579.html but that page does not offer any clear way to tell whether a table needs to be compressed or not. What is the preferred way to decide whether a table has unused space? thanks, bryan
