Unnesting of coalesced subqueries

The 12cR1 way used by Oracle to coalesce two correlated disjunctive subqueries has prevented a real life query from unnesting its two subqueries causing a serious performance issue. This article examines a representative model of this real life query. The first paragraph explains the any-to-exists subquery conversion. The second paragraph shows how two correlated exists subqueries can be coalesced to form a single subquery. Once these two transformations introduced the third paragraph will examine how the previously coalesced subqueries can be unnested. It will particularly show that the ability of Oracle to unnest two coalesced correlated subqueries depends strongly on the coalescing method as this one differs from version to version.

1. Any-to-exists subquery conversion

Before coalescing two (or more) subqueries Oracle starts first by transforming ANY and ALL subqueries into EXISTS and NOT EXISTS respectively. Consider, for example, the following query:

select
    a.id1
   ,a.n1
   ,a.start_date
from 
    t1 a
where 
   (a.id1 in
         (select b.id
          from t2 b
          where b.status = 'COM')
   or a.id1 in
         (select c.id1
          from t2 c
          where c.status = 'ERR')
     );

If you examine its corresponding 10053 CBO trace file you will find that its two disjunctive subqueries have been first converted from an any-to-exists subqueries as shown below:

Query After Exists Conversion:******* UNPARSED QUERY IS *******

select
  a.id1 id1,
  a.n1 n1,
  a.start_date start_date
from
  t1 a
where
  exists
  ( select b.id id
    from t2 b
    where b.status='COM'
    and   a.id1 =b.id)
or exists
  ( select c.id1 id1
    from t2 c
    where c.status='ERR'
    and a.id1 =c.id1)
  );
 

This is generally the preduled to or, a preliminary part of, a subquery coalescing transformation presented in the next section

2. Subquery Coalescing

The preceding any-to-exists transformed query is nothing else than an instance of the following simplified one:

select 
  {list of columns}
from
  table
where 
  predicate

The predicate in this particular case is acting as two disjunctive correlated subqueries of the same type:

-- first subquery
exists
  ( select b.id id
    from t2 b
    where b.status = 'COM'
    and   a.id1    = b.id)

-- second subquery
 exists
  ( select c.id1 id1
    from t2 c
    where c.status ='ERR'
    and a.id1 = c.id1)

If these two subqueries are not coalesced they will probably generate two accesses to the same table t2. This is why Oracle can, under certain conditions, coalesce two conjunctive or disjucntive subqueries into a single one. Applied to the current query the coalescing process yields the following new transformed query:

Query After Ex/Any Disj SQ coalescing:******* UNPARSED QUERY IS *******

select
  a.id1 id1,
  a.n1 n1,
  a.start_date start_date
from
  t1 a
where
  exists
  (
    select
      0 id
    from
      t2 b
    where
      b.status  = 'COM'
    and a.id1   = b.id
    or b.status ='ERR'
    and a.id1   = b.id1
  );

The execution plan of the doubly transformed query is shown below:

alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   9890 |
|*  1 |  FILTER            |      |      1 |        |   9890 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |  10000 |  10000 |
|*  3 |   TABLE ACCESS FULL| T2   |  10000 |      1 |   9890 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter((("B"."ID"=:B1 AND "B"."STATUS"='COM') OR ("B"."ID1"=:B2 AND
              "B"."STATUS"='ERR')))

If you still have any doubts about whether the two subqueries have been coalesced or not then the single apparition of table T2 in the plan together with the predicate n°3 will certainly clear out this doubt for good. Indeed, it is the subquery coalescing process that resulted into Oracle visiting table T2 only one time.

In addition in the outline data used to generate the above execution plan reproduced here below:

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$ACD206C8")
      COALESCE_SQ(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$7C83FF7B")
      COALESCE_SQ(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$1" "A"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$ACD206C8" "B"@"SEL$2")
      END_OUTLINE_DATA
  */

The COALESCE_SQ(@”SEL$3″) and COALESCE_SQ(@”SEL$2″)represent the hints used to force a subquery coalescing transformation. If you want to cancel such a subquery coalescing transformation then the NO_COALESCE_SQ(qb_name) hint will be the one you should use. You can also change the hidden parameter _optimizer_coalesce_subqueries governing this transformation which defaults to true in 12cR1.

3. Subquery unnesting
        3.1. The 12cR1 effect

The any-to-exist conversion followed by the subquery coalescing should have normally paved the way for subquery unnesting transformation. Unfortunately, the real life query, which the actual model is a true representation of, the coalesced subqueries have not been unnested altering dramatically the performance of the original query as shown via its corresponding sql monitor report:

-- Get the report_id corresponding to the triplet (sql_id,sql_exec_id, sql_exec_start)
 select 
      report_id
    , key1 sql_id
    , key2 sql_exec_id
    , key3 sql_exec_start
  from
     dba_hist_reports
  where
     component_name ='sqlmonitor'
  and key1 = '97r22vwap7x9t';

--  Use this report_id to get the historical SQL monitor report
select
     dbms_auto_report.report_repository_detail
     (rid   => 8934
     ,type => 'text')
  from dual;

Global Information
---------------------  
 Status                :  DONE (ALL ROWS)       
 Instance ID           :  1                     
 Session               :  skskslsls (1343:42100) 
 SQL ID                :  97r22vwap7x9t         
 SQL Execution ID      :  16777216              
 Execution Started     :  06/27/2017 11:11:35   
 First Refresh Time    :  06/27/2017 11:11:41 
 Last Refresh Time     :  06/27/2017 12:02:57 
 Duration              :  3082s                    
 Module/Action         :  xxxxxxxxxxxx          
 Service               :  xxxxxxxxx          
 Program               :  xxxxxxxxxxx                  
 Fetch Calls           :  13             

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    3083 |    3079 |     3.67 |    13 |  294M  |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=4071256796)
========================================================================
| Id |               Operation |   Name   |  Rows   | Execs |   Rows   |
|    |                         |          | (Estim) |       | (Actual) |
========================================================================
|  0 | SELECT STATEMENT        |          |         |     1 |     1010 |
|  1 |   FILTER                |          |         |     1 |     1010 |
|  2 |    TABLE ACCESS FULL    | TABLE1   |   89457 |     1 |    89457 |
|  3 |    TABLE ACCESS FULL    | TABLE2   |       1 | 89457 |     1010 |
========================================================================

The FILTER at operation n°1 acts exactly as NESTED LOOPS join would do: for each row from table TABLE1 (A-Rows=89,457) looks for rows in table TABLE2 (Starts = 89,457) that satisfy the filter predicate n°3.

This is exactly what happens when a correlated subquery is not unnested: it will be evaluated as many times as there are rows in the outer query block.

       3.2. The 11gR2 effect

The on call DBA told me that this query was performing very well before the upgrade. My immediate reflex was then to run the same query under the previous 14 OFE(Optimizer Features Enable) . Useless to say that it completed very quickly using the following execution plan:

Plan hash value: 3372386476
-------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Time     |
-------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |       |          |
|*  1 |  HASH JOIN            |          |  2202 | 00:00:01 |
|   2 |   VIEW                | VW_NSO_1 |  2202 | 00:00:01 |
|   3 |    HASH UNIQUE        |          |  2202 | 00:00:01 |
|   4 |     UNION-ALL         |          |       |          |
|*  5 |      TABLE ACCESS FULL| TABLE2   |  1101 | 00:00:01 |
|*  6 |      TABLE ACCESS FULL| TABLE2   |  9890 | 00:00:01 |
|   7 |   TABLE ACCESS FULL   | TABLE1   | 89457 | 00:00:01 |
-------------------------------------------------------------

The presence of the term VW_NSO_1 at operation id n°2 in the previous plan is a sign of Oracle having unnested the two correlated subqueries in order to eliminate the prolific FILTER effect of multi-starting the TABLE2outer full scan operation.

However, the presence of TABLE2 twice in the execution plan tends to suggest that Oracle has not used the 12cR1 technic to coalesce the two correlated subqueries. Indeed in contrast to 12cR1, in 11gR2 Oracle has merged the two subqueries into a single one but without eliminating the two select against TABLE2. In the corresponding 10053 trace file we can read the following lines:

Final query after transformations:******* UNPARSED QUERY IS *******

select
  a.id1 id1,
  a.n1 n1,
  a.start_date start_date
from
  (  (select c.id1 id1 from t2 c where c.status='ERR')
    union
     (select b.id id from t2 b where b.status='COM')
  ) vw_nso_1,
  t1 a
where
  a.id1= vw_nso_1.id1;

-- 11.2.0.4
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery removal for query block SEL$3 (#2)
RSW: Not valid for subquery removal SEL$3 (#2)
Subquery unchanged.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#3)
SU:   Checking validity of unnesting subquery SEL$3 (#2)
../..
SU:   Checking validity of unnesting subquery SET$E74BECDC (#6)
SU:   Passed validity checks.
SU:   Transform an ANY subquery to semi-join or distinct.

With the OFE set to 11.2.0.4 Oracle has transformed the two disjunctive subqueries into a single subquery in which the dijsunction has been replaced by a union operation (instead of union all because a distinct operation has to be applied for the in-list elements). The result of this transformation has been transformed into a inline view before being unnested with the outer block query:

Registered qb: SET$7FD77EFD 0x1c710c88 (SUBQ INTO VIEW FOR COMPLEX UNNEST SET$E74BECDC)

Thanks to the unnesting of the inline view ( VW_NSO_1) a more peformant and suitable hash join (at least for the real life query) has been used instead of the mutli-executed filter operation.

4. Why the unnesting has not been possible in 12cR1?

As always the 10053 trace file reproduced below (reduced to the bare minimum) gives an answer to the posed question:

-- 12.1.0.1
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$ACD206C8 (#2)
SU: SU bypassed: Invalid correlated predicates.
SU: Validity checks failed.
*******************************

It seems that Oracle has been unable to unnest the two subqueries it has coalesced because they are using two different correlated predicates as shown below:

where
  exists
  (
    select
      0 id
    from
      t2 b
    where
      b.status  = 'COM'
    and a.id1   = b.id  --1st correlated predicate
    or b.status ='ERR'
    and a.id1   = b.id1 -- 2nd correlated predicate
  );

Had those two correlated predicates been identical the unnesting of the coalesced disjunctive correlated subqueries would have been possible as demonstrated below:

explain plan for
select
    a.id1
   ,a.n1
   ,a.start_date
from 
    t1 a
where 
   (a.id1 in
         (select b.id1 –- spot the change here
          from t2 b
          where b.status = 'COM')
   or a.id1 in
         (select c.id1
          from t2 c
          where c.status = 'ERR')
        );

select * from table(dbms_xplan.display(format =>'+outline'));

------------------------------------------------------
| Id  | Operation                    | Name  | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT             |       |    10 |
|   1 |  NESTED LOOPS                |       |    10 |
|   2 |   NESTED LOOPS               |       |    10 |
|   3 |    SORT UNIQUE               |       |  9890 |
|*  4 |     TABLE ACCESS FULL        | T2    |  9890 |
|*  5 |    INDEX UNIQUE SCAN         | T1_PK |     1 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |
------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      SEMI_TO_INNER(@"SEL$82F4A621" "B"@"SEL$2")
      NLJ_BATCHING(@"SEL$82F4A621" "A"@"SEL$1")
      USE_NL(@"SEL$82F4A621" "A"@"SEL$1")
      LEADING(@"SEL$82F4A621" "B"@"SEL$2" "A"@"SEL$1")
      INDEX(@"SEL$82F4A621" "A"@"SEL$1" ("T1"."ID1"))
      FULL(@"SEL$82F4A621" "B"@"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      COALESCE_SQ(@"SEL$2")
      OUTLINE(@"SEL$7C83FF7B")
      COALESCE_SQ(@"SEL$3")
      OUTLINE(@"SEL$ACD206C8")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$ACD206C8")
      OUTLINE_LEAF(@"SEL$82F4A621")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("B"."STATUS"='COM' OR "B"."STATUS"='ERR')
   5 - access("A"."ID1"="B"."ID1")

Note
-----
   - this is an adaptive plan

5. Summary

The Oracle way of coalescing subqueries seems to be going back and forth between different versions. Two dijsunctive correlated subqueries have been differently coalesced in 11.2.0.4 and 12.1.0.1. While in the former they are classically transformed into a single subquery combining two branches of a union of two tables, in the later release they have been coalesced to form a single subquery accesing a single table. The 12cR1 and 12cR2 way of coalescing two correlated subqueries introduces a new requirement for their unnesting: they need to have the same correlated predicates. Thereby you should watch out carrefully your 12c queries having two (or more) correlated subqueries. If they are using different (invalid:-)) predicates they might be still coalesced but they will not be unnested. If you judge that the unnesting of your subqueries is better than using their coalesced version as a predicate of a FILTER operation then you have at your dispoable the NO_COALESCE_SQ(qb_name)or the _optimizer_coalesce_subqueries hidden parameter to cancel the subqueries coalescing transformation.

6. Bonus

Parallel index rebuild: Oracle versus MS-SQL Server

A couple of months ago I have been prompted by this MS-SQL Server article about rebuilding indexes in parallel. The bottom line of this article is that MS-SQL Server engine will not use parallel run when rebuilding a unique index starting with a column having a single distinct value. This article will first demonstrate this claim using MS-SQL Server 2016 and then extend the same demonstration to Oracle in its latest version.

1. SQL Server

The followings will create a table and its unique three columns composite clustered index

select @@version
Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)  
Dec 13 2016 04:40:28   Copyright (c) Microsoft Corporation 
Developer Edition (64-bit) on Windows 8.1 6.3 <X64> (Build 9600: ) 

-- create table
create table t (c1 int, c2 int, c3 int);

set nocount on
begin tran
declare @i int = 0
while @i < 10000000
begin
    insert into t values (@i, @i, @i)
    set @i = @i + 1
end
commit tran

-- create a unique clustered index
create unique clustered index ix on t(c1,c2,c3)

Now that we are done with the model we are going to rebuild the above clustered index using parallel run

-- This will use parallel plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

Before rebuilding the index I have activated the ”Include Actual Execution Plan” icon of SSMS (SQL Server Management Studio) so that I got the following “row source” plan where the two yellow arrows indicate that the rebuild has been, indeed, parallelized:

However has the first column c1 of the index been single valued the index would have not been rebuild parallely by the SQL Server engine as the followings demonstrate:

-- set the leading column of the clustered index to a single value
 update t set c1 = 1

-- update statistics with 100% sample
update statistics t with fullscan

–- This will use serial plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

The execution plan of the second index rebuild show that this time MS-SQL refuses to obey the parallel hint and decides to go serially as shown below:

The MS-SQL documentation stipulates that when the leading key of the index of a non-partitioned table has a single distinct value it becomes useless to give a parallel thread a range of single value to process them. In other words the leading column should have multiple distinct values in order for parallelism to be worth a utilization.

Now that we have set the scene for MS-SQL let’s reproduce it for Oracle

2. Oracle

SQL> select banner from v$version where rownum=1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t (c1 int, c2 int, c3 int);

SQL> begin
       for j in 1..10000000
     loop
        insert /*+ append */ into t values (j, j, j);
     end loop;
     commit;
     end;
     /

SQL> create unique index ix on t(c1,c2,c3);

SQL> exec dbms_stats.gather_table_stats(user, 't');

SQL> select 
         column_name
         ,num_distinct
         ,histogram
    from 
        all_tab_col_statistics
    where table_name = 'T'
    order by 2;

COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ------------ ---------------
C1              9914368 NONE
C3              9914368 NONE
C2              9914368 NONE

It’s now time to use parallelism to rebuild the above index

SQL> alter index ix rebuild parallel 4;

select
    dfo_number, tq_id, server_type, instance, process, num_rows
from
   v$pq_tqstat
order by
    dfo_number, tq_id, server_type, instance, process;

DFO_NUMBER      TQ_ID SERVER_TYPE    INSTANCE PROCESS    NUM_ROWS
---------- ---------- ------------ ---------- -------- ----------
         1          0 Consumer              1 P000        2923571
         1          0 Consumer              1 P001        2623371
         1          0 Consumer              1 P002        2270919
         1          0 Consumer              1 P003        2182139
         1          0 Producer              1 P004        2638901
         1          0 Producer              1 P005        2637316
         1          0 Producer              1 P006        2611722
         1          0 Producer              1 P007        2114251
         1          0 Ranger                1 QC               12
         1          1 Consumer              1 QC                4
         1          1 Producer              1 P000              1
         1          1 Producer              1 P001              1
         1          1 Producer              1 P002              1
         1          1 Producer              1 P003              1


Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |
|                |       |         | Time(s) | Time(s) | Waits(s) |
===================================================================
| PX Coordinator | QC    |         |    0.10 |    0.02 |     0.01 |
| p000           | Set 1 |       1 |    5.53 |    4.12 |     0.02 |
| p001           | Set 1 |       2 |    5.48 |    3.60 |     0.01 |
| p002           | Set 1 |       3 |    4.87 |    3.20 |     0.03 |
| p003           | Set 1 |       4 |    4.97 |    3.03 |     0.01 |
| p004           | Set 2 |       1 |    1.05 |    1.05 |          |
| p005           | Set 2 |       2 |    1.01 |    0.83 |          |
| p006           | Set 2 |       3 |    1.04 |    1.03 |          |
| p007           | Set 2 |       4 |    0.99 |    0.84 |          |
===================================================================

SQL Plan Monitoring Details (Plan Hash Value=3277881472)
===========================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    |
|    |                            |          | (Estim) |      | Active(s) |
===========================================================================
|  0 | CREATE INDEX STATEMENT     |          |         |      |         1 |
|  1 |   PX COORDINATOR           |          |         |      |         1 |
|  2 |    PX SEND QC (ORDER)      | :TQ10001 |      82 |      |         1 |
|  3 |     INDEX BUILD UNIQUE     | IX       |         |      |         4 |
|  4 |      SORT CREATE INDEX     |          |      82 |      |         6 |
|  5 |       PX RECEIVE           |          |      82 |    2 |         4 |
|  6 |        PX SEND RANGE       | :TQ10000 |      82 |    2 |         2 |
|  7 |         PX BLOCK ITERATOR  |          |      82 |    2 |         1 |
|  8 |          TABLE ACCESS FULL | T        |      82 |    2 |         1 |
===========================================================================

As expected Oracle, in accordance with MS-SQL Server, has obeyed the instruction of rebuilding the index in parallel.

Let’s now unify the distinct values of the leading column c1 of the index and then try rebuilding it parallely:

 SQL> update t set c1 = 1;

SQL> exec dbms_stats.gather_table_stats(user, 't');

SQL> select 
         column_name
         ,num_distinct
         ,histogram
      from 
        all_tab_col_statistics
      where table_name = 'T'
      order by 2;

COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ------------ ----------
C1                    1 NONE
C3              9914368 NONE
C2              9914368 NONE

SQL> alter index ix rebuild parallel 4;

select
    dfo_number, tq_id, server_type, instance, process, num_rows
from
   v$pq_tqstat
order by
    dfo_number, tq_id, server_type, instance, process;
DFO_NUMBER      TQ_ID SERVER_TYPE     INSTANCE PROCESS     NUM_ROWS
---------- ---------- ------------- ---------- --------- ----------
         1          0 Consumer               1 P000         2923571
         1          0 Consumer               1 P001         2623371
         1          0 Consumer               1 P002         2270919
         1          0 Consumer               1 P003         2182139
         1          0 Producer               1 P004         2694213
         1          0 Producer               1 P005         2542784
         1          0 Producer               1 P006         2814202
         1          0 Producer               1 P007         1950991
         1          0 Ranger                 1 QC                12
         1          1 Consumer               1 QC                 4
         1          1 Producer               1 P000               1
         1          1 Producer               1 P001               1
         1          1 Producer               1 P002               1
         1          1 Producer               1 P003               1

Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    |
|                |       |         | Time(s) | Time(s) | Waits(s) |
===================================================================
| PX Coordinator | QC    |         |    0.78 |    0.02 |     0.01 |
| p000           | Set 1 |       1 |    5.16 |    3.95 |     0.01 |
| p001           | Set 1 |       2 |    5.10 |    3.56 |     0.01 |
| p002           | Set 1 |       3 |    4.09 |    3.15 |     0.01 |
| p003           | Set 1 |       4 |    4.70 |    3.23 |     0.00 |
| p004           | Set 2 |       1 |    1.38 |    1.08 |     0.02 |
| p005           | Set 2 |       2 |    1.26 |    1.23 |     0.03 |
| p006           | Set 2 |       3 |    1.32 |    1.12 |     0.03 |
| p007           | Set 2 |       4 |    1.31 |    0.99 |     0.03 |
===================================================================

SQL Plan Monitoring Details (Plan Hash Value=3277881472)
===========================================================================
| Id |         Operation          |   Name   |  Rows   | Cost |   Time    |
|    |                            |          | (Estim) |      | Active(s) |
===========================================================================
|  0 | CREATE INDEX STATEMENT     |          |         |      |         2 |
|  1 |   PX COORDINATOR           |          |         |      |         7 |
|  2 |    PX SEND QC (ORDER)      | :TQ10001 |     10M |      |         1 |
|  3 |     INDEX BUILD UNIQUE     | IX       |         |      |         4 |
|  4 |      SORT CREATE INDEX     |          |     10M |      |         5 |
|  5 |       PX RECEIVE           |          |     10M | 2397 |         3 |
|  6 |        PX SEND RANGE       | :TQ10000 |     10M | 2397 |         2 |
|  7 |         PX BLOCK ITERATOR  |          |     10M | 2397 |         1 |
|  8 |          TABLE ACCESS FULL | T        |     10M | 2397 |         1 |
===========================================================================		

In contrast to MS-SQL server which refuses to use parallelism when rebuilding a unique clustered index starting with a column having a unique distinct value, Oracle has successfully rebuild the same kind of unique index in approximatively the same execution time.

3. SUMMARY

We’ve considered two instances of the same unique composite index:

1. A unique composite index starting with a column having a unique distinct value
2. A unique composite index starting with a more selective column

It is interesting to see that Oracle, in contrast to MS-SQL Server, is in the capacity of using parallelism when rebuilding both type of indexes. In addition Oracle rebuild execution time of the second type of indexes is as good as that of the first type.