Home » RDBMS Server » Server Administration » enabling PK on a big table (Solaris 2.10 Oracle 11.2.0.2)
enabling PK on a big table [message #558485] Fri, 22 June 2012 17:09 Go to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
I have a partioned table that has close to 2 billion rows and a PK
of all columns. Becuase of time constrains my APP team wants the PK disabled while they pump into hundreds of thousands of rows with a batch process.

Now I am finding when I enable the PK its eating up close to close
to 200GB of temp space.

Is there something I can do to reduce the amount of temp space being used?

Re: enabling PK on a big table [message #558486 is a reply to message #558485] Fri, 22 June 2012 17:13 Go to previous messageGo to next message
BlackSwan
Messages: 23059
Registered: January 2009
Senior Member
>Is there something I can do to reduce the amount of temp space being used?
No
There Ain't No Such Thing As A Free Lunch!

As they say in Engineering - You can have it Good, Fast, Or Cheap. Pick any two!
If it is Good & Fast, it won't be Cheap.
If it is Good & Cheap, it won't be Fast.
If it is Fast & Cheap, it won't be Good.
Re: enabling PK on a big table [message #558489 is a reply to message #558486] Fri, 22 June 2012 21:26 Go to previous messageGo to next message
John Watson
Messages: 4804
Registered: January 2010
Location: Global Village
Senior Member
Well, in this case perhaps there is a free lunch. Or at least, a free drink with every meal.

A primary key (or unique key) constraint requires an index. If there is no index on the columns, Oracle will create a unique index when you define the constraint. If you disable the constraint, this unique index has to be dropped. When you enable it, the index is built again. That is why you need all that temp space, and why it takes so long. The answer is to drop the constraint, and pre-create a NON-unique index, which is the default type. Then create the constraint, which will use your already extant index. You will find that now you can disable the constraint, and the index survives. Enabling the constraint will now be much faster.Like this:
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table t1 (c1 number);
alter table t1 add constraint t1pk primary key(c1);
select object_name,object_type from user_objects;
alter table t1 modify constraint t1pk disable;
select object_name,object_type from user_objects;
alter table t1 modify constraint t1pk enable;
select object_name,object_type from user_objects;
alter table t1 drop constraint t1pk;
create index i1 on t1(c1);
alter table t1 add constraint t1pk primary key(c1);
select object_name,object_type from user_objects;
alter table t1 modify constraint t1pk disable;
select object_name,object_type from user_objects;
alter table t1 modify constraint t1pk enable;


However, there are other things to think about. First, have you proved that disabling the constraint actually makes the insert faster? Second, have you looked into optiomizing this batch load? Third, you say "a PK of all columns" which sounds to me as though it should be an index organized table, not a heap table with a separate index. Fourth, there is a lot you can do to optimize index creation.

enabling PK on a big table [message #558658 is a reply to message #558489] Mon, 25 June 2012 08:33 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
John,

Thank you for your detailed reponse. I was wondering if you could articulate a bit more on how the steps you described saves on temp space usage. I can see how it would be faster to break this down into 2 steps but I am unsure how this can save me space?



create index i1 on t1(c1);
alter table t1 add constraint t1pk primary key(c1);



Is this something I can measure with the following query?


SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
 P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
 COUNT(*) statements
 FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
 AND S.paddr = P.addr
 AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
 P.program, TBS.block_size, T.tablespace
 ORDER BY sid_serial;



To answer your questions below the primary key is made up of 11 columns not all the columns in the table. To me this sounds like it is way over kill and the app/data needs to be restructured.

Secondly, I don't have any concrete timimgs, I am in the process of running these tests myself as I am unsure about the source who has provided me the information.

Thank you for your exterise and any suggestions you may have regarding this issue.
Re: enabling PK on a big table [message #558735 is a reply to message #558658] Tue, 26 June 2012 04:12 Go to previous messageGo to next message
John Watson
Messages: 4804
Registered: January 2010
Location: Global Village
Senior Member
Temp space is needed when you create the index. I am sure beyond all reasonable doubt that you are dropping and creating the index EVERY TIME you disable and enable the consrtaint. Do it my way, and you don't drop the index. Therefore you never need any temp space again.
Re: enabling PK on a big table [message #559096 is a reply to message #558735] Thu, 28 June 2012 13:38 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Another thing that I have been doing to speed up large index builds is to increase the sort memory so that the sort will occur in memory.
alter system set pga_aggregate_target=20g scope=memory sid='*';
This can be added to the spfile to make it part of the next startup with the following.
alter system set pga_aggregate_target=20g scope=spfile sid='*';
Re: enabling PK on a big table [message #559099 is a reply to message #559096] Thu, 28 June 2012 13:46 Go to previous message
Michel Cadot
Messages: 59794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have to build indexes in a session, do not increase pga_aggregate_target, set workarea_size_policy to MANUAL and sort_area_size to a large value for this session (that is using ALTER SESSION).

Regards
Michel
Previous Topic: what is wrong in the code
Next Topic: listener log
Goto Forum:
  


Current Time: Wed Nov 26 23:53:07 CST 2014

Total time taken to generate the page: 0.21703 seconds