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: Wed, 4 Aug 2010 10:32:53 +0530
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
>
>
CREATE TABLE FACT_TEST
(
DATE_KEY NUMBER,
col1 NUMBER,
col2 NUMBER,
col3 NUMBER,
col4 NUMBER
)
PARTITION BY RANGE (DATE_KEY)
SUBPARTITION BY HASH (col1,col2)
PARTITION UNASSIGNED VALUES LESS THAN (MAXVALUE)
NOLOGGING
COMPRESS
SUBPARTITIONS 16
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
/
alter table FACT_TEST add constraint pk_test primary key (date_key,col1,col2);
begin
dbms_stats.gather_table_stats(
user,
'FACT_TEST',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 250'
);
end;
/
declare
l_part number;
l_date varchar2(20);
l_sql_stmt varchar2(200);
l_part_limit number;
l_insert_stmt varchar2(2000);
l_start_time pls_integer:=NULL;
l_elapsed_time pls_integer:=NULL;
begin
for i in 1 .. 10
loop
l_date := '20100404';
l_part := to_number(to_char(to_date(l_date, 'yyyymmdd') + i, 'yyyymmdd'));
l_part_limit := to_number(to_char(to_date(l_date, 'yyyymmdd') + i+1,
'yyyymmdd'));
dbms_output.put_line('output==='||l_part);
l_sql_stmt := 'ALTER TABLE fact_test SPLIT PARTITION UNASSIGNED at
('||l_part ||' ) INTO (PARTITION D_'||l_part_limit||' , PARTITION UNASSIGNED)
';
--dbms_output.put_line('This is stmt =='||l_sql_stmt);
execute immediate l_sql_stmt ;
l_start_time := dbms_utility.get_time;
l_insert_stmt := 'Insert /*+append */ into FACT_TEST (<COLUMN_LIST>)
select '||l_part ||' as DATE_KEY, <COLUMN_LIST>
from FACT_TEST1
where date_key = 20091216';
--dbms_output.put_line('insert stmt =='||l_insert_stmt);
execute immediate l_insert_stmt;
commit;
l_elapsed_time := dbms_utility.get_time - l_start_time;
dbms_output.put_line('inserted in partiton '||l_part ||' time taken
=='||l_elapsed_time/100 ||'secs');
end loop;
end;
/
Other related posts:
