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

From: Anupam Pandey <my.oralce_at_gmail.com>
Date: Wed, 4 Aug 2010 10:32:53 +0530
Message-ID: <AANLkTinrKSKCOkEo+K381MDQRudGn0QhzUxm_oz7Oqh5_at_mail.gmail.com>

In another test run I was expecting the loading of that fact table to take more as the new partitons get added to that table with primary key enabled. But it turned out to be a flat (almost) time for insert or loading for that table with primary key enabled.

I was expecting it to take more time with the increase in number of rows because primary key validation will be a serial job ( correct me if i am wrong)
and with the increase in data oracle will have to do more work with subsequent loading .

I have atached the log file and script..

Plz let me know your thought on it .

Thanks And Regards,

   Anupam

On Wed, Aug 4, 2010 at 10:23 AM, Anupam Pandey <my.oralce_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l




text/plain attachment: script.txt

Received on Wed Aug 04 2010 - 00:02:53 CDT