Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 22 Feb 2010 @ 13:51:12 GMT


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


Subj:   Avoiding an all-row scan
 
From:   mjrodriguezp

Hi again:

I hope someone can help with this:

--THIS QUERY TAKES LESS THAN ONE MINUTE TO COMPLETE

     SELECT * FROM
     BDP_tABLAS.PCL_HIS_CONTRATOS WHERE fec_vigencia = 1091231;

--THIS OTHER QUERY IS ENDLESS

     SELECT *
     FROM BDP_tABLAS.PCL_HIS_CONTRATOS a
     INNER JOIN BDD_TABLAS.FIN_BORRAR_1 B
     ON (A.fec_vigencia = B.fec_vigencia );

--THIS OTHER QUERY IS ALSO ENDLESS

     SELECT *
     FROM BDP_tABLAS.PCL_HIS_CONTRATOS a
     WHERE fec_vigencia IN (SELECT fec_vigencia
       FROM BDD_TABLAS.FIN_BORRAR_1)

- The table BDP_tABLAS.PCL_HIS_CONTRATOS IS PARTITIONED LIKE THIS PARTITION BY RANGE_N(fec_vigencia BETWEEN DATE '2007-12-31' AND DATE '2020-01-01' EACH INTERVAL '1' DAY )


- 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;


Result

     Fec_vigencia
     b 2009-12-31b

The first query goes to the right partition. It doesn't do the all-row scan.

The second and the third one do the all-row scan.

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?


Thanks a lot for any help!!



     
  <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