All Forums Database
teradatauser2 236 posts Joined 04/12
08 Oct 2012
partition question

Hi,
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 ,
 NO RANGE);
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.

robpaller 159 posts Joined 05/09
08 Oct 2012

What do your table statistics look like?  
Generally an inequality condition is going to result in a FTS.

teradatauser2 236 posts Joined 04/12
08 Oct 2012

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

Remi 6 posts Joined 07/09
09 Oct 2012

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

You must sign in to leave a comment.