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

Your results are more or less what I would expect.  The primacy key
validation is done thru an index look up and based on your script Oracle
will create a unique index so the look up to validate a key is quite
fast. (You can have a non unique index support a unique key but you have
to create the non-unique index first then add the unique/primary key
constraint.)

 

You may want to check out Richard Foote blog
(http://richardfoote.wordpress.com/) for some great info on indexes. 

 

Also there was only script, I didn't see a log file of the run. 

 

-----------------------

Ric Van Dyke

Hotsos Enterprises

-----------------------

 

Hotsos Symposium 

March 6 - 10, 2011 

You have to be there, yea I'm talking to you. 

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Anupam Pandey
Sent: Wednesday, August 04, 2010 1:03 AM
To: oracle Freelists
Cc: pandey83@xxxxxxxxx
Subject: Re: Is it a good idea to have primary key on DW table

 

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

Other related posts: