Skip to Main Content
  • Questions
  • Rebuild partition index through procedure is slow

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 24, 2020 - 6:34 pm UTC

Answered by: Connor McDonald - Last updated: July 27, 2020 - 9:09 am UTC

Category: PL/SQL - Version: 11.2.0.4

Viewed 100+ times

You Asked

Hello Tom,

I have come across a very strange problem. I have a partitioned table where my ETL loads data. Before data load generally the local partitioned indexes for a particular month say June month will be marked as UNUSABLE and once load is done it is rebuilt using a procedure where the procedure simply does below -

ALTER TABLE SCOTT.PART_TABLE MODIFY PARTITION JUN2020 REBUILD UNUSABLE LOCAL INDEXES ;

This statement rebuilds all 15 individual partitions for JUN2020 that were marked UNUSABLE and runs for about 30 min through the procedure


Strange thing is if I run the same statement directly in my sqlplus session it completes in about 30 seconds.

SQL> ALTER TABLE SCOTT.PART_TABLE MODIFY PARTITION JUN2020 REBUILD UNUSABLE LOCAL INDEXES ;

Table altered.

Elapsed: 00:00:35.87


Can you please throw some light on this ?


and we said...

I can't replicate your findings....but please read on past the demo

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>


Having said that, I got bitten by similar issue to this a few years back, but it was with explicit rebuild calls for each index. The cause in that case was that temporary space needed is released at the end of the database call.

So if you rebuilds 10 indexes in SQL Plus, we are doing this:

- rebuild #1 (consume big temp segment)
- database call is complete and we are returning control to the client
- release the big temp segment

- rebuild #2 (consume big temp segment)
- database call is complete and we are returning control to the client
- release the big temp segment

etc etc

If you do this in a PLSQL loop, we are doing this:

- rebuild #1 (consume big temp segment)
- database call is NOT complete
- rebuild #2 (consume ANOTHER big temp segment)
- database call is NOT complete
- rebuild #3 (consume ANOTHER big temp segment)
etc etc

In my case, the elapsed time was burnt doing lots and lots of extensions to our tempfile.

So perhaps log a call with Support and see if perhaps there is a related issue for your particular patch level/platform etc.




and you rated our response

  (1 rating)

Reviews

July 27, 2020 - 5:06 am UTC

Reviewer: Pulakesh das from India

Thanks for your time Tom . Greatly appreciate it. Today morning I found that if I disable oltp compression for that partition it gives consistent time in both sqlplus and procedure. Perhaps in compressed blocks it has something to do with the temp usage behaviour as you pointed out. Strange though.
Connor McDonald

Followup  

July 27, 2020 - 9:09 am UTC

I will try replicate that here now that you've provided more info

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database