Archives of the TeradataForum
Message Posted: Mon, 26 Nov 2012 @ 13:46:09 GMT
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?
FINA RODRIGUEZ POSADA
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|