What is subpartition in oracle with example?

What is subpartition in oracle with example?

Partitioning and subpartitioning of tables and indexes is a technique for creating a single logical entity, a table or index, mapping multiple separate segments allowing the optimizer to access a smaller number of blocks to respond to a SQL statement.

What is Oracle Subpartition template?

A subpartition template simplifies the specification of subpartitions by not requiring that a subpartition descriptor be specified for every partition in the table. Instead, you describe subpartitions only one time in a template, then apply that subpartition template to every partition in the table.

What is table subpartition in oracle?

Subpartitioning—also known as composite partitioning—is the further division of each partition in a partitioned table. This is also known as composite partitioning. Note. SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules as PARTITION BY HASH and PARTITION BY KEY , respectively.

How do I create a Subpartition to a partitioned table in Oracle?

You use the MODIFY PARTITION ADD SUBPARTITION clause of the ALTER TABLE statement to add a list subpartition to a [range | list | interval]-list partitioned table. For an interval-list partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.

How to add subpartition for existing table in Oracle?

So the subpartition should be like: SUBPARTITION “SP_US” VALUES ( ‘US’ ) in SUBPARTITION TEMPLATE for exisitng INT_TMP table.

How to specify a subpartition template for a list?

If you specified the tablespaces at the partition level (for example, tbs_1 for partition q1_1999, tbs_2 for partition q2_1999, tbs_3 for partition q3_1999, and tbs_4 for partition q4_1999) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition.

Is there an upper limit to subpartition in ALTER TABLE?

The ALTER TABLE… ADD SUBPARTITION command adds a subpartition to an existing partition; the partition must already be subpartitioned. There is no upper limit to the number of defined subpartitions. New subpartitions must be of the same type (LIST, RANGE or HASH) as existing subpartitions.

Can a subpartition be added to an existing partition?

ADD SUBPARTITION command adds a subpartition to an existing partition; the partition must already be subpartitioned. There is no upper limit to the number of defined subpartitions. New subpartitions must be of the same type (LIST, RANGE or HASH) as existing subpartitions.