 |
 |
Archives of the TeradataForum
Message Posted: Fri, 30 Nov 2001 @ 21:04:58 GMT
| Subj: | | Re: Derived Tables and 3710 Error |
| |
| From: | | Dieter N�th |
Inline
| | ----- Original Message -----
From: "Claybourne Barrineau"
Sent: Friday, November 30, 2001 1:58 PM
Subject: Derived Tables | |
| | It is currently possible to join more than 64 tables in a single query if you use a series of derived tables. | |
Are you sure about that? I always thought, 64 base tables is the maximum number, no matter if they're from a view or a derived table. I
just tried an explain on the demo version using a 70 table self join of dbc.next ;-) Without DTs: Error, with DTs: After 5 minutes of 100%
CPU for the Parser task i tried to cancel, but QueryMan and BTEQ hung :-(
| | 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... | |
So they'll treat Derived Tables they way it should be: a DT is just a special kind of a view and views are resolved before optimization.
Most of the code today is written by tools like MicroStrategy Agent and this tools is using a lot derived tables. So if DTs are resolved,
the optimizer is able to really optimize, instead of using the stupid way Agent is proposing.
BTW, i heard that rumour, too. And there'll probably be a kind of on/off switch, because DTs are the only way to force the optimizer to
do joins in a certain order.
| | 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. | |
The new default for MaxParseTreeSegs in R4.1 is 1000 with a maximum of 2000 ;-))
| | 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? | |
No optimizer is capable of considering ALL combinations: The possible combinations for a x table join are worse than x! The ! is not an
exclamation mark, it's the maths symbol for faculty (don't know if that's the english word).
I like the example using grains of sand in the manuals SQL Ref. 2 (R4.1) Chapter 2: Optimizer Join Plans:
2.0*10^20 possible join orders for a 16 table join --> All the grains of all the beaches and all the desserts in the world
1.2*10^124 for 64 tables --> All the grains required to fill the known universe
Dieter
| |
