Oracle 如何为非常大的表/用完临时空间创建约束和索引

我正在新服务器上重建数据库，并且已通过datapump exp/imp重新加载了表。我有一个特定的表，它有一个按年划分的范围分区，并且有大约200亿行数据。当我尝试添加约束和索引时，我遇到了临时空间不足的错误，并且将其增加到约500gb，并且仍然空间不足。

我确实弄清楚了如何通过创建索引不可用，然后分别重建每个分区来处理索引，这些分区可以工作，但还没有弄清楚如何创建约束。

如果我用NOVALIDATE选项创建约束，我会避免使用临时空间吗？

没有添加任何数据，所以我知道没有dup，我只是希望它继续执行。

所以，让我们创建一个巨大的表，看看会发生什么:

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;

你指的是dups，所以我猜你是在谈论主/唯一约束。和检查约束等不太可能需要temp反正:

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

这是一张很大的表。所以我们需要大量的temp来添加一个唯一的约束:

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

通过使它无效，我们可以节省多少？

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

什么？!没什么？？

为什么？

那么当您创建一个唯一的约束或主键时，Oracle数据库会创建一个唯一的索引来支持它。即使你不验证约束:

select index_name, uniqueness from user_indexes
where  table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
U                              UNIQUE

那么，如果我们创建一个非唯一索引作为添加约束的一部分，会发生什么:

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

嗯。再好不过了...

除非索引已经到位!

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

没有临时使用 :)

因此，如果您可以创建第一个，然后使用这些索引添加约束，则可以解决此问题。

注意为了支持唯一/主键，您需要全局索引或以分区键为前缀的本地索引。所以你现有的 “按分区重建” 策略可能不起作用...