Enhanced Partitioning Metadata in Oracle 23c

Oracle 23c has a lot of small, but useful enhancements that makes life of developers easier. For example two new columns in the data dictionary views for partition metadata.

The description of the LONG data type in the SQL Language Reference of the Oracle documentation starts with the following paragraph:

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

Although this message is quite old, there are still several data dictionary views containing LONG columns. Of course it’s not possible to replace them easily (because of “backward compatibility”), but it’s not fun to use them in SQL queries. For example, if I want to extract the high value date for each partition of a RANGE partitioned table with SUBSTR, I have to do this in a PL/SQL function. The SUBSTR function on LONG columns is not supported in SQL queries.

The good message is: For the data dictionary views for partition maintenance (ALL_TAB_PARTITIONS, etc.), two additional columns are available in Oracle 23c:

• HIGH_VALUE_CLOB contains the same contents as column HIGH_VALUE, but in a CLOB (characted large object) field. This makes it easier to use the data in SQL statements, e.g. with a SUBSTR function as in the following example.
• HIGH_VALUE_JSON contains the high value information in JSON format. This can be helpful for complex definitions of high value expressions, e.g. for LIST partitioning with multiple values. See example at the end of this blog post.

These two new columns are availble in the following data dictionary views:

• USER_TAB_PARTITIONS, USER_TAB_SUBPARTITIONS, USER_IND_PARTITIONS, USER_IND_SUBPARTITIONS
• ALL_TAB_PARTITIONS, ALL_TAB_SUBPARTITIONS, ALL_IND_PARTITIONS, ALL_IND_SUBPARTITIONS
• DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS
• CDB_TAB_PARTITIONS, CDB_TAB_SUBPARTITIONS, CDB_IND_PARTITIONS, CDB_IND_SUBPARTITIONS

I will explain the advantages of these additional metadata columns in two examples.

Example 1: Extract date values for RANGE partitioned table

RANGE or INTERVAL partitioning is often used for DATE columns. In this case, the value in HIGH_VALUE (or HIGH_VALUE_CLOB) column has the following format:

TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

From this string, I want to extract the highlighted date string with the SUBSTR function. This causes an error on HIGH_VALUE, but works fine with the new column HIGH_VALUE_CLOB. I can also extract the date from the new column HIGH_VALUE_JSON, which contains the high value in the following JSON format:

{"high_value":"2020-01-01T00:00:00"}

To illustrate this, I convert my demo table ORDERS into a monthly-partitioned table and select the date values from USER_TAB_PARTITIONS with the SUBSTR resp. JSON_VALUE function on the two new columns:

 
SQL> ALTER TABLE orders
  2  MODIFY PARTITION BY RANGE(order_date) 
  3  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  4  (PARTITION old_data VALUES LESS THAN (DATE'2020-01-01'));
 
Table ORDERS altered.
 
SQL> SELECT table_name
  2       , partition_name
  3       , SUBSTR(high_value_clob, 11, 10) date_from_clob
  4       , JSON_VALUE(high_value_json, '$.high_value') date_from_json
  5    FROM user_tab_partitions
  6   WHERE table_name = 'ORDERS'
  7  ORDER BY partition_position
  8  FETCH FIRST 10 ROWS ONLY;
 
TABLE_NAME      PARTITION_NAME       DATE_FROM_CLOB  DATE_FROM_JSON      
--------------- -------------------- --------------- --------------------
ORDERS          OLD_DATA             2020-01-01      2020-01-01T00:00:00 
ORDERS          SYS_P1082            2020-04-01      2020-04-01T00:00:00 
ORDERS          SYS_P1083            2020-05-01      2020-05-01T00:00:00 
ORDERS          SYS_P1084            2020-06-01      2020-06-01T00:00:00 
ORDERS          SYS_P1085            2020-07-01      2020-07-01T00:00:00 
ORDERS          SYS_P1086            2020-08-01      2020-08-01T00:00:00 
ORDERS          SYS_P1087            2020-09-01      2020-09-01T00:00:00 
ORDERS          SYS_P1088            2020-10-01      2020-10-01T00:00:00 
ORDERS          SYS_P1089            2020-11-01      2020-11-01T00:00:00 
ORDERS          SYS_P1090            2020-12-01      2020-12-01T00:00:00 
 
