Re: External tables and compressed files

No way I know of to read compressed data from a flat file via external tables (yet) - but have you considered looking at the data segment compression options available in Oracle9i R2 - i.e leave the history data in the table, but let Oracle compress it. This was designed for exactly the scenario you where talking about, and some people are reporting a 3-5 reduction in storage space requirements.

See
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/physical .htm

> 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 - 21:45:24 CST