Hi Appan,
In my experience I have seen that Query 2 does not use partition pruning
because it's not a straight up filtering and involves using functions (aka
UDFs).
What version of Hive are you using ?
Thanks,
Viral
On Thu, May 14, 2015 at 1:48 PM, Appan Thirumaligai <appanhiv...@gmail.com>
wrote:
> Hi,
>
> I have a question on Hive Optimizer. I have a table with partition columns
> eg.,Sales partitioned by year, month, day. Assume that I have two years
> worth of data on this table. I'm running two queries on this table.
>
> Query 1: Select * from Sales where year=2015 and month = 5 and day between
> 1 and 7
>
> Query 2: Select * from Sales where concat_ws('-',cast(year as
> string),lpad(cast(month as string),2,'0'),lpad(cast(day as string),2,'0'))
> between '2015-01-01' and '2015-01-07'
>
> When I ran Explain command on the above two queries I get a Filter
> operation for the 2nd Query and there is no Filter Operation for the first
> query.
>
> My question is: Do both queries use the partitions or is it used only in
> Query 1 and for Query 2 it will be a scan of all the data?
>
> Thanks for your help.
>
> Thanks,
> Appan
>
