Archives of the TeradataForum

Message Posted: Sat, 08 Feb 2002 @ 20:58:34 GMT

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

Another variation on this theme can be triggered by the data demographics. I have a set of production queries to build cubes. They run every night. The queries build the cubes up using two date ranges (transaction date and event date). Each query has a where clause with:

WHERE EventDate BETWEEN :EDate1 and :EDate2 AND TransactionDate IN (:TDATE3,:TDate4,...)

The Edate range is always one month and the TDate range is usually only one day. The EDate is in a medium table (~2 million rows) and the TDate is in a very large table that is joined to the medium table via another very large table with the same PI as the large table.

If the number of EDate and/or Tdate instances is over a threshold the optimizer will select the Edate qualified set from the medium table first and copy them to all amps and do a nested join via a secondary index on the second large table. This table has the same PI as the big table - this is a good plan.

However if instances of the Edate and/or Tdate range is under some threshold, the optimizer does a product join of the large table and the medium table. This is a very bad plan that runs for a very long time and fails on a spool error. The optimizer will do this even though the explain says it has no confidence in its estimates of the two spool sizes.

So I play games with the SQL to get the optimizer to do the right thing - e.g. change the "IN (:Tdate..." to a BETWEEN and cast the tdate from a date to an integer.

I could use my time more productively if Teradata had a facility to bind a plan and make it static. Good developers and DBAs usually know their data demographics better than the optimizer.

Cheers,

Bob Doss

Spirit Lake Consulting Pty. ltd.
Address:
Roseville, NSW
2069 Australia

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