I am rebuilding a database on a new server and I have reloaded the tables via datapump exp/imp. I have one particular table that has a range partition by year and has roughly 20+ billion rows of data. When I try to add the constraints and indexes I was getting errors that I was running out of TEMP space and I had increased it to about 500GB and was still running out of space.
I did figure out how to handle the indexes by creating the index unusable and then rebuilding each partition individually which works but have not figured out how to get the constraints created.
Will I avoid using TEMP space if I create the constraint with the NOVALIDATE option?
No data has been added so I know that there are no dups, I just want it enforced going forward.
So, let's create a massive table and see what happens:
create table t as
with rws as (
select * from dual
connect by level <= 10000
)
select rownum c1, sysdate c2, dbms_random.string('a', 20) c3
from rws cross join rws;You refer to dups, so I'm guessing you're talking about primary/unique constraints. And check constraints etc. are unlikely to need temp anyway:
alter table t add constraint c check ( c1 > 0 ) ;
select n.name, value from v$mystat ms
join v$statname n
on n.STATISTIC# = ms.STATISTIC#
where lower(n.name) like 'phy%temp%';
NAME VALUE
---------------------------------------------------------------- ----------
physical reads direct temporary tablespace 0
physical writes direct temporary tablespace 0
It's a huge table. So we need a lot of temp to add a unique constraint:
alter table t add constraint u unique (c1, c3) ;
select n.name, value from v$mystat ms
join v$statname n
on n.STATISTIC# = ms.STATISTIC#
where lower(n.name) like 'phy%temp%';
NAME VALUE
---------------------------------------------------------------- ----------
physical reads direct temporary tablespace 591234
physical writes direct temporary tablespace 591234
How much do we save by making it novalidated instead?
alter table t drop constraint u;
alter table t add constraint u unique (c1, c3) novalidate;
select n.name, value from v$mystat ms
join v$statname n
on n.STATISTIC# = ms.STATISTIC#
where lower(n.name) like 'phy%temp%';
NAME VALUE
---------------------------------------------------------------- ----------
physical reads direct temporary tablespace 1182468
physical writes direct temporary tablespace 1182468
WHAT?! Nothing??
Why's that?
Well when you create a unique constraint or primary key, Oracle Database creates a unique index to support it. Even if you don't validate the constraint:
select index_name, uniqueness from user_indexes
where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
U UNIQUE
So what happens if we create a non-unique index as part of adding the constraint:
alter table t drop constraint u;
alter table t add constraint u unique (c1, c3) using index (
create index i on t (c1, c3)
) novalidate ;
select n.name, value from v$mystat ms
join v$statname n
on n.STATISTIC# = ms.STATISTIC#
where lower(n.name) like 'phy%temp%';
NAME VALUE
---------------------------------------------------------------- ----------
physical reads direct temporary tablespace 1773702
physical writes direct temporary tablespace 1773702
Hmmm. No better...
Unless the index is already in place!
alter table t drop constraint u keep index;
alter table t add constraint u unique (c1, c3)
using index i novalidate ;
select n.name, value from v$mystat ms
join v$statname n
on n.STATISTIC# = ms.STATISTIC#
where lower(n.name) like 'phy%temp%';
NAME VALUE
---------------------------------------------------------------- ----------
physical reads direct temporary tablespace 1773702
physical writes direct temporary tablespace 1773702
No temp usage :)
So if you can create the first, then add the constraints using these indexes you can get around this issue.
Note to support unique/primary keys, either you need global indexes or local ones prefixed with the partition key. So your existing "rebuild by partition" strategy may not work...