Archives of the TeradataForum

Message Posted: Mon, 26 Nov 2012 @ 13:46:09 GMT

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

Hi everyone,

Imagine having the table FINA_BORRAR with a primary index on the column fec_inicio_vigencia:

     CREATE MULTISET TABLE FINA_BORRAR ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           id_tienda INTEGER NOT NULL,
           val_acum INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,-4 ,-3 ,-2 ,-1 ),
           fec_inicio_vigencia DATE FORMAT 'YYYYMMDD' NOT NULL,
           fec_fin_vigencia DATE FORMAT 'YYYYMMDD' NOT NULL) PRIMARY INDEX (  fec_inicio_vigencia  );

     COLLECT STATISTICS ON FINA_BORRAR COLUMN (fec_inicio_vigencia);

If I try to access the table through the column with the index as shown, the explain tells me that "unique primary index" is used:

     SELECT * FROM FINA_BORRAR
     WHERE fec_inicio_vigencia = DATE;

     Explain SELECT * FROM DIM_VSE_DESA.FINA_BORRAR WHERE fec_inicio_vigencia = DATE;

       1) First, we do a single-AMP RETRIEVE step from
          DIM_VSE_DESA.FINA_BORRAR by way of the primary index
          "DIM_VSE_DESA.FINA_BORRAR.fec_inicio_vigencia = DATE '2012-11-27'"
          with no residual conditions.  The estimated time for this step is
          0.00 seconds.
       -> The row is sent directly back to the user as the result of
          statement 1.  The total estimated time is 0.00 seconds.

But, If I try to access the table as shown (with the between and also happens with the ">" and "<") , no primary index is used.

     SELECT * FROM DIM_VSE_DESA.FINA_BORRAR
     WHERE fec_inicio_vigencia BETWEEN DATE AND DATE+1;

       1) First, we lock a distinct DIM_VSE_DESA."pseudo table" for read on
          a RowHash to prevent global deadlock for DIM_VSE_DESA.FINA_BORRAR.
       2) Next, we lock DIM_VSE_DESA.FINA_BORRAR for read.
       3) We do an all-AMPs RETRIEVE step from DIM_VSE_DESA.FINA_BORRAR by
          way of an all-rows scan with a condition of (
          "(DIM_VSE_DESA.FINA_BORRAR.fec_inicio_vigencia <= DATE
          '2012-11-27') AND (DIM_VSE_DESA.FINA_BORRAR.fec_inicio_vigencia >=
          DATE '2012-11-26')") into Spool 1 (group_amps), which is built
          locally on the AMPs.  The size of Spool 1 is estimated with no
          confidence to be 1 row (37 bytes).  The estimated time for this
          step is 0.01 seconds.
       4) 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.01 seconds.

Can someone tell me if there is a way for accessing the table in these cases using the index?

Thanks.

FINA RODRIGUEZ POSADA
Analista BI

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