Archives of the TeradataForum

Message Posted: Wed, 03 Aug 2005 @ 14:20:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

          >   VARCHAR2() to VARCHAR() CHARACTER SET LATIN
          >   NVARCHAR2() to VARCHAR() CHARACTER SET UNICODE

Besides data types Oracle extensively uses NLS settings. The latter are practically unheard of in Teradata.

VARCHAR2 can mean different things depending on these NLS settings. It could be Latin but it could be Greek, Cyrillic or perhaps even Thai. These languages are not covered by standard Teradata Latin character set, so the VARCHAR2s of this kind might need to be mapped to UNICODE as well. One of the consequences: double storage requirement!

The storage might double for NVARCHAR2 as well if the Oracle NLS was UTF8 and most of the chars stored were 1-byte long (like, for example, in a column with mostly English entries with just occasional Japanese values). Speaking of the UTF8, I am not sure all the chars stored in Oracle are guaranteed to migrate to Teradata. For example, 3-byte chars. For those, the company might need to do a special conversion before loading the data to Teradata. This conversion might need to be implemented in the new ETL processes if there is no way to prevent the data sources from producing them.

Even such things as the length of fields might be worth thinking about, especially with the fixed-length counterparts, i.e., CHAR(N). NVARCHAR2(N) in Oracle may imply the column length in bytes (that's even the default nowadays, I believe) whereas Teradata seems to support only the length in chars. Go figure the optimal length if you don't want to waste too much space and at the same time need to avoid pesky "max column length exceeded" messages.

The list can go on and on. Of course, it is not meant to discourage anybody from the migration but I think it is essential to be aware what the migration actually implies. Because otherwise, it is a guaranteed failure, at least in commercial terms.

Regards,

Victor

	<Prev	Next>		<<First	<Prev	Next>	Last>>