10 rows selected. 
 

Example 2: Extract list of countries for LIST partitioned table

For the second example, I use LIST partitioning on a demo table ADDRESSES. The partition key is the country code of each address. For countries with many addresses (Switzerland, Germany, Great Britain), a separate partition is created. Other countries are combined into regional partitions. For additional countries that are not defined in one of the partitions, a DEFAULT partition is created.

When we select the list of partitions from USER_TAB_PARTITIONS, we can see the list of country coded in the HIGH_VALUE or HIGH_VALUE_CLOB column as a comma-separated list:

 
SQL> ALTER TABLE addresses
  2  MODIFY PARTITION BY LIST(ctr_code)
  4  ,PARTITION germany       VALUES ('DE')
  5  ,PARTITION great_britain VALUES ('GB')
  6  ,PARTITION europe_others VALUES ('FR','IT','AT','DK','NL')
  7  ,PARTITION north_america VALUES ('CA','US')
  8  ,PARTITION asia_pacific  VALUES ('IN','SG','AU','NZ')
  9  ,PARTITION world_other   VALUES (DEFAULT));
 
Table ADDRESSES altered.
 
SQL> SELECT partition_name
  2       , high_value_clob
  3    FROM user_tab_partitions
  4   WHERE table_name = 'ADDRESSES'
  5  ORDER BY partition_position;
 
PARTITION_NAME       HIGH_VALUE_CLOB               
-------------------- ------------------------------
SWITZERLAND          'CH'                          
GERMANY              'DE'                          
GREAT_BRITAIN        'GB'                          
EUROPE_OTHERS        'FR', 'IT', 'AT', 'DK', 'NL'  
NORTH_AMERICA        'CA', 'US'                    
ASIA_PACIFIC         'IN', 'SG', 'AU', 'NZ'        
WORLD_OTHER          DEFAULT                       
 
7 rows selected. 
 

But what if I want to have a separate row for each country code? There are several ways how this can be implemented in Oracle SQL, but none of them is very easy. Read Chris Saxon’s blog post How to split comma separated value strings into rows in Oracle Database for further information.

In Oracle 23c, the new columns HIGH_VALUE_JSON can be used to retrieve a list of country codes. The high value for partition EUROPE_OTHERS is stored in this format:

{"high_value":["FR","IT","AT","DK","NL"]}

This nested array of country codes can be converted to rows with a NESTED clause on column HIGH_VALUE_JSON:

 
SQL> SELECT partition_name
  3    FROM user_tab_partitions 
  4         NESTED high_value_json.high_value[*]
  5            COLUMNS (country VARCHAR2(2) PATH '$')
  6   WHERE table_name = 'ADDRESSES'
  7  ORDER BY partition_position;
 
PARTITION_NAME       COUNTRY
-------------------- -------
SWITZERLAND          CH     
GERMANY              DE     
GREAT_BRITAIN        GB     
EUROPE_OTHERS        DK     
EUROPE_OTHERS        AT     
EUROPE_OTHERS        IT     
EUROPE_OTHERS        FR     
EUROPE_OTHERS        NL     
NORTH_AMERICA        CA     
NORTH_AMERICA        US     
ASIA_PACIFIC         IN     
ASIA_PACIFIC         SG     
ASIA_PACIFIC         AU     
ASIA_PACIFIC         NZ     
WORLD_OTHER                 
 
15 rows selected. 
 

Summary

Oracle 23c consists of many new features, especially for developers. For a complete list of all the new features, read the Oracle Database New Features manual of the Oracle 23c documentation. If you want to test some of the new features, download and install the Oracle Database 23c Free – Developer Release. It is free and ready to use!