Skip to Main Content
  • Questions

  • Rebuild Index in parallel after direct load

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Wolfgang.

Asked: January 22, 2016 - 12:48 pm UTC

Last updated: January 27, 2016 - 2:04 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello,

we have a table with two partitions PART1 and PART2 (List partitioning) and local indexes on it.
Only one partition is filled with data, the other partition is empty.

In a nightly batchjob we check if PART1 or PART2 is filled and then load new data in the other partition:
INSERT /*+append*/ INTO t select /*+parallel(...)*/col1,col2,2 as partitionid FROM... ;
alter table truncate partition part1;


So we use these two partitions to provide new data and when we're done we delete the old data and switch to the new data.
As you have seen we use PQ to speed up the Load process, but the build of the indexes after the load is done with a single process, as
they are defined with the noparallel clause.

Is there a way to tell oracle to build the index in parallel without using 'alter index i parallel xy'?

and Chris said...

You can force parallel DDL at the session level:

create table t as
  select rownum r from dual connect by level <= 100;
 
create index i on t (r);

explain plan for 
 alter index i rebuild;
 
select * from table(dbms_xplan.display(null, null, 'BASIC'));

---------------------------------------                                                                                                                         
| Id  | Operation              | Name |                                                                                                                         
---------------------------------------                                                                                                                         
|   0 | ALTER INDEX STATEMENT  |      |                                                                                                                         
|   1 |  INDEX BUILD NON UNIQUE| I    |                                                                                                                         
|   2 |   SORT CREATE INDEX    |      |                                                                                                                         
|   3 |    INDEX FAST FULL SCAN| I    |                                                                                                                         
--------------------------------------- 
 
alter session force parallel ddl parallel 4;

explain plan for 
 alter index i rebuild;
 
select * from table(dbms_xplan.display(null, null, 'BASIC'));

------------------------------------------------                                                                                                                
| Id  | Operation                   | Name     |                                                                                                                
------------------------------------------------                                                                                                                
|   0 | ALTER INDEX STATEMENT       |          |                                                                                                                
|   1 |  PX COORDINATOR             |          |                                                                                                                
|   2 |   PX SEND QC (ORDER)        | :TQ10001 |                                                                                                                
|   3 |    INDEX BUILD NON UNIQUE   | I        |                                                                                                                
|   4 |     SORT CREATE INDEX       |          |                                                                                                                
|   5 |      PX RECEIVE             |          |                                                                                                                
|   6 |       PX SEND RANGE         | :TQ10000 |                                                                                                                
|   7 |        PX BLOCK ITERATOR    |          |                                                                                                                
|   8 |         INDEX FAST FULL SCAN| I        |                                                                                                                
------------------------------------------------

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_PCLXUTIL ?!?!?!?!

Rajeshwaran, Jeyabal, January 22, 2016 - 2:04 pm UTC

Since it is of Local index (no global index in place), we can make use of DBMS_PCLXUTIL package calls.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4080843007946#9525415900346683861
Chris Saxon
January 22, 2016 - 4:11 pm UTC

Yep, that would work too.

A reader, January 22, 2016 - 2:20 pm UTC

Hello ,

we don't use alter Index rebuild.
I was talking about the index maintenance after a direct load. This is done in a single thread (still inside the insert Statement). Sorry for this misapprehension.
Chris Saxon
January 22, 2016 - 4:56 pm UTC

I'm not sure what you mean. Oracle still maintains the indexes during a direct path load:

http://docs.oracle.com/database/121/SUTIL/GUID-DD1AA9FC-B775-42E2-9BD9-5E43056A1418.htm#SUTIL1329

There's only maintenance after the load if you drop/disable them.

A reader, January 22, 2016 - 5:17 pm UTC

For each index there is a Sort/Merge Operation (one can see it in Toads Session Browser). This Sort Merge is done one after the other and still is Part of the direct load process.
As far as I know Oracle merges a temporary created Index into the real Index.
Connor McDonald
January 23, 2016 - 3:01 am UTC

yep.

Parallel Index Maintenance

Shimmy, January 26, 2016 - 9:56 pm UTC

Looking at the SQL you posted, you are not inserting in parallel, you are only fetching in parallel.
If you want to INSERT in parallel you can ALTER SESSSION and enable parallel DML and then specify PARALLEL HINT along with your APPEND
Or if you want to INSERT in parallel and also, do INDEX maintenance in PARALLEL, then do ALTER SESSION FORCE PARALLEL DML PARALLEL 4. That will do the index maintenance as in parallel.


Chris Saxon
January 27, 2016 - 2:04 am UTC

Thanks for your input

Wolfgang, February 01, 2016 - 2:54 pm UTC

Shimmy, that did it :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.