Using templates for your subpartitions is a handy shortcut to avoid specifying attributes for every subpartition in the table.
Oracle calls this concept "vertical striping", since it allows for each subpartition matching the template to end up in the same tablespace and thus allows you to "stripe" your partitions across multiple tablespaces, even though they logically belong to different partitions.
You only describe subpartitions once in the template; Oracle will then apply the template to every partition you create in the table.
The original table's layout is shown below. It is an imaginative tables that will store received documents in a governmental office.
In this example, the documents stored in the CLOB column will be saved in the same tablespace based on year, rather than the type of document it represents.
It will create
* A heap-organized, LIST-LIST partitioned table
* A column of type CLOB (character large object). We will call the CLOB object "REC_DOCS_XML_CLOB"
* 2 pre-defined LIST partitions
* 4 pre-defined LIST sub-partitions
* A subpartition template to simplify future additions of partitions.
CREATE TABLE RECEIVED_DOCUMENTS
(
UUID VARCHAR2(160 BYTE),
FISCAL_YEAR NUMBER(4),
DOCUMENTTYPE VARCHAR2(100 CHAR),
DOCUMENTNAME VARCHAR2(1000 CHAR),
DOCUMENTSTATE VARCHAR2(30 CHAR),
VALID CHAR(1 BYTE),
CREATED_TIMESTAMP NUMBER(20),
VERSION NUMBER(20),
DATA_XML CLOB,
FORMAT VARCHAR2(1000 CHAR),
PERIOD VARCHAR2(1000 CHAR)
)
LOB (DATA_XML) STORE AS SECUREFILE REC_DOCS_XML_CLOB(
TABLESPACE RECEIVED_DOCUMENTS_LOB_DATA
COMPRESS HIGH
)
-- The table definition is stored in the tablespace DOCS_DATA
TABLESPACE DOCS_DATA
PARTITION BY LIST (DOCUMENTTYPE)
SUBPARTITION BY LIST (PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION SP_2014 VALUES ('2014') TABLESPACE DOCS_2014,
SUBPARTITION SP_2015 VALUES ('2015') TABLESPACE DOCS_2015,
SUBPARTITION SP_2016 VALUES ('2016') TABLESPACE DOCS_2016,
SUBPARTITION SP_DEFAULT VALUES (default) TABLESPACE DOCS_DATA
)
(
PARTITION SETTELM
VALUES ('SETTLEM_ACCEPTED', 'SETTLEM_REJECTED'),
PARTITION APPLICATIONS
VALUES ('SINGLE_PARENT_SUPP','UNEMPLOYMENT_SUPP','CHILD_SUPP','HOUSING_SUPP')
)
;
If you later would like to add a partition, it is as simple as
ALTER TABLE RECEIVED_DOCUMENTS
ADD PARTITION APPLICANT VALUES ('FAMILY','SINGLE_PARENT','ASYLUM_SEEKER')
;
Similarly, dropping a partition with its subpartition would be done with:
ALTER TABLE RECEIVED_DOCUMENTS
DROP PARTITION APPLICATION;
Notice that if you have a
default partition to handle incoming data that doesn't fit in any particular partition, you will get
an error when attempting to add a partition.
Sources:
"Specifying Subpartition Templates to Describe Composite Partitioned Tables"
"Specifying a Subpartition Template for a *-List Partitioned Table"
