Friday, July 9, 2010

Teradata optimizer anomaly to beware of

I’ve been working with Teradata for more than four years and I just “discovered” this anomaly which was a problem in my SQL scripts that Teradata told me would return 2 quadrillion rows and take 6500 hours to complete.

If you include a column in the SELECT statement that does not come from a database.table/view in the FROM statement, Teradata merrily does a Cartesian join on the database.table/view.

Try this:

SELECT COUNT( <database>.<table or view>.<column>)

Teradata doesn’t seem to care that there is a missing FROM clause and reads every row from <database>.<table or view>.

If the <database>.<table or view>.<column> is part of a larger SQL that has joins to one or more tables and this particular table is not in the FROM clause, Teradata makes a Cartesian join to every other table in the SQL for every row in the "missing" <database>.<table or view>.

My problem was the result of a copy and paste operation from a query that worked, but I didn't catch the change needed for the new query.

No comments:

Post a Comment