Having Fun with Auto-List Partitioning

I just wanted to write a small demo script for Auto-List Partitioning in Oracle 12.2. Instead, I spent an entire evening finding a bug in the Oracle data dictionary. Fortunately, it’s not a dramatic one.

When I was messing around with Auto-List Partitioning last week to prepare a demo script for a training session, I was confused because my example looked different than the examples in the documentation and on all the Oracle blogs I’ve seen. Because I wanted to know what happened (and because I was in a hotel in a boring village), I spent some time to find an explanation for the strange behavior. But before I tell you what happened, a short introduction about Auto-List Partitioning.

Auto-List Partitioning

Auto-List Partitioning was introduced with Oracle 12c Release 2 and is an extension of LIST Partitioning. Similar to INTERVAL Partitioning, new partitions can now be created automatically when new rows are inserted into a LIST partitioned table. For example, I create a table to store locations in different countries. For each country, a new partition should be created. Because I don’t know yet what countries will be used, I can create table LOCATIONS_1 with only one partition (for my home country). The keyword AUTOMATIC after the partition clause tells Oracle to create a new partition of every new value that will be inserted.

 

CREATE TABLE locations_1 (city_name VARCHAR2(10) NOT NULL

                         ,country_code VARCHAR2(2) NOT NULL)

PARTITION BY LIST (country_code) AUTOMATIC

  (PARTITION p_switzerland VALUES (‘CH’));

 

Now, I insert some rows into the table:

 

INSERT INTO locations_1 VALUES (‘Zurich’, ‘CH’);

INSERT INTO locations_1 VALUES (‘Bern’, ‘CH’);

INSERT INTO locations_1 VALUES (‘Basel’, ‘CH’);

INSERT INTO locations_1 VALUES (‘Stuttgart’, ‘DE’);

INSERT INTO locations_1 VALUES (‘Hamburg’, ‘DE’);

INSERT INTO locations_1 VALUES (‘Vienna’, ‘AT’);

INSERT INTO locations_1 VALUES (‘Copenhagen’, ‘DK’);

COMMIT;

 

The each distinct country code, a new partition is created automatically. After the INSERT statements, four partitions are available for table LOCATIONS_1: The initial partition P_SWITZERLAND and three new partitions that were generated in the background. Like with INTERVAL partitioning, the partition names are system-generated. Column HIGH_VALUE in the data dictionary view shows what country is stored in which partition:

 

SELECT table_name, partition_name, high_value

  FROM user_tab_partitions

 WHERE table_name = ‘LOCATIONS_1’

 ORDER BY table_name, partition_position;

 

TABLE_NAME           PARTITION_NAME  HIGH_VALUE

——————–——————– —————————— ———-———-

LOCATIONS_1          P_SWITZERLAND   ‘CH’      

LOCATIONS_1          SYS_P3833       ‘DE’      

LOCATIONS_1          SYS_P3834       ‘AT’      

LOCATIONS_1          SYS_P3835       ‘DK’      

 

Exactly what I expected and what is documented everywhere. This works perfectly for the usual data types like VARCHAR2, DATE, NUMBER, etc. But unfortunately, the demo table I wanted to use for my example contains a CHAR column to store the country codes.

And Now the Same with CHAR…

What do you expect if the partition key has data type CHAR instead of VARCHAR2? Probably the same thing I expected: It works in the same way. Since the ISO-2 county code has always two characters, there will be no issue with trailing blanks. So, let’s create a new table LOCATIONS_2 with a CHAR column for the partition key.

 

CREATE TABLE locations_2 (city_name VARCHAR2(10) NOT NULL

                         ,country_code CHAR(2) NOT NULL)

PARTITION BY LIST (country_code) AUTOMATIC

  (PARTITION p_switzerland VALUES (‘CH’));

 

I insert the same data into this table, and Auto-List Partitioning creates three new partitions.

 

INSERT INTO locations_2 VALUES (‘Zurich’, ‘CH’);

INSERT INTO locations_2 VALUES (‘Bern’, ‘CH’);

INSERT INTO locations_2 VALUES (‘Basel’, ‘CH’);

INSERT INTO locations_2 VALUES (‘Stuttgart’, ‘DE’);

INSERT INTO locations_2 VALUES (‘Hamburg’, ‘DE’);

INSERT INTO locations_2 VALUES (‘Vienna’, ‘AT’);

INSERT INTO locations_2 VALUES (‘Copenhagen’, ‘DK’);

