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).

enter image description here

  • 3
    Are you sure those are no composite PKs, not multiple PKs? – Remus Rusanu Jun 21 '12 at 14:54
  • Yes you are correct. I had the terminology incorrect. Thanks for correcting me. +1 That is a Composite Primary Key (multiple columns uniquely identifying each record in that table) – MacGyver Jun 21 '12 at 15:39

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.

  • In this specific fact table, we make all of our foreign keys primary keys, much like a bridge table, but it's mainly for finding data in our data warehouse. So how does Netezza sort data? I'm very new to Netezza. – MacGyver Jun 21 '12 at 3:43
  • @MacGyver A table only has one primary key. I think what you are saying is that the foreign keys are all primary keys in their respective tables (and not just any unique key). Why don't you post your schema? Netezza doesn't enforce referential integrity nor does it enforce uniqueness in primary keys. – Cade Roux Jun 21 '12 at 12:37
  • see my latest update – MacGyver Jun 21 '12 at 14:48
  • It turns out TwinFin (our hardware used with Netezza) allows the data to be spread out across multiple nodes. That's how it gets its performance, despite the inability to create indices. – MacGyver Jun 27 '12 at 3:15
  • @MacGyver You may find that the dimensional model does not work well for large databases on massively parallel systems due to skew issues (because facts on one node will have to find dimensions on many other nodes) - and that these have to be managed. – Cade Roux Jun 27 '12 at 14:42

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.