Archives of the TeradataForum

Message Posted: Fri, 30 Nov 2001 @ 12:58:15 GMT

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

All,

It is currently possible to join more than 64 tables in a single query if you use a series of derived tables. I hear rumor than Teradata is going to treat derived tables differently in the future (ie., tables inside of a derived query will be considered for joins to tables outside of the derived query as part of the generation of a query execution plan.) This being the case, no one will be able to execute a query which joins more than 64 tables. Not really a problem for me or most others I assume...

However, I do have a piece of production code which joins ~35 tables (made up of about 20 objects including several non-base table views which count as more than 1 table.) In this case, we join ~20 tables in a outer query and ~15 tables in a derived query. If I attempt to re-write this code without the derived query, I run into problems with the amount of 64K tree segments allocated to the MaxParseTreeSegs setting (error 3710 - which we can avoid by increasing the number of trees.)

Wouldn't it be reasonable to assume that when the new Teradata RDBMS begins treating derived queries in the rumored way that the above query will begin to give me the same 3710 error which I receive if I re-write the query joining all 35 tables without a derived query? Also, even if I can avoid the Error 3710 message, wouldn't the average time to build an execution plan for this query dramatically increase because all possible 2-way table join combinations will need to be considered?

Not urgent, just curious.

Thanks,

Claybourne Barrineau

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