Shulou(Shulou.com)06/01 Report--

[Enmo College] INTERVAL partition has been upgraded, do you know?! Features of the new INTERVAL partition:

Special point 1:

More conveniently, the INTERVAL partition does not have to be specified when the table is created, and even if the RANGE partition table has been created, it can be changed to become an INTERVAL partition:

Tips

This makes all existing RANGE partition tables can take advantage of INTERVAL partitioning, and INTERVAL partitioning also supports compound partitioning, INTERVAL-HASH, INTERVAL-LIST and INTERVAL-RANGE partitioning are supported.

Note:

Because the partitions of INTERVAL are automatically created on demand, you need to use a subpartition template to determine how the subpartitions of the new partition are created. If no subpartition template is established, the new partition will contain only one subpartition.

Feature 2:

Another feature of INTERVAL partitions is that the partition key value is not allowed to be null:

Tips

This limitation is also easy to understand, because there is no MAXVALUE partition in the INTERVAL partition, so the NULL value is "greater than" all partition key values.

If the partition key value may be empty, then the INTERVAL partition is not applicable, but it is rare for the partition key value to be empty.

In fact, as the name implies, the INTERVAL partition needs to provide an INTERVAL, but there is no INTERVAL for character types, so only NUMBER types and DATE types support INTERVAL partitions.

The INTERVAL partition of type NUMBER is very simple, so only the relatively complex INTERVAL partition of type DATE is described here.

There are two ways to limit the InterVal value.

01

Through the NUMTOYMINTERVAL or NUMTODSINTERAL function:

02

Make direct use of INTERVAL expressions:

No matter which method is used, it is actually the INTERVAL method, which, unlike the ADD_MONTHS function, does not automatically deal with the problem at the end of the month, so the INTERVAL in monthly units cannot use the date of more than 28 days as the upper limit of the last range partition:

It is actually very simple to avoid this kind of error. The simplest thing is to use 28 days as the partition upper limit:

Or create a maximum partition to avoid logs exceeding 28 days as the upper limit of the last partition:

Note:

DATE here generally refers to several date-related data types, including DATE, TIMESTANP, TIMESTAMP WITH ZONE, and TIMESTAMP WITH LOCAL ZONE.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.