Partition Pruning and NLS Settings

NLS (National Language Support) parameters are very useful for multilingual applications in an Oracle database. But it is important to understand their impact on query performance. I was aware that NLS is important for indexes, but never thought about its impact on partitioning.

In a performance review for a new customer, I analyzed the partitioning strategy of their application. Several tables were partitioned, but for some of them, partition pruning didn’t work. I was confused at the beginning, but when I looked at the NLS settings of the application, I learned that the flexibility of National Language Support has an impact on the partitioning strategy. Actually, it is clear, but I wasn’t aware of that. In this blog post, I want to show what happens when linguistic and case-insensitive sort and comparison is enabled.

Partitioning Pruning with Default NLS Settings

For the following tests, I use a simple example from a previous blog post. The example uses auto-list partitioning, but the behaviour is the same for all partitioning methods with text columns as a partition key. I create a partitioned table with a LIST partition for each different country and insert some rows. The result is a table with 8 rows, stored in 4 partitions.

 

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’));

 

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;

 

 

When I select the two cities in Germany, I expect that Oracle is able to use partition pruning and will scan only the partition for country_code = ‘DE’. As we can see in the execution plan, it works perfectly: a PARTITION RANGE SINGLE is performed on the second partition of table LOCATIONS_1:

 

EXPLAIN PLAN FOR

SELECT * FROM locations_1

WHERE country_code = ‘DE’;

 

SELECT * FROM TABLE(dbms_xplan.display(format => ‘-cost -bytes’));

  

——————————————————————————–

| Id  | Operation             | Name        | Rows  | Time     | Pstart| Pstop |

——————————————————————————–

|   0 | SELECT STATEMENT      |             |     2 | 00:00:01 |       |       |

|   1 |  PARTITION LIST SINGLE|             |     2 | 00:00:01 |   KEY |   KEY |

|   2 |   TABLE ACCESS FULL   | LOCATIONS_1 |     2 | 00:00:01 |     2 |     2 |

——————————————————————————–

 

 

Changing the NLS Settings

On the database of my customer, the parameters NLS_COMP and NLS_SORT are changed in a logon trigger. This is used for linguistic and case-insensitive matches for sort and compare operations. In my test case, I set the parameters on session level to the same values as my customer uses:

 

ALTER SESSION SET nls_comp = LINGUISTIC;

ALTER SESSION SET nls_sort = BINARY_CI;

 

 

These settings change the execution plan of the statement above. Partition pruning does not work anymore. Instead, a PARTITION LIST ALL on all four partitions is performed. The reason is visible in the predicate information of line 2: An NLSSORT operation on the partition key prevents Oracle to do partition pruning.

 

—————————————————————————–

| Id  | Operation          | Name        | Rows  | Time     | Pstart| Pstop |

—————————————————————————–

|   0 | SELECT STATEMENT   |             |     2 | 00:00:01 |       |       |

|   1 |  PARTITION LIST ALL|             |     2 | 00:00:01 |     1 |     4 |

|*  2 |   TABLE ACCESS FULL| LOCATIONS_1 |     2 | 00:00:01 |     1 |     4 |

—————————————————————————–

 

Predicate Information (identified by operation id):

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

 

   2 – filter(NLSSORT(“COUNTRY_CODE”,’nls_sort=”BINARY_CI”’)=HEXTORAW(‘646500’))

 

 

For large tables – and usually partitioned tables are large – this is a bad message: Partition pruning doesn’t work when the NLS parameters are not set to the default value BINARY. Or in other words: We can decide between linguistic / case-insensitive matches or good query performance. So, how can we solve this issue?

Solution: Data-Bound Collations

Since Oracle 12.2, Data-Bould Collations allow to define the multilingual behaviour of match and compare strings for each individual column that uses a character data type. This gives us more flexibility than the old NLS settings with NLS_SORT and NLS_COMP. Unlike with the old NLS settings, it is not only possible to change the multilingual settings on database or session level, but for individual tables or even columns. For our test case, we could for example enable case-insensitive matches on column CITY_NAME, but not on the partition key COUNTRY_CODE:

 

CREATE TABLE locations_2 (city_name    VARCHAR2(10) COLLATE BINARY_CI NOT NULL

                         ,country_code VARCHAR2(2) NOT NULL ) 

PARTITION BY LIST (country_code) AUTOMATIC

(PARTITION p_switzerland VALUES (‘CH’));

   

 

But even if a data-bound collation is defined for the partition key, partition pruning works:

 

CREATE TABLE locations_2 (city_name    VARCHAR2(10) COLLATE BINARY_CI NOT NULL

                         ,country_code VARCHAR2(2) COLLATE BINARY_CI NOT NULL )

PARTITION BY LIST (country_code) AUTOMATIC

(PARTITION p_switzerland VALUES (‘CH’));

   

 

EXPLAIN PLAN FOR

SELECT * FROM locations_2

WHERE country_code = ‘DE’;

 

SELECT * FROM TABLE(dbms_xplan.display(format => ‘-cost -bytes’));

 

————————————————————————————-

| Id  | Operation                  | Name        | Rows  | Time     | Pstart| Pstop |

————————————————————————————-

|   0 | SELECT STATEMENT           |             |     1 | 00:00:01 |       |       |

|   1 |  PARTITION LIST SINGLE     |             |     1 | 00:00:01 |   KEY |   KEY |

|   2 |   TABLE ACCESS STORAGE FULL| LOCATIONS_2 |     1 | 00:00:01 |     2 |     2 |

————————————————————————————-

 

 

My customer currently uses Oracle 12.1, so they are not able to use Data-Bound Collations at the moment. But upgrading to Oracle 12c Release 2 is not enough. Additionally, the initialization parameter MAX_STRING_SIZE must be set to EXTENDED. Because this parameter cannot be changed back, it has to be decided carefully for each database whether Data-Bound Collations should be used or not.