Archives of the TeradataForum

Message Posted: Tue, 28 Mar 2006 @ 11:25:02 GMT


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

Subj:		Estimated Rows...

From:		Cappelli, Andrea

I wonder why the optimizer return these estimated rows: The table POSIZIONE_GLOBALE_MESE is 113 Gb and count 491,071,727 rows its DDL is :

     CREATE SET TABLE DMKSL526.POSIZIONE_GLOBALE_MESE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           COD_CLIENTE DECIMAL(9,0) TITLE 'cd cliente' NOT NULL,
           COD_RAPPORTO CHAR(21) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd rapporto' NOT NULL,
           COD_UNV_RAPPRO CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd univoco rapporto prodotto' NOT NULL,
           COD_PRODOTTO_COMM CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd prodotto comm' NOT NULL,
           DTA_RIFERIMENTO DATE FORMAT 'YYYY-MM-DD' TITLE 'dt riferimento' NOT NULL,
           FLG_TIPO_PRODOTTO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'tipologia possesso prodotto' NOT NULL,
           COD_BANCA CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd banca' NOT NULL,
           COD_DIVISA CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd divisa',
           COD_FIL_RAP CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd filiale rapporto',
           COD_SEDE_RAP CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'cd sede rapporto',
           FLG_DIPENDENTE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'fl dipendente',
           DTA_ACC_RAP DATE FORMAT 'YYYY-MM-DD' TITLE 'dt apertura rapporto',
           DTA_EST_RAP DATE FORMAT 'YYYY-MM-DD' TITLE 'dt estinzione rapporto',
           IMP_SALDO DECIMAL(17,2) TITLE 'imp saldo',
           IMP_SALDO_MEDIO DECIMAL(17,2) TITLE 'imp saldo medio',
           IMP_UTILIZZATO DECIMAL(17,2) TITLE 'imp utilizzato',
           IMP_ACCORDATO DECIMAL(17,2) TITLE 'imp accordato',
           NUM_OPERAZIONI_D DECIMAL(4,0) TITLE 'nr operazioni dare',
           NUM_OPERAZIONI_A DECIMAL(7,0) TITLE 'nr operazioni avere',
           IMP_OPERAZIONI_D DECIMAL(17,2) TITLE 'imp operazioni dare',
           IMP_OPERAZIONI_A DECIMAL(17,2) TITLE 'imp operazioni avere',
           IMP_RATA DECIMAL(17,2) TITLE 'imp rata',

           TSP_INSERIMENTO TIMESTAMP(6) TITLE 'ts inserimento' DEFAULT CURRENT_TIMESTAMP(6),
           TSP_ULT_MODIFICA TIMESTAMP(6) TITLE 'TS ULTIMA MODIFICA')

     UNIQUE PRIMARY INDEX X1MPK138 ( COD_CLIENTE ,COD_RAPPORTO ,COD_UNV_RAPPRO ,
     COD_PRODOTTO_COMM ,DTA_RIFERIMENTO ,FLG_TIPO_PRODOTTO ,COD_BANCA )

     PARTITION BY RANGE_N(DTA_RIFERIMENTO
         BETWEEN DATE '2003-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' MONTH )
     INDEX X2POSMESE ( COD_CLIENTE ,DTA_RIFERIMENTO ,COD_BANCA )

     INDEX nusi_order ( DTA_EST_RAP ) ORDER BY VALUES ( DTA_EST_RAP );

These are the statistics collected on the table:

     Date Time Unique Values Column Names
     06/03/19 15:23:47            4,652,058 COD_CLIENTE
     06/03/19 15:35:07           22,946,539 COD_RAPPORTO
     06/03/19 15:37:34            1,588,990 COD_UNV_RAPPRO
     06/03/19 15:39:58                  602 COD_PRODOTTO_COMM
     06/03/19 15:42:07                   22 DTA_RIFERIMENTO
     06/03/19 15:44:20                    2 FLG_TIPO_PRODOTTO
     06/03/19 15:46:37                    3 COD_BANCA
     06/03/19 15:48:53                   31 COD_DIVISA
     06/03/19 15:51:23                1,913 COD_FIL_RAP
     06/03/19 15:53:45                   74 COD_SEDE_RAP
     06/03/19 15:55:56                    2 FLG_DIPENDENTE
     06/03/19 15:58:30               13,841 DTA_ACC_RAP
     06/03/28 09:55:51               12,818 DTA_EST_RAP
     06/03/19 16:04:38           12,079,608 IMP_SALDO
     06/03/19 16:07:44            8,354,853 IMP_SALDO_MEDIO
     06/03/19 16:10:09            3,195,385 IMP_UTILIZZATO
     06/03/19 16:12:30            1,619,093 IMP_ACCORDATO
     06/03/19 16:14:43                1,161 NUM_OPERAZIONI_D
     06/03/19 16:16:55                3,583 NUM_OPERAZIONI_A
     06/03/19 16:19:38            4,305,870 IMP_OPERAZIONI_D
     06/03/19 16:22:15            3,690,118 IMP_OPERAZIONI_A
     06/03/19 16:24:26              204,103 IMP_RATA
     06/03/19 16:26:44                   79 TSP_INSERIMENTO
     06/03/19 16:28:30                    1 TSP_ULT_MODIFICA
     06/03/19 15:11:31          472,707,502 COD_CLIENTE,COD_RAPPORTO,COD_UNV_RAPPRO,COD_PRODOTTO_COMM,
                                                   DTA_RIFERIMENTO,FLG_TIPO_PRODOTTO,COD_BANCA
     06/03/19 15:17:55           82,452,640 COD_CLIENTE,DTA_RIFERIMENTO,COD_BANCA
     06/03/16 00:55:41                   65 DTA_RIFERIMENTO,COD_BANCA

The select statement retrieves 4695 rows but the explain estimes with high confidence 32 rows. The sistem have 28 AMP.

WHY??????????????

Thanks for help.....

     SELECT DTA_EST_RAP
     FROM POSIZIONE_GLOBALE_MESE CPRO
     WHERE cpro.dta_est_rap ='2006-02-01'


      Explanation
        1) First, we lock a distinct DMKSL526."pseudo table" for read on a
           RowHash to prevent global deadlock for DMKSL526.CPRO.
        2) Next, we lock DMKSL526.CPRO for read.
        3) We do an all-AMPs RETRIEVE step from DMKSL526.CPRO by way of index
           # 8 without accessing the base table "DMKSL526.cpro.Field_1037 =
           DATE '2006-02-01'" with no residual conditions into Spool 1
           (group_amps), which is built locally on the AMPs.  The input table
           will not be cached in memory, but it is eligible for synchronized
           scanning.  The size of Spool 1 is estimated with high confidence
           to be 32 rows.  The estimated time for this step is 0.03 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.03 seconds.


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

Attachments

Library

Quick Reference