Archives of the TeradataForum

Message Posted: Sun, 22 Jan 2006 @ 23:07:30 GMT

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

A performance footnote on my earlier post: when applying a 1-row constants/limits table to another table with CROSS JOIN, it is very important to have a unique primary index on the 1-row table and to supply the index value as a literal in the where clause. The UPI reference via literal sends the optimizer a clear message on row count.

For example:

     from
      dbc.dbase as ddb
       CROSS JOIN
      dbc.syssecdefaults as ssd
     where
      ssd.primeindex = 1

Yields a step like:

     2) Next, we do a single-AMP RETRIEVE step from dbc.ssd by way of the
        unique primary index "dbc.ssd.PrimeIndex = 1" with no residual
        conditions into Spool 2 (all_amps), which is duplicated on all
        AMPs.  The size of Spool 2 is estimated with high confidence to be
        6 rows.  The estimated time for this step is 0.03 seconds.

If you forget the UPI reference like I did in the original post:

     from
      dbc.dbase as ddb
       CROSS JOIN
      dbc.syssecdefaults as ssd

You get this kind of sub-optimal step:

     3) We do an all-AMPs RETRIEVE step from dbc.ssd by way of an all-rows
        scan with no residual conditions into Spool 2 (all_amps), which is
        duplicated on all AMPs.  The size of Spool 2 is estimated with low
        confidence to be 36 rows.  The estimated time for this step is
        0.03 seconds.

Collecting statistics on the 1-row table will offset this to some extent, but my experience says that the optimizer loses confidence in the results more quickly than if the UPI is specified. In simple queries you'll never notice the difference, but in complex queries the row count uncertainty can push you into some really sub-optimal plans.

/dave hough

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