the more is the unique key distribution more faster will be the query response.

In your case you have distributed Temp 1 on USER_ID and in Temp 2 on DATE, as per my understanding, you must have date column with same data w.r.t. to USER_ID column . Due to this you are getting less time in Temp 1 and more time in Temp 2.

As per your problem, it seems like it does matter with order of declaration for Distribution key in table.

Snippets increases with the data distribution on table.

yes, that is what i am saying that it does matter with column distribution. Netezza is giving priority to first distribution key as per your analysis.

Try this query to verify your distribution across all SPUs with your tables

select datasliceid, count(datasliceid) as “Rows”

from table_name group by datasliceid order by “Rows”;

While Shawn has no doubt consummately answered this OP - as an additional note - for your consideration -

If you actually use columns to filter a query (as in count of distinct user_ids), you may also experience "process skew" and "date" types are notorious for this effect. It is why date types are really bad choices for distribution keys.

Here's an example:

Let's say I have 2 billion records representing 2 years of retail data. I distribute the data on "transaction date" and when I check the distribution, the data is physically spread out very evenly.

Now when I query for the data, say for the day after Thanksgiving to get a peak sales count, the database responds slowly. Why? Because all of the data for this day has been put on one SPU, so only one SPU is involved in supporting your query. It is obvious why this is a bad state of affairs. If I were to redistribute the data on random, it would get better performance simply because the query will involve more SPUs.

A better use for the transaction data above, is as a zone map, because this will make every SPUs activity more efficieint.

So in your case, you might have great distribution physically, but when you are trying to get a select-distinct in one form, it leverages fewer SPUs in the process, slowing down the query.

Back to your issue on aggregation, Whenever you aggregate something using a CTAS, Netezza's default behavior is to leverage the fields in the group-by clause as the distribution key. You can and usually should override this default with a distribute-on clause. If you let Netezza default, what you will forego is a co-located write. This is very important because a CTAS without a co-located write will blast data all over the machine rather than keeping it localized on the SPU. Redistribution is less efficient than a co-located write.

In addition - using multiple distribution keys rarely has any value. Mainly because once you distribute on those keys, you are required to use ALL of the keys in joins if you want to get co-location. Using less than all of them will functionally work, but will be slower. Distribution keys are not indexes - they are cat'd and hashed together, and the hash value determines which SPU it will land on. If you don't use all the keys in subsequent joins, Netezza will not be able to perform a co-located join and will have to scan the table. So the distribution key did not help you much, except to spread the data out.

Recommendation - stay away from dates as distribution keys - this is a known best practice and pitfall. Also stay away from multiple distribution keys unless you have an obvious and necessary reason to use them this way.

Want more insight - there is a whole chapter in "Netezza Underground" (Amazon.com) dedicated to distribution and how it works.