Re: Is it a good idea to have primary key on DW table
- From: Anupam Pandey <my.oralce@xxxxxxxxx>
- To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Aug 2010 09:48:20 +0530
Hi All,
Thanks for suggestions and thoughts on this ..
Here are the answers to those questions asked above :-
1. Index created for PK support is global .
2. Primary key has been created to guarantee data integrity primarily .
Thanks And Regards,
Anupam
On Thu, Aug 5, 2010 at 3:58 AM, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
> 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<http://us.mc1202.mail.yahoo.com/mc/compose?to=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:
