Re: External tables and compressed files

Mark and Jonathan,

I did a test on few of our tables, and the compression ratio is not too good. So coming back to what Jonathan suggested, I would like to know in little more detail as to how I would use the 1st alternative. If I start a pipe, when the machine shutsdown for maintainance, the pipe would go away, so will I need to start a pipe every time machine reboots?

Also, Jonathan suggests to avoid external tables completely and use sql loader. Can you please tell me in very short how this can be done, I would really appreciate it.

thanks again for your input.

S. Adenwala

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<av103u$n92$1$8300dec7_at_news.demon.co.uk>...
> 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
Received on Tue Jan 07 2003 - 08:50:05 CST