| enabling PK on a big table [message #558485] |
Fri, 22 June 2012 17:09  |
 |
BeefStu
Messages: 208
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 #558489 is a reply to message #558486] |
Fri, 22 June 2012 21:26  |
John Watson
Messages: 8960
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 |
|
BeefStu
Messages: 208
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 |
John Watson
Messages: 8960
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 #559099 is a reply to message #559096] |
Thu, 28 June 2012 13:46 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
