Partition Elimination - Using Subquery Route

Database
Enthusiast

Partition Elimination - Using Subquery Route

Hi,

 We have the following tables being used in a query:

FACT TABLE:

CT MY_FACT (fact_key integer, dim1_key integer, dim2_key integer) primary index (fact_key)

partition by (range_n (dim1_key between 0 and 10000000 EACH 100000, NO RANGE, UNKNOWN));

DIM TABLE;

CT MY_DIM (dim1_key integer, dim1_code char(10), dim1_id char(6)) primary index (dim1_code);

QUERY:

sel count(*) from MY_FACT

where dim1_key in (5202400,3002054);

-- This query accesses 2 partitions as desired.

sel count(*) from MY_FACT

where dim1_key in (sel dim1_key from MY_DIM where dim1_id = '1210');

-- This query doesnt seem to access the partition since it gets the value for PPI column from sub query. Is there any way we can re-write the query in order to have the partitions accesessed.

Thank You

2 REPLIES
Teradata Employee

Re: Partition Elimination - Using Subquery Route

Hi,

There is no way the explain can predict the set of values returned by your subquery, so the partition elimination won't work here.

However, this doesn't necessarily mean that the partitionning won't make any difference joining these two tables, but not easy to predict, so have a look at the explain.

//Remi

Junior Supporter

Re: Partition Elimination - Using Subquery Route

Hi Remi,

I have a similar question, in one of my queries the PPI column value is fetched from a join to another table.

like a.col1 betweeb b.col1 and b.col2.

here col1 is a PPI column , but partition elimination is not used. I checked the explain, its going for a all amp retrive with elimination.

Is there a way out ? Its a query on informatica.