Following up on Chris Antognini’s findings regarding deferred segment creation (here and here) I noticed another restriction that still finds no mention even in the latest Oracle documentation. I vaguely remember to have come across some issues in lower 11gR2 releases and some of them have been fixed in later patch sets. The problem went along the line of:

alter session set deferred_segment_creation = true;

create table t2 parallel 4
as 
  select /*+ parallel (t1, 4) */ *
  from t1 where 1 = 2
;

select segment_name, segment_type from user_segments where segment_name = 'T1';
SEGMENT_NAME SEGMENT_TYPE
------------ ------------
T1           TABLE

Note: This test war run against Oracle 11.2.0.1 as this particular issue is fixed in at least 11.2.0.4 and later

Now, why in gods name would you run CTAS in parallel when you know the query does not return any rows you might think.
Well, the problem becomes more subtle when we start combining features, namely partitioning and parallel DDL. Following I have prepared a few test cases to demonstrate the issue.

First let’s create the source table to select from:

create table t1
as
  select
      rownum id
    , round(dbms_random.value(1,4), 0) code
  from dual connect by level <= 100
;

The CODE column contains four distinct number values ranging from 1 to 4.

Next, we create a list partitioned table by using CTAS (create table as select) from our source table. All parts of this SQL run serially:

create table t3
segment creation deferred
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T3' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T3           TABLE PARTITION   CODE_1

This works as expected. Only the relevant partition was materialized as a segment to hold the data for CODE value 1.

This time, we create the same table structure using parallel DDL:

create table t4
segment creation deferred
parallel 4
partition by list (code) (
    partition code_1 values (1)
  , partition code_2 values (2)
  , partition code_3 values (3)
  , partition code_4 values (4)
)
as
  select *
  from t1
  where code = 1
;

select segment_name, segment_type, partition_name from dba_segments where owner = 'TESTUSER' and segment_name = 'T4' order by segment_name, partition_name;
SEGMENT_NAME SEGMENT_TYPE      PARTITION_NAME
------------ ---------------   --------------
T4           TABLE PARTITION   CODE_1
T4           TABLE PARTITION   CODE_2
T4           TABLE PARTITION   CODE_3
T4           TABLE PARTITION   CODE_4

Oracle has materialized all four partitions even though only one of them contains data. The same also happens when the source query doesn’t return any rows, e.g. when CODE = 5.

Usually, there’s not much sense in running DDL in parallel while the SELECT providing the data runs serially. I have left out parallel processing of the SELECT part for simplicity as my tests have shown that it doesn’t affect the segment creation in any way.

Foot note: All test have been run on Oracle 12.1.0.2 and 11.2.0.4 except when where otherwise