Archives of the TeradataForum
Message Posted: Wed, 21 Jun 2006 @ 10:43:24 GMT
Here is the case I met:
I have a 2mm row table left join to a 39 row table on the PI of the small. It works fine, duplicate the small one, when both are permanent tables. But when I tried to change the big table to volatile table, it redistributed the bigger table to do a merge join! In the explaination, I think it knew how big the volatile table is (please see the expected output rows in step4), but seems I am wrong. So my question is: does the optimizer know the size of a volatile table? Below is a part of the explanation.
4) We do an all-AMPs RETRIEVE step from SWENG.chk by way of an all-rows scan with no residual conditions into Spool 2 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The result spool file will not be cached in memory. The size of Spool 2 is estimated with high confidence to be 2,185,747 rows. The estimated time for this step is 33.94 seconds. 5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to cdc_scratch.exch by way of a RowHash match scan with a condition of ( "(cdc_scratch.exch.DAY_OF_RATE_DT >= :MIN_CRE_DT) AND (cdc_scratch.exch.DAY_OF_RATE_DT <= :MAX_CRE_DT)"). Spool 2 and cdc_scratch.exch are left outer joined using a merge join, with a join condition of ("(CURRENCY_ID = cdc_scratch.exch.CURNCY_ID) AND (CREATED_DT = cdc_scratch.exch.DAY_OF_RATE_DT)"). The result goes into Spool 1 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The size of Spool 1 is estimated with index join confidence to be 2,185,747 rows. The estimated time for this step is 0.45 seconds.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|