Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!canoe.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: sadenwala@my-deja.com (SA)
Newsgroups: comp.databases.oracle.server
Subject: External tables and compressed files
Date: 1 Jan 2003 17:26:25 -0800
Organization: http://groups.google.com/
Lines: 27
Message-ID: <8feb930a.0301011726.6b0776fd@posting.google.com>
NNTP-Posting-Host: 68.85.45.28
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1041470785 1856 127.0.0.1 (2 Jan 2003 01:26:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 2 Jan 2003 01:26:25 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.server:170955
X-Received-Date: Wed, 01 Jan 2003 18:25:56 MST (news.easynews.com)

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