Archives of the TeradataForum

Message Posted: Fri, 14 Sep 2007 @ 21:45:54 GMT

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

You just modify your SQL ;)

We've recently converted a couple moderately complex applications (back end processing and serving web sites) from Oracle to Teradata. The single most important thing is that you completely understand what your application is supposed to do. Not just "this SQL does this, then this SQL does that," but the what the whole process is supposed to accomplish. Then you can start to figure out the best way to do it in Teradata, which may not at all be the same steps in the same order as Oracle.

The other single most important thing is that you really know how to use Teradata. Not just the differences in SQL, but how the whole approach to physical design and query tuning changes, how stats and compression and partitioning work, all that. I can't tell you how many times we thought we had something done that turned out not to be workable because there was something we'd overlooked or had never even heard of. We invested a lot of time and dollars scoping things out long before we started work, bringing in external trainers, and hiring very good contractors to help us learn and get through the process.

Overall, it's a function of how deeply you've leveraged Oracle's features. If your procs are really just wrappers for a series of SQL statements or take a few inputs and return a record set or a string, it will be closer to just modifying your SQL.

The more you've used P/L SQL features (arrays, nested tables, packages, global variables, cursors, loops, bulk binds/forall, etc.), the harder it will be. That's why you have to grok what you're really after, so you can start thinking about how to put it together without those features.

Oracle is really a strong programming platform with an excellent database built into it. If you're highly dependent on those language features, you might have to consider adding a application tier to hold the programming part and just push basic select/insert/update/delete to TD.

Some issues we contended with were:

1) TD can't return a record set from a stored procedure.

2) No autonomous transactions.

3) Limits on dynamic SQL (owner & creator must be the same) and getting tables, views, stored procs, etc. working with how users and databases are set up here.

4) Converting an application using GUIDs to uniquely identify records in multiple tables to using identity columns. This meant loading existing data to map GUID to identity AND set up multiple tables to generate identities without any overlaps (we staggered the starting number, not the range available to each table).

5) Teradata's relatively weak date handling - we had to move a lot of logic to the calendar table and build our own version with relative dates built in. (Once you're used to it, there are some neat tricks you can do with dividing and multiplying dates to get first/last of month and things like that.)

6) Going to c-based UDFs was a blow.

7) Had to touch many queries just to clean up things like nvl, nvl2, decode, etc.

8) Collapsing cursor loops that opened other cursors or did complex updates into single SQL statements.

9) Managing storage with numeric fields, since TD used a fixed number of bytes instead of only however many you need.

10) Separate date and timestamp datatypes. Blech.

On the plus side, we have a process that took hours on (an overloaded and poorly configured) Oracle and now takes around 20 minutes. That's nice.

Best of luck!

Dave

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