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