Archives of the TeradataForum

Message Posted: Wed, 11 Dec 2002 @ 14:06:54 GMT

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

If you have a fairly non-selective secondary index the optimizer will use that to count the table rows rather than doing a full table scan. This can be a lot faster (depending on how non-selective the index is). On our system we have a table with 183,236,440 rows. One of our secondary indexes has approximately 25,296 distinct values (this is the least selective index on the table). The optimizer uses this index when doing a simple count(*). The query runs in 6 seconds rather than minutes. Here is the explain:

  3) We do a SUM step to aggregate from ffs.claimbase by way of a
     traversal of index # 24 without accessing the base table with no
     residual conditions.  Aggregate Intermediate Results are computed
     globally, then placed in Spool 3.  The input table will not be
     cached in memory, but it is eligible for synchronized scanning.
     The size of Spool 3 is estimated with high confidence to be 1 row.
  4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
     an all-rows scan into Spool 1, which is built locally on the AMPs.
     The size of Spool 1 is estimated with high confidence to be 1 row.
     The estimated time for this step is 0.17 seconds.

I'm not suggesting that you create a secondary index for this purpose only, but it is a nice side benefit that I thought should be pointed out.

Good luck.

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