rebuild时也会使用临时表空间
ask tom上关于rebuild index 有这么一段话:
If you need to rebuild your indexes, you need 2x the space -- you'll have the old and the new index for a period of time. If you do it online, you'll need additional space to hold the changes that are made during the rebuild as well
2×的空间是当前index所在的tablespace中使用空间
排序操作相关的 stat号
SQL> select name,statistic# from v$statname where name like '%sort%';
NAME STATISTIC#
---------------------------------------------------------------- ----------
sorts (memory) 341
sorts (disk) 342
sorts (rows) 343
SQL> select STATISTIC#,value from v$mystat where STATISTIC# in (341,342,343);
STATISTIC# VALUE
---------- ----------
341 1384
342 12
343 19144681
SQL> alter index idx_name rebuild online;
Index altered.
SQL> select STATISTIC#,value from v$mystat where STATISTIC# in (341,342,343);
STATISTIC# VALUE
---------- ----------
341 1405
342 13
343 20739817
可以看出各种sort stat都增加了。
SQL> alter index idx_name rebuild;
Index altered.
SQL> select STATISTIC#,value from v$mystat where STATISTIC# in (341,342,343);
STATISTIC# VALUE
---------- ----------
341 1405
342 14
343 22334953
在rebulid index online的时候走的是full table scan,这时候也是需要排序的,而且排序的次数会比较多