RE: Is it a good idea to have primary key on DW table
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <my.oralce@xxxxxxxxx>, "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Aug 2010 08:57:51 -0500
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:
