External tables and compressed files

I am looking for ways to use external tables to work with compressed files on Solaris platform.

Background:
In our datawarehouse, we put the historical data in compressed flat files and drop the historical tables to make room for the new tables. We create and load the historical tables from these flat files on an as needed basis. Because of space limitations, we can allow only 3 months worth of historical data to be made available at any given time.

With 9i, we could make all of the historical data available to our users through external tables, since the flat files are stored on the disk. The only problem is that the flat files are compressed. Leaving them uncompressed is not a possible solution, again due to the space limitations (Estimated 80 GB needed for un-compressed flat files).

I doubt if the external tables have any means of directly reading from compressed files, so I would like to get suggestions as to how I can uncompress a flat file on-demand when a user tries to query an external table. Performance is not a major concern. I could run some cron job at night to re-compress all files that were uncompressed during the day.

Any ideas will be greatly appreciated.

S. Adenwala Received on Wed Jan 01 2003 - 19:26:25 CST