Archives of the TeradataForum
Message Posted: Mon, 25 Jun 2012 @ 19:42:04 GMT
Some weirdness going on here, methinks ...
I've got some dynamically built up Sql, the final code of which is very simple actually :
sLSqlStatementTx = "LOCKING TESTLEGACYXREF_T.BOOKINGXREF FOR ACCESS CREATE MULTISET TABLE TESTSUBSET_W.BKGXREF_20120625,FREESPACE=0 AS ( SEL A1.* FROM TESTLEGACYXREF_T.BOOKINGXREF A1 INNER JOIN TESTSUBSET_W.BKGXREF_SEED_TBL_20120625 X1 ON X1.COL1_TX = BUL_ID_LEG AND X1.COL2_TX = ORD_ID_LEG ) WITH DATA;"
This is executed in the usual way :
Now, ok, there's no stats at all on my Work Table (BKGXREF_SEED_TBL) and, up until our recent upgrade to V13.1, the Optimizer had no trouble in running the statement.
Now, however, running that statement through the Stored Procedure gives a PRODUCT JOIN (as I see it through ViewPoint), and what 'it' thinks has got 144 rows has actually got 383,655 rows, hence why it's running like the proverbial dog - as you can imagine.
Thing is, when I do an explain plan on the very same Sql in SqlAssistant, I get two Tables redistributed, then
all-rows scan. Spool 2 and Spool 3 are joined using a HASH JOIN
The result comes back pretty quickly, as it did through the Stored Procedure prior to upgrade of Teradata V13
Sure, I could run Stats on the Work Table before running the Stored Procedure, but it's just a bit of a pain and, perhaps more importantly at present, I'm at a loss as to see why I'm getting two different Explain Plans.
Can anyone offer an explanation ?
Regards and thanks.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|