 |
 |
Archives of the TeradataForum
Message Posted: Mon, 09 Mar 2009 @ 17:03:24 GMT
| Subj: | | Losing rows with covering index |
| |
| From: | | Bernard, Vincent |
I have written a number of covering indexes to test for performance improvements and am seeing what I can only describe as anomalies.
The optimizer is retrieving data from the index rather than the base table, but the row count is off in more than one instance. Here is an
example:
RowCount - 564,760.00 SELECT itm_id , SUM ( trn_cases_blnc_qty ) AS boh
FROM testtable GROUP BY itm_id ;
RowCount - 564,854.00 SELECT itm_id , SUM ( trn_cases_blnc_qty ) AS
boh FROM basetable GROUP BY itm_id ;
The test table includes this index which is being accessed:
INDEX IDX7_LOTLN_NUSI ALL ( ITM_ID ,TRN_CASES_BLNC_QTY )
ORDER BY HASH (TESTTABLE);
This is the explain plan:
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct dba_workspace. "pseudo table" for read on a RowHash to prevent global deadlock for testtable.
| |
| | 2) | Next, we lock dba_workspace.testtable for read.
| |
| | 3) | We do an all-AMPs SUM step to aggregate from testtable by way of a traversal of index # 28 without accessing the base table with no
residual conditions, and the grouping identifier in field 1026. Aggregate Intermediate Results are computed globally, then placed in Spool 3.
The size of Spool 3 is estimated with high confidence to be 564,760 rows. The estimated time for this step is 0.22 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the
AMPs. The size of Spool 1 is estimated with high confidence to be 564,760 rows. The estimated time for this step is 0.09 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.31 seconds.
| |
Is there any documented reason for the disparity in the row count?
Thank you.
Vincent Bernard
| |
