Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, guest.

Asked: November 04, 2015 - 5:11 pm UTC

Last updated: January 22, 2016 - 7:51 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Oracle database version: 11.2.0.3

The following syntax works in my test:

alter table t1 truncate partition p1 update global indexes parallel 8;

However, I cannot find proof that Oracle really used parallelism when updating global indexes.

Can Oracle use parallelism for updating global indexes?

Thanks!

and Connor said...

Here's a demo

SQL> create table t1 ( x int, y int , z int )
  2  partition by list (x )
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3)
  6  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t1
  2  select 1, rownum, rownum
  3  from ( select 1 from dual connect by level <= 1000 ),
  4       ( select 1 from dual connect by level <= 10000 );

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert /*+ APPEND */ into t1
  2  select 2, rownum, rownum
  3  from ( select 1 from dual connect by level <= 1000 ),
  4       ( select 1 from dual connect by level <= 10000 );

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert /*+ APPEND */ into t1
  2  select 3, rownum, rownum
  3  from ( select 1 from dual connect by level <= 1000 ),
  4       ( select 1 from dual connect by level <= 10000 );

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index IX1 on T1 ( y ) ;

Index created.

SQL> create index IX2 on T1 ( z ) ;

Index created.

SQL>
SQL> alter table t1 truncate partition p1 update global indexes parallel 4;
[running]


THen in another session I check to see what other sessions have been fired off

SQL> select sid, status, username, sql_id from v$session where username = 'SCOTT';

       SID STATUS   USERNAME                                      SQL_ID
---------- -------- --------------------------------------------- -------------
        12 ACTIVE   SCOTT                                         g4w438qx7f5h7
        13 ACTIVE   SCOTT                                         g4w438qx7f5h7
       125 ACTIVE   SCOTT                                         g4w438qx7f5h7
       126 ACTIVE   SCOTT                                         g4w438qx7f5h7
       242 ACTIVE   SCOTT                                         g4w438qx7f5h7
       243 ACTIVE   SCOTT                                         g4w438qx7f5h7
       245 INACTIVE SCOTT                                         g4w438qx7f5h7
       360 ACTIVE   SCOTT                                         g4w438qx7f5h7
       365 INACTIVE SCOTT                                         g4w438qx7f5h7
       
       
SQL> @text g4w438qx7f5h7

CHILD_NUMBER SQL_FULLTEXT
------------ --------------------------------------------------------------------------------
           0 insert /*+ RELATIONAL("T1") PARALLEL("T1",4) APPEND NESTED_TABLE_SET_SETID NO_RE
             F_CASCADE */ into "SCOTT"."T1" partition ("P1") (select /*+ RELATIONAL("T1") P
             ARALLEL("T1",4) */  *  from "SCOTT"."T1" partition ("P1") ) delete global inde
             xes
             



So yes, parallel facilities can be used.

(This test in 11.2.0.4, and I'm the only user of this database)

Rating

  (2 ratings)

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

Comments

update global index with parallel

guest, November 05, 2015 - 4:05 pm UTC

Thanks Connor for the test case!

Thanks

A reader, January 22, 2016 - 6:43 am UTC

update global indexes internally calls the insert statement. its interesting
Connor McDonald
January 22, 2016 - 7:51 am UTC

If you trace Oracle operations, often you'll see all sorts of extended syntax stuff going on.

Which is interesting, and good to know .... but doesnt mean you can use it :-)

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.