Partition not selected by optimizer

Database
Teradata Employee

Partition not selected by optimizer

Hi folks,

I have a problem and I would like if you can advise if anything can be done about that.

I have quite huge table (~2 billion records) which is partitioned by date. In order to query effectively my SQLs need to use these partitions to retrieve data in reasonable time.

I need to dynamically select  the dates for which I will retrieve the data and here is what I found: if I use following clause the optimizer does not limit searching to date partitions but does full table scan instead:

EXAMPLE 1: WHERE date in ( select  date from some_other table)            => not working ???

If I use text variable then optimizer does proper partition elemination:

EXAMPLE 2: WHERE date in ( ${predefined_set_of_dates})                      => working

Can someone advise me if there is something I can do in order to have the WHERE clause build like in the first (EXAMPLE 1) case? This one is much more flexible (and elegant) and it is hard for me to think that Teradata is restrained only to text strings...

I will be very gratefull for any piece of advice on that.

2 REPLIES
Enthusiast

Re: Partition not selected by optimizer

In EXAMPLE 1, the optimizer has no idea what values are in some_other_table (it is going to change this into an inner join anyway). It doesn't "peek" at the values in some_other_table. Whereas in EXAMPLE 2, the optimizer knows exactly what the values are and can use the PPI correctly.

We run into this all the time and we build the query dynamically in a script to populate the predefined_set_of_dates. It is a bit more work, but it gives a big payback in query performance.

R

Teradata Employee

Re: Partition not selected by optimizer

We do the same thing now. I only thought there is something I'm missing.