Archives of the TeradataForum

Message Posted: Mon, 05 Apr 2004 @ 19:20:10 GMT

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

Well, the results are in and the answer is ... (drumroll, please) ...

Yes, as Carrie anticipated, a fallback Join Index is used when an amp goes down and the underlying base table is not fallback.

I tested three combinations of table fallback and aggregate join index fallback:

1) Table fallback - Yes, JI fallback - Yes

2) Table fallback - Yes, JI fallback - No

3) Table fallback - No, JI fallback - Yes

My primary interest was in option 3). The cost of fallback on the smaller aggregate JI is much less than the cost of fallback on the underlying base table. Ninety-five percent of end-user reports can be satisfied through the JI, giving us a cheaper option of maintaining uptime on most user reports through an amp-down condition. Of course, the ETL loading would be delayed until the amp was restored.

All DML operations (INSERT, UPDATE, DELETE, SELECT) on the base table will execute if they impact only healthy amps, ie. as long as the explain plan does not indicate an all-amps step that requires a spool on the down amp. In all three case, I was able to do single-amp primary index inserts, updates and deletes when the target primary index amp was online. These operations would fail, in options 2) & 3), only when the DML statement resolved to the down amp.

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.

In summary, everything worked flawlessly and I might add, transparently. If there was any way to execute the submitted query, Teradata would find a way and execute it. The optimizer did not get hung up on the "possibility" of hitting a down amp or the "unavailability" of certain objects in an amp-down situation, it generated efficient plans (using the best available object(s)) and executed them. Only if the actual down amp was required to participate and could not satisfy the request did Teradata generate the error, and abort the request.

Donald Maxwell

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