Archives of the TeradataForum
Message Posted: Tue, 03 Apr 2001 @ 17:16:53 GMT
| Subj: | | Primary keys in Fact tables for Star Schemas |
| |
| From: | | Dave Ahnell |
We are migrating over several Oracle decision support databases to Teradata. Each is modeled in a Star Schema design because that
was the best design for Oracle. We do not have the time to redesign the data structures into a more normalized design, realizing
that this works the best with Teradata.
We can set up the primary key on the Fact table to contain a single column that is nearly unique, but that is rarely filtered
upon, or create the primary key to contain the 10-15 columns that comprise the join keys (foreign keys from the dimension tables).
This latter approach would allow us to order the columns in such a way that the most frequently filtered upon columns appear first in
the key. This latter index would also be nearly unique.
I realize that the fewer columns in the primary index the better, but we're trying to solve a query performance issue we're
seeing. Oracle uses data range partitioning to seperate data in the fact table based on the partitioning key. Queries run in
Teradata are running quite a bit longer than the same query run against Oracle when the Oracle partitioning key is filtered upon.
I'm looking for suggestions from other sites that are using Star Schemas in Teradata to identify how they are tackling this issue.
Any other comments are welcome as well.
David Ahnell
EDS E.solutions
DVC Business Intelligence Services
| 
