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. NLS is important for indexes, but becareful about its impact on partitioning.

the partitioning strategy of application is several tables were partitioned, but for some of them, partition pruning didn’t work. It is confused at the beginning, but when it’s looked at the NLS settings of the application, it’s seen that the flexibility of National Language Support has an impact on the partitioning strategy.

Partitioning Pruning with Default NLS Settings

The example uses auto-list partitioning, but the behavior is the same for all partitioning methods with text columns as a partition key. 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 selected 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 the application, 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. here the parameters are set on session level to the same values:

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 |

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

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.

ref: https://danischnider.wordpress.com/2019/10/18/partition-pruning-and-nls-settings/