since teradata 14, count(*) without where condition was computed as cylinder level
Explain
sel count(*) from SV_RY_CAMP_KIN
1) First, we lock a distinct DEV_TRVL_RAW."pseudo table" for read on
a RowHash to prevent global deadlock for
DEV_TRVL_RAW.SV_RY_CAMP_KIN.
2) Next, we lock DEV_TRVL_RAW.SV_RY_CAMP_KIN for read.
3) We do an all-AMPs SUM step to aggregate from
DEV_TRVL_RAW.SV_RY_CAMP_KIN by way of
a cylinder index scan with
no residual conditions. Aggregate Intermediate Results are
computed globally, then placed in Spool 3. The size of Spool 3 is
estimated with high confidence to be 1 row (23 bytes). The
estimated time for this step is 2.83 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 (25 bytes). The estimated time for this
step is 0.01 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 2.83 seconds.
Explain sel
count(*) from SV_RY_CAMP_KIN where HIZUKE >= cast(date'2014-05-01' as timestamp) ;
1) First, we lock a distinct DEV_TRVL_RAW."pseudo table" for read on
a RowHash to prevent global deadlock for
DEV_TRVL_RAW.SV_RY_CAMP_KIN.
2) Next, we lock DEV_TRVL_RAW.SV_RY_CAMP_KIN for read.
3) We do an all-AMPs SUM step to aggregate from
DEV_TRVL_RAW.SV_RY_CAMP_KIN by way of an
all-rows scan with a
condition of ("DEV_TRVL_RAW.SV_RY_CAMP_KIN.HIZUKE >= TIMESTAMP
'2014-05-01 00:00:00.000000'"). Aggregate Intermediate Results
are computed globally, then placed in Spool 3. The size of Spool
3 is estimated with high confidence to be 1 row (23 bytes). The
estimated time for this step is 16.23 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 (25 bytes). The estimated time for this
step is 0.01 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 16.24 seconds.
in older version, it was done by all row scan
Explain sel
count(*) from SV_RY_CAMP_KIN
1) First, we lock a distinct DWHRUN."pseudo table" for read on a
RowHash to prevent global deadlock for DWHRUN.SV_RY_CAMP_KIN.
2) Next, we lock DWHRUN.SV_RY_CAMP_KIN for read.
3) We do an all-AMPs SUM step to aggregate from DWHRUN.SV_RY_CAMP_KIN
by way of an
all-rows scan 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 (23 bytes). The estimated time for
this step is 3 minutes and 57 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 (25 bytes). The estimated time for this
step is 0.00 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 3 minutes and 57 seconds.
