 |
 |
Archives of the TeradataForum
Message Posted: Mon, 05 Apr 2004 @ 19:59:21 GMT
| Subj: | | Re: Aggregate Join Index & Fallback |
| |
| From: | | Victor Sokovin |
Thumb's up, Donald. We keep learning on this forum all the time. Insert drumroll and as many timpani as you wish!
| | In the case of covered SELECTs, the optimzer uses the most efficient "available" object to satisfy the query. For example, in option 2)
with all amps available, the optimizer generated an explain plan using the JI. With an amp down, it generated an explain plan using the underlying
base table. Note that I tested covered SELECTs through LOCK FOR ACCESS views. The optimzer locked only the object it used, and did not attempt to
lock the "unavailable" object. | |
That's the rare case when less disk space used will bring you a better performance in case of covered queries. If you use the expensive
table fallback and no JI fallback (as in option 2), covered queries will no longer be covered when one AMP is down, and TD will have to re-
aggregate. Whereas the potentially much cheaper option 3 will give you the same good performance as with all AMPs online. Well, I call this a
marketing opportunity missed. This fact should be mentioned in the TD docs in bold! I think with the current version only Carrie was able to
predict this test result.
All in all, it looks like you, Donald, have found a nice Easter egg. Many thanks for sharing it with us.
Victor
| |
