Today my friend asked me a question. Is it possible to alter a partitioned table. I said, ya you can. Then he twisted his question. If the partitioned table is having subpartition template, then adding a partition to an already partitioned table will consider the subpartion template or should be specified explicitly.
For whom, who doesn’t know about subpartition template. Here are some brief idea on subpartition template. You can create subpartitions in a composite partitioned table using a subpartition template. Whenever a composite partition table is having a subpartition descriptor, it may be required to specify the subpartition to every partition. Instead of doing that, you describe subpartitions only once in a template, then apply that subpartition template to every partition in the table.
Below is a simple demo.
I have created a table sales having a range partition with a subpartion by list. The partitioned table is having a subpartition template dividing each zones.
16:14:50 scott@ORCL>CREATE TABLE sales
16:15:01 2 ( deptno number, item_no varchar2(20),
16:15:01 3 txn_date date, txn_amount number, state varchar2(2))
16:15:01 4 PARTITION BY RANGE (txn_date)
16:15:01 5 SUBPARTITION BY LIST (state)
16:15:01 6 SUBPARTITION TEMPLATE
16:15:01 7 (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE users,
16:15:01 8 SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE users,
16:15:01 9 SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE users,
16:15:01 10 SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE users,
16:15:01 11 SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE users,
16:15:01 12 SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE users,
16:15:01 13 SUBPARTITION others VALUES (DEFAULT ) TABLESPACE users
16:15:01 14 )
16:15:01 15 (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
16:15:01 16 PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
16:15:01 17 PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
16:15:01 18 PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
16:15:01 19 );
Table created.
Elapsed: 00:00:00.70
16:15:03 scott@ORCL>
Now, altering the table by adding new partition to it.
16:15:03 scott@ORCL>ALTER TABLE sales ADD PARTITION q5_1999 VALUES LESS THAN ( TO_DATE('01-JUN-2000','DD-MON-YYYY'));
Table altered.
Elapsed: 00:00:00.38
16:15:58 scott@ORCL>
Now lets check whether the subpartition has been added to the new partition for which the subpartition templates was specified
16:31:19 scott@ORCL>col TABLE_OWNER for a11
16:31:19 scott@ORCL>col PARTITION_NAME for a14
16:31:19 scott@ORCL>col HIGH_VALUE for a10
16:31:19 scott@ORCL>col TABLE_NAME for a21
16:31:19 scott@ORCL>col SUBPARTITION_NAME for a17
16:31:19 scott@ORCL>col TABLESPACE_NAME for a15
16:31:19 scott@ORCL>select TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME from USER_TAB_SUBPARTITIONS where table_name='SALES' and
PARTITION_NAME='Q5_1999';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH SUBPARTITION_POSITION TABLESPACE_NAME
--------------------- -------------- ----------------- ---------- ----------------- --------------------- ---------------
SALES Q5_1999 Q5_1999_NORTHWEST 'OR', 'WA' 10 1 USERS
SALES Q5_1999 Q5_1999_SOUTHWEST 'AZ', 'UT' 16 2 USERS
, 'NM'
SALES Q5_1999 Q5_1999_NORTHEAST 'NY', 'VM' 16 3 USERS
, 'NJ'
SALES Q5_1999 Q5_1999_SOUTHEAST 'FL', 'GA' 10 4 USERS
SALES Q5_1999 Q5_1999_MIDWEST 'SD', 'WI' 10 5 USERS
SALES Q5_1999 Q5_1999_SOUTH 'AL', 'AK' 10 6 USERS
SALES Q5_1999 Q5_1999_OTHERS DEFAULT 7 7 USERS
7 rows selected.
Elapsed: 00:00:00.04
16:31:20 scott@ORCL>
It can be seen the subpartition for the new partition has been assigned with the subpartition template specified for the partitioned table.
Further going with the script,
16:40:10 scott@ORCL>set long 1000000
16:40:21 scott@ORCL>execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:40:23 scott@ORCL>execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:40:23 scott@ORCL>execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
16:40:24 scott@ORCL>select DBMS_METADATA.GET_DDL('TABLE','SALES') from dual;
DBMS_METADATA.GET_DDL('TABLE','SALES')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."SALES"
( "DEPTNO" NUMBER,
"ITEM_NO" VARCHAR2(20),
"TXN_DATE" DATE,
"TXN_AMOUNT" NUMBER,
"STATE" VARCHAR2(2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS"
PARTITION BY RANGE ("TXN_DATE")
SUBPARTITION BY LIST ("STATE")
SUBPARTITION TEMPLATE (
SUBPARTITION "NORTHWEST" VALUES ( 'OR', 'WA' )
TABLESPACE "USERS" ,
SUBPARTITION "SOUTHWEST" VALUES ( 'AZ', 'UT', 'NM' )
TABLESPACE "USERS" ,
SUBPARTITION "NORTHEAST" VALUES ( 'NY', 'VM', 'NJ' )
TABLESPACE "USERS" ,
SUBPARTITION "SOUTHEAST" VALUES ( 'FL', 'GA' )
TABLESPACE "USERS" ,
SUBPARTITION "MIDWEST" VALUES ( 'SD', 'WI' )
TABLESPACE "USERS" ,
SUBPARTITION "SOUTH" VALUES ( 'AL', 'AK' )
TABLESPACE "USERS" ,
SUBPARTITION "OTHERS" VALUES ( DEFAULT )
TABLESPACE "USERS" )
(PARTITION "Q1_1999" VALUES LESS THAN (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" ,
PARTITION "Q2_1999" VALUES LESS THAN (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" ,
PARTITION "Q3_1999" VALUES LESS THAN (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" ,
PARTITION "Q4_1999" VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" ,
PARTITION "Q5_1999" VALUES LESS THAN (TO_DATE(' 2000-06-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
TABLESPACE "USERS" ) ;
Elapsed: 00:00:00.23
16:40:25 scott@ORCL>
Shrikant's Blog 
Is there a performance impact (during dmls e.g) when modifying the subpartition template?
Partition and subpartition are defined based on application logic. So it depends, why are you modifying and for what purpose. It may or may not have a performance impact based on the query condition execution.