Archives of the TeradataForum
Message Posted: Sat, 08 Feb 2002 @ 20:58:34 GMT
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.
Spirit Lake Consulting Pty. ltd.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|