Archives of the TeradataForum
Message Posted: Fri, 13 Oct 2006 @ 08:33:08 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|