Oracle 通过过程重建分区索引很慢

你好，汤姆，

我遇到了一个非常奇怪的问题。我有一个分区表，其中我的ETL加载数据。通常在数据加载之前，特定月份的本地分区索引表示6月月份将被标记为不可用，一旦加载完成，将使用以下过程进行重建-

ALTER TABLE SCOTT.PART_TABLE修改分区JUN2020重建不可用的本地索引;

此语句为2020年6月重建所有被标记为不可用的15个单独分区，并通过该过程运行约30分钟


奇怪的是，如果我直接在我的sqlplus会话中运行相同的语句，它在大约30秒内完成。

SQL> ALTER TABLE SCOTT.PART_TABLE修改分区JUN2020重建不可用的本地索引;

表变了。

逝去: 00:00:35.87


你能在这上面放些光吗？

我无法复制您的发现 .... 但是请阅读过去的演示

SQL> create table t
  2  partition by list ( x )
  3  (
  4    partition p1 values (1)
  5  )
  6  tablespace largets 
  7  as select 1 x, d.*
  8  from dba_objects d,
  9   ( select 1 from dual connect by level <= 400 );

Table created.

SQL>
SQL>  create index ix1  on t ( owner ) local tablespace largets  ;

Index created.

SQL> create index ix2  on t ( object_name ) local tablespace largets  ;

Index created.

SQL> create index ix3  on t ( object_id ) local tablespace largets  ;

Index created.

SQL> create index ix4  on t ( created ) local tablespace largets  ;

Index created.

SQL> create index ix5  on t ( last_ddl_time ) local tablespace largets  ;

Index created.

SQL> create index ix6  on t ( data_object_id ) local tablespace largets  ;

Index created.

SQL> alter index ix1 modify partition p1 unusable;

Index altered.

SQL> alter index ix2 modify partition p1 unusable;

Index altered.

SQL> alter index ix3 modify partition p1 unusable;

Index altered.

SQL> alter index ix4 modify partition p1 unusable;

Index altered.

SQL> alter index ix5 modify partition p1 unusable;

Index altered.

SQL> alter index ix6 modify partition p1 unusable;

Index altered.

SQL>
SQL> create or replace
  2  procedure ppp is
  3  begin
  4    execute immediate 'alter table t modify partition p1 rebuild unusable local indexes' ;
  5  end;
  6  /

Procedure created.

SQL>
SQL> set timing on
SQL> exec ppp

PL/SQL procedure successfully completed.

Elapsed: 00:02:47.63
SQL> set timing off
SQL>
SQL> alter index ix1 modify partition p1 unusable;

Index altered.

SQL> alter index ix2 modify partition p1 unusable;

Index altered.

SQL> alter index ix3 modify partition p1 unusable;

Index altered.

SQL> alter index ix4 modify partition p1 unusable;

Index altered.

SQL> alter index ix5 modify partition p1 unusable;

Index altered.

SQL> alter index ix6 modify partition p1 unusable;

Index altered.

SQL>
SQL> set timing on
SQL> alter table t modify partition p1 rebuild unusable local indexes;

Table altered.

Elapsed: 00:02:46.72
SQL>
SQL>

话虽如此，几年前，我被类似的问题所困扰，但这是对每个索引的明确重建呼吁。在这种情况下，原因是在数据库调用结束时释放了所需的临时空间。

因此，如果您在SQL Plus中重建10个索引，我们正在这样做:

-重建 #1 (消耗大温度段)
-数据库调用完成，我们将控制权返回给客户端
-释放大温度段

-重建 #2 (消耗大温度段)
-数据库调用完成，我们将控制权返回给客户端
-释放大温度段

等等

如果你在PLSQL循环中这样做，我们正在这样做:

-重建 #1 (消耗大温度段)
-数据库调用未完成
-重建 #2 (消耗另一个大的临时部分)
-数据库调用未完成
-重建 #3 (消耗另一个大的临时部分)
等等

就我而言，经过的时间被烧掉了，对我们的tempfile做了很多很多扩展。

因此，也许可以与支持人员进行通话，看看您的特定补丁级别/平台等是否存在相关问题。