Creating Empty Partitioned Table With Create Table As Select in Parallel Creates Segments (Doc ID 1438774.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 05-Oct-2013***

Symptoms

Creating a partitioned table in parallel with CTAS, creates segments for all the partitions/subpartitions.

If we just create the table without parallel clause or create the table and load with insert/select, only the "required" partitions are created (leaving the others empty as per deferred segment feature).

The following testcase demonstrates the symptoms.

Starting 11g Release 2 deferred segment creation feature was introduced, and is in effect by default.
The value  of the parameter that controls the feature is deferred_segment_creation, and its default value is TRUE.

As expected no segment is created for the simple CREATE TABLE statement when creating empty table.

drop table test1;
drop table test2;
drop table test3;
create table test1 (c1 number, c2 number);

*************** CREATE TABLE ************************

create table test2 (c1 number, c2 number) parallel
PARTITION BY LIST(c1)
SUBPARTITION BY RANGE(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 VALUES LESS THAN(100),
SUBPARTITION SP2 VALUES LESS THAN(200))
(
PARTITION P1 VALUES(0),
PARTITION P2 VALUES(1001)
);

column segment_name format a20
select segment_name, sum(bytes/1024/1024), count(*), segment_type
from user_segments where segment_name in ('TEST1','TEST2','TEST3')
group by segment_name,segment_type;
-> no rows selected

 As expected no segment is created when inserting no rows into an existing empty table.

*************** INSERT AS SELECT ************************

insert into test2 SELECT C1,C2 FROM test1;

select segment_name, sum(bytes/1024/1024), count(*), segment_type
from user_segments where segment_name in ('TEST1','TEST2','TEST3')
group by segment_name,segment_type;
-> no rows selected


When Create Table As Select (CTAS) is used to create empty partitioned or sub-partitioned table in parallel, then segments are created which is unexpected.

*************** CTAS in PARALLEL ************************

create table test3 parallel
PARTITION BY LIST(c1)
SUBPARTITION BY RANGE(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 VALUES LESS THAN(100),
SUBPARTITION SP2 VALUES LESS THAN(200))
(
PARTITION P1 VALUES(0),
PARTITION P2 VALUES(1001)
)
as SELECT C1,C2 FROM test1;

select segment_name, sum(bytes/1024/1024), count(*), segment_type
from user_segments where segment_name in ('TEST1','TEST2','TEST3')
group by segment_name,segment_type;

SEGMENT_NAME         SUM(BYTES/1024/1024)   COUNT(*) SEGMENT_TYPE
-------------------- -------------------- ---------- ----------------------
TEST3                                  32          4 TABLE SUBPARTITION


If we remove parallel clause from the above CTAS, no segment is created for the empty table, again expected behaviour.

drop table test3 purge;
create table test3
PARTITION BY LIST(c1)
SUBPARTITION BY RANGE(c2)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 VALUES LESS THAN(100),
SUBPARTITION SP2 VALUES LESS THAN(200))
(
PARTITION P1 VALUES(0),
PARTITION P2 VALUES(1001)
)
as
SELECT C1,C2 FROM test1;

select segment_name, sum(bytes/1024/1024), count(*), segment_type
from user_segments where segment_name in ('TEST1','TEST2','TEST3')
group by segment_name,segment_type;
-> no rows selected



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms