Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 13 Oct 2006 @ 08:33:08 GMT


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


Subj:   How to get the REAL explaination for a query with variable
 
From:   Weng, Silei

Hi All,

I need some advice on "explain" queries.

I have a big table in the database which was created as a PPI table. The partition is on the CREATED_DATE column.

What I want to do is like following:

     Select * from BIG_TABLE, BATCH_TABLE
     where CRERATED_DATE = max(BATCH_TABLE.BATCH_DATE);

I explained the SQL above and found that the optimizer will not use the PPI for the join. So I changed it to:

     .export data file /home/test_ppi.dat
     select max(BATCH_DATE) from BATCH_TABLE;
     .export reset

     .import data file /home/test_ppi.dat
     explain
     using (ppi_date date)
     Select * from BIG_TABLE
     where CRERATED_DATE = :ppi_date

The result is:

Next, we do an all-AMPs RETRIEVE step from a single partition of BIG_TABLE with a condition of (" BIG_TABLE = :ppi_date ") with a residual condition of ("BIG_TABLE = :ppi_date") into Spool 1 (all_amps), which is built locally on the AMPs.


So my question is: does it do use the PPI as the explaination says? How could it use the PPI when it does not know the value of :ppi_date?


Best regards,

Ray



     
  <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