Re: how to test a string for a valid date in sql

From: Jaap W. van Dijk <>
Date: Mon, 14 Dec 2009 18:02:48 GMT
Message-ID: <>

On Sun, 13 Dec 2009 15:39:46 +0100, Robert Klemme <> wrote:

>On 13.12.2009 14:21, Gerard H. Pille wrote:
>> 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).
>> Context switches are very measurable, as is the number of times a stored
>> procedure or function is called.
>Btw I did not ask whether they are measurable as such but whether OP had
>_verified_ that these context switches are actually an issue in his
>situation. While a context switch does have a price it may turn out
>that other approaches do have higher prices in particular situations.
>The only way to _know_ it is usually to test and measure.
>> A table lookup will never beat a
>> couple of lines of sql "logic".
>When it comes to using words like "never" and "always" I am very
>cautious. Next someone might turn up with an example where a table
>lookup beats what you call "SQL logic" by an order of magnitude.
> robert
>remember.guy do |as, often| as.you_can - without end

I replaced the function with the same logic in a case statement. Tje function and therefore the case statement are called more than once for each record. I processed some 9 million records. Due to the change the runtime dropped with a factor of 2.8!

Jaap. Received on Mon Dec 14 2009 - 12:02:48 CST

Original text of this message