Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 06 Sep 2002 @ 14:04:45 GMT


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


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.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016