Re: data cleansing: externally or internally?
From: joel garry <joel-garry_at_home.com>
Date: Fri, 4 Nov 2011 17:12:04 -0700 (PDT)
Message-ID: <155e37ce-71ae-443b-b69b-2b23b29b8082_at_e5g2000prf.googlegroups.com>
On Nov 3, 11:51 pm, geos <g..._at_nowhere.invalid> wrote:
> there is a big text file with dirty data. a company wants it to be
> clean. there are some known patterns expressed as like or regexp. I
> first thought about two approaches:
> 1) do this on the system level
> 2) or in a database
> for the latter case it looks to me that I could use external tables or
> load data into temporary table and then do the cleaning.
>
> I am looking for pros and cons of each variant. my intuition tells me
> that loading into temporary table would give the most flexibility but
> also take additional space. I am not sure about the other methods. I
> would appreciate your opinion about what I should pay attention to when
> choosing the other methods. how are they restricted in terms of
> performance, flexibility and capabilities (eg. multitable loading)? I am
> also interested in good practices and your experience in similar cases
> you can share.
>
> thank you,
> geos
>
> --
> NOTE: Follow Up set to comp.databases.oracle.misc
Date: Fri, 4 Nov 2011 17:12:04 -0700 (PDT)
Message-ID: <155e37ce-71ae-443b-b69b-2b23b29b8082_at_e5g2000prf.googlegroups.com>
On Nov 3, 11:51 pm, geos <g..._at_nowhere.invalid> wrote:
> there is a big text file with dirty data. a company wants it to be
> clean. there are some known patterns expressed as like or regexp. I
> first thought about two approaches:
> 1) do this on the system level
> 2) or in a database
> for the latter case it looks to me that I could use external tables or
> load data into temporary table and then do the cleaning.
>
> I am looking for pros and cons of each variant. my intuition tells me
> that loading into temporary table would give the most flexibility but
> also take additional space. I am not sure about the other methods. I
> would appreciate your opinion about what I should pay attention to when
> choosing the other methods. how are they restricted in terms of
> performance, flexibility and capabilities (eg. multitable loading)? I am
> also interested in good practices and your experience in similar cases
> you can share.
>
> thank you,
> geos
>
> --
> NOTE: Follow Up set to comp.databases.oracle.misc
Out of database. I've done this so many times, whenever the database is the limiting factor I take it out of the db, clean, and put it back in. Two biggest factors are redo generation and memory limitations, the latter of which are handled better by unix and pipelining. Plus, it's easier to split transforms into little pieces and pipe them together.
jg
-- _at_home.com is bogus. http://www.computerworld.com/s/article/9221459/Oracle_s_best_of_breed_strategy_as_described_by_president_Mark_Hurd?taxonomyId=154Received on Fri Nov 04 2011 - 19:12:04 CDT