COMMIT;

 

When I did this for my demo case, I checked the partitions with the data dictionary view USER_TAB_PARTITIONS – and was surprised about the result:

 

SELECT table_name, partition_name, high_value

  FROM user_tab_partitions

 WHERE table_name = ‘LOCATIONS_2’

ORDER BY table_name, partition_position;

 

TABLE_NAME           PARTITION_NAME  HIGH_VALUE

——————–——————– —————————— ———-———-

LOCATIONS_2          P_SWITZERLAND   ‘CH’      

LOCATIONS_2          SYS_P3843       :1        

LOCATIONS_2          SYS_P3844       :1        

LOCATIONS_2          SYS_P3845       :1        

 

Three additional partitions were created as expected, but the HIGH_VALUE column in the data dictionary view does not show the individual values, but the value ‘:1’ for all system-generated partitions. Something is wrong here…

Further Investigations

If the partition values are not stored in the data dictionary, partition pruning will not work! So, I first tried to run a query that should be able to read only one partition. We’re lucky: Partition pruning works as usual, as you can see in the execution plan. For the query that reads the locations in Germany, a PARTITION RANGE SINGLE operation is executed, and the columns PSTART and PSTOP show that only the second partition is scanned.

 

EXPLAIN PLAN FOR

SELECT * FROM locations_2

 WHERE country_code = ‘DE’;

                                                                                                                         

——————————————————————————————————————————————————                   

| Id  | Operation             | Name        | Rows  | … | Pstart| Pstop |                   

—————————————–————————————————————————————————————-                   

|   0 | SELECT STATEMENT      |             |     2 |     |       |       |                   

|   1 |  PARTITION LIST SINGLE|             |     2 |     |   KEY |   KEY |                   

|   2 |   TABLE ACCESS FULL   | LOCATIONS_2 |     2 |     |     2 |     2 |                   

————————————-—————————————————————————————————————–                   
 

Hovever, if partition pruning works, this means that the values must be stored somewhere in the data dictionary tables. My next guess was that the data dictionary views USER_TAB_PARTITIONS, ALL_TAB_PARTITIONS and DBA_TAB_PARTITIONS select the wrong information. But all of them query the same data: column HIBOUNDVAL of table SYS.TABPART$. After a while, I found the lost information: The BLOB column BHIBOUNDVAL in the same table contains the boundary values for all partitions.

 

SQL> SELECT o.subname, tp.hiboundval, tp.bhiboundval 

  2    FROM sys.obj$ o

  3    JOIN sys.tabpart$ tp ON (tp.obj# = o.obj#)

  4   WHERE o.name = ‘LOCATIONS_2’;

 

SUBNAME         HIBOUNDVAL BHIBOUNDVAL         

—————————— ———-———- ——————–——————–

P_SWITZERLAND   ‘CH’       024348              

SYS_P3843       :1         024445              

SYS_P3844       :1         024154              

SYS_P3845       :1         02444B              

 

With a little imagination, it is easy to read the information in the BLOB field. The first byte (02) contains the length, the following bytes the ASCII codes of the characters:

• 43 48 = CH
• 44 45 = DE
• 41 54 = AT
• 44 4B = DK

What is the Impact?

For me, it seems to be a bug in the data dictionary. I see no reason why partition keys of data type CHAR should be handled differently to all other data types So what is the consequence of this little mistake? How can we deal with it? There are several solutions or workarounds:

• Avoid CHAR columns. In most cases, VARCHAR2 is the better choice for character strings. As long as you consistently use VARCHAR2 instead of the CHAR data type, you will have no troubles with Auto-List Partitioning.
• Query the SYS tables directly. Of course, you need DBA privileges for this. And you have to remember the ASCII codes of the characters – or you use SQL Developer to view the text values in the binary BHIBOUNDVAL column.
• Tell Oracle to fix the bug. That’s the next thing I will do: Open a service request on My Oracle Support and look how long it takes until the bug is fixed.

Update (21-January-2019)

By the way: In Oracle 18c, the behaviour is the same as on Oracle 12.2.

Update (31-May-2019)

Now, after installing Oracle 19.3, I’m happy to see that the data dictionary views USER_TAB_PARTITIONS, ALL_TAB_PARTITIONS and DBA_TAB_PARTITIONS return the correct result for HIGH_VALUE, even for data type CHAR. Finally, I can close the service request I opened in February 2018.