Re: how to test a string for a valid date in sql
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 13 Dec 2009 14:21:50 +0100
Message-ID: <4b24ea6e$0$2853$ba620e4c_at_news.skynet.be>
Robert Klemme wrote:
> On 11.12.2009 11:01, Jaap W. van Dijk wrote:
>> I'm loading millions of records in a transformation step in a
>> datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the
>> transformation is converting a date-string in a VARCHAR2 column into a
>> date in a DATE column, something like
>>
>> CASE WHEN date-string is valid THEN TO_DATE(date-string) ELSE NULL
>> END.
>>
>> Is it possible to test a string for a valid date in Oracle 9i/10g/11g
>> without resorting to PL/SQL? If I use a homemade PL/SQL function, this
>> is performed once for each record, which increases the load time
>> considerably due to the millions of SQL - PL/SQL context switches back
>> and forth.
>
> Did you actually measure that or is that an assumption? Not sure whether
> the regexp approach does suffer from the very same problem.
>
> Here's another sketch of an idea for the case that you actually have
> much less dates than rows: load all the dates with DISTINCT into a table
> with two columns: original string, converted date. Do an UPDATE using
> the custom PL/SQL function and finally do the import with a join against
> that table. Maybe you can even pull that off with a WITH clause. Of
> course, there are many variables in there so the approach might not be
> feasible at all in your case (e.g. index on the original text column
> helps).
>
> Kind regards
>
> robert
>
Date: Sun, 13 Dec 2009 14:21:50 +0100
Message-ID: <4b24ea6e$0$2853$ba620e4c_at_news.skynet.be>
Robert Klemme wrote:
> On 11.12.2009 11:01, Jaap W. van Dijk wrote:
>> I'm loading millions of records in a transformation step in a
>> datawarehouse with an INSERT INTO ... SELECT ... FROM ... Part of the
>> transformation is converting a date-string in a VARCHAR2 column into a
>> date in a DATE column, something like
>>
>> CASE WHEN date-string is valid THEN TO_DATE(date-string) ELSE NULL
>> END.
>>
>> Is it possible to test a string for a valid date in Oracle 9i/10g/11g
>> without resorting to PL/SQL? If I use a homemade PL/SQL function, this
>> is performed once for each record, which increases the load time
>> considerably due to the millions of SQL - PL/SQL context switches back
>> and forth.
>
> Did you actually measure that or is that an assumption? Not sure whether
> the regexp approach does suffer from the very same problem.
>
> Here's another sketch of an idea for the case that you actually have
> much less dates than rows: load all the dates with DISTINCT into a table
> with two columns: original string, converted date. Do an UPDATE using
> the custom PL/SQL function and finally do the import with a join against
> that table. Maybe you can even pull that off with a WITH clause. Of
> course, there are many variables in there so the approach might not be
> feasible at all in your case (e.g. index on the original text column
> helps).
>
> Kind regards
>
> robert
>
Context switches are very measurable, as is the number of times a stored procedure or function is called. A table lookup will never beat a couple of lines of sql "logic". Received on Sun Dec 13 2009 - 07:21:50 CST