Thanks for the question, Osama.

Asked: May 02, 2018 - 8:31 am UTC

Last updated: May 03, 2018 - 10:33 am UTC

Version: 12.2.0.1.0 - 64bit

Viewed 1000+ times

You Asked

Dears,

I have a problem regarding automatic list partitioning with hash sub-partitioning. the problem is the automatic created hash sub-partitions are not well distributed on the correct table spaces although the first hash-partitions are well distributed. the following example shows the problem :

I have created table as follow:

CREATE TABLE t3 (
  id           NUMBER,
  description  VARCHAR2(50),
  year number,
  created_date DATE,
  CONSTRAINT t3_pk PRIMARY KEY (year,id))
;

then I altered it this way to make it partitioned:

alter table t3 modify
partition by list (year) automatic 
subpartition by hash (id) subpartitions 3 store in (gls_ts_01,gls_ts_02,gls_ts_03)
(partition p1_2015 values (2015)) online;

and then run this query :

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='T3'

the result is fine as follow:

TABLE_NAME        PARTITION_NAME      SUBPARTITION_NAME      TABLESPACE_NAME
-------------          -----------------        ------------------     ----------------------
T3         P1_2015        SYS_SUBP2748      GLS_TS_01
T3         P1_2015        SYS_SUBP2749      GLS_TS_02
T3         P1_2015        SYS_SUBP2750      GLS_TS_03

as you can see, each sub-partition is stored in different tablespace which is fine.

when adding anothr 'year' value an automatic partition is created with hash sub-partitions :

insert into t3 values (19, 'sadasd', 2016 , '01/jan/16');

Now when running the same request I got the following result:

select TABLE_NAME,PARTITION_NAME,subPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions where TABLE_NAME='T3';

TABLE_NAME   PARTITION_NAME   SUBPARTITION_NAME  TABLESPACE_NAME
----------- --------------------- ------------------ ----------------------
T3         SYS_P2754       SYS_SUBP2752      GLS_TS
T3         SYS_P2754       SYS_SUBP2753      GLS_TS
T3         SYS_P2754       SYS_SUBP2751      GLS_TS
T3         P1_2015        SYS_SUBP2748      GLS_TS_01
T3         P1_2015        SYS_SUBP2749      GLS_TS_02
T3         P1_2015        SYS_SUBP2750      GLS_TS_03

as you notice all the new created hash sun-partitions are stored in the GLS-TS which is the list-partitions TS.

adding another different value for year will create 1 partition with 3 sub-partitions as follow:

insert into t3 values (42, 'sadasd', 2017 , '01/jan/17');

select TABLE_NAME,PARTITION_NAME,subPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions where TABLE_NAME='T3';

TABLE_NAME        PARTITION_NAME       SUBPARTITION_NAME      TABLESPACE_NAME
---------------------- ---------------------- ---------------------- ----------------------
T3         SYS_P2754       SYS_SUBP2752      GLS_TS
T3         SYS_P2754       SYS_SUBP2753      GLS_TS
T3         SYS_P2754       SYS_SUBP2751      GLS_TS
T3         P1_2015        SYS_SUBP2748      GLS_TS_01
T3         P1_2015        SYS_SUBP2749      GLS_TS_02
T3         P1_2015        SYS_SUBP2750      GLS_TS_03
T3         SYS_P2758       SYS_SUBP2755      GLS_TS
T3         SYS_P2758       SYS_SUBP2756      GLS_TS
T3         SYS_P2758       SYS_SUBP2757      GLS_TS

9 rows selected.

as you notice all the new created hash sun-partitions are stored wrongly in the wrong TS.

Is there is a way to manage the new created sub-partitions to be distributed on the correct TSs ?

your early response will be greatly appreciated.
Thanks in advance for your help.

Regards,
Osama

and Chris said...

You need to specify a subpartition template:

create tablespace ts1 datafile 'ts1.dbf' size 1m autoextend on;
create tablespace ts2 datafile 'ts2.dbf' size 1m autoextend on;
create tablespace ts3 datafile 'ts3.dbf' size 1m autoextend on;

CREATE TABLE t3 (
  id           NUMBER,
  description  VARCHAR2(50),
  year number,
  created_date DATE,
  CONSTRAINT t3_pk PRIMARY KEY (year,id)
);

alter table t3 modify
partition by list (year) automatic 
subpartition by hash (id) subpartition template (
  subpartition p1 tablespace ts1, 
  subpartition p2 tablespace ts2, 
  subpartition p3 tablespace ts3
) (
  partition p1_2015 values (2015)
) online;

select table_name,partition_name,subpartition_name,tablespace_name 
from   user_tab_subpartitions where table_name='T3';

TABLE_NAME   PARTITION_NAME   SUBPARTITION_NAME   TABLESPACE_NAME   
T3           P1_2015          P1_2015_P1          TS1               
T3           P1_2015          P1_2015_P2          TS2               
T3           P1_2015          P1_2015_P3          TS3   

insert into t3 values (19, 'sadasd', 2016 , date'2016-01-01');
insert into t3 values (42, 'sadasd', 2017 , date'2017-01-01');

select table_name,partition_name,subpartition_name,tablespace_name 
from   user_tab_subpartitions where table_name='T3';

TABLE_NAME   PARTITION_NAME   SUBPARTITION_NAME   TABLESPACE_NAME   
T3           P1_2015          P1_2015_P1          TS1               
T3           P1_2015          P1_2015_P2          TS2               
T3           P1_2015          P1_2015_P3          TS3               
T3           SYS_P731         SYS_SUBP729         TS2               
T3           SYS_P731         SYS_SUBP730         TS3               
T3           SYS_P731         SYS_SUBP728         TS1               
T3           SYS_P735         SYS_SUBP732         TS1               
T3           SYS_P735         SYS_SUBP733         TS2               
T3           SYS_P735         SYS_SUBP734         TS3

Rating

(3 ratings)

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

Comments

Thanks so much for your prompt reply

Osama Ammar, May 02, 2018 - 10:57 am UTC

thanks so much Chris,
another related two questions are
1- can I store the list partitions in one TB and all the other hash partitions in the three different TBs??

2-can I alter the already partitioned tables with the old way i sent you before by adding subpartition template or I have to drop tables, recreate them and then apply the subpartition template ????

note, I have data in the already partitioned tables.

May 03, 2018 - 10:32 am UTC

See the answers below.

If you want to change the tablespace for existing (sub)partitions, you can move them:

select table_name,partition_name,subpartition_name,tablespace_name 
from   user_tab_subpartitions where table_name='T3';

TABLE_NAME   PARTITION_NAME   SUBPARTITION_NAME   TABLESPACE_NAME   
T3           P1_2015          P1_2015_P1          TS1               
T3           P1_2015          P1_2015_P2          TS2               
T3           P1_2015          P1_2015_P3          TS3  

alter table t3 move subpartition P1_2015_P2 tablespace ts1;
alter table t3 move subpartition P1_2015_P3 tablespace ts1;

select table_name,partition_name,subpartition_name,tablespace_name 
from   user_tab_subpartitions where table_name='T3';

TABLE_NAME   PARTITION_NAME   SUBPARTITION_NAME   TABLESPACE_NAME   
T3           P1_2015          P1_2015_P1          TS1               
T3           P1_2015          P1_2015_P2          TS1               
T3           P1_2015          P1_2015_P3          TS1

answers to above questions

Rajeshwaran, Jeyabal, May 02, 2018 - 3:01 pm UTC

#1 - once a table is sub partitioned, then subpartitions become physical segments (that consume storage in the tablespace), partitions become logical containers -no storage in tablespace is consumed at partition level.

so you can't have partitions in one tablespace and subpartitions in another tablespace, only the sub-partitions consume the real storage.

demo@ORA12C> create table t
  2  partition by list( yr ) automatic
  3  subpartition by hash(object_id)
  4  subpartition template
  5  ( subpartition sp1 tablespace users,
  6    subpartition sp2 tablespace ts_data)
  7  ( partition p_2017 values (2017) ,
  8    partition p_2018 values (2018)  )
  9  as
 10  select a.*, extract(year from created) yr
 11  from all_objects a;

Table created.

demo@ORA12C> select segment_name,partition_name,segment_type,tablespace_name
  2  from user_segments
  3  where segment_name ='T' ;

SEGME PARTITION_NAME  SEGMENT_TYPE         TABLESPACE
----- --------------- -------------------- ----------
T     P_2017_SP1      TABLE SUBPARTITION   USERS
T     P_2017_SP2      TABLE SUBPARTITION   TS_DATA
T     P_2018_SP1      TABLE SUBPARTITION   USERS
T     P_2018_SP2      TABLE SUBPARTITION   TS_DATA

demo@ORA12C>

#2 - you can add subpartition template at anytime you need. but the subpartition template will take place only for the newly added partitions, and not impact the old/existing partitions in place.

demo@ORA12C> create table t
  2  partition by list( yr ) automatic
  3  subpartition by hash(object_id) subpartitions 2
  4  ( partition p_2017 values (2017) ,
  5    partition p_2018 values (2018)  )
  6  as
  7  select a.*, extract(year from created) yr
  8  from all_objects a;

Table created.

demo@ORA12C> select segment_name,partition_name,segment_type,tablespace_name
  2  from user_segments
  3  where segment_name ='T' ;

SEGME PARTITION_NAME  SEGMENT_TYPE         TABLESPACE
----- --------------- -------------------- ----------
T     SYS_SUBP12561   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12562   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12563   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12564   TABLE SUBPARTITION   TS_DATA

demo@ORA12C> alter table t set subpartition template (
  2     subpartition sp1 tablespace ts_data,
  3     subpartition sp2 tablespace users
  4  ) ;

Table altered.

demo@ORA12C>
demo@ORA12C> select segment_name,partition_name,segment_type,tablespace_name
  2  from user_segments
  3  where segment_name ='T';

SEGME PARTITION_NAME  SEGMENT_TYPE         TABLESPACE
----- --------------- -------------------- ----------
T     SYS_SUBP12561   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12562   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12563   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12564   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12581   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12582   TABLE SUBPARTITION   USERS
T     SYS_SUBP12584   TABLE SUBPARTITION   TS_DATA
T     SYS_SUBP12585   TABLE SUBPARTITION   USERS

8 rows selected.

demo@ORA12C>

May 03, 2018 - 10:33 am UTC

Yep, thanks for explaining.

Thanks so much for your prompt reply

Osama Ammar, May 03, 2018 - 7:12 am UTC

Despite of thousands of requests, I really appreciate you prompt reply. that is what make Oracle as Oralce. well done

best wishes,

Osama

May 03, 2018 - 10:21 am UTC

You're welcome!

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks so much for your prompt reply

answers to above questions

Thanks so much for your prompt reply

More to Explore

Design

Why are all table subpartitions going in the same tablespace?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks so much for your prompt reply

answers to above questions

Thanks so much for your prompt reply

More to Explore

Design