partition question

Junior Supporter

partition question


I have a table tab1 with partition on a integer type column col with partition defn

PARTITION BY RANGE_N(col BETWEEN 1 ,2 ,3 ,4 ,6 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,21 ,23 ,24 ,25  AND 26 ,


I have a query like:

sel * from tab1 where col<>3. This query is going for a full table scan and not using partition.

I wanto write a query like :

sel* from tab1 where col in (,1,2,4..). This uses partition elimination. But the question is, how do i give the values in the 'no range' as i am not aware what values it might have.

This is a very big table with a size of around 3 TB and the first query shows a large time in explain.


Re: partition question

What do your table statistics look like?  

Generally an inequality condition is going to result in a FTS.

Junior Supporter

Re: partition question

yeah..i want to remove the inequality and put an in condition

Teradata Employee

Re: partition question

To answer the last question, if I get it right, NO RANGE is just another partition for any value out of the range you have defined. So partition elimination can also be leveraged for a value that is out of range.

Of course, this is as efficient as good you are in predicting the demographics, so you might end up with a very large and often accessed "NO RANGE" partition which might not tun to be too helpful...