Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is SQL plenty good enough for complex data transformation?

Re: Is SQL plenty good enough for complex data transformation?

From: Mark D Powell <>
Date: Fri, 16 Nov 2007 11:21:39 -0800 (PST)
Message-ID: <>

On Nov 15, 5:02 pm, joel garry <> wrote:
> On Nov 15, 12:58 pm, Robert <> wrote:
> > When doing complex data transformation ( data preparation, data
> > manipulation, etc. ) , perhaps when preparing data for statistical
> > analysis, or perhaps in an ETL context, is the SQL programming
> > language plenty good enough?
> > For that type of work, can you be just as productive using SQL syntax,
> > and not using at all the SAS or SPSS programming languages?
> > I think that the pitch of the ELT folks ( ELT vs ETL) is : with the
> > SQL programming language, there is no use for any other data
> > manipulation programming language.
> Depends on whether the transformations you are doing are easily
> supported by the language. Many transformations are difficult to
> describe in relational terms, so there is a built-in tension between
> different subsets of the language, even by one vendor. Oracle has an
> excellent set of language extensions. However, I do some things that
> are not easily supported by them in a performant manner. These things
> tend to be "bad design," or more accurately, "insane requirements,"
> such as the same entities being defined in different manners by
> different users - for example, massive reorganization of the data, but
> some people want to see new data as if it were the old data, and
> others want to rewrite history. But you can't limit what people are
> wanting to see and willing to pay for by language limitations or
> bias, that would be backwards. So, sometimes even ksh, unix sort and
> sqlloader win, even if generally less productive. The productivity of
> the most productive environment can be blown by something just outside
> the problem space it is designed for.
> But I may have a minority opinion. I used to consider Celko's
> puzzlers defacto proof that some commonly desired things are
> unnecessarily hard in SQL.
> jg
> --
> is bogus.

Yes, how suitable SQL is for data transformations depends on the exact nature of the transformations required and the data. Oracle provides a rich set of functions that can be used to transform and concatenate data but some things might be more efficiently handled outside the database.

Which version and edition of Oracle you are working with would also affect what features are available to you for performing the work.

HTH -- Mark D Powell -- Received on Fri Nov 16 2007 - 13:21:39 CST

Original text of this message