The partitions of a range-list partitioned table are logical structures only, because their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.
The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified. This example results in the following subpartition descriptions:. All subpartitions inherit their physical attributes, other than tablespace, from tablespace level defaults. This is because the only physical attribute that has been specified for partitions or subpartitions is tablespace.
There are no table level physical attributes specified, thus tablespace level defaults are inherited at all levels. The range partitions of a range-range composite partitioned table are similar to non-composite range partitioned tables. The following example illustrates how range-range partitioning might be used. The example tracks shipments.
The service level agreement with the customer states that every order is delivered in the calendar month after the order was placed.
The following types of orders are identified:. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range. These orders likely exceed customers' expectations.
L LATE : orders that were only delivered starting the second calendar month after the order was placed. 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. The subpartition template is used whenever a subpartition descriptor is not specified for a partition.
If a subpartition descriptor is specified, then it is used instead of the subpartition template for that partition. If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created. For range-hash, interval-hash, and list-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.
Example creates a range-hash partitioned table using a subpartition template and displays the subpartition names and tablespaces. Each subpartition has a tablespace specified. It is required that if a tablespace is specified for one subpartition in a subpartition template, then one must be specified for all.
Example Creating a range-hash partitioned table with a subpartition template. For -list partitioned tables, the subpartition template can describe the subpartitions in detail. It saves a lot of time as the subpartition definition can be written once and used multiple times even in cases like dynamic partition where specifying the subpartition description is not possible.
Beena Emerson Jul 12, High availability. Setting the template Usage Modifying Template Catalog Information Tablespace Handling Creating a large number of subpartitions can be time-consuming as all the details - name, bound, tablespace - needs to be specified for each partition separately.
Usage Once the subpartition template is set it is used whenever a new partition is created without any subpartition description. When the subpart template is reset, the partsubparttemplate column is set to NULL.
Tablespace Handling The tablespace used in the subpartition template cannot be dropped. Ready to take the next step with PostgreSQL? Contact Us. Automatic list partitioning is unsupported at the subpartition level. But I don't see the need for this here. You know there are 12 months in a year! You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels.
And of course, keep up to date with AskTOM via the official twitter account. Questions Can I user automatic List in subpartitions? Question and Answer. You Asked Dears, I have a table that contains two columns one for year and the other for month.
0コメント