Archives of the TeradataForum

Message Posted: Fri, 15 Dec 2006 @ 17:15:20 GMT

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

Hello :

I have noticed an anomaly when I run a simple select against a partitioned table. I get a different query plan and very different exec times depending on whether I use a literal or a calculated expression to evaluate a partitioned column. The table StoreItemDay is partitioned by Book_date, and when I use the query with a literal as shown below, it runs as expected, using the partitioning, and returns in a second :

     sel * from prodroundysdb.storeitemday where book_date = '2006-12-10'

However, when I use a calculated expression with a date function, I get a full table scan with runtimes exceeding 10 minutes. Here is the query plan, and steps 2 and 3 have each over 10 minutes, with 150 MM i/0s. It even reads a table, [TBId=0x0003 0xAA2D], that is not even part of the from clause.

     sel * from prodroundysdb.storeitemday where book_date = date -1

We lock prodroundysdb.[TBId=0x0003 0xAA2D] for read and we lock prodroundysdb.storeitemday for read.

We do an All-AMPs RETRIEVE step from prodroundysdb.[TBId=0x0003 0xAA2D] by way of an all-rows scan into Spool 18180, which is redistributed by hash code to all AMPs.

We do an All-AMPs JOIN step from Spool 18180 (Last Use) by way of an all-rows scan, which is joined to table storeitemday. Spool 18180 and table storeitemday are joined using a merge join . The result goes into Spool 18179, which is built locally on the AMPs.

We send out an END TRANSACTION step to all AMPs involved in processing the request.

Thanks for your help!

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