Re: External tables and compressed files

I haven't tried this yet, but since you are running Unix, you might try using a unix pipe in the create table external command. Then uncompress the file into the pipe before starting the SELECT that reads it and pumps it into a proper table.

(BTW - In your case, I might ignore the whole external table bit. Since your requirement is an ad hoc reload, I'd probably try this with SQL*Ldr and direct path loads).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





SA wrote in message
<8feb930a.0301011726.6b0776fd_at_posting.google.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