Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 03 Apr 2001 @ 17:16:53 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


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



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023