need more inputs, its dangerous to decide by just considering one sample query fired on Fact tables. if this is the only query which is fired all the time on this FACT table, you can distribute the FACT on PK3. what are the distributions u used when u got the bad plan?

I would not be so quick to say that broadcasting the customer table is ok.  If the customer table has 100 million records in it, that can become a very big problem.  It is not uncommon to distribute your fact table on your customer_id column in this case.  As has already been pointed out, however, I have no idea what else is running on the system.  Distributing your fact table on customer_id might benefit this specific query but slow down 95% of the other common queries in your system.

A very common problem in situations like this is that you might have some 'generic' customer key values which occur in large percentage of your records.  For example, if you have a generic customer key such as 'Unknown Customer' that key might be 25-50% of your records.  In such a case if you distribute on customer_id that will result in highly skewed data and terrible performance.

Even if you are just joining on customer_id as in your SQL, if the optimizer decides to redistrbute the fact table and the customer table on customer id to perform the join, you can end up pushing an excessivly large number of records to a single CPU core and thus your query will take forever to complete.

Try not to focus on the pk/fk as the summation of the problem. You can have very different pk/fk from your distribution. A distribution does not have to participate as a pk or fk.

Think about this - if your tables are all distributed on X, where your pk and fk are always Y and Z, then your Y and Z form the functional connection for the data, where X forms the

performance substrate for comparing them between tables. In many cases X may be a primary key. You would join on all three, even though X might not have anything to do with the

problem-at-hand, excluding it will cost you in performance.

So find the common denominator for all of your tables in play. Distribute them on a common key and then apply your query accordingly, using the regular functiional join logic but also

including the distribution key to connect them on the hardware. It doesn't matter if the distribution key actually plays a functional role in the data as long as it keep the data on the same

SPU during the join of the functional keys.

If you want to solve the problem of this particular query only by creating a copy of the fact table and redistibuting it on the necessary key. Use this strategy only if you are having the report this query is used frequently