What is the best way to index a Netezza fact table with lots of primary keys. For now, let’s suppose we have 10 primary keys. This table is mainly used to store meta data about our database.

===================

6/21/2012 – update

This is the diagram for SQL Server or Oracle, not Netezza. I haven’t built this table on Netezza yet. Some of the primary keys you see in the fact table are also foreign keys (concept_cd, provider_id, encounter_num are 3 example FKs).

You can’t have more than one primary key on a table – I assume you mean foreign keys. There is no best way to do this, because it depends upon your workload. In some indexes one column may come first, while another may have a completely different selection of columns, let alone order.

In the end it doesn’t really matter, because Netezza doesn’t have indexes in the first place, so I’m not sure what you’re getting at.

That is a composite primary key made up of all those columns. Together they are “unique” (Netezza doesn’t enforce this) and uniquely identify the row. This is typical in a dimensional design. The FKs of a large number of dimensions uniquely identify a fact – usually a date/time for the snapshot, and something like a stock ticker or customer or whatever.

Netezza uses distribution keys to spread data across data slices. The distribution key is usually the primary key in other databases. Selecting a good distribution key increases the performance of your queries.