 |
 |
Archives of the TeradataForum
Message Posted: Fri, 06 Sep 2002 @ 14:04:45 GMT
| Subj: | | Why all-amp macro? |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Friday, September 06, 2002 09:54 -->
I created a table with an index that should cause the table to go to one amp. So why is my query going all-amp?
CREATE SET TABLE FACDISCREADYT ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
NULL_COL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT NULL COMPRESS ,
NUPI_CONST_F CHAR(1) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL DEFAULT 'F',
FACORD_NO CHAR(12) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
FAC_DESIG_REFORMATTED CHAR(5) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
FAC_TYP CHAR(6) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
FAC_TERMA CHAR(11) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
FAC_TERMZ CHAR(11) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
CREATE_DATE DATE FORMAT 'YYYYMMDD' NOT NULL,
CREATE_TIME INTEGER FORMAT '9(8)' NOT NULL,
NOTIFYERROR CHAR(8) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC,
NOTIFYERRORTEXT VARCHAR(80) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC)
PRIMARY INDEX ( NUPI_CONST_F );
replace macro a_kds026
as
(
SELECT facord_no (CHAR(12)),
fac_desig_reformatted (CHAR(5)),
fac_typ (CHAR(6)),
fac_terma (CHAR(11)),
fac_termz (CHAR(11)),
create_date (CHAR(8)),
create_time (CHAR(8)),
notifyerror (CHAR(8)),
notifyerrortext (CHAR(80))
FROM facdiscready
ORDER BY create_date, create_time;
);
explain exec a_kds026;
*** Help information returned. 11 rows.
*** Total elapsed time was 1 second.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock corpdbtt.FACDISCREADYT for access.
| |
| | 2) | Next, we do an all-AMPs RETRIEVE step from corpdbtt.FACDISCREADYT by way of an all-rows scan with no residual conditions into Spool
1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is
estimated with low confidence to be 3 rows. The estimated time for this step is 0.15 seconds.
| |
| | 3) | 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.15 seconds.
| |
Thanks.
| |
