Archives of the TeradataForum

Message Posted: Wed, 20 Jan 2010 @ 14:53:24 GMT


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

Subj:		Re: Workaround to avoid product join

From:		Victor Sokovin

Of course, it necessarily causes a product join. As Table A can have up to 150 million rows, this is not acceptable. Unfortunately, this issue fell on my shoulders and I need to resolve it. I must admit that I am running out of ideas, as the product join somehow makes sense in order to achieve the goal. So if someone can help, I would appreciate very much.

You've hit a few tough issues with this one!

1. Inequality Join = join conditions involve only operations like <, <=, >, >=. Always been a source of performance problems.

2. Scalar subqueries = subqueries placed within SELECTed columns of the parent query.

For example,

www.teradataforum.com/teradata/20090902_161807.htm ["warfarin thread"]

www.teradataforum.com/teradata/20030317_231946.htm

Scalar subqueries were expected to be implemented seven years ago? Really? The first sign of them can only be found in Release 13!

I mention the scalar subqueries because they would allow the use of the reference table to introduce the Minute IDs immediately in the SELECT without actually joining two tables. But this is only if scalar queries are not translated back to ordinary (outer) joins. I suspect (but did not try it) R13 implementation does the latter.

Maybe there is an OLAP solution with some artificial tricks. Not sure it is worth spending time on it, though. If you just want something simple that only scans the main table once and does all calculations in a single pass (the most economical scenario) consider extending the CASE to include all the reference values. I admit there are a lot of them (but the fixed number of them, which is a rare bonus!) and the query won't look pretty but it will work.

Well, you may hit the limit on the number of clauses in the CASE but that should be easy to fix by splitting into multiple CASEs or something.

Now, I'll run and hide from any accusations of hard-coding reference values, not following the Great Metadata Management Imperatives or urban myths etc.

Victor


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

Attachments

Library

Quick Reference