 |
 |
Archives of the TeradataForum
Message Posted: Tue, 30 Sep 2003 @ 19:52:35 GMT
| Subj: | | Date interpretation change |
| |
| From: | | Rudel Simard |
Hi,
We just upgrated to V2R5.0 from V2R4.1 and having some difference between date interpretation by the system. Take a look at the explain
above and you will see the difference.
Does anybody know if it's a system parameter we have to change or if it's a new way than V2R5 interprete the date.
Rudel Simard
Régie de l'assurance-maladie du Québec
Administration des données
V2R5
explain
select count(*)
from d1.t1
where dat_serv = '01/01/01'
;
*** Help information returned. 20 rows.
*** Total elapsed time was 1 second.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct donne."pseudo table" for read on a RowHash to prevent global deadlock for d1.t1.
| |
| | 2) | Next, we lock d1.t1 for read.
| |
| | 3) | We do an all-AMPs SUM step to aggregate from d1.t1 by way of index # 12 without accessing the base table " d1.t1 .Field_1050 =
DATE '1901-01-01'" 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. The estimated time for this step is 0.17 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 1 row. The estimated time for this step is 0.03 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.
| |
V2R4
explain
select count(*)
from d1.t1
where dat_serv = '01/01/01';
*** Help information returned. 18 rows.
*** Total elapsed time was 1 second.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct donne."pseudo table" for read on a RowHash to prevent global deadlock for d1.t1.
| |
| | 2) | Next, we lock d1.t1 for read.
| |
| | 3) | We do a SUM step to aggregate from d1.t1 by way of index # 12 without accessing the base table " d1.t1 .Field_1050 = DATE '2001-
01-01'" 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.
| |
| | 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.
| |
Rudel Simard
Régie de l'assurance-maladie du Québec
Administration des données
| |
