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
| 
