This document compares some of the features of Oracle SQL with Teradata. It is meant as a learning tool for its author and doesn't pretend to be either comprehensive in any way, or even scientific. The SQL features selected for comparison reflect author's own preferences and bias.
In recent years I spent a significant amount of time working with Oracle. My interest has always been SQL and PL/SQL. Recently I started a new contract where I am using Teradata. It seems that Teradata can compete with Oracle in its SQL capabilities. As a part of my learning experience I am writing this article to compare certain features in Oracle and Teradata that I find interesting. Because I am using free versions of Oracle and Teradata which are not tuned for performance, I am not going to compare SQL performance. Since both vendors claim full SQL capabilities in their free versions I assume usage of free versions of software doesn't affect the features.
Oracle is on version 11i right now,
and Teradata is on version 13 which is also referred as V2R6.
In my comparison I am using a free version of Oracle 10i (Oracle Database Express Edition) and a free version of Teradata 13 (Teradata Express).
It seems the only books available on Teradata are published by Tom Coffings and are enormously expensive (around $200. Even Amazon or Ebay sells them for about $100). Basically all the books are at this site: http://www.coffingdw.com/book_purchase.asp and the cost of each of them is $199.99. And the interesting fact is that all these books are circa 2004.
As for Oracle – there are hundreds of good books and the prices are reasonable ($25-$70).
Teradata seems to have a very good set of various Certification tracks (http://www.teradata.com/t/WorkArea/DownloadAsset.aspx?id=6232) which may satisfy any flavor an IT professional wants to take (though it seems they all are pertinent to V2R5 [Teradata 12]).
Oracle appears to have improved its Certification Program. It used to be that there were only two tracks – OCP Developer and OCP DBA. Now it seems there are more Certification Tracks (http://www.orafaq.com/wiki/Certification_FAQ and http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=141), though there is still no SQL type certification.
Teradata allows use of column aliases in WHERE conditions:
and this statement yields correct results:
Whereas in Oracle you cannot use aliases in where condition (unless the aliased statement is inside a bigger one):
This produces an error message:
Interestingly, in Teradata you can use column aliases even in expressions:
Results
Note that acct_nbr2 was used even before it was defined.
Teradata allows one to use table aliases
which works correctly:
however, mixing aliases and real table names causes a cartesian product
returns way too many rows:
In Oracle:
mixing aliases and real table names gives you an error message
Another interesting syntax difference between Teradata and Oracle is that for a table alias in Oracle you cannot use the word 'as'
results in error:
In Oracle you can use column numbers in order by clause
returns results
but not in group by expressions:
yields an error:
Teradata, on the other hand, has no problem with using column numbers in group by expressions:
results:
Sometimes when you are working with SQL you may need an extra row or two.
Oracle has its infamous DUAL table that could be used for that:
produces
Teradata exhibits similar behavior without table name:
With the results:
However there is an interesting twist in this:
If you need more than one row, Oracle does this easily:
results:
while Teradata for some reason stumbles:
Statement
fails:
So, how do you create a dummy result set in Teradata without having a table to select from?
It looks like the best solution in Teradata is
which produces
Basically one has to use another table that is accessible to everyone.
There is one more peculiarity with Teradata:
produces
Note that the name 'Ovechkin' was truncated to Ovech. Since Teradata doesn't know the length of the last_name column it defaults it to the length of this column in the first row. So how do you handle this?
Here is the statement:
Notice the cast statement.
While trying to construct a SQL statement I frequently have to select either all or some columns from the result of a SQL.
In Oracle, for instance it would look like this:
which will result in
However, a similar SQL in Teradata:
fails with a very cryptic message:
Turns out the correct way of writing this SQL in Teradata is
which will return
Teradata expects a table alias whereas Oracle doesn't.
Often you may need to experiment with a long SQL as if it was a view before (or instead of) making it a view.
You can do something like this in Oracle:
results:
Obviously, in reality the usage of tab_a is a lot more complicated than in my example ('select * from tab_a'), for instance, you may need to use it in a complex join. And tab_a SQL may obviously be a lot more complex than a simple union of four statements.
Teradata uses a slightly different syntax:
results in
Notice that Teradata requires you to specify the columns of the “with” table, while Oracle doesn’t.
If you omit the list of columns (as I did initially, coming from Oracle) you will get this error message
which you can appreciate only after you figured it out.
It turns out that multiple WITH are not supported in Teradata:
results in
But Oracle supports multiple WITH:
returns
Sometimes developers have to deal with selfpointing tables: like an employee table where there is a manager-Id field pointing to another row in the same table. This is sometimes called a 'build-of-material' table.
Oracle has a special clause CONNECT BY designed to handle these kind of tables:
Let's create a selfpointing table:
results:
Build a hierarchy the way Oracle does:
result:
As you notice there is Oracle 'pseudo' column LEVEL and one of oracle functions specifically designed for CONNECT BY statements SYS_CONNECT_BY_PATH. Also to get started I had to supply the top executive (START WITH employee_id=3)
As you can see while the hierarchy is obvious, the siblings are not sorted. In order to get siblings sorted you need to use a special order clause:
result:
Teradata offers a completely different way of approaching this type of selfjoins:
Here is the Teradata table to match Oracle:
results:
In order to build a hierarchy Teradata is using so called “recursive” SQL . The idea here is that you have to build a “seed” row (that’s the one Oracle gets in a START WITH clause) and then you specify how to get the rest of the rows.
A statement
gives us a seed row
Now the recursive part:
results:
You may notice that because of a recursive nature of Teradata statement the physical sequence of records doesn't reflect the hierarchy: Ovechkin works for Black who works for Smith who works for Brown, but you would have a hard time trying to figure it by looking at the results.
Since Oracle apparently walks the hierarchy as it sees it the records are returned in a more natural sequence.
To build the path you could try something like:
But you would get this:
This is because of the same problem we already discussed: Teradata doesn’t know the length of the path column and takes it from the very first row (which is one character). So we will have to cast it into VARCHAR(100):
results :
Now, how do we build an indented “hierarchical” name? In Oracle we used a function RPAD that allowed is to do the padding at the right side. Teradata doesn't have this function, so we have to use a fairly clumsy superposition of functions:
results
Pay attention to use of level1 in expression 5*level1 as we discussed before you can use the alias name (which level1 is) even if it is defined in the same statement.
Now to completely match Oracle we need to make sure rows are displayed reflecting the real heararchy.
If we sort by path column we will get the desired result. So the final statement to traverse the ‘build-of-material’ table is this:
result:
I am not a "statistical analyst" and use Max and Min functions out of all the possible analytical functions. I am not that much interested in AVG or Deviation type of functionality. However, many times I needed a functionality where I need to "look ahead" and compare a row value with the same column a few rows ahead.
Here is how it is done in Oracle:
result:
Teradata's implementation of Analytical processing seems to be centered around group functions - MAX, MIN, etc., so trying to just display a value of a column three rows ahead requires "faking" the max:
result:
When using analytical functions how do you limit the result set? Let's say you want to display only the first two table names for each database. "WHERE" clause works on detail level. "HAVING" works on a "GROUP BY" level. What about analitical functions?
Teradata offers a new SQL clause "qualify"
result:
Oracle doesn't have the Qualify clause altogether. However, Oracle's ability to do nested query comes to rescue:
result:
To select first N rows Teradata uses your typical TOP N syntax:
result:
though one can get "fancy" and use analytic fuctions and qualify clause:
result:
Oracle, on the other hand doesn't have a TOP N syntax, and uses somewhat different approach:
result:
Oracle uses a pseudo column rownum which is used in the inner select.
TBD
TBD
TBD
Teradata has a few interesting features when creating tables. Take a look
We have format and default clauses specified in this table definition. DEFAULT is a standard clause and does what you would expect. The FORMAT only works when one uses Teradata BTEQ tool as opposed to a Teradata SQL Assistant.
returns this:
In Teradata SQL Assistant:
In Teradata BTEQWin:
The interesting part, though, is that one can get format and default data using a simple SQL statement:
and see the results:
In Oracle there is no concept of a format stored with the table definition, but you can get at least a default value like this:
results:
There is also an option in Teradata to specify column titles:
results:
Interestingly, this feature makes it harder to find out the column names in case you want to select only a few columns.
In Oracle, however, a concept of column titles exists only in SqlPlus environment.
Oracle has a pretty comprehensive set of functions which could be used in SQL statements. For instance, here is a list of string function Oracle supports:
And here is a list of character functions Teradata offers:
The same situation seems to be with other types of functions.
TBD
TBD
TBD
TBD
TBD
TBD
This is an interesting feature that Teradata has but Oracle doesn’t (to the best of my knowledge).
When we learned about relational modeling and relational databases we were told that their implementation is based on a mathematical Set theory. Usually after that the only connection with Set theory you would ever see would be usage of words like Union, Intersect, Minus. That’s it. A set in Set theory is a collection of distinct objects (as opposed to a multiset, which may contain duplicates).
Those who worked with Oracle knows that one can create a table without keys and store many rows, which are in fact duplicates:
with a result
What if you want a table with no duplicates (as they taught in Set theory)?
One would think it should be easy - just add a unique index and voila!
The problem with this approach is that any attempt to insert duplicate rows in one statement fails:
So if you have some query results with possible duplicate rows that you want to insert into your table eliminating duplicates you would have to use DISTINCT first. That doesn't solve the problem completely either because while you took care of duplicates in your input set, what if the new rows collide with the ones already in the table?
The solution in Oracle would be either a significantly complicated SQL or a PL/SQL code that takes care of duplicates.
Here is how it is done in Teradata (for the illustration we are also creating a MULTISET table to show the difference):
Notice: the only difference here is a key word SET as opposed to MULTISET. Now let's try to insert a few duplicate rows:
Here is a message from SQL Assistant:
Notice: We tried to load 4 rows, the message says “1 rows processed”
Let's see what we have in the table:
result:
So, the duplicates disappeared silently. Now try to insert a row that conflicts with a row already in the table:
The result is
So again, the duplicate row disappeared silently without generating an error.
What happens if we do the same thing with the MULTISET table with the UNIQUE constraint?
the result is what we would expect.
The question, of course, is was it all worth doing? I searched on the Net how this feature is used and it seems it causes a lot of confusion, whereas the only implementation mentioned on the Net is to get rid of full duplicates.
Also silent disappearance doesn’t make SET table implementation any more mathematical, but it is certainly an interesting and unusual feature.