Oracle functions in TD 14

Extensibility

Oracle functions in TD 14

Before 14 the Oracle functions where implemented as UDF's in TD.  Now in TD 14 they are built in. Is it still using UDFs? Or how is it implemented.

My question is more: In most cases there are alternatives in ansi SQl, like decode can be replaced by a case; nvl by a coalesce,.... Does it give the same performance or is it worth migrating these functions anyway and is it more offered to speed up migrations from Oracle to TD.

Thanks

Wim

6 REPLIES
Junior Contributor

Re: Oracle functions in TD 14

Hi Wim,

those new functions in TD14 are called "Embedded Services System Functions" or "FastPath UDFs", they're comparable to built-in functions, only Teradate R&D can add it to the database.

IMHO some functions like oReplace, oTranslate, to_date, months_between, etc. add new  functionality.

Others like decode, nvl, etc. are mainly for easier porting from Oracle, so of course you should not use them in any new code.

Dieter

Junior Supporter

Re: Oracle functions in TD 14

Wim:

Talking about performance, we did some test after migrating from TD12 to TD14. We noticed that the new embedded functions were about 5 times faster than the old UDF's.

A simple test case here (in spanish, as usual):

http://carlosal.wordpress.com/2013/02/27/rendimiento-de-las-nuevas-funciones-nativas-en-teradata-14/

HTH.

Cheers.

Carlos.

Re: Oracle functions in TD 14

Carlos and Dieter,

Thanks a lot for the information. I had some worries because I had the impressions that in the past those UDF were not always the most performant solution. We will avoid using the typical Oracle functions like nvl but if it is impossible than we still can port it next year to TD without any concerns about speed.

best regards,

Wim

Junior Supporter

Re: Oracle functions in TD 14

Hi Dieter,

With reference to your post above, do you mean we should not be using functions like deode,nvl etc as they might not perform good ?

also, i could not understand the difference between functions like oreplace,otranslate and functions like decode ?

--Samir

Junior Contributor

Re: Oracle functions in TD 14

Functions like DECODE or NVL are proprietary/legacy Oracle functions which are easily rewritten using Standard SQL's CASE, there's no reason to write any new code using them.

But there's no efficient way to rewrite oReplace/oTranslate...

Junior Supporter

Re: Oracle functions in TD 14

Got it, thanks !!

--Samir