Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!sjc70.webusenet.com!news.webusenet.com!wn11feed!worldnet.att.net!204.127.198.203!attbi_feed3!attbi_feed4!attbi.com!sccrnsc01.POSTED!not-for-mail User-Agent: Microsoft-Outlook-Express-Macintosh-Edition/5.02.2022 Subject: Re: External tables and compressed files From: Mark Townsend Newsgroups: comp.databases.oracle.server Message-ID: References: <8feb930a.0301011726.6b0776fd@posting.google.com> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit Lines: 40 NNTP-Posting-Host: 12.236.73.7 X-Complaints-To: abuse@attbi.com X-Trace: sccrnsc01 1041479124 12.236.73.7 (Thu, 02 Jan 2003 03:45:24 GMT) NNTP-Posting-Date: Thu, 02 Jan 2003 03:45:24 GMT Organization: AT&T Broadband Date: Thu, 02 Jan 2003 03:45:24 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:170961 X-Received-Date: Wed, 01 Jan 2003 21:26:54 MST (news.easynews.com) 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