Re: Is it a good idea to have primary key on DW table

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: my.oralce@xxxxxxxxx, taral.desai@xxxxxxxxx
  • Date: Wed, 4 Aug 2010 15:28:41 -0700 (PDT)

I'm not sure why you see this as a performance issue vs. a scheduling 
1.  I would consider it normal impact to have waits until the primary key was 
created-  always a better idea to have the pk or any index for that matter, 
built at creation time or during a maintenance window.
2.  I have utilized indexes both the INDEX and USE_HASH hints to "narrow" down 
my lead in table for a hash join between one fact table and another.  If a full 
table scan is required, passing over a partition scan, then an index can be 
very handy.
3.  Are you attempting to guarantee data integrity here or simply require an 
index for performance?  Is the index cost-effective when you consider the 
impact for inserts/updates/deletes to the table and if it's partitioned, is 
this a global index that will hinder maintenance operations?

Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
"Go away before I replace you with a very small and efficient shell script..."

--- On Wed, 8/4/10, Taral Desai <taral.desai@xxxxxxxxx> wrote:

From: Taral Desai <taral.desai@xxxxxxxxx>
Subject: Re: Is it a good idea to have primary key on DW table
To: my.oralce@xxxxxxxxx
Cc: "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>, pandey83@xxxxxxxxx
Date: Wednesday, August 4, 2010, 3:35 PM

Also, to add

Constraints in DW is not just for integrity but also for performance. If you 
have right constraints then it can improve performance. 

Optimizer is smart enough to see that and will give you plan accordingly

On Tue, Aug 3, 2010 at 11:53 PM, Anupam Pandey <my.oralce@xxxxxxxxx> wrote:

Hi Listers,
                I was trying to do a poc whether its good idea to have the 
primary key constraint on FACT table in DW envioronment or not .

Here is what i found :-

1. Once I loaded the table for few days and started the primary key creation 
process ,other select queries on that table was blocked.



alter table FACT_TEST add constraint pk_FACT_TEST primary key (<COLUMN_LIST>) 

select date_key,count(*) from fact_test group by date_key 

Thanks & Regards,
Taral Desai


Other related posts: