Archives of the TeradataForum

Message Posted: Mon, 25 Jun 2012 @ 19:42:04 GMT

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

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 :

     CALL DBC.SysExecSQL(:sLSqlStatementTx);

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.

David Clough

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