Range Partitioning-Partitioning

The first type we will look at is a range partitioned table. The following CREATE TABLE statement creates a range partitioned table using the column RANGE_KEY_COLUMN. All data with a RANGE_KEY_COLUMN strictly less than 01-JAN-2021 will be placed into the partition PART_1, and all data with a value strictly less than 01-JAN-2022 (and greater than or equal to 01-JAN-2021) will go into partition PART_2. Any data not satisfying either of those conditions (e.g., a row with a RANGE_KEY_COLUMN value of 01-JAN-2022 or greater) will fail upon insertion, as it cannot be mapped to a partition:
$ sqlplus eoda/foo@PDB1
SQL> CREATE TABLE range_example( range_key_column date NOT NULL,data varchar2(20))PARTITION BY RANGE (range_key_column)( PARTITION part_1 VALUES LESS THAN
(to_date(’01/01/2021′,’dd/mm/yyyy’)), PARTITION part_2 VALUES LESS THAN
(to_date(’01/01/2022′,’dd/mm/yyyy’)));Table created.

Note We are using the date format DD/MM/YYYY in the CREATE TABLE statement to make this international. If we used a format of DD-MON-YYYY, then the CREATE TABLE would fail with ORA-01843: not a valid month if the abbreviation of January was not Jan on your system. The NLS_LANGUAGE setting would affect this. I have used the three-character month abbreviation in the text and inserts, however, to avoid any ambiguity as to which component is the day and which is the month.

Figure 13-1 shows that Oracle will inspect the value of the RANGE_KEY_COLUMN and, based on that value, insert it into one of the two partitions.


Figure 13-1.  Range partition insert example

The rows inserted were specifically chosen with the goal of demonstrating that the partition range is strictly less than and not less than or equal to. We first insert the value 15-DEC-2020, which will definitely go into partition PART_1. We also insert a row with a date/time that is one second before 01-JAN-2021—that row will also go into partition PART_1 since that is less than 01-JAN-2021. However, the next insert of midnight on 01-­ JAN-2021 goes into partition PART_2 because that date/time is not strictly less than the partition range boundary for PART_1. The last row obviously belongs in partition PART_2 since it is greater than or equal to the partition range boundary for PART_1 and less than the partition range boundary for PART_2.

We can confirm that this is the case by performing SELECT statements from the individual partitions:
SQL> select to_char(range_key_column,’dd-mon-yyyy hh24:mi:ss’) from range_example partition (part_1);
TO_CHAR(RANGE_KEY_COLUMN,’DD-
15-dec-2020 00:00:00
31-dec-2020 23:59:59

SQL> select to_char(range_key_column,’dd-mon-yyyy hh24:mi:ss’) from range_example partition (part_2);
TO_CHAR(RANGE_KEY_COLUMN,’DD-
01-jan-2021 00:00:00
31-dec-2021 23:59:59

You might be wondering what would happen if you inserted a date that fell outside of the upper bound. The answer is that Oracle would raise an error:
SQL> insert into range_example( range_key_column, data )values( to_date( ’01-jan-2022 00:00:00′, ‘dd-mon-yyyy hh24:mi:ss’ ),’application data…’ );insert into range_example
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

There are two approaches to the preceding situation—one would be to use interval partitioning described later or to use a catch-all partition, which we’ll demonstrate now. Suppose you want to segregate 2020 and 2021 dates into their separate partitions as we have, but you want all other dates to go into a third partition. With range partitioning, you can do this using the MAXVALUE clause, which looks like this:

SQL> drop table range_example purge;
SQL> CREATE TABLE range_example( range_key_column date,data varchar2(20))
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN
(to_date(’01/01/2021′,’dd/mm/yyyy’)), PARTITION part_2 VALUES LESS THAN
(to_date(’01/01/2022′,’dd/mm/yyyy’)), PARTITION part_3 VALUES LESS THAN
(MAXVALUE));Table created.

Now when you insert a row into that table, it will go into one of the three partitions— no row will be rejected, since partition PART_3 can take any value of RANGE_KEY_COLUMN that doesn’t go into PART_1 or PART_2 (even null values of the RANGE_KEY_COLUMN will be inserted into this new partition).

Hash Partitioning

When hash partitioning a table, Oracle will apply a hash function to the partition key to determine in which of the N partitions the data should be placed. Oracle recommends that N be a number that is a power of 2 (2, 4, 8, 16, and so on) to achieve the best overall distribution, and we’ll see shortly that this is absolutely good advice.

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*

BACK TO TOP