Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 22 Feb 2010 @ 14:59:35 GMT


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


Subj:   Re: Avoiding an all-row scan
 
From:   Dieter Noeth

mjrodriguezp wrote:

  - The table BDD_TABLAS.FIN_BORRAR_1 has only one row with the date I want to use SELECT * FROM BDD_TABLAS.FIN_BORRAR_1;  


Statistics on FIN_BORRAR_1.fec_vigencia, so the optimizer knows about that single row? If not it will estimate the number of rows equal to the number of AMPs in your system.

Statistics on PCL_HIS_CONTRATOS: fec_vigencia/PARTITION?

Is that all-row-scan really a full-table-scan or "enhanced by dynamic partition elimination"?


  The thing is than I need to have the date in a field of a table like this because it can change.  


  Is there a way to optimize the second or third query using the table FIN_BORRAR_1?  


If that table is always a single row you might try a view:

     REPLACE VIEW FIN_BORRAR_1 AS
     SELECT DATE '2009-12-31' AS fec_vigencia;

In TD12 the optimizer will completely remove the view-access and create exactly the same plan as the first query.

Btw, don't write dates using the old integer format anymore, better use "DATE '2010-02-22'"


Dieter



     
  <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: 15 Jun 2023