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

I'm not sure why you see this as a performance issue vs. a scheduling 
conflict...
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.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
 
"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.

    



 
SID
SQL_ID
SQL_HASH_VALUE
BLOCKING_INSTANCE
BLOCKING_SESSION
SQL_TEXT

1
322
5adg0zfm9zrat
2795494745
 
 
alter table FACT_TEST add constraint pk_FACT_TEST primary key (<COLUMN_LIST>) 

2
389
9n8jftdhvb5q0
1639290560
1
322
select date_key,count(*) from fact_test group by date_key 



-- 
Thanks & Regards,
Taral Desai



      

Other related posts: