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
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:
