Archives of the TeradataForum

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

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

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