Q:

Does the order of partitioned columns in WHERE clause matter

If we have a HIVE table partitioned by col,col2,col3 then while doing a SELECT on this table if I want to specify these columns in WHERE clause, do they have to appear in the same order for it to take advantage of partitioning? I mean, logically yes, but doesn't HIVE compiler understand their actual order irrespective of what order I specify them in. It should be able to apply the correct order internally because HIVE already knows that the correct order is col2,col2,col3 so it use it in that way. HIVE does lot of optimization on top of the query that we define, so in this I guess it should do the same.

A:

No, that order shouldn't matter. Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query. It still makes sense to write the query using the logical order of partitioned columns for better human understanding